将变量绑定到Pandas.read_sql的SQLAlchemy查询 [英] Binding variables to SQLAlchemy query for Pandas.read_sql

问题描述

是否可以将变量绑定到Pandas.read_sql语句中使用的SQLAlchemy查询？

Is it possible to bind variables to a SQLAlchemy query used in a Pandas.read_sql statement?

在WHERE子句中使用％s不起作用，并且有关cx_Oracle状态：

Using %s in the WHERE clause does not work and the documentation for cx_Oracle states:

cursor.execute（'SELECT * FROM employee WHERE Department_id =：dept_id）

cursor.execute('SELECT * FROM employees WHERE department_id=:dept_id)

在熊猫中不建议直接使用cx_Oracle驱动程序，也不是可行的选择。

Using the cx_Oracle driver directly is deprecated in Pandas and is not a viable option.

我有一个组列表我需要遍历WHERE语句，因为SELECT *的内存太大，无法在单个PC上处理。

I have a list of groups I need to iterate through the WHERE statement as SELECT * is too large in memory to handle on a single PC.

示例：

选择*
来自双
WHERE GROUP_NAME =％s

返回此错误：

（cx_Oracle.DatabaseError）ORA-00911：无效字符 ... WHERE GROUP_NAME =％s

(cx_Oracle.DatabaseError) ORA-00911: invalid character ... WHERE GROUP_NAME = %s

推荐答案

如您所见此处， cx_Oracle.paramstyle 是命名为而不是格式。根据 PEP 249 ，您必须使用<$ c $ 已命名 参数样式的c>：name 语法：

As you can see here, cx_Oracle.paramstyle is named not format. According to PEP 249 you have to use the :name syntax for named paramstyle:

import pandas as pd
sql = '''
 SELECT *
 FROM DUAL
 WHERE GROUP_NAME = :name
'''
df = pd.read_sql(sql, params={'name': the_name_you_want})

这篇关于将变量绑定到Pandas.read_sql的SQLAlchemy查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！