How Can I Use Parameters with SQL Queries in Pandas?

Using SQL query parameters in Pandas

Pandas allows passing parameters when executing SQL queries, using the read_sql() function.

Parameter passing syntax

Parameters can be passed as a list, tuple or dictionary. However, the supported syntax depends on the database driver used.

List or tuple parameters

This method involves using the %s placeholder or question mark ? in the SQL query. Parameters are passed as a list or tuple, in the same order as they appear in the query.

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

Dictionary parameters

When using dictionary parameters, the key-value pairs in the dictionary correspond to the parameter names in the SQL query. Parameter names in queries should use %(name)s syntax.

<code class="language-python">params = {"dstart": datetime(2014, 6, 24, 16, 0), "dfinish": datetime(2014, 6, 24, 17, 0)}
df = psql.read_sql('select "Timestamp", "Value" from "MyTable" where "Timestamp" BETWEEN %(dstart)s AND %(dfinish)s',
                     db, params, index_col=['Timestamp'])</code>

Suggested syntax

The recommended syntax for passing parameters using SQL queries in Pandas is to use named parameters (dict) or positional parameters (list or tuple), depending on the syntax supported by the database driver.

The above is the detailed content of How Can I Use Parameters with SQL Queries in Pandas?. For more information, please follow other related articles on the PHP Chinese website!