Python and SQLAlchemy: Understanding the ORM

Table of Contents

  1. Introduction
  2. Prerequisites
  3. Installation
  4. Creating a SQLAlchemy Model
  5. Querying the Database
  6. Updating Records
  7. Deleting Records
  8. Conclusion

Introduction

In this tutorial, we will explore how to use SQLAlchemy’s Object-Relational Mapping (ORM) in Python. SQLAlchemy is a popular library for working with databases in Python, and the ORM provides a high-level interface to interact with the database using Python classes and objects. By the end of this tutorial, you will understand how to create and manipulate database records using SQLAlchemy’s ORM.

Prerequisites

To follow this tutorial, you should have a basic understanding of Python programming. Familiarity with databases and SQL would also be beneficial but is not required.

Installation

Before we start, we need to install SQLAlchemy. Open your terminal and run the following command: pip install SQLAlchemy If you’re using a virtual environment, make sure it is activated before running the command.

Creating a SQLAlchemy Model

To begin, let’s create a simple model representing a User table in a hypothetical database. Open a new Python file and import sqlalchemy: ```python 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(50))
    email = Column(String(50))
``` In the above code, we define a `User` class that inherits from `declarative_base()`, which is a base class provided by SQLAlchemy to create our model classes. We also specify the table name as `'users'` using the `__tablename__` attribute.

The id, name, and email fields are defined as Column objects. We specify the data types (Integer and String) and other optional attributes, such as primary_key=True for the id column.

Querying the Database

Once we have defined our model, we can use it to query the database. Let’s see how to fetch all users from the users table: ```python from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///database.db')
Session = sessionmaker(bind=engine)
session = Session()

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

for user in users:
    print(user.name, user.email)
``` In the above code, we create an engine using `create_engine` function, passing the database connection URL as an argument. If you're using a different database, replace `'sqlite:///database.db'` with the appropriate connection URL.

Next, we create a Session class using sessionmaker and bind it to the engine. Then, we create a session object by calling Session(). A session represents a transactional scope and is responsible for managing the operations on our database.

To fetch all users from the users table, we use session.query(User). This returns a query object representing a SELECT statement. We can call all() on the query to execute it and retrieve all matching records.

Finally, we iterate over the users list and print the name and email of each user.

Updating Records

To update a record, we can modify the corresponding object and commit the changes to the database: ```python user = session.query(User).first() user.email = ‘[email protected]’

session.commit()
``` In the above code, we fetch the first user from the `users` table using `session.query(User).first()`. We then update the `email` attribute of the user object.

To persist the changes, we call session.commit(), which flushes all pending changes to the database.

Deleting Records

Deleting records is as simple as querying the object and calling session.delete(): ```python user = session.query(User).filter_by(name=’John’).first() session.delete(user)

session.commit()
``` In the above code, we query the `User` object with `name='John'` using `filter_by()` and retrieve the first matching record. We then delete the user object by calling `session.delete()`. Finally, we commit the changes to the database using `session.commit()`.

Conclusion

In this tutorial, we explored SQLAlchemy’s ORM and learned how to create and manipulate database records using Python classes and objects. We covered creating a SQLAlchemy model, querying the database, updating records, and deleting records. SQLAlchemy provides a powerful and intuitive way to work with databases in Python, making it a valuable tool for web development and data-driven applications.

Published:

Related Articles