Appending to a Pandas Dataframe From a pd.read_sql Output

Question

I'm coming from R but need to do this in Python for various reasons. This very well could be a basic PEBKAC issue with my Python more than anything with Pandas, PyODBC or anything else.

Please bear with me.

My current Python 3 code:

import pandas as pd
import pyodbc 
cnxn = pyodbc.connect(DSN="databasename", uid = "username", pwd = "password")

querystring = 'select order_number, creation_date from table_name where order_number = ?'

orders = ['1234',
'2345',
'3456',
'5678']

for i in orders:
   print(pd.read_sql(querystring, cnxn, params = [i]))

What I need is a dataframe with the column names of "order_number" and "creation_date."

What the code outputs is:

Sorry for the screenshot, couldn't get the formatting right here.

Having read the dataframe.append page, I tried this:

df = pd.DataFrame()

for i in orders:
       df.append(pd.read_sql(querystring, cnxn, params = [i]))

That appears to run fine (no errors thrown, anyway).

But when I try to output df, I get

Empty DataFrame
Columns: []
Index: []

So surely it must be possible to do a pd.read_sql with params from a list (or tuple, or dictionary, ymmv) and add those results as rows into a pd.DataFrame().

However, I am failing either at my Stack searching, Googling, or Python in general (with a distinct possibility of all three).

Any guidance here would be greatly appreciated.

Answer 1

How about

for i in orders: df = df.append(pd.read_sql(querystring, cnxn, params = [i]))

Answer 2

Need to assign the result:

df = df.append(pd.read_sql(querystring, cnxn, params = [i]))

Answer 3

you may try to do it this way:

df = pd.concat([pd.read_sql(querystring, cnxn, params = [i] for i in orders], ignore_index=True)

so you don't need an extra loop ...

alternatively if your orders list is relatively small, you can select all your rows "in one shot":

querystring = 'select order_number, creation_date from table_name where order_number in ({})'.format(','.join(['?']*len(orders)))

df = pd.read_sql(querystring, cnxn, params=orders)

generated SQL

In [8]: querystring
Out[8]: 'select order_number, creation_date from table_name where order_number in (?,?,?,?)'

Answer 4

You can create the a dataframe directly by using the following code:

df = pd.read_sql_query(querystring, cnxn)