从使用绑定变量的数据库查询中创建 pandas 数据框 [英] creating a pandas dataframe from a database query that uses bind variables
本文介绍了从使用绑定变量的数据库查询中创建 pandas 数据框的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用Oracle数据库.我可以做的很多:
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)
以下作品:
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?
推荐答案
尝试使用 pandas.io.sql.read_sql_query
.我使用的是熊猫版本0.20.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)
这篇关于从使用绑定变量的数据库查询中创建 pandas 数据框的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文