How do I use a dataframe in SQLalchemy

Question

I have two dataframes of demographic and medication data that I created from a pipe-delimited file:

demo='xxyy1046_demo.txt'
demog=pd.read_table(demo, delimiter='|', header=0)
demog.info()

med='xxyy1046_medication.txt'
meds=pd.read_table(med, delimiter='|', header=0)
meds.info() #n=2654

I now want to use SQLalchemy to convert these dataframes in SQL table objects that I can select, join, group_by, etc.

from sqlalchemy import create_engine, column, select, Table, Metadata
engine = create_engine('sqlite://', echo=False)
demog.to_sql('Demog_sql', con=engine)

metadata = sql.Metadata()
demog_sql = Table('Demog_sql', metadata, autoload=True, autoload_with=engine)
stmt = select([demog_sql.columns.FirstName]) 
print(stmt)

I get the result: SELECT "Demog_sql"."FirstName" FROM "Demog_sql"

This is not what I want! I want to be able to manipulate the data using select statements, joins, etc. I am doing something wrong with the metadata step. How do I fix this?

Answer 1

You are printing the string representation of the statement object itself. It seems you would like to execute it instead. For example:

results = engine.execute(stmt).fetchall()

For joins and the like you would call further methods on your statement, best explained in the tutorial, which you should read before you continue.

In some cases you do not need to upload your data to SQL for joins etc. Pandas can do those too between dataframes.