Pandas read_sql query with multiple choices

Question

Can read_sql query handle sql script with multiple select statements?

I have an MSSQL query that performs different tasks, but I do not want to write an individual query for each case. I would like to write only one query and pull out a few tables.

I need multiple requests in the same script because the requests are related and this makes 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...) 

This leads to two separate query results.

The following python code:

 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 entered.

In any case, I can get both query results (possibly with a dictionary), which will prevent me from separating the query from several scripts.

Answer 1

You can 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 execute the result:

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

---

Another option is to use UNION for two results, that is, execute concat in SQL.