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.
append() function is used to append rows of other dataframe to the end of the given dataframe, returning a new dataframe object. Columns not in the original dataframes are added as new columns and the new cells are populated with NaN value. Parameters: other : DataFrame or Series/dict-like object, or list of these.
Using loc[] to Append The New List to a DataFrame. By using df. loc[index]=list you can append a list as a row to the DataFrame at a specified Index, In order to add at the end get the index of the last record using len(df) function.
The append() method appends a DataFrame-like object at the end of the current DataFrame. The append() method returns a new DataFrame object, no changes are done with the original DataFrame.
How about
for i in orders:
df = df.append(pd.read_sql(querystring, cnxn, params = [i]))
Need to assign the result:
df = df.append(pd.read_sql(querystring, cnxn, params = [i]))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With