creating a pandas dataframe from a database query that uses bind variables

Question

I'm working with an Oracle database. I can do this 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 bind variables, like so:

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

I've tried these variations:

   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 solution is just...inellegant. If I can, I'd like to do this without creating the cursor object. Is there a way to do the whole thing using just pandas?

privod · Accepted Answer · 2017-06-30 03:17:29Z

1

Try using pandas.io.sql.read_sql_query. I used pandas version 0.20.1, I used it, it worked out:

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)

answered Jun 30, 2017 at 3:17

privod

2885 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paul H · Accepted Answer · 2013-02-15 02:04:26Z

0

As far as I can tell, pandas expects that the SQL string be completely formed prior to passing it along. With that in mind, I would (and always do) 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 feed into psql.frame_query just fine. (it does in my experience with postgres, mysql, and sql server).

edited Feb 15, 2013 at 2:04

answered Feb 15, 2013 at 1:58

Paul H

68.7k23 gold badges165 silver badges139 bronze badges

5 Comments

David Marx Over a year ago

I'd strongly advise against forming your SQL this way as it leaves your code vulnerable to SQL injection attacks. Even if your code/database isn't in a position to be vulnerable, you shouldn't get in the practice of forming your SQL this way. Bind variables are the safe way to go.

Paul H Over a year ago

@DavidMarx agreed. I shouldn't have assumed the OP was working from a command-line (or just a basic script) like I normally do.

David Marx Over a year ago

[FYI: I'm OP] Yeah, this is a self-contained file. I don't foresee any real issues with SQL injection in my current program since the people who will be using it will have direct access to the database anyway, but I would like to know for the future if I can use pandas in the way I described.

Paul H Over a year ago

Oops. I see that now. FWIW, I'm able to recreate your experience/frustration using pyodbc on my databases. From my perspective, the path of least resistance is to hack on pandas so that read_frame can take a cursor object. Maybe I'll be able to get a PR together soon.

Paul H Over a year ago

well your cur.fetchall() technique covers that. i'll stop blabbering now.

Collectives™ on Stack Overflow

creating a pandas dataframe from a database query that uses bind variables

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related