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
– mHelpMe, Commented Oct 31, 2018 at 15:27
If you look at the documentation about pyodbc cursor objects, 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
– 9769953, Commented Nov 5, 2018 at 9: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
– Karl, Commented Nov 9, 2018 at 7:19

benvc · Accepted Answer · 2018-11-09 18:06:46Z

6

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

edited Nov 9, 2018 at 18:06

answered Nov 9, 2018 at 17:56

benvc

15.3k4 gold badges39 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

m33n · Accepted Answer · 2018-11-13 08:34:10Z

6

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, 2018 at 8:34

answered Nov 5, 2018 at 8:57

m33n

1,76118 silver badges41 bronze badges

4 Comments

9769953 Over a year ago

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.

m33n Over a year ago

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

9769953 Over a year ago

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.

Gord Thompson Over a year ago

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

Collectives™ on Stack Overflow

How to return a list from SQL query using pyodbc?

2 Answers 2

Comments

EDIT:

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

EDIT:

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related