Search code examples
pythonpostgresqlpgpygresql

Execute SQL select in statement using pygresql(pg module)

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.

Solution

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