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-07T14:41:04.943

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,))

score 1 · Answer 2 · answered Mar 06 '19 at 06:21

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 06 '19 at 06:21

user3322481

305
2
4

It didn't work either. I got error ERROR: syntax error at or near "?" – sudhir Mar 06 '19 at 06:30
How about revise tup_ids to str(tup_ids)? – user3322481 Mar 06 '19 at 06:32
statement = 'select sum(column1) from table_name where column2 in ({0})'.format( ', '.join(['%s'] * len(tup_ids))) conn1.execute(statement, tup_ids) – user3322481 Mar 06 '19 at 06:52
This didn't work either. This statement broke my code – sudhir Mar 06 '19 at 07:08
@sudhir you should use the [DB-API 2 interface](http://www.pygresql.org/contents/tutorial.html#first-steps-with-the-db-api-2-0-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()`. – shmee Mar 06 '19 at 07:37
Worked with pgdb module. Thanks – sudhir Mar 06 '19 at 10:04

Execute SQL select in statement using pygresql(pg module)

2 Answers2