Pandas read_sql query with multiple selects

Question

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

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.

For example:

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.

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.

how do you want to have tow different sets of columns in one data frame? — MaxU - stand with Ukraine, Jul 28 '16 at 20:37
I was hoping there was a way to create a dictionary with all data frames inside. — Austin B, Jul 28 '16 at 20:57
How do the queries relate? Devise an SQL script with CROSS JOIN, INNER JOIN, or SQL Server's CROSS APPLY into one statement. Then import into pandas at once for migration into dictionary of dataframes by a column indicator. — Parfait, Jul 28 '16 at 22:37

score 8 · Accepted Answer · answered Jul 28 '16 at 21:51

8

You could do the following:

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])

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

answered Jul 28 '16 at 21:51

Andy Hayden

359,921
101
625
535

The .split method works better for me here, as the two queries are unrelated (but share similar derivations). Thanks! – Austin B Jul 29 '16 at 12:38

Pandas read_sql query with multiple selects

1 Answers1

Linked