带有多个选择的 pandas read_sql查询 [英] Pandas read_sql query with multiple selects

问题描述

read_sql查询可以处理带有多个select语句的sql脚本吗?

Can read_sql query handle a sql script with multiple select statements?

我有一个执行不同任务的MSSQL查询，但我不想为每种情况编写一个单独的查询.我只想编写一个查询并提取多个表.

I have a MSSQL query that is performing different tasks, but I don't want to have to write an individual query for each case. I would like to write just the one query and pull in the multiple tables.

我想在同一个脚本中进行多个查询，因为这些查询是相关的，这使得更新脚本更加容易.

I want the multiple queries in the same script because the queries are related, and it making updating the script easier.

例如:

SELECT ColumnX_1, ColumnX_2, ColumnX_3

FROM Table_X
INNER JOIN (Etc etc...)

----------------------
SELECT ColumnY_1, ColumnY_2, ColumnY_3

FROM Table_Y
INNER JOIN (Etc etc...)

这将导致两个单独的查询结果.

Which leads to two separate query results.

后续的python代码是:

The subsequent python code is:

scriptFile = open('.../SQL Queries/SQLScript.sql','r')
script = scriptFile.read()
engine = sqlalchemy.create_engine("mssql+pyodbc://UserName:PW!@Table")
connection = engine.connect()

df = pd.read_sql_query(script,connection)
connection.close()

仅引入查询中的第一个表.

Only the first table from the query is brought in.

无论如何，我都可以提取两个查询结果(也许带有字典)，这将避免我不得不将查询分为多个脚本.

Is there anyway I can pull in both query results (maybe with a dictionary) that will prevent me from having to separate the query into multiple scripts.

推荐答案

您可以执行以下操作:

queries = """
SELECT ColumnX_1, ColumnX_2, ColumnX_3

FROM Table_X
INNER JOIN (Etc etc...)
---
SELECT ColumnY_1, ColumnY_2, ColumnY_3

FROM Table_Y
INNER JOIN (Etc etc...)
""".split("---")

现在您可以查询每个表并合并结果:

Now you can query each table and concat the result:

df = pd.concat([pd.read_sql_query(q, connection) for q in queries])

---

另一种选择是对两个结果使用UNION，即在SQL中执行concat.

---

Another option is to use UNION on the two results i.e. do the concat in SQL.

这篇关于带有多个选择的 pandas read_sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！