使用带有参数化查询的 pyodbc 将数据库读入数据帧 [英] Read database into dataframe using pyodbc with parameterized query

问题描述

我正在使用 pyodbc 从 SQL Server 获取数据，使用此处显示的脚本:

I'm using pyodbc to get data from a SQL Server, using the script shown here:

conn = pyodbc.connect('DSN=DATASOURCE')
tbl = "SELECT TableA.Field_1 \
    FROM TableA \
    WHERE TableA.Date>=2019/04/01"
SQL_Query = pd.read_sql_query(tbl, conn)
conn.close

现在我想把这个查询变成一个 Python 函数，在那里我可以改变上面例子中的日期 (2019/04/01) 作为函数变量.

Now I want to make this query into a Python function, where I can change the date (2019/04/01) in the example above as a function variable.

我发现 pyodbc 提供了参数化，但都在上下文中cursor.execute 函数.

I found pyodbc offers parameterization, but all in the context of cursor.execute function.

理想情况下，我想创建一个这样的函数:

Ideally, I'd like to create a function like this:

def DB_Query(date):
    conn = pyodbc.connect('DSN=DATASOURCE')
    tbl = "SELECT TableA.Field_1 \
    FROM TableA \
    WHERE TableA.Date>=?", date
    SQL_Query = pd.read_sql_query(tbl, conn)
    conn.close
    return SQL_Query

显然这是行不通的，因为 tbl 必须是普通字符串，但是是否可以将 pyodbc 的参数化功能与 pandas 的 pd.read_sql_query 或 <代码>pd.read_sql?

Apparently this doesn't work because tbl has to be a normal string, but is it possible to use pyodbc's parameterization feature together with pandas' pd.read_sql_query or pd.read_sql?

推荐答案

您可以通过设置 params 以与 cursor.execute 相同的方式参数化 read_sql_query 参数:https://pandas.pydata.org/docs/reference/api/pandas.read_sql_query.html

You can parameterize read_sql_query in the same way as cursor.execute by setting the params parameter: https://pandas.pydata.org/docs/reference/api/pandas.read_sql_query.html

SQL Server 示例:

Example for SQL Server:

import pandas as pd

sql = '''
 select *
 from Table
 where Column = ?
'''
df = pd.read_sql(sql, params=[query_param])

Oracle 示例:

import pandas as pd

sql = '''
 select *
 from table
 where Column = :query_param
'''
df = pd.read_sql(sql, params={'query_param': 'query_value'})

这篇关于使用带有参数化查询的 pyodbc 将数据库读入数据帧的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！