python - 使用 Python Pandas 将参数传递给 SQL CTE

Question

我正在使用 pandas.io.sql 执行一个包含 CTE 的 SQL 脚本，我想做这样的事情:

import pandas.io.sql as psql
param1 = 'park'
param2 = 'zoo'

sqlstr = ("""WITH CTE_A AS (    
                          SELECT * 
                          FROM A 
                          WHERE A.Location = param1),
                  CTE_B AS (
                            SELECT * 
                            FROM B 
                            WHERE B.Location = param2)
            SELECT A.*, B.*
            FROM C
            INNER JOIN A
            ON C.something = A.something
            INNER JOIN B
            ON C.something = B.something 
            WHERE C.combined = param1 || param2
            )

我想做这样的事情

    result = psql.frame_query(sqlstr, con = db, params = (param1,param2))

谁能帮我用 Pandas 传递这两个参数？

Answer 1

我知道如何做这样的事情的唯一方法是做下面的事情。但是，它没有利用 Pandas 中的 psql 包。

import pyodbc
import pandas

conn = pyodbc.connect('yourconnectionstring')
curs = conn.cursor()

param1 = 'park'
param2 = 'zoo'

sqlstr = """WITH CTE_A AS (    
                          SELECT * 
                          FROM A 
                          WHERE A.Location = param1),
                  CTE_B AS (
                            SELECT * 
                            FROM B 
                            WHERE B.Location = param2)
            SELECT A.*, B.*
            FROM C
            INNER JOIN A
            ON C.something = A.something
            INNER JOIN B
            ON C.something = B.something 
            WHERE C.combined = ?|| ?;"""
q = curs.execute(sqlstr,[param1,param2]).fetchall()
df = pandas.DataFrame(q)

curs.close()
conn.close()

这会传递参数以避免 SQL 注入(inject)，并以包含您的结果的 DataFrame 对象结束