将对象传递给Python SQL

Question

我有一个通过ODBC连接获取的pandas数据框（df）：

import pandas.io.sql as psql
handle = pyodbc.connect('...')
df1 = psql.frame_query("select * from Table1 where... [some queries on columns]")
# below is a pandas df resulting from the above SQL query
df1 = pd.DataFrame([[1, 'F', 11111, 500, 60], [2, 'M', 22222, 400, 30], [3, 'M', 33333, 5400, 78], [4, 'F', 44444, 5400, 45], [5, 'M', 55555, 8914, 66]], columns = ['ID','Gender','ZipCd','Spend','Age'])

现在我想在同一个数据库的另一个表上运行一个单独的查询；作为其中一个条件，我想提取与df1中的ID匹配的行（例如，下面的方式是行不通的）。

df2 = psql.frame_query("select * from Table2 where ID = ? and StatusCd in ('104', '106', '112', '115')", df1['ID'])
# The ID variable is a common unique identifier b/n the 2 tables

我的问题是，如何将df1['ID']作为一个元素列表来查询df2？比如说...where ID in (1,2,3,...)，但是要用df1['ID']作为包含这个列表的对象。这样可以返回在df2中ID与df1匹配的记录，以及其他查询条件。

我对R的语法比较熟悉，所以从概念上讲，我的问题和这个很相似：将R变量传递给RODBC的sqlQuery？

最终，我想要筛选出表1中的记录，只保留在表2中找到的记录（也就是说，包含表2中所需的StatusCds）。在这方面，我相信有更有效的方法来调用数据，可能只需要一个查询，但我对python或SQL还不够熟悉。

进一步评论

我提到pyodbc是因为我最初是通过这个模块从我的SQL服务器获取数据的；也许pyodbc是处理这种任务的更有效的方法？不过我主要是R和电子表格的用户，而pandas对我来说是最容易学习的工具。

Answer 1

我觉得这个问题的答案在于写一个更好的SQL查询语句：

psql.frame_query("select... from Table1 as t1
                 inner join Table 2 t2
                 on t1.ID = t2.ID
                 where [add various queries from both tables]", handle)

我决定关闭这个帖子，因为它更像是一个SQL问题，可以通过基本的文档来回答。

Answer 2

在你的原始查询中使用 INNER JOIN，这样可以从 table1 中返回那些在 table2 中有匹配 ID 的行，特别是你需要的状态码。同时，把状态码放在一个变量里，并在执行 SQL 语句时使用参数化的方式。代码大概是这样的：

...
codes = ("104", "106", "112", "115")
sql = """select Table1.*
         from Table1
         inner join Table2
            on Table1.ID = Table2.ID
         where Table2.StatusCd in (?, ?, ?, ?)"""
df1 = psql.frame_query(sql, codes)
...

Answer 3

frame_query 这个函数可以接受一个可选的 params 参数，这个参数可以是一个列表或者元组，用来在 SQL 查询中使用。如果你想要包含一组参数，可以利用 Python 的字符串格式化来放置正确数量的占位符。

举个例子：

placeholders = ','.join(['?'] * df1['ID'].count())
query = ("select * from Table2 where ID in ({}) "
         "and StatusCd in ('104', '106', '112', '115')").format(placeholders)
df2 = psql.frame_query(query, params=df1['ID'].tolist())

如果有三个 ID，那么查询字符串会是 select * from Table2 where ID in (?, ?, ?) and StatusCd in ('104', '106', '112', '115')。

不过，发送的参数数量是有限制的，所以如果你的参数数量非常多，可能需要分批进行多个查询，然后把得到的数据合并在一起。