将变量绑定到 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 Employees WHERE Department_id=:dept_id)

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

直接使用 cx_Oracle 驱动程序在 Pandas 中已被弃用，并且不是一个可行的选择.

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 是 named 不是 format.根据 PEP 249 你必须使用 :namenamed paramstyle 的 语法:

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屋！