Execute SQL select in statement using pygresql(pg module)

Question

I am working on a python script to connect to postgress databases using pygresql module. Below is the code

I am trying to run the query by passing tuple as parameter. query looks like this:

select sum(column1) from table_name where column2 in %s,(tuple,).

But I keep getting error "ERROR: syntax error at or near "%"".

import pg
tup_ids=('a','b','c')
def connection(cs):   
  """
      :param cs: cs is connection string
      :return:
       """
      conn=pg.connect(cs)
      return conn
conn1 = connection(conn_string)
conn1.query('select sum(column1) from table_name where column2 in %s',(tup_ids,).

I am able to execute query using psycopg2 module. I am not able to pass tuple parameter for pg module. I have been pgresql documentation. I am not sure where I am doing wrong.

FYI: I need to use pygresql module only. Please help.

Cito · Accepted Answer · 2019-03-07 14:41:04Z

PyGreSQL "classic" (the pg module) supports PostgreSQL native parameters in its query method, labelled $1, $2, ... Unfortunately, these accept only individual values, so you must construct a parameter list with as many values as your tuple first. This is very simple, though:

con = pg.connect(cs)
p_list = ','.join('$%d' % n for n in range(1, len(tup_ids) + 1))
q = con.query('select sum(column1) from table_name where column2 in (%s)' % p_list, tup_ids)
print(q.getresult())

Alternatively, you can use the query_formatted method available in the DB wrapper class of PyGreSQL classic (it is recommended to use that wrapper instead of raw connections anyway, because it adds a lot of convenience methods). This method uses Python formatting, so it must be used like this:

db = pg.DB(cs)
p_list = ','.join(['%s'] * len(tup_ids))
q = db.query_formatted(
    'select sum(column1) from table_name where column2 in (%s)' % p_list, tup_ids)
print(q.getresult())

By using ANY instead of IN in your SQL statement, you can avoid creating a parameter list, and pass the values as a single list:

db = pg.DB(cs)
q = db.query_formatted(
    'select sum(column1) from table_name where column2 = any(%s)', [list(tup_ids)])
print(q.getresult())

The PyGreSQL DB API 2 module (pgdb) also uses Python formatting and works similarly:

con = pgdb.connect(cs)
cur = con.cursor()
cur.execute('select sum(column1) from table_name where column2 = any(%s)', [list(tup_ids)])
print(cur.fetchone())

Note that we always passed the parameters separately. Do not be tempted to pass a query with Python-formatted values, as this is error prone and a safety issue ("SQL injection"), even if it looks simpler:

con = pg.connect(cs)  # do NOT do this:
q = con.query('select sum(column1) from table_name where column2 in %s' % (tup_ids,))

user3322481 · Accepted Answer · 2019-03-06 06:21:20Z

1

Please use "?" symbol in your SQL select command

In your case, you should revise to:

conn.query('select sum(column1) from table_name where column2 in ?',(tup_ids,))

Then it should execute with success

answered Mar 6, 2019 at 6:21

user3322481

3253 silver badges4 bronze badges

6 Comments

sudhir Over a year ago

It didn't work either. I got error ERROR: syntax error at or near "?"

user3322481 Over a year ago

How about revise tup_ids to str(tup_ids)?

user3322481 Over a year ago

statement = 'select sum(column1) from table_name where column2 in ({0})'.format( ', '.join(['%s'] * len(tup_ids))) conn1.execute(statement, tup_ids)

sudhir Over a year ago

This didn't work either. This statement broke my code

shmee Over a year ago

@sudhir you should use the DB-API 2 interface. From the connection conn1 you first create a cursor: curs = conn1.cursor(). Format the SQL statement as outlined above: statement = 'select sum(column1) from table_name where column2 in ({0})'.format( ', '.join(['%s'] * len(tup_ids))), then execute the statement on the cursor: curs.execute(statement, tup_ids). To get the results use fetchall on the cursor: result = curs.fetchall().

|

Collectives™ on Stack Overflow

Execute SQL select in statement using pygresql(pg module)

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related