Convert list[adodbapi.apibase.SQLrow] to pd.DataFrame

Question

Among sql-server connectors adodbapi is the only one that's working in my environment.

import adodbapi

conn = adodbapi.connect("PROVIDER=SQLOLEDB;Data Source={0};Database={1}; \
       UID={2};PWD={3};".format(server,db,user,pwd))
cursor = conn.cursor()
query_list = [row for row in cursor]

type(query_list[0]) = adodbapi.apibase.SQLrow

How to convert this list into a pandas df?

Thanks

score 6 · Accepted Answer · answered Jun 05 '19 at 17:46

This is how I did it:

import adodbapi as ado
import numpy as np
import pandas as pd

def get_df(data):
    ar = np.array(data.ado_results) # turn ado results into a numpy array
    df = pd.DataFrame(ar).transpose() # create a dataframe from the array
    df.columns = data.columnNames.keys() # set column names
    return df

with ado.connect('yourconnectionstring') as con:
    with con.cursor() as cur:
        sql_str = 'yourquery'
        cur.execute(sql_str)
        data = cur.fetchall()
        df = get_df(data)

score 0 · Answer 2 · answered May 10 '19 at 10:11

0

This may help:

import pandas as pd

.......
ur_statements
.......

query_list = [row for row in cursor]
df = pd.DataFrame({'col':query_list })
print (df)

answered May 10 '19 at 10:11

ashishmishra

363
2
14

1

getting `KeyError: '_typ'` – shanlodh May 10 '19 at 10:13
Can i see your list – ashishmishra May 10 '19 at 10:17
list looks like this: ``; will check the links, thanks – shanlodh May 10 '19 at 10:26

Parfait · Answer 3 · 2019-09-17T19:52:04.570

Consider pandas' read_sql to directly query the database. Currently, though you will recieve an error:

KeyError: '_typ'

However, there is a working fix thanks to @TomAubrunner on this Github ticket which appears to be a bug in adodbapi.

Find location of adodpapi: print(adodbapi.__file__)
Open the script in folder: apibase.py
Locate: return self._getValue(self.rows.columnNames[name.lower()]) and replace with below try/execpt block:
```
try:
    return self._getValue(self.rows.columnNames[name.lower()])
except:
    return False
```

Once done, simply run as you would any DB-API pandas connection even with qmark parameters:

import pandas as pd
import adodbapi

conn = adodbapi.connect("PROVIDER=SQLOLEDB;Data Source={0};Database={1}; \
       UID={2};PWD={3};".format(server,db,user,pwd))

# WITHOUT PARAMS
df = pd.read_sql("SELECT * FROM myTable", conn)

# WITH PARAMS    
df = pd.read_sql("SELECT * FROM myTable WHERE [Col]= ?", conn, params=['myValue'])

conn.close()

Convert list[adodbapi.apibase.SQLrow] to pd.DataFrame

3 Answers3