I’m spending the summer at the Recurse Center, where I’m working with a group of other awesome programmers to learn and self-study programming full-time for three months.

My first few weeks here, I’ve primarily been learning how to populate a database with Python. Yelp has given me access to the Yelp academic dataset, which I recieved as a JSON file, but to make it easy to query I wanted to put it in a relational database. I started off using sqlite3, which is a great module but essentially requires you to write SQL by hand. Python then passes your queries directly to the database. This is a big hassle if you want to automate queries, say, to run a bunch of insert statements.

This is where SQLalchemy comes in very handy. This module is a powerful object-relational mapper (ORM) that allows the user to abstract away most of the actual SQL. Instead, you can define classes for each of your tables, and run queries using methods and an abstracted ‘SQL session’. This has several advantages. First, and most important to me, it means that you don’t have to build queries by hand, which is much less of a hassle than sqlite3. For more complicated queries, SQLalchemy does allow you to write and submit your own SQL statements (I found this especially useful for debugging). Second, it makes your code less database-specific. If you’re using the object-oriented features of SQLalchemy, most of your code should work regardless of which SQL flavor you choose, since all of the language-specific details are abstracted away. Finally, it’s easier to keep your database safe from injection attacks without hassle, since SQLalchemy handles it under the hood for you. However, this abstraction and convenience comes with a fairly steep learning curve, and the documentation is good but sometimes overwhelming. Hopefully this post will be a useful resource for anyone hoping to learn the basics!

Engine and Session setup

There are several layers of abstraction to help you communicate with your database. The process that communicates between Python and your database is called the engine. Here’s how I created my engine:

import logging
from sqlalchemy import create_engine
engine =
create_engine('postgresql://postgres:password@localhost/yelp_restaurant',
echo = True)

The logging module is used for the echo = True option, which prints helpful error messages as needed. You can see that I’m using postgresql, with username postgres and password password (I’ve set up the database on my computer rather than an external server, so I’m not overly worried about anyone hacking into it). localhost is the host name, so I’m just telling the engine that it can find the database on my computer. yelp_restaurant is what I’ve named my database, since I’m going to be storing restaurant and review information.

If you wanted to deal with the engine directly, you would need to set up a connection, and communicate via the connection. However, it’s more convenient to use a session, which handles the engine under the hood. I like to think of the session as temporary version control for your database queries. The session takes in your SQL, stores it in a safe place, and submits it all when you tell it to. First we need to create a session to work with:

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()

Here I’m linking a session to the engine and intializing it. From now on, I can totally ignore the engine and let the session do all of the communication work for me.

Using the session

There are four major session methods that I have used so far: query, add, flush, and commit. As the name suggests, the query method allows you to query the database, letting SQLalchemy write the SELECT statements for you. It returns a query object, which can be updated or refined in future calls. Here is an example from my code:

id = session.query(Restaurant).filter(Restaurant.restaurant_id == val)
id = session.query(id.exists()).scalar()

I will talk more about the table classes in my next post, but for now, know that Restaurant is a class I created for generating and interacting with a table of restaurants in Yelp. Here I am querying the restaurant database for all restaurants where the restaurant_id is equal to a specific value. I assign this query to id so that I can refine this query. Using exists() and scalar(), I am turning this query into an EXISTS query, and I’m assigning the output (1 or 0) to id.

The other three methods are related to the “session version control” I mentioned before. The version control analogy breaks down fairly quickly, since you can’t check out old commits or branch, but I found it to be a useful analogy for distinguishing the different session methods. If I run session.add(myrestaurant), I am creating the SQL to add the object myrestaurant (an instance of class Restaurant) to my table. SQLalchemy stores this as a pending statment, but does not run it yet. I think of this like staging changes using git add. This is nice because you can update the SQL if you need to, or you can get rid of pending changes using session.rollback(). Note that these changes are pending on the SQLalchemy side; your database of choice has not seen your SQL at all.

Flushing a session sends all pending changes to the database, but does not run them. This means that the changes are still pending, but are now pending on the database side. It’s not exactly equivalent, but I consider this similar to git commit, where you are submitting changes, but others on the project can’t see the effects yet. You can flush a session yourself using session.flush().

Finally, committing a session is similar to a git push, in that it updates the database to reflect all of the changes you’ve made on the SQLalchemy side. When you use session.commit(), your SQL commands are finally run on the database side. This means that your changes will be permanently made. One thing to note is that committing automatically flushes any pending changes, so you may not actually need to flush changes yourself.

It’s not necessarily intuitive to know when to flush or commit a session. My goal is to try to commit sensible chunks at once: if I’m inserting a bunch of lines, to send one commit for those; if I’m deleting lines and then querying the database, to explicitly commit before the query. If you query a session, it will flush anything pending first, but I find that it’s easier to understand what you are querying if you make it explicit in your code.

In Part 2, I’ll discuss how to set up classes for working with your tables, and how to create, add to, and query these tables using the architecture you’ve set up.