19.3 Understanding SQLAlchemy ORM

Section 3 of 10-~ 12 min read-Synced from Cuantum content

SQLAlchemy is a comprehensive and powerful toolkit that offers a wide range of enterprise-level persistence patterns, designed to enable efficient and high-performing database access. It provides a simple and Pythonic domain language that is easy to use and understand.

The concept of Object Relational Mapping, or ORM, is a technique that allows for the connection of the rich objects of an application to tables in a relational database management system. By using ORM, the properties and relationships of the objects in an application can be easily stored and retrieved from a database without the need to write SQL statements directly, thereby reducing the overall database access code.

In addition, the ORM in SQLAlchemy is constructed on top of the Core, providing a full suite of mapping capabilities between Python classes and relational databases. This means that SQLAlchemy provides a flexible and comprehensive approach to database access, which can be tailored to suit the specific needs of your application.

Example:

Let's start with a simple example of creating a SQLAlchemy Session, which is the main object used to interact with an ORM-mapped database:

from sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmaker engine = create_engine('postgresql+psycopg2://myuser:mypassword@localhost:5432/mydatabase') Session = sessionmaker(bind=engine) session = Session()

Here we first create an engine that knows how to connect to the database, then define a Session class that will serve as a factory for new Session instances, and finally create a session that we can use to talk to the database.

This session is a handle to the database, similar to a cursor in a traditional database API, but with many more features. You can use it to query the database, modify the database, and transactionally persist changes to the database.

Now that we have a session, we can use it to execute SQL queries. But before we can do that, we need to define our data models.

from sqlalchemy import Column, Integer, Stringfrom sqlalchemy.ext.declarative import declarative_base 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 "<User(name='%s', fullname='%s', nickname='%s')>" % (                            self.name, self.fullname, self.nickname)

In this code, we define a User class that includes fields for an id, name, fullname, and nickname. The class uses SQLAlchemy's Declarative system, which provides a convenient way to declare schemas and models in a single class declaration.

In the next section, we will look at how to use these models to perform database operations using the SQLAlchemy ORM.