Fetch data from multiple result sets - SQL Server to Python-CodePudding

How to union or make the multiple results sets to one, so that I can fetch the data to python dataframe

EXEC sp_MSforeachdb
@command1 = '
IF not exists(select 1 where ''?'' in  (''master'',''model'',''msdb'',''tempdb'',''dpa_dpa_sw1'',''dpa_repository''))


EXEC [?].dbo.sp_MSforeachtable
 @command1 = ''SELECT TOP 1 db_name() AS DB_Name, ''''&'''' AS  Table_name , * from &'', @replacechar=''&'' '

Python Trial

def db_connect ():
    server, username, password = 'xx.xx.xx.xx', 'sa', 'xxxx'
    conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};'
                          'SERVER='   server
                            ';UID='   username   ';PWD='   password)
    cursor = conn.cursor()
    query = ("EXEC sp_MSforeachdb @command1 = 'IF not exists(select 1 where ''?'' "
             "in  (''master'',''model'',''msdb'',''tempdb'')) EXEC [?].dbo.sp_MSforeachtable"
             " @command1 = ''SELECT TOP 1 db_name() AS DB_Name, ''''&'''' AS  Table_name"
             " , * from &'', @replacechar=''&'' ';")
    df = pd.read_sql(query, conn)
    conn.close()
    return df
df = db_connect()

Result

| DB_Name | Table_name          | id | _NAME           | _NUMERICID | _VALUE | _TIMESTAMP              | _QUALITY |
|---------|---------------------|----|-----------------|------------|--------|-------------------------|----------|
| aaaa    | [dbo].[aaa_exhaust] | 1  | aaaa_vib.00.41 | 0          | 2085   | 2022-08-06 00:30:43.517 | 192      |

In the above case I get only 1st result set, I need results of all result sets in dataframe.

CodePudding user response：

I'm not sure if Pandas can handle multiple result sets like that. You can always use pyodbc's cursor functionality to iterate the multiple result sets, though, and construct a DataFrame like the following...

import pandas as pd
import pyodbc

server, username, password = "127.0.0.1,1433", "sa", "StrongPassw0rd"
connstring = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=" server ";UID=" username ";PWD=" password
conn = pyodbc.connect(connstring)
cursor = conn.cursor().execute("""
select 1 as A;
select 2 as B;
select 3 as C;
""")

buffer = []
while True:
    # Get the column names for the current result set
    columnNames = [col[0] for col in cursor.description]

    # Get the data rows for the current result set
    for dataRow in cursor.fetchall():
        buffer.append({name: dataRow[index] for index, name in enumerate(columnNames)})

    # Iterate to the next result set, otherwise we're "done"
    if not cursor.nextset():
        break

cursor.close()
conn.close()

# Convert the buffer to a DataFrame and display the result
df = pd.DataFrame(buffer)
print(df.to_string())

Which outputs the following:

     A    B    C
0  1.0  NaN  NaN
1  NaN  2.0  NaN
2  NaN  NaN  3.0