I have a bunch of Excel files, I read them in, do stuff with them and then write them into a SQLite DB.

I need to query those tables, my basic query takes 32 minutes because there are no primary keys set. Doing that by hand doesn't work because I read Excel files and write to DB on a regular base, redoing the primary keys every day is not a solution.

Haven't found anything in the documentation so far.

Any ways to do that in pandas?

Had an issue with this today and figured others might benefit from the solution.

I've been creating some of the tables for the Postgres database in my Flask app with Pandas to_sql method (the datasource is messy and Pandas handles all the issues very well with very little coding on my part). The rest of the tables are initialized with a SQLAlchemy model, which allows my to easily query the tables with session.query:

db.session.query(AppUsers).filter_by(id = uId).first()

I couldn't however query the tables created by Pandas because they weren't part of the model. Enter automap_base from sqlalchemy.ext.automap (tableNamesDict is a dict with only the Pandas tables):

metadata = MetaData()
metadata.reflect(db.engine, only=tableNamesDict.values())
Base = automap_base(metadata=metadata)
Base.prepare()

Which would have worked perfectly, except for one problem, automap requires the tables to have a primary key. Ok, no problem, I'm sure Pandas to_sql has a way to indicate the primary key... nope. This is where it gets a little hacky:

for df in dfs.keys():
    cols = dfs[df].columns
    cols = [str(col) for col in cols if 'id' in col.lower()]
    schema = pd.io.sql.get_schema(dfs[df],df, con=db.engine, keys=cols)
    db.engine.execute('DROP TABLE ' + df + ';')
    db.engine.execute(schema)
    dfs[df].to_sql(df,con=db.engine, index=False, if_exists='append')

I iterate thru the dict of DataFrames, get a list of the columns to use for the primary key (i.e. those containing id), use get_schema to create the empty tables then append the DataFrame to the table.

Now that you have the models, you can explicitly name and use them (i.e. User = Base.classes.user) with session.query or create a dict of all the classes with something like this:

alchemyClassDict = {}
for t in Base.classes.keys():
    alchemyClassDict[t] = Base.classes[t]

And query with:

res = db.session.query(alchemyClassDict['user']).first()

Hope this helps someone.

Lets say I have an excel with columns [Class ID (reference key), Mean Score, Mode Score, Median Score] and I have an SQL database with columns [Class_ID (primary key), Mean_Score, Mode_Score, Median_Score, Last_updated]

1. How can I use pandas on python to read in the excel and update the SQL database with reference to the primary key?

2. Would the difference in column names matter?

3. How can I keep track of when was the last time the class id was updated?

4. What if there is new class ID of data that does not exist in the SQL database?

5. Looking at the df.itterrows() function, what if I have a lot of columns is there a way I tuple,list,dictionary it?