Using SQLAlchemy ​

A database table is a two-dimensional table with multiple rows and columns. To represent the contents of a table in Python, a list can be used to represent multiple rows, where each element in the list is a tuple representing a row. For example, the user table containing id and name can be represented as:

[
    ('1', 'Michael'),
    ('2', 'Bob'),
    ('3', 'Adam')
]

The data structure returned by Python's DB-API looks like this.

However, using tuple to represent a row makes it hard to understand the structure of the table. If a tuple is represented as an instance of a class, the table structure becomes clearer:

class User(object):
    def __init__(self, id, name):
        self.id = id
        self.name = name

[
    User('1', 'Michael'),
    User('2', 'Bob'),
    User('3', 'Adam')
]

This is known as ORM (Object-Relational Mapping), which maps the structure of a relational database table to objects. Isn't it simple?

But who handles this conversion? This is where ORM frameworks come in.

In Python, the most popular ORM framework is SQLAlchemy. Let's explore how to use SQLAlchemy.

Installing SQLAlchemy ​

First, install SQLAlchemy using pip:

$ pip install sqlalchemy

Then, let's use SQLAlchemy with the user table we created in the MySQL test database:

Step 1: Import SQLAlchemy and Initialize DBSession ​

# Import:
from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import declarative_base

# Create the base class for objects:
Base = declarative_base()

# Define the User class:
class User(Base):
    # Table name:
    __tablename__ = 'user'

    # Table structure:
    id = Column(String(20), primary_key=True)
    name = Column(String(20))

# Initialize the database connection:
engine = create_engine('mysql+mysqlconnector://root:password@localhost:3306/test')
# Create the DBSession type:
DBSession = sessionmaker(bind=engine)

The code above completes the SQLAlchemy initialization and defines the class for each table. If there are multiple tables, continue to define other classes, such as School:

class School(Base):
    __tablename__ = 'school'
    id = ...
    name = ...

The create_engine() function is used to initialize the database connection. SQLAlchemy uses a connection string in the following format:

'database_type+driver_name://username:password@host:port/database_name'

Simply replace username, password, and other details as needed.

Step 2: Adding a Record to the Database Table ​

With ORM, adding a record to the database table can be seen as adding a User object:

# Create a session object:
session = DBSession()
# Create a new User object:
new_user = User(id='5', name='Bob')
# Add the object to the session:
session.add(new_user)
# Commit to save to the database:
session.commit()
# Close the session:
session.close()

The key steps are obtaining a session, adding the object to the session, and finally committing and closing. The DBSession object can be considered as the current database connection.

Step 3: Querying Data from the Database ​

With ORM, the query results can be User objects instead of tuple. SQLAlchemy provides the following query interface:

# Create a Session:
session = DBSession()
# Create a Query, with filter as the WHERE condition. Use one() to return a single row or all() to return all rows:
user = session.query(User).filter(User.id == '5').one()
# Print the type and name attribute of the object:
print('type:', type(user))
print('name:', user.name)
# Close the Session:
session.close()

The output will be:

type: <class '__main__.User'>
name: Bob

As shown, ORM establishes the association between rows of the database table and the corresponding objects, enabling conversion between the two.

Step 4: Establishing One-to-Many and Many-to-Many Relationships ​

Since relational databases can implement one-to-many and many-to-many relationships using foreign keys, ORM frameworks can also provide functionalities for one-to-many, many-to-many relationships between objects.

For example, if a User owns multiple Book instances, a one-to-many relationship can be defined as follows:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class User(Base):
    __tablename__ = 'user'

    id = Column(String(20), primary_key=True)
    name = Column(String(20))
    # One-to-many:
    books = relationship('Book')

class Book(Base):
    __tablename__ = 'book'

    id = Column(String(20), primary_key=True)
    name = Column(String(20))
    # The "many" side of the relationship with book table linked to user table through a foreign key:
    user_id = Column(String(20), ForeignKey('user.id'))

When querying a User object, the books attribute will return a list containing multiple Book objects.

Summary ​

An ORM framework serves to automatically convert between a row in a database table and an object.
Properly using ORM requires understanding the principles of relational databases.