Search This Blog

Using ORM (Object-Relational Mapping) with SQLAlchemy

 

Using ORM (Object-Relational Mapping) with SQLAlchemy

Object-Relational Mapping (ORM) is a programming technique that allows you to interact with a relational database using object-oriented concepts. In Python, one of the most popular ORM libraries is SQLAlchemy. SQLAlchemy provides both a low-level SQL expression language and a high-level ORM to interact with databases more easily.

In this tutorial, we'll walk through how to set up and use SQLAlchemy for performing CRUD operations on a database using ORM.

1. Installing SQLAlchemy

To begin using SQLAlchemy, you need to install the library. You can do this with pip:

pip install sqlalchemy

If you plan to use MySQL or PostgreSQL, you’ll also need the respective database drivers:

  • For MySQL: pip install mysql-connector-python
  • For PostgreSQL: pip install psycopg2

For this example, we’ll use SQLite as the database.

2. Setting Up SQLAlchemy

SQLAlchemy’s ORM requires two key components:

  • Engine: Responsible for managing the connection to the database.
  • Session: Used to interact with the database, handling transactions and queries.
  • Declarative Base: Used to define classes that map to tables in the database.

2.1 Setting Up the Database and Model

Let’s define the User model and set up the SQLite database connection using SQLAlchemy.

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

# Define the base class for the model
Base = declarative_base()

# Define the User class (mapped to a table in the database)
class User(Base):
    __tablename__ = 'users'  # Table name in the database

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

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

# Create an SQLite database engine
engine = create_engine('sqlite:///example.db', echo=True)

# Create all tables (if they don't exist already)
Base.metadata.create_all(engine)

In the code above:

  • Base is used to define the User class, which will be mapped to the users table in the database.
  • create_engine connects to the SQLite database. sqlite:///example.db creates a new SQLite file-based database (example.db).
  • Base.metadata.create_all(engine) ensures that the table is created if it doesn't already exist.

2.2 Creating a Session

A session is used to interact with the database. It’s responsible for persisting and querying objects.

# Create a session maker that will establish a session to the database
Session = sessionmaker(bind=engine)
session = Session()

3. Performing CRUD Operations Using SQLAlchemy ORM

Now, let’s look at how to perform CRUD operations using the User class.

3.1 Create: Adding Data to the Database

To add data, you create an instance of the User class and then add it to the session.

# Create a new User object
new_user = User(name="Alice", age=30)

# Add the object to the session
session.add(new_user)

# Commit the transaction to persist the data to the database
session.commit()

In the above code:

  • We create a User object and set its name and age attributes.
  • session.add() adds the object to the session, but it isn't saved until session.commit() is called.

3.2 Read: Querying Data from the Database

You can query the database to fetch data using the session.query() method.

# Query all users in the database
users = session.query(User).all()

# Print each user's details
for user in users:
    print(user)

You can also filter results, sort them, or limit the number of results returned.

# Query for users older than 25
users_over_25 = session.query(User).filter(User.age > 25).all()

# Print the filtered users
for user in users_over_25:
    print(user)

3.3 Update: Modifying Existing Data

To update a record, first retrieve the object from the database, modify its attributes, and then commit the changes.

# Retrieve a user by name
user_to_update = session.query(User).filter(User.name == "Alice").first()

# Update the user's age
if user_to_update:
    user_to_update.age = 31
    session.commit()  # Commit the transaction

In the above code:

  • We use session.query().filter() to find the user with the name "Alice."
  • We modify the age attribute and call session.commit() to save the changes.

3.4 Delete: Removing Data from the Database

To delete a record, retrieve the object, and then call session.delete() to mark it for deletion.

# Retrieve a user to delete
user_to_delete = session.query(User).filter(User.name == "Alice").first()

# Delete the user
if user_to_delete:
    session.delete(user_to_delete)
    session.commit()  # Commit the transaction

After calling session.delete(), you need to call session.commit() to finalize the deletion.

4. Handling Transactions

SQLAlchemy automatically wraps operations in a transaction. However, you can manually control transactions using session.begin() and session.rollback().

# Manually start a transaction
session.begin()

try:
    # Create a new user
    new_user = User(name="Bob", age=22)
    session.add(new_user)

    # Commit the transaction
    session.commit()

except Exception as e:
    # Rollback in case of an error
    session.rollback()
    print(f"Error occurred: {e}")

In this example, if an error occurs, the transaction is rolled back, ensuring that no partial data is committed.

5. Closing the Session

Always close the session after completing your database operations to free resources.

session.close()

6. Using Relationships (Optional)

SQLAlchemy also supports defining relationships between tables (one-to-many, many-to-many). Here’s an example where a Post model has a one-to-many relationship with a User model.

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Post(Base):
    __tablename__ = 'posts'

    id = Column(Integer, primary_key=True)
    title = Column(String)
    content = Column(String)
    user_id = Column(Integer, ForeignKey('users.id'))

    # Define the relationship to the User model
    user = relationship("User", back_populates="posts")

User.posts = relationship("Post", order_by=Post.id, back_populates="user")

In this case, a User can have multiple Post objects associated with it. You can add a post to a user and query them using SQLAlchemy’s ORM capabilities.

7. Summary

In this tutorial, we learned how to use SQLAlchemy ORM to interact with a database:

  • We set up a connection to a SQLite database and defined a User model.
  • We performed basic CRUD operations: creating, reading, updating, and deleting records.
  • We handled transactions and used error handling to ensure the integrity of the database.
  • Finally, we learned about relationships between models and how to define them in SQLAlchemy.

SQLAlchemy makes it easy to interact with databases using object-oriented programming, and its ORM capabilities significantly reduce the amount of boilerplate code required for database operations.

Subscribe to: Posts (Atom)

Popular Posts