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.

Answer 1

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.