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
– MaxU - stand with Ukraine, Commented Jul 28, 2016 at 20:37
I was hoping there was a way to create a dictionary with all data frames inside. — Austin B
– Austin B, Commented Jul 28, 2016 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
– Parfait, Commented Jul 28, 2016 at 22:37

Andy Hayden · Accepted Answer · 2016-07-28 21:51:37Z

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, 2016 at 21:51

Andy Hayden

378k110 gold badges640 silver badges546 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Austin B Over a year ago

The .split method works better for me here, as the two queries are unrelated (but share similar derivations). Thanks!

Collectives™ on Stack Overflow

Pandas read_sql query with multiple selects

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related