SQLAlchemy — why use it?

“I love manually writing SQL commands for every possible database query!”

— No One, Ever

I spent maybe a week learning about SQL and its native commands near the beginning of phase 3, and that was already plenty! Enter the Python library that would spare at least this programmer a whole lot of misery…

What is SQLAlchemy?

SQLAlchemy is an expansive ORM library for interacting with SQL databases in Python. Within, you’ll find methods for reading, adding, removing, and editing data (ie. the all-important CRUD operations), all without having to write a single SQL command. Hallelujah!

Installation and Setup

Step by step, here’s how one installs SQLAlchemy and uses its capabilities to define and persist a schema. (This section is for my personal reference as much as for any reader.)

While in your working directory, run this command in the terminal:

pip install sqlalchemy
#or alternately, for a virtual environment
pipenv install sqlalchemy

Then, in a fresh .py file, add the necessary import statements at the top:

from sqlalchemy import Column, String, Integer, create_engine #any data types you'll need
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Then, in the body of the .py file:

Base = declarative_base()

class Data(Base):
  __tablename__ = 'data'

  id = Column(Integer(), primary_key=True)
  name = Column(String())

  #for printing any instance of the class
  def __repr__(self):
    return f'{self.id} - {self.name}'

This class defines the structure of the database table — its columns and corresponding data types. There must always be an id column with a unique id as the primary key; this will ensure that every row in the database is unique even if the rest of its data is not. Base is passed into the class as a parameter, and will collect the class’s various attributes (metadata) so that an SQL table can be generated.

This is the code that must be run in order to create the database and interact with it. Type these commands into a Python shell in the terminal (after importing the .py file), or turn the .py file itself into an executable and run it:

engine = create_engine('sqlite:///database.db')
Base.metadata.create_all(engine) #command that creates the .db file; unnecessary after running once

Session = sessionmaker(bind=engine)
session = Session()

Here’s what the complete code looks like.

#!/usr/bin/env python3

from sqlalchemy import Column, String, Integer, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Data(Base):
  __tablename__ = 'data'

  id = Column(Integer(), primary_key=True)
  name = Column(String())

  def __repr__(self):
      return f'{self.id} - {self.name}'

#code to be run
if __name__ == '__main__':
    engine = create_engine('sqlite:///database.db')
    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    session = Session()

#database commands here

If all goes well, there should now be a .db file in your directory. And now… time for Session() to shine!

Creating and Modifying Data

Data entries are added to a database via objects. In this case, instances of the Data class would be translated into rows on the SQL table.

#primary key ID is automatically assigned
entry_1 = Data(name="Steve") #creates an instance of Data with 'name' value passed in
entry_2 = Data(name="Sharon")
 
session.add(entry_1) #creates row in database by passing in created object
session.add(entry_2)

session.commit() #populates all previous changes into .db file
session.close() #ends session

There should now be two new rows of data in your table. To confirm for yourself, Session() can also do that as long as a __repr__ method is defined in the table class.

all_data = session.query(Data).all() #retrieves all data rows as a list

for row in all_data:
  print(row)

#=> 1 - Steve
#   2 - Sharon

Want to delete a particular row? No prob, Bob!

row_to_delete = session.query(Data).filter_by(id=1).first() #this is why a unique id is important!

session.delete(row_to_delete)

session.commit()
session.close()

As with the .all() method, the .first() method does exactly what it appears to do. Rather than retrieve all data entries, it will only retrieve the first one that satisfies the .filter_by() parameter. Speaking of filtering…

Querying Data

What’s made SQLAlchemy shine for me so far are its powerful, intuitive, and customizable methods for retrieving data. Filter() takes in a Boolean expression that returns all rows that return True, while filter_by() returns all rows whose specified attribute matches the passed-in value.

selected_rows = session.query(Data).filter("S" in name).all()
print(selected_rows) 
#=> [1 - Steve, 2 - Sharon]

selected_row = session.query(Data).filter_by(name='Sharon').first()
#=> 2 - Sharon

So what?

Obviously this is just the tip of the iceberg, but these SQLAlchemy basics alone will take one’s project decently far. My own phase 3 final project, the Expense Tracker, is testament to that!

A Python-based CLI application for tracking and editing personal expenses

Some real code blocks from my project that may look familiar…

#encapsulated into a function w/ session and data class passed in as parameters
def retrieve_all(session, expense):
    return session.query(expense).order_by(expense.date).all() #order_by() sorts returned list

#user selection passed in to filter method to display custom results
filtered_results = session.query(expense).filter(expense.category_id == selection).order_by(expense.date).all()
for item in filtered_results:
    print(item)

#edit a database entry after it's selected via passed-in id
def edit(session, expense, id):
    selected_expense = session.query(expense).filter_by(id=id).first()
    values = validate_input() #input validation function returns list of valid values
    #session.query() returns object, therefore dot notation can be used to edit values
    selected_expense.title = values[0]
    selected_expense.amount = values[1]
    selected_expense.category_id = values[2]
    selected_expense.date = values[3]
    session.commit() #always remember to commit!

In terms of efficiency, it’s far preferable to relegate as much computation as possible to the back-end (ie. these SQL queries) rather than front-load it all to Python. I could have retrieved all the results every time the filter() function was called and used list comprehensions or ‘if’ statements to sort through them, but that would have been an unnecessary memory burden. Far neater and more efficient to get only what you need!