从使用绑定变量的数据库查询创建pandas数据框

Question

我正在使用Oracle数据库。我能做到这些：

    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)

但是我不知道怎么处理绑定变量，比如这样：

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

我尝试过这些不同的方法：

   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)

下面这个可以用：

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

但是这个解决方案就是……不太优雅。如果可以的话，我想知道有没有办法只用pandas来完成整个操作，而不需要创建游标对象。

Answer 1

根据我的理解，pandas希望在你把SQL字符串传递给它之前，这个字符串已经完全准备好了。考虑到这一点，我通常会使用字符串插值的方法：

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

这样就能得到

SELECT * FROM customers 
WHERE id BETWEEN 1234 AND 5678

所以这个应该可以顺利地传入psql.frame_query。根据我的经验，这在postgres、mysql和sql server中都能正常工作。

Answer 2

试试用 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)