Geek asks and answers

Creating pandas data from a database query using bind variables

I am working with an Oracle database. I can do so much:

    import pandas as pd
    import pandas.io.sql as psql
    import cx_Oracle as odb
    conn = odb.connect(_user +'/'+ _pass +'@'+ _dbenv)

    sqlStr = "SELECT * FROM customers"
    df = psql.frame_query(sqlStr, conn)

But I don’t know how to handle binding variables, for example:

    sqlStr = """SELECT * FROM customers 
                WHERE id BETWEEN :v1 AND :v2
             """

I tried these options:

   params  = (1234, 5678)
   params2 = {"v1":1234, "v2":5678}

   df = psql.frame_query((sqlStr,params), conn)
   df = psql.frame_query((sqlStr,params2), conn)
   df = psql.frame_query(sqlStr,params, conn)
   df = psql.frame_query(sqlStr,params2, conn)

The following works:

   curs = conn.cursor()
   curs.execute(sqlStr, params)
   df = pd.DataFrame(curs.fetchall())
   df.columns = [rec[0] for rec in curs.description]

but this decision is simply ... inelegant. If possible, I would like to do this without creating a cursor object. Is there a way to do all this using only pandas?

+5
source share
2 answers

As far as I can tell, pandas expects the SQL string to be fully formed before it passes. With that in mind, I would (and always did) use string interpolation:

params = (1234, 5678)
sqlStr = """
SELECT * FROM customers 
WHERE id BETWEEN %d AND %d
""" % params
print(sqlStr)

which gives

SELECT * FROM customers 
WHERE id BETWEEN 1234 AND 5678

So that should be great. (he has experience with postgres, mysql and sql server).

+1

pandas.io.sql.read_sql_query. pandas 0.20.1, , :

import pandas as pd
import pandas.io.sql as psql
import cx_Oracle as odb
conn = odb.connect(_user +'/'+ _pass +'@'+ _dbenv)

sqlStr = """SELECT * FROM customers 
            WHERE id BETWEEN :v1 AND :v2
"""
pars = {"v1":1234, "v2":5678}
df = psql.frame_query(sqlStr, conn, params=pars)
+1

More articles:


All Articles