A quick overview of SQLAlchemy

Here at 4Degrees, we often have new interns and employees coming on board with lots of questions. Most of those questions can be answered with a bit of research or a quick chat. But the questions about SQLAlchemy have proven particularly difficult to answer quickly or research independently. So we’ve put together a quick guide to how we use it!

What is SQLAlchemy?

For our main web application, we use the Python Flask web framework. Flask doesn’t necessitate the use of SQLAlchemy, but the two are closely linked and we have decided to adopt it.

SQLAlchemy is the abstraction or interface layer between our web application and our database. As with most database abstraction layers, SQLAlchemy is an ORM (more on that in a second).

Using SQLAlchemy means that we never write SQL directly. SQLAlchemy introduces a new syntax that takes the place of “raw” SQL.

The main reason we use SQLAlchemy is that by using it instead of raw SQL we fully protect the 4Degrees web app from SQL injection attacks.

What is an ORM?

ORM stands for object-relational mapping. An ORM is a package or library that abstracts database calls into an object-oriented programming construct. In our case, that means that each database table becomes an object with a set of database methods attached to it (for SELECT, INSERT, DELETE, etc.).

The primary reason people build and use ORMs is to bring database interaction code back to the “native” language you’re writing in. In our case, we write database interaction in Python rather than in SQL.

At 4Degrees, we do not care about this “native is better” argument. You will really struggle to use SQLAlchemy if you don’t understand the underlying SQL. We instead use SQLAlchemy primarily for its SQL injection protection.

Example of how to query

# Print out user 1’s contact names
contactList = contact.query.filter(contact.user_id == 1)
for eachContact in contactList:
    print(eachContact.full_name)
# Print the names, sorted by added date
contactList2 = contactList.order_by(contact.time)
for eachContact in contactList2:
    print(eachContact.full_name)
# Change contactList from a db iterable into a real list
contactList = contactList.all()

See above for a basic example on how to get results out of a table and iterate over them.

The first query just gets all the contacts with a user_id of 1.

The second query builds on the first query (and re-runs it) by sorting the results by the time attribute.

The third query (which runs again) gets the results from the database and saves them in a Python list. This is never necessary, but can have substantial performance implications if it means you can prevent multiple queries to the database.

Example of how to insert

# contact model: contact(full_name, user_id)
newContact = contact('Ablorde Ashigbi', 1)
db.session.add(newContact)
db.session.commit()

In proper OOP fashion, you insert a new record by instantiating the class. The instantiation must then be added to the current database session and committed. The actual communication with the database doesn’t happen until the session is committed.

Defining a model

class contact(db.Model):
    id = db.Column('id', db.Integer, primary_key=True)
    time = db.Column('time', db.Integer, default=time.time())
    user_id = db.Column(db.Integer, db.ForeignKey('user.id', ondelete='CASCADE'))
    associated_user = db.relationship('user', lazy='select')
    def __init__(self, full_name, user_id):
        self.full_name = full_name
        self.user_id = user_id

This is where you define the contact table and how SQLAlchemy should create a “contact” class to represent it. Note that the __init__ method corresponds to the constructor you use up in the insert example.

Example of how to update

theContact = contact.query.filter(contact.id == 1).first()
if theContact is not None:
    theContact.full_name = 'Ablorde (Awesome) Ashigbi'
    db.session.commit()

Get the record from the database and then just edit it, making sure to save/commit your session. Not the most efficient, but pretty intuitive.

Example of how to delete

theContact = contact.query.filter(contact.id == 1).first()
if theContact is not None:
    db.session.delete(theContact)
    db.session.commit()

Same deal: not the most efficient, but pretty intuitive.