SQLAchemy SQLite: Complete Guide to CRUD

Welcome! Whether you’re new to database programming or a seasoned pro, this guide will walk you through using SQLAlchemy to perform CRUD (Create, Read, Update, Delete) operations on an SQLite database using Python.

Why SQLAlchemy?

SQLAlchemy is the leading ORM (Object Relational Mapper) for Python. It allows you to map Python classes and objects to database tables and rows to streamline database interactions. The key benefits SQLAlchemy provides include:

• Simplified SQL and database interaction from Python
• Abstraction of vendor-specific SQL dialects
• Powerful querying capabilities and expression language
• Connection pooling and transaction management
• And more!

By using SQLAlchemy, you don’t have to write raw SQL. The ORM translates your Python code into the appropriate SQL behind the scenes. This makes it easier to work with relational databases in Python without needing to be an SQL expert.

Getting Started

To follow along, you’ll need:

• Python 3
• SQLAlchemy installed (pip install sqlalchemy)
• Access to a SQLite database (we’ll use in-memory SQLite for the examples)

I’ll also assume basic knowledge of Python and SQL databases. If you need a refresher, here’s a quick primer on SQLite data types to understand the examples.

Let’s dive in and see how SQLAlchemy makes CRUD so much easier!
Got it, here is an added installation section:

Installing Python SQLAlchemy

Before using SQLAlchemy, you’ll need to install it along with a Python DB API-compatible database driver.

For SQLite, first install SQLAlchemy:

pip3 install sqlalchemy

Then install the SQLite driver:

pip3 install sqlalchemy.dialects.sqlite

That’s it! These two packages are all you need to start using SQLAlchemy with SQLite.

The same overall process applies when installing SQLAlchemy for use with other databases like PostgreSQL, MySQL, SQL Server, and more. Just install the required dialect after the base sqlalchemy package.

Now SQLAlchemy is ready to import and use for database access and ORM mappings!

import sqlalchemy
from sqlalchemy import create_engine
# ...

Creating a Table with SQLAlchemy

We’ll start by creating a basic users table to demonstrate the CRUD operations. Here’s the code:

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

engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    nickname = Column(String)
    
    def __repr__(self):
        return f"<User(name={self.name}, fullname={self.fullname}>"
        
Base.metadata.create_all(bind=engine)

This creates a SQLite database in memory, defines a User table with columns for id, name, fullname, and nickname, and uses SQLAlchemy’s declarative_base() system to map it to a Python User class.

The key things to note:

• The User class subclasses declarative_base() to become a mapped ORM class
• Each column maps to a SQLAlchemy Column with a data type
• We can define custom methods like __repr__ to change the display
• Calling create_all() builds the table in the database

And that’s it! The table is now ready for us to use for the CRUD examples.

INSERT – Creating New Rows with SQLAlchemy

To insert new rows into the users table, we start by creating User objects and adding them to the session:

from sqlalchemy.orm import sessionmaker

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

# Create objects
user1 = User(name='John', fullname='John Williams', nickname='johnny')  
user2 = User(name='Susan', fullname='Susan Smith', nickname='suzy')

# Add to session 
session.add(user1) 
session.add(user2)

# Commit (required to write changes to database)
session.commit()  
print(user1.id, user2.id)

This follows the basic SQLAlchemy workflow:

1. Create a Sessionmaker to produce session instances
2. Instantiate User objects and populate their attributes
3. Add the objects to the session
4. Commit to write the INSERT statements

After committing, the id columns will be auto-generated primary key values from SQLite. Easy!

Of course, we can wrap this in a function to reuse the insert logic:

def insert_user(session, name, fullname, nickname):
    user = User(name=name, fullname=fullname, nickname=nickname)
    session.add(user)
    session.commit()
    return user

john = insert_user(session, 'John', 'John Williams', 'johnny')
print(john.id) 

And there you have it – a reusable way to insert rows into the users table!

READ – Querying for Results with SQLAlchemy

SQLAlchemy provides extensive capabilities for querying database tables and retrieving results. Let’s run some basic queries on the users table:

All Rows

To get all rows, just query the User model:

users = session.query(User).all()
print(users)

This prints:

[<User(name=John, fullname=John Williams)>, <User(name=Susan, fullname=Susan Smith)>]

By Primary Key

Grab a single user by primary key using get():

user = session.query(User).get(1) 
print(user)

Prints:

<User(name=John, fullname=John Williams)>

By Field Value

Filter by any column using SQLAlchemy’s flexible filtering system:

john = session.query(User).filter_by(name='John').first()
print(john)  

Which prints:

<User(name=John, fullname=John Williams)> 

We can filter across multiple columns, aggregate fields, join tables, sort result sets and much more. SQLAlchemy provides filter options for nearly any query need.

UPDATE – Modifying Rows with SQLAlchemy

Updating rows follows a similar workflow to inserting:

1. Retrieve the object by primary key
2. Modify attributes
3. Commit to write updates

For example:

user = session.query(User).get(1)
user.nickname = 'JJ'
session.commit() 
print(user.nickname)

This prints:

JJ

Just by updating the Python object attribute, SQLAlchemy handles translating that change into an UPDATE statement behind the scenes on commit.

For extra reusability, we can add a custom update method to our User class:

class User(Base):
  #...

  def update(self, name=None, fullname=None, nickname=None):
      if name:
          self.name = name
      if fullname:
          self.fullname = fullname
      if nickname:
          self.nickname = nickname
      session.commit()
          
user = session.query(User).get(1)
user.update(nickname='John John')
print(user.nickname)

And we’ve built an update helper that abstracts away the save/commit logic!

DELETE – Removing Rows with SQLAlchemy

Finally, deleting rows uses the same pattern of fetching objects and removing:

user = session.query(User).get(1)  
session.delete(user)
session.commit()

This issues a DELETE statement for that particular object’s row.

And that covers the core CRUD operations with SQLAlchemy!

Wrap Up

I hope this tutorial gave you a great overview of how easy it is to implement full CRUD functionality with SQLAlchemy and SQLite. Here’s a quick recap:

• Create – session.add(), insert()
• Read- session.query(), get(), filters
• Update – Modify attributes and commit()
• Delete – session.delete() and commit()

With just these basic concepts, you can build rich Python applications on top of SQLite or any other SQL database supported by SQLAlchemy.

The key takeaways:

• SQLAlchemy provides a Python ORM layer to abstract SQL
• Database tables map to Python classes
• Insert, query, update and delete operations map to intuitive object operations
• Changes only write on commit()

To dive deeper, be sure to check out SQLAlchemy’s excellent documentation, along with the rest of my site for more SQL and data science tutorials!