How to return a list from SQL query using pyodbc?

Question

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())

just a select query, very simple for testing purpose. so about 10 columns, dates, intergers, text — mHelpMe, Oct 31 '18 at 15:27
If you look at [the documentation about pyodbc cursor objects](https://github.com/mkleehammer/pyodbc/wiki/Objects#cursors), you'll find that each row has a set of attributes that correspond in name to the column names from your table. While I have no database to test, I have a slight feeling that pyodbc doesn't actually follow the Python DB API 2.0 fully, and that a single row is not a list of its columns. — 9769953, Nov 05 '18 at 09:06
Why do you want to return the data as a list? Data from SQL Server are in table format. The analogue of that in Python is a Pandas DataFrame and the easiest way to get that is via pd.read_sql with a pyodbc connection. See m33n's answer below, in my estimation that is the best solution to your problem — Karl, Nov 09 '18 at 07:19

benvc · Accepted Answer · 2018-11-09T18:06:46.190

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'}

m33n · Answer 2 · 2018-11-13T08:34:10.617

5

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

edited Nov 13 '18 at 08:34

answered Nov 05 '18 at 08:57

m33n

1,622
15
38

I don't think this helps: my guess is that Pandas does something very similar to `list(cursor.fetchall())` under the hood, and you'd be stuck with a dataframe similar to the output obtained in the question. – 9769953 Nov 05 '18 at 09:07
Why would you prefer a list instead of a dataframe? A list is a simpler data structure but you will need to know the ordering of your columns in you query for example. With pandas you can deal with the column name directly! Also it has a large number of operations that you can apply to your data after queried that can be executed in parallel with very simple code. Pandas is one of the main python libraries nowadays :) – m33n Nov 05 '18 at 09:10
That's not my point: the dataframe itself will likely contain columns such as given in the question: "0, Row, 1, Row object of pyodbc module", *not* "128929, 2018-01-01, Bob, 105.3" as preferred. A dataframe would be equally unusable as a (double) list. – 9769953 Nov 05 '18 at 10:08
2

@9769953 - *"the dataframe itself will likely contain columns such as given in the question: '0, Row, 1, Row object of pyodbc module'"*. No, it won't. pandas will unpack the `pyodbc.Row` objects into a proper DataFrame. Try it and see. – Gord Thompson Nov 05 '18 at 17:16

How to return a list from SQL query using pyodbc?

2 Answers2

EDIT: