So instead, with a little meta-programming magic, I wrote a loop that goes through a SQLAlchemy table object’s columns and explicitly defines them as attributes back on the object and gives it the name c_. When I type t_names.c into a Jupyter cell and then hit Tab, I get nothing. I originally thought that Jupyter’s built-in auto-complete of defined variables and functions would also extend to whatever was held in this mysterious c collection. Auto-complete column namesĪbove, the second loop containing the more SQLAlchemy-ish query, you’ll see that I was able to access the first_name column of the names table by writing t_names.c.first_name. For more complicated ones and especially if I have many queries that are subtly different, I like using the more programmatic SQLAlchemy way. The second loop runs the same exact query, but uses more SQLAlchemy features.įor really simple or one off queries, I generally prefer just writing the raw SQL. The first loop uses string substitution to parameterize a query. Below, I show a simple query that gets run in a for loop with results saved as pandas Dataframes. Now that we have tables represented as Python objects, I can start doing some fun things like parameterizing queries in loops and then kicking it off as I walk away to get a pastry while data gets fetched. Screenshot of table ‘names’ with three recordsįor the table names shown above, the t_table object gets loaded with this: Reflected SQLAlchemy Table object shown in Jupyter T_table = sqlalchemy.Table('names', meta, autoload=True) Rather, I write something like this to load a SQLAlchemy table object based upon a table in the database: conn_string = create_engine(conn_string) I’m often working with tables that have dozens of columns, I don’t want to go through the pain of specifying every single column and column type. Getting startedįirst off, I take advantage of SQLAlchemy’s reflection functionality.

Since I work mostly in Jupyter for my analysis contracts, I wanted to share with others how I use SQLAlchemy to DRY up my queries and get auto-complete for table and column names. Other times, it’s from writing the same SQL clause over and over again, copying and pasting and setting up hazards for my future self. Many times, my irritation comes from not having a good SQL editor that auto-completes table and column names. Even in the wrong hands, it can do powerful things! And yet, as a programmer-turned-data scientist, I’m annoyed every time that I have to write a query. It’s easy to learn and with the right training it can do very powerful things.

Autocomplete in jupyterlab