Mastering SQLAlchemy with Python: A Comprehensive Guide to Database Interaction

Someone

4 min read

·

Nov 20, 2024

--

Listen

Share

In this blog, we will explore how to use SQLAlchemy to interact with an SQLite database. This powerful Python library allows us to map database tables to Python classes, making it easier to perform operations like querying, updating, and deleting data.

from sqlalchemy import create_engine

engine = create_engine('sqlite:///Database/example_alchemy.db', echo=True)

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

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

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

    def __repr__(self):
        return f"<User(name='{self.name}', email='{self.age}')>"

Base.metadata.create_all(engine)


from sqlalchemy.orm import sessionmaker

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

user = User(name='Someone', age=100)
session.add(user)
session.commit()

user = User(name='Someone Else', age=84)
session.add(user)
session.commit()


users = session.query(User).all()
for user in users:
    print(user)

user = session.query(User).filter_by(id=1).first()
print(user)


user1 = session.query(User).filter_by(name="Someone").first()
user1.age = 101
session.commit()

session.delete(session.query(User).filter_by(name="Someone Else").first())
session.commit()


users = session.query(User).all()
for user in users:
    print(user)

session.close()

We’ll walk through a step-by-step explanation of the code, with each section clearly defined and its function explained in detail.

Importing Necessary Libraries

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

In the initial step, we import the necessary modules from SQLAlchemy:

• create_engine is used to establish a connection to the database.
• Column, Integer, String are the tools to define the columns in our database tables.
• declarative_base helps us create a base class for defining our ORM (Object Relational Mapping) models.

This setup is essential before we can create our database tables and perform any operations.

Creating the Database Engine

engine = create_engine('sqlite:///Database/example_alchemy.db', echo=True)

• ’sqlite:///Database/example_alchemy.db’ is the URL pointing to our database file. If the file doesn’t exist, SQLAlchemy will automatically create it.
• echo=True enables SQLAlchemy logging, so we can see the raw SQL queries generated by SQLAlchemy. This helps in debugging and understanding how SQLAlchemy interacts with the database.

Defining the Base Class for Models

Base = declarative_base()

• declarative_base() is used to create a base class for all our database models. Every model we define in this program will inherit from Base. This allows SQLAlchemy to automatically map our Python classes to corresponding tables in the database.

With this base class in place, we can now define our models (tables) and their relationships.

Defining the `User` Class

class User(Base):
  __tablename__ = 'users'

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

  def __repr__(self):
    return f"<User(name='{self.name}', email='{self.age}')>"

In this section, we define a User class which will represent the users table in our database:

• __tablename__ = ‘users’ tells SQLAlchemy that this class corresponds to the users table.
• We define three columns:
  — id is an integer and serves as the primary key.
  — name is a string that stores the name of the user.
  — age is an integer that stores the user’s age.
  
  The __repr__ method defines how a User object is represented as a string. This helps when we print instances of the User class.

Creating the Database Tables

Base.metadata.create_all(engine)

After defining the User class, we use Base.metadata.create_all(engine) to create the tables in the database. This will automatically generate the users table in the SQLite database, corresponding to our User class.

Setting Up the Session

from sqlalchemy.orm import sessionmaker

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

• We import sessionmaker from sqlalchemy.orm to create a session.
• The session is the gateway to interact with the database. It allows us to add, commit, query, and delete data from the database.
• Session() creates a session instance bound to the engine.

The session is the object we will use throughout the program to interact with the database.

Adding Data to the Database

user = User(name='Someone', age=100)

session.add(user)
session.commit()

In this part, we create a new User object and add it to the session:

• We create a User object with name=’Someone’ and age=100.
• session.add(user) adds this user object to the session.
• session.commit() commits the transaction, saving the new user in the database.

Similarly, we add another user to the database:

user = User(name='Someone Else', age=84)

session.add(user)
session.commit()

Both users are now stored in the users table of the database.

Querying All Users from the Database

users = session.query(User).all()
for user in users:
  print(user)

Here, we retrieve all users from the database using session.query(User).all(). This query returns a list of all User objects in the users table.

• We then iterate over the users list and print each user object.
• The __repr__ method defined earlier determines how the User objects are printed.

Output:

<User(name='Someone', email='100')>
<User(name='Someone Else', email='84')>

Querying a Specific User by ID

user = session.query(User).filter_by(id=1).first()
print(user)

In this section, we query the users table for the user with id=1:

• filter_by(id=1) filters the results by the id column.
• .first() returns the first result matching the filter, or None if no result is found.

Output:

<User(name='Someone', email='100')>

Updating a User’s Data

user1 = session.query(User).filter_by(name="Someone").first()
user1.age = 101

session.commit()

• We query the database for the user named “Someone” and retrieve the first result.
• Then, we update the user’s age to 101.
• Finally, we commit the changes to the database.

This is how we modify an existing record in the database.

Deleting a User

session.delete(session.query(User).filter_by(name="Someone Else").first())
session.commit()

To delete a user, we:

• Query the users table to find the user named “Someone Else”.
• Use session.delete() to remove this user from the session.
• session.commit() saves the changes and removes the user from the database.

Verifying the Changes

users = session.query(User).all()
for user in users:
  print(user)

Finally, we query the database again to print all users after making the changes (adding, updating, and deleting).

Output:

<User(name='Someone', email='101')>

As you can see, the user named “Someone Else” has been removed from the database, and the user named “Someone” now has an updated age.

Closing the Session

session.close()

Once all database operations are complete, it’s a good practice to close the session. This releases any resources held by the session.

Conclusion

This program demonstrates how to use SQLAlchemy to interact with a SQLite database. We’ve covered the basics, from connecting to the database to adding, querying, updating, and deleting records. SQLAlchemy provides a high-level API that makes it easy to perform complex database operations in Python with minimal boilerplate code.

By mastering these fundamental operations, you can start building more advanced applications that rely on relational databases.

You can find the complete code on my Github and follow my journey on Twitter for more updates and learning content.