76

Are there any examples of how to pass parameters with an SQL query in Pandas?

In particular I'm using an SQLAlchemy engine to connect to a PostgreSQL database. So far I've found that the following works:

df = psql.read_sql(('select "Timestamp","Value" from "MyTable" '
                     'where "Timestamp" BETWEEN %s AND %s'),
                   db,params=[datetime(2014,6,24,16,0),datetime(2014,6,24,17,0)],
                   index_col=['Timestamp'])

The Pandas documentation says that params can also be passed as a dict, but I can't seem to get this to work having tried for instance:

df = psql.read_sql(('select "Timestamp","Value" from "MyTable" '
                     'where "Timestamp" BETWEEN :dstart AND :dfinish'),
                   db,params={"dstart":datetime(2014,6,24,16,0),"dfinish":datetime(2014,6,24,17,0)},
                   index_col=['Timestamp'])

What is the recommended way of running these types of queries from Pandas?

sophros
  • 11,665
  • 7
  • 38
  • 61
tobycoleman
  • 1,492
  • 1
  • 14
  • 34

1 Answers1

112

The read_sql docs say this params argument can be a list, tuple or dict (see docs).

To pass the values in the sql query, there are different syntaxes possible: ?, :1, :name, %s, %(name)s (see PEP249).
But not all of these possibilities are supported by all database drivers, which syntax is supported depends on the driver you are using (psycopg2 in your case I suppose).

In your second case, when using a dict, you are using 'named arguments', and according to the psycopg2 documentation, they support the %(name)s style (and so not the :name I suppose), see http://initd.org/psycopg/docs/usage.html#query-parameters.
So using that style should work:

df = psql.read_sql(('select "Timestamp","Value" from "MyTable" '
                     'where "Timestamp" BETWEEN %(dstart)s AND %(dfinish)s'),
                   db,params={"dstart":datetime(2014,6,24,16,0),"dfinish":datetime(2014,6,24,17,0)},
                   index_col=['Timestamp'])
joris
  • 121,165
  • 35
  • 238
  • 198
  • 1
    That's very helpful - I am using psycopg2 so the '%(name)s syntax works perfectly. – tobycoleman Jun 26 '14 at 07:21
  • 1
    We should probably mention something about that in the docstring:https://github.com/pydata/pandas/issues/7573 – joris Jun 26 '14 at 07:34
  • 9
    This solution no longer works on Postgres - one needs to use the `:notation`, and then be sure to wrap the SQL string with `sqlalchemy.text()` – hamx0r Nov 03 '15 at 22:49
  • This doesn't work on SQLite with SQLAlchemy's engine. – rjurney Jun 11 '19 at 14:56
  • 3
    SQLite uses the `:name` style, see https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.execute – joris Jun 13 '19 at 06:05
  • 1
    The correct characters for the parameter style can be looked up dynamically by the way in nearly every database driver via the paramstyle attribute. This is acutally part of the PEP 249 definition. – CheradenineZK Aug 27 '20 at 11:34
  • For SQL Server use `?`. E.g., to call a stored proc with parameters, having a connection `cnn`, it is like `pd.read_sql("[dbo].[MyProc] ?,?" , cnn, params=[a,b])` – Nick Oetjen Jul 15 '21 at 14:12