How to return a list from SQL query using pyodbc?

user_2730554 0
Viewed 11607

I am trying to run a select query to retrieve data from SQL Server using pyodbc in python 2.7. I want the data to be returned in a list. The code I have written is below.

It works, kinda, but not in the way I expected. My returned list looks something like below:

Index     Type     Size        Value
0         Row      1           Row object of pyodbc module
1         Row      1           Row object of pyodbc module
...
105       Row      1           Row object of pyodbc module

I was hoping to see something like below (i.e. my table in SQL)

ActionId   AnnDate      Name    SaleValue
128929     2018-01-01   Bob     105.3
193329     2018-04-05   Bob     1006.98
...
23654      2018-11-21   Bob     103.32

Is a list not the best way to return data from a SQL query using pyodbc?

Code

import pyodbc


def GetSQLData(dbName, query):

    sPass = 'MyPassword'
    sServer = 'MyServer\\SQL1'
    uname = 'MyUser'

    cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                    "Server=" + sServer + ";"
                    "Database=" + dbName + ";"
                    "uid=" + uname + ";pwd=" + sPass)

    cursor = cnxn.cursor()
    cursor.execute(query)

    return list(cursor.fetchall())
python-2.7 pyodbc list python
2 Answers

If you want to return your query results as a list of lists with your column names as the first sublist (similar to the example output in your question), then you can do something like the following:

import pyodbc


cnxn = pyodbc.connect("YOUR_CONNECTION_STRING")
cursor = cnxn.cursor()

cursor.execute("YOUR_QUERY")

columns = [column[0] for column in cursor.description]
results = [columns] + [row for row in cursor.fetchall()]

for result in results:
    print result

# EXAMPLE OUTPUT
# ['col1', 'col2']
# ['r1c1', 'r1c2']
# ['r2c1', 'r2c2']

Depending on how you are using the results, I often find it more useful to a have a list of dicts. For example:

results = [dict(zip(columns, row)) for row in cursor.fetchall()]

for result in results:
    print result

# EXAMPLE OUTPUT
# {'col1': 'r1c1', 'col2':'r1c2'}
# {'col1': 'r2c1', 'col2':'r2c2'}
user_99589420

There is even a better option than a list, try Pandas DataFrame! It helps to deal with column names and apply column wise operations!

import pandas as pd
import pyodbc


def GetSQLData(dbName, query):

    sPass = 'MyPassword'
    sServer = 'MyServer\\SQL1'
    uname = 'MyUser'

    cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                    "Server=" + sServer + ";"
                    "Database=" + dbName + ";"
                    "uid=" + uname + ";pwd=" + sPass)


    df = pd.read_sql(cnxn, query)

    return df  # Pandas Dataframe

EDIT:

If you prefer a list of lists, (this means one list per row) you can obtain it by:

df.values.tolist()  # list of lists

But I highly recommend you to start working with pandas

user_83806380
Related