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: [![enter image description
here](http://i.stack.imgur.com/A7Vd3.jpg)](http://i.stack.imgur.com/A7Vd3.jpg)

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

Having read the [dataframe.append page](http://pandas.pydata.org/pandas-
docs/stable/generated/pandas.DataFrame.append.html), 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: 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 (?,?,?,?)'
    

