Introduction

The SQLAlchemy SQL Toolkit and Object Relational Mapper is a comprehensive set of tools for working with databases and Python. It provides a full suite of well-known enterprise-level persistence patterns, designed for efficient and high-performing database access. SQLAlchemy and Django’s ORM are two of the most widely used object-relational mapping tools in the Python community.

The SQLAlchemy has three ways of working with database data:

• Raw SQL
• SQL Expression language
• ORM

Unlike many other ORM (Object Reational Mapping) tools, SQLAlchemy allows to use pure SQL statements. We can always resort to raw SQL. The SQL Expression API allows you to build SQL queries using Python objects and operators. The Expression API is an abstraction of the pure SQL statements and deals with various implementation differences between databases. The SQLAlchemy Object Relational Mapper presents a method of associating user-defined Python classes with database tables. The SQLAlchemy ORM is based on the SQL Expression language.

SQLAlchemy components

Engine

Engine is the starting point of any SQLAlchemy application. The engine is an abstraction of the database and its API. It works with the connection pool and the Dialect component to deliver the SQL statements from the SQLAlchemy to the database. The engine is created using the create_engine() function. It can be used to directly interact with a database, or can be passed to a Session object to work with the object-relational mapper.

Dialect

Dialect is the system SQLAlchemy uses to communicate with various types of DBAPI implementations and databases. All dialects require that an appropriate DBAPI driver is installed. SQLAlchemy has dialects for many popular database systems including Firebird, Informix, Microsoft SQL Server, MySQL, Oracle, PostgreSQL, SQLite, or Sybase. The Dialect is created from the supplied connection string.

MetaData

MetaData comprises of Python objects that describe tables and other schema-level objects. Database metadata can be expressed by explicitly naming the various components and their properties, using constructs such as Table, Column, or ForeignKey. MetaData can be easily generated by SQLAlchemy using a process called reflection.

Session

Inside the ORM, the primary interface for persistence operations is the Session. The Session establishes all conversations with the database and represents a container for all the objects which we have loaded or associated with it during its lifespan. It provides the entry point to acquire a Query object, which sends queries to the database using the Session object’s current database connection, populating result rows into objects that are then stored in the Session.

SQLAlchemy Raw SQL

SQLAlchemy is not a pure ORM toolkit. It also allows to execute raw SQL statements when needed.

Example:

from sqlalchemy import create_engine

eng = create_engine('sqlite:///:memory:')

with eng.connect() as con:
    
    rs = con.execute('SELECT 5')
        
    data = rs.fetchone()[0]

from sqlalchemy import create_engine
from sqlalchemy.sql import text

eng = create_engine("sqlite:///:memory:")

with eng.connect() as con:

    con.execute(text('DROP TABLE IF EXISTS Cars'))
    con.execute(text('''CREATE TABLE Cars(Id INTEGER PRIMARY KEY, 
                 Name TEXT, Price INTEGER)'''))

    data = ( { "Id": 1, "Name": "Audi", "Price": 52642 },
             { "Id": 2, "Name": "Mercedes", "Price": 57127 },
             { "Id": 3, "Name": "Skoda", "Price": 9000 },
             { "Id": 4, "Name": "Volvo", "Price": 29000 },
             { "Id": 5, "Name": "Bentley", "Price": 350000 },
             { "Id": 6, "Name": "Citroen", "Price": 21000 },
             { "Id": 7, "Name": "Hummer", "Price": 41400 },
             { "Id": 8, "Name": "Volkswagen", "Price": 21600 }
    )
    
    for line in data:
        con.execute(text("""INSERT INTO Cars(Id, Name, Price) 
            VALUES(:Id, :Name, :Price)"""), **line)
    
    rs = con.execute(text('SELECT * FROM Cars'))
    print(rs.keys())

SQLAlchemy Expression Language

https://docs.sqlalchemy.org/en/latest/core/tutorial.html

The SQLAlchemy Expression Language presents a system of representing relational database structures and expressions using Python constructs. These constructs are modeled to resemble those of the underlying database as closely as possible, while providing a modicum of abstraction of the various implementation differences between database backends. While the constructs attempt to represent equivalent concepts between backends with consistent structures, they do not conceal useful concepts that are unique to particular subsets of backends. The Expression Language therefore presents a method of writing backend-neutral SQL expressions, but does not attempt to enforce that expressions are backend-neutral.

The SQLAlchemy Expression Language represents relational database structures and expressions using Python constructs. The expression language improves the maintainability of the code by hiding the SQL language and thus allowing not to mix Python code and SQL code.

create & insert table

from sqlalchemy import (create_engine, Table, Column, Integer, 
    String, MetaData)
from sqlalchemy.sql import select    
    
eng = create_engine('sqlite:///:memory:')

with eng.connect() as con:
    
    meta = MetaData(eng)
    cars = Table('Cars', meta,
         Column('Id', Integer, primary_key=True),
         Column('Name', String),
         Column('Price', Integer)
    )

    cars.create()
    
    ins1 = cars.insert().values(Id=1, Name='Audi', Price=52642)
    con.execute(ins1)

    ins2 = cars.insert().values(Id=2, Name='Mercedes', Price=57127)
    con.execute(ins2)
    
    ins3 = cars.insert().values(Id=3, Name='Skoda', Price=6000)
    con.execute(ins3) 

query table

from sqlalchemy import create_engine, Table, MetaData
from sqlalchemy.sql import select, asc, and_
    
eng = create_engine('sqlite:///test.db')

with eng.connect() as con:

    meta = MetaData(eng)
    cars = Table('Cars', meta, autoload=True)  

    stm = select([cars.c.Name, cars.c.Price]).where(and_(cars.c.Price > 10000, cars.c.Name.like('%en'))).limit(3)..order_by(asc(cars.c.Name))
    rs = con.execute(stm) 

    print(rs.fetchall())

join table

from sqlalchemy import (create_engine, Table, Column, Integer, 
    String, ForeignKey, MetaData)
from sqlalchemy.sql import select    
    
eng = create_engine('sqlite:///test.db')

with eng.connect() as con:
    
    meta = MetaData(eng)
    
    authors = Table('Authors', meta, autoload=True)
    books = Table('Books', meta, autoload=True)

    stm = select([authors.join(books)])
    rs = con.execute(stm) 

    for row in rs:
        print row['Name'], row['Title']

SQLAlchemy Object Relational Mapper

A common task when programming any web service is the construction of a solid database backend. In the past, programmers would write raw SQL statements, pass them to the database engine and parse the returned results as a normal array of records. Nowadays, programmers can write Object-relational mapping (ORM) programs to remove the necessity of writing tedious and error-prone raw SQL statements that are inflexible and hard-to-maintain.

http://zetcode.com/db/sqlalchemy/orm/

https://docs.sqlalchemy.org/en/latest/orm/tutorial.html

The SQLAlchemy Object Relational Mapper presents a method of associating user-defined Python classes with database tables, and instances of those classes (objects) with rows in their corresponding tables. It includes a system that transparently synchronizes all changes in state between objects and their related rows, called a unit of work, as well as a system for expressing database queries in terms of the user defined classes and their defined relationships between each other.

Python Class <-> Database Table Python Class Instance <-> Database Table Row

The ORM is in contrast to the SQLAlchemy Expression Language, upon which the ORM is constructed. Whereas the SQL Expression Language, introduced in SQL Expression Language Tutorial, presents a system of representing the primitive constructs of the relational database directly without opinion, the ORM presents a high level and abstracted pattern of usage, which itself is an example of applied usage of the Expression Language.

A successful application may be constructed using the Object Relational Mapper exclusively. In advanced situations, an application constructed with the ORM may make occasional usage of the Expression Language directly in certain areas where specific database interactions are required.

Connection

For this tutorial we will use an in-memory-only SQLite database. To connect we use create_engine():

>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///:memory:', echo=True)

The echo flag is a shortcut to setting up SQLAlchemy logging, which is accomplished via Python’s standard logging module. With it enabled, we’ll see all the generated SQL produced.

The return value of create_engine() is an instance of Engine, and it represents the core interface to the database, adapted through a dialect that handles the details of the database and DBAPI in use. In this case the SQLite dialect will interpret instructions to the Python built-in sqlite3 module.

Lazy Connecting

The Engine, when first returned by create_engine(), has not actually tried to connect to the database yet; that happens only the first time it is asked to perform a task against the database.

When using the ORM, we typically don’t use the Engine directly once created; instead, it’s used behind the scenes by the ORM as we’ll see shortly.

Declare a Mapping

There are three most important components in writing SQLAlchemy code:

• A Table that represents a table in a database.
• A mapper that maps a Python class to a table in a database.
• A class object that defines how a database record maps to a normal Python object.

Instead of having to write code for Table, mapper and the class object at different places, SQLAlchemy’s declarative allows a Table, a mapper and a class object to be defined at once in one class definition.

Declarative, which allows us to create classes that include directives to describe the actual database table they will be mapped to.

Classes mapped using the Declarative system are defined in terms of a base class which maintains a catalog of classes and tables relative to that base - this is known as the declarative base class. Our application will usually have just one instance of this base in a commonly imported module. We create the base class using the declarative_base() function, as follows:

>>> from sqlalchemy.ext.declarative import declarative_base

>>> Base = declarative_base()

Now that we have a “base”, we can define any number of mapped classes in terms of it. We will start with just a single table called users, which will store records for the end-users using our application.

>>> from sqlalchemy import Column, Integer, String
>>> class User(Base):
...     __tablename__ = 'users'
...
...     id = Column(Integer, primary_key=True)
...     name = Column(String)
...     fullname = Column(String)
...     password = Column(String)
...
...     def __repr__(self):
...        return "<User(name='%s', fullname='%s', password='%s')>" % (
...                             self.name, self.fullname, self.password)

The User class defines a __repr__() method, but note that is optional; we only implement it in this tutorial so that our examples show nicely formatted User objects.

When our class is constructed, Declarative replaces all the Column objects with special Python accessors known as descriptors; this is a process known as instrumentation. The “instrumented” mapped class will provide us with the means to refer to our table in a SQL context as well as to persist and load the values of columns from the database.

the __init__() method

Our User class, as defined using the Declarative system, has been provided with a constructor (e.g. __init__() method) which automatically accepts keyword names that match the columns we’ve mapped. We are free to define any explicit __init__() method we prefer on our class, which will override the default method provided by Declarative.

Create a Schema

With our User class constructed via the Declarative system, we have defined information about our table, known as table metadata. The object used by SQLAlchemy to represent this information for a specific table is called the Table object, and here Declarative has made one for us. We can see this object by inspecting the __table__ attribute

The Table object is a member of a larger collection known as MetaData. When using Declarative, this object is available using the .metadata attribute of our declarative base class.

The MetaData is a registry which includes the ability to emit a limited set of schema generation commands to the database. As our SQLite database does not actually have a users table present, we can use MetaData to issue CREATE TABLE statements to the database for all tables that don’t yet exist. Below, we call the MetaData.create_all() method, passing in our Engine as a source of database connectivity.

>>> Base.metadata.create_all(engine)

Session

We’re now ready to start talking to the database. The ORM’s “handle” to the database is the Session. When we first set up the application, at the same level as our create_engine() statement, we define a Session class which will serve as a factory for new Session objects:

>>> from sqlalchemy.orm import sessionmaker
>>> Session = sessionmaker(bind=engine)

This custom-made Session class will create new Session objects which are bound to our database. Then, whenever you need to have a conversation with the database, you instantiate a Session:

>>> session = Session()

The above Session is associated with our SQLite-enabled Engine, but it hasn’t opened any connections yet. When it’s first used, it retrieves a connection from a pool of connections maintained by the Engine, and holds onto it until we commit all changes and/or close the session object.

http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#session-faq-whentocreate

Adding and Updating Objects

To persist our User object, we add() it to our Session:

>>> ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
>>> session.add(ed_user)

At this point, we say that the instance is pending; no SQL has yet been issued and the object is not yet represented by a row in the database. The Session will issue the SQL to persist Ed Jones as soon as is needed, using a process known as a flush.

Once an object with a particular primary key is present in the Session, all SQL queries on that Session will always return the same Python object for that particular primary key; it also will raise an error if an attempt is made to place a second, already-persisted object with the same primary key within the session.

Which objects have been modified?

session.dirty

Which objects just been created?

session.new

We tell the Session that we’d like to issue all remaining changes to the database and commit the transaction, which has been in progress throughout. We do this via commit().

session.commit()

The connection resources referenced by the session are now returned to the connection pool. Subsequent operations with this session will occur in a new transaction, which will again re-acquire connection resources when first needed.

Rolling Back

Since the Session works within a transaction, we can roll back changes made too

>>> session.rollback()

Querying

Query object

A Query object is created using the query() method on Session. This function takes a variable number of arguments, which can be any combination of classes and class-instrumented descriptors. Below, we indicate a Query which loads User instances. When evaluated in an iterative context, the list of User objects present is returned:

SQL>>> for instance in session.query(User).order_by(User.id):
...     print(instance.name, instance.fullname)

The Query also accepts ORM-instrumented descriptors as arguments. Any time multiple class entities or column-based entities are expressed as arguments to thequery() function, the return result is expressed as tuples:

SQL>>> for name, fullname in session.query(User.name, User.fullname):
...     print(name, fullname)

The tuples returned by Query are named tuples, supplied by the KeyedTuple class, and can be treated much like an ordinary Python object. The names are the same as the attribute’s name for an attribute, and the class name for a class:

SQL>>> for row in session.query(User, User.name).all():
...    print(row.User, row.name)

You can control the names of individual column expressions using the label() construct, which is available from any ColumnElement-derived object, as well as any class attribute which is mapped to one (such as User.name):

SQL>>> for row in session.query(User.name.label('name_label')).all():
...    print(row.name_label)

Basic operations with Query include issuing LIMIT and OFFSET, most conveniently using Python array slices and typically in conjunction with ORDER BY:

SQL>>> for u in session.query(User).order_by(User.id)[1:3]:
...    print(u)

filter(), which uses more flexible SQL expression language constructs. These allow you to use regular Python operators with the class-level attributes on your mapped class:

SQL>>> for name, in session.query(User.name).\
...             filter(User.fullname=='Ed Jones'):
...    print(name)

The Query object is fully generative, meaning that most method calls return a new Query object upon which further criteria may be added. For example, to query for users named “ed” with a full name of “Ed Jones”, you can call filter() twice, which joins criteria using AND:

SQL>>> for user in session.query(User).\
...          filter(User.name=='ed').\
...          filter(User.fullname=='Ed Jones'):
...    print(user)

Filter operator

Here’s a rundown of some of the most common operators used in filter():

• equals:

  query.filter(User.name == 'ed')
  

• not equals:

  query.filter(User.name != 'ed')
  

• LIKE:

  query.filter(User.name.like('%ed%'))
  

Note

ColumnOperators.like() renders the LIKE operator, which is case insensitive on some backends, and case sensitive on others. For guaranteed case-insensitive comparisons, use ColumnOperators.ilike().

• ILIKE (case-insensitive LIKE):

  query.filter(User.name.ilike('%ed%'))
  

Note

most backends don’t support ILIKE directly. For those, the ColumnOperators.ilike() operator renders an expression combining LIKE with the LOWER SQL function applied to each operand.

• IN:

  query.filter(User.name.in_(['ed', 'wendy', 'jack']))
  
  # works with query objects too:
  query.filter(User.name.in_(
      session.query(User.name).filter(User.name.like('%ed%'))
  ))
  

• NOT IN:

  query.filter(~User.name.in_(['ed', 'wendy', 'jack']))
  

• IS NULL:

  query.filter(User.name == None)
  
  # alternatively, if pep8/linters are a concern
  query.filter(User.name.is_(None))
  

• IS NOT NULL:

  query.filter(User.name != None)
  
  # alternatively, if pep8/linters are a concern
  query.filter(User.name.isnot(None))
  

• AND:

  # use and_()
  from sqlalchemy import and_
  query.filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))
  
  # or send multiple expressions to .filter()
  query.filter(User.name == 'ed', User.fullname == 'Ed Jones')
  
  # or chain multiple filter()/filter_by() calls
  query.filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')
  

Note

Make sure you use and_() and not the Python and operator!

• OR:

  from sqlalchemy import or_
  query.filter(or_(User.name == 'ed', User.name == 'wendy'))
  

Note

Make sure you use or_() and not the Python or operator!

• MATCH:

  query.filter(User.name.match('wendy'))
  

Note

match() uses a database-specific MATCH or CONTAINS function; its behavior will vary by backend and is not available on some backends such as SQLite.

Fetch results

A number of methods on Query immediately issue SQL and return a value containing loaded database results. Here’s a brief tour:

• all() returns a list:

  >>> query = session.query(User).filter(User.name.like('%ed')).order_by(User.id)
  SQL>>> query.all()
  [<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>,
        <User(name='fred', fullname='Fred Flinstone', password='blah')>]
  

• first() applies a limit of one and returns the first result as a scalar:

  SQL>>> query.first()
  <User(name='ed', fullname='Ed Jones', password='f8s7ccs')>
  

• one() fully fetches all rows, and if not exactly one object identity or composite row is present in the result, raises an error. With multiple rows found:

  >>> user = query.one()
  Traceback (most recent call last):
  ...
  MultipleResultsFound: Multiple rows were found for one()
  

  With no rows found:

  >>> user = query.filter(User.id == 99).one()
  Traceback (most recent call last):
  ...
  NoResultFound: No row was found for one()
  

  The one() method is great for systems that expect to handle “no items found” versus “multiple items found” differently; such as a RESTful web service, which may want to raise a “404 not found” when no results are found, but raise an application error when multiple results are found.

• one_or_none() is like one(), except that if no results are found, it doesn’t raise an error; it just returns None. Like one(), however, it does raise an error if multiple results are found.

• scalar() invokes the one() method, and upon success returns the first column of the row:

  >>> query = session.query(User.id).filter(User.name == 'ed').\
  ...    order_by(User.id)
  SQL>>> query.scalar()
  1
  

Using raw SQL

Literal strings can be used flexibly with Query, by specifying their use with the text() construct, which is accepted by most applicable methods. For example,filter() and order_by():

>>> from sqlalchemy import text
SQL>>> for user in session.query(User).\
...             filter(text("id<224")).\
...             order_by(text("id")).all():
...     print(user.name)

Bind parameters can be specified with string-based SQL, using a colon. To specify the values, use the params() method:

SQL>>> session.query(User).filter(text("id<:value and name=:name")).\
...     params(value=224, name='fred').order_by(User.id).one()

Counting

Query includes a convenience method for counting called count():

SQL>>> session.query(User).filter(User.name.like('%ed')).count()

For situations where the “thing to be counted” needs to be indicated specifically, we can specify the “count” function directly using the expression func.count(), available from the funcconstruct. Below we use it to return the count of each distinct user name:

>>> from sqlalchemy import func
SQL>>> session.query(func.count(User.name), User.name).group_by(User.name).all()
[(1, u'ed'), (1, u'fred'), (1, u'mary'), (1, u'wendy')]

To achieve our simple SELECT count(*) FROM table, we can apply it as:

SQL>>> session.query(func.count('*')).select_from(User).scalar()
4

The usage of select_from() can be removed if we express the count in terms of the User primary key directly:

SQL>>> session.query(func.count(User.id)).scalar()

Querying with join

To construct a simple implicit join between User and Address, we can use Query.filter() to equate their related columns together. Below we load the Userand Address entities at once using this method:

SQL>>> for u, a in session.query(User, Address).\
...                     filter(User.id==Address.user_id).\
...                     filter(Address.email_address=='jack@google.com').\
...                     all():
...     print(u)
...     print(a)

The actual SQL JOIN syntax, on the other hand, is most easily achieved using the Query.join() method:

SQL>>> session.query(User).join(Address).\
...         filter(Address.email_address=='jack@google.com').\
...         all()

Query.join() knows how to join between User and Address because there’s only one foreign key between them. If there were no foreign keys, or several, Query.join() works better when one of the following forms are used:

session.query(User).join(Address, User.id==Address.user_id)    # explicit condition
session.query(User).join(User.addresses)                       # specify relationship from left to right
session.query(User).join(Address, User.addresses)              # same, with explicit target
session.query(User).join('addresses')                          # same, using a string

Alias

When querying across multiple tables, if the same table needs to be referenced more than once, SQL typically requires that the table be aliased with another name, so that it can be distinguished against other occurrences of that table. The Query supports this most explicitly using the aliased construct. Below we join to the Address entity twice, to locate a user who has two distinct email addresses at the same time:

>>> from sqlalchemy.orm import aliased
>>> adalias1 = aliased(Address)
>>> adalias2 = aliased(Address)
SQL>>> for username, email1, email2 in \
...     session.query(User.name, adalias1.email_address, adalias2.email_address).\
...     join(adalias1, User.addresses).\
...     join(adalias2, User.addresses).\
...     filter(adalias1.email_address=='jack@google.com').\
...     filter(adalias2.email_address=='j25@yahoo.com'):
...     print(username, email1, email2)

Subquery

The Query is suitable for generating statements which can be used as subqueries. Suppose we wanted to load User objects along with a count of how many Address records each user has. The best way to generate SQL like this is to get the count of addresses grouped by user ids, and JOIN to the parent. In this case we use a LEFT OUTER JOIN so that we get rows back for those users who don’t have any addresses, e.g.:

SELECT users.*, adr_count.address_count FROM users LEFT OUTER JOIN
    (SELECT user_id, count(*) AS address_count
        FROM addresses GROUP BY user_id) AS adr_count
    ON users.id=adr_count.user_id

Using the Query, we build a statement like this from the inside out. The statement accessor returns a SQL expression representing the statement generated by a particular Query - this is an instance of a select() construct, which are described in SQL Expression Language Tutorial:

>>> from sqlalchemy.sql import func
>>> stmt = session.query(Address.user_id, func.count('*').\
...         label('address_count')).\
...         group_by(Address.user_id).subquery()

Once we have our statement, it behaves like a Table construct, such as the one we created for users at the start of this tutorial. The columns on the statement are accessible through an attribute called c:

SQL>>> for u, count in session.query(User, stmt.c.address_count).\
...     outerjoin(stmt, User.id==stmt.c.user_id).order_by(User.id):
...     print(u, count)

Above, we just selected a result that included a column from a subquery. What if we wanted our subquery to map to an entity ? For this we use aliased() to associate an “alias” of a mapped class to a subquery:

SQL>>> stmt = session.query(Address).\
...                 filter(Address.email_address != 'j25@yahoo.com').\
...                 subquery()
>>> adalias = aliased(Address, stmt)
>>> for user, address in session.query(User, adalias).\
...         join(adalias, User.addresses):
...     print(user)
...     print(address)

Exists

There is an explicit EXISTS construct, which looks like this:

>>> from sqlalchemy.sql import exists
>>> stmt = exists().where(Address.user_id==User.id)
SQL>>> for name, in session.query(User.name).filter(stmt):
...     print(name)

The Query features several operators which make usage of EXISTS automatically. Above, the statement can be expressed along the User.addresses relationship using any():

SQL>>> for name, in session.query(User.name).\
...         filter(User.addresses.any()):
...     print(name)

any() takes criterion as well, to limit the rows matched:

SQL>>> for name, in session.query(User.name).\
...     filter(User.addresses.any(Address.email_address.like('%google%'))):
...     print(name)

Relationship operator

Here’s all the operators which build on relationships - each one is linked to its API documentation which includes full details on usage and behavior:

• __eq__() (many-to-one “equals” comparison):

  query.filter(Address.user == someuser)
  

• __ne__() (many-to-one “not equals” comparison):

  query.filter(Address.user != someuser)
  

• IS NULL (many-to-one comparison, also uses __eq__()):

  query.filter(Address.user == None)
  

• contains() (used for one-to-many collections):

  query.filter(User.addresses.contains(someaddress))
  

• any() (used for collections):

  query.filter(User.addresses.any(Address.email_address == 'bar'))
  
  # also takes keyword arguments:
  query.filter(User.addresses.any(email_address='bar'))
  

• has() (used for scalar references):

  query.filter(Address.user.has(name='ed'))
  

• Query.with_parent() (used for any relationship):

  session.query(Address).with_parent(someuser, 'addresses')
  

Deleting

Let’s try to delete jack and see how that goes. We’ll mark the object as deleted in the session, then we’ll issue a count query to see that no rows remain:

>>> session.delete(jack)
SQL>>> session.query(User).filter_by(name='jack').count()
0

So far, so good. How about Jack’s Address objects ?

SQL>>> session.query(Address).filter(
...     Address.email_address.in_(['jack@google.com', 'j25@yahoo.com'])
...  ).count()
2

Uh oh, they’re still there ! Analyzing the flush SQL, we can see that the user_id column of each address was set to NULL, but the rows weren’t deleted. SQLAlchemy doesn’t assume that deletes cascade, you have to tell it to do so.

delete cascade

We will configure cascade options on the User.addresses relationship to change the behavior.

>>> class User(Base):
...     __tablename__ = 'users'
...
...     id = Column(Integer, primary_key=True)
...     name = Column(String)
...     fullname = Column(String)
...     password = Column(String)
...
...     addresses = relationship("Address", back_populates='user',
...                     cascade="all, delete, delete-orphan")

Further detail on configuration of cascades is at Cascades. The cascade functionality can also integrate smoothly with the ON DELETE CASCADE functionality of the relational database. See Using Passive Deletes for details.

Column Type

用 Column 来定义一列。列名就是您赋值给那个变量的名称。如果您想要在表中使用不同的名称，您可以提供一个想要的列名的字符串作为可选第一个参数。主键用 primary_key=True 标记。可以把多个键标记为主键，此时它们作为复合主键。

列的类型是 Column 的第一个参数。您可以直接提供它们或进一步规定（比如提供一个长度）。下面的类型是最常用的:

Integer	一个整数
String (size)	有长度限制的字符串
Text	一些较长的 unicode 文本
DateTime	表示为 Python datetime 对象的 时间和日期
Float	存储浮点值
Boolean	存储布尔值
PickleType	存储为一个持久化的 Python 对象
LargeBinary	存储一个任意大的二进制数据

Relationship

Import statements

from sqlalchemy import Table, Column, Integer, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

One To Many

One person has many addresses.

class Person(Base):
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    addresses = relationship(Address, order_by=Address.id, backref='person',
                                lazy='dynamic')

class Address(Base):
    id = Column(Integer, primary_key=True)
    email = Column(String(50))
    person_id = Column(Integer, ForeignKey('person.id'))
    person = relationship(Person, backref='addresses')

• The above class introduces the ForeignKey construct, which is a directive applied to Column that indicates that values in this column should be constrained to be values present in the named remote column.
• A second directive, known as relationship(), tells the ORM that the Address class itself should be linked to the User class, using the attribute Address.user. relationship() uses the foreign key relationships between the two tables to determine the nature of this linkage, determining that Address.user will be many to one. An additional relationship() directive is placed on the User mapped class under the attribute User.addresses. In bothrelationship() directives, the parameter relationship.back_populates is assigned to refer to the complementary attribute names; by doing so, each relationship() can make intelligent decision about the same relationship as expressed in reverse; on one side, Address.user refers to a User instance, and on the other side, User.addresses refers to a list of Address instances. The relationship.back_populates parameter is a newer version of a very common SQLAlchemy feature called relationship.backref. The relationship.backref parameter hasn’t gone anywhere and will always remain available! The relationship.back_populates is the same thing, except a little more verbose and easier to manipulate. The two complementing relationships Address.user and User.addresses are referred to as a bidirectional relationship, and is a key feature of the SQLAlchemy ORM.

• a FOREIGN KEY constraint in most (though not all) relational databases can only link to a primary key column, or a column that has a UNIQUE constraint.
• a FOREIGN KEY constraint that refers to a multiple column primary key, and itself has multiple columns, is known as a “composite foreign key”. It can also reference a subset of those columns.
• FOREIGN KEY columns can automatically update themselves, in response to a change in the referenced column or row. This is known as the CASCADE referential action, and is a built in function of the relational database.
• FOREIGN KEY can refer to its own table. This is referred to as a “self-referential” foreign key.

Person instance can access the assoicated addresses via addresses relationship, and Address instance can also access the associated person via person attribute.

lazy

‘select’ (默认值) 就是说 SQLAlchemy 会使用一个标准的 select 语句必要时一次加载数据。 ‘joined’ 告诉 SQLAlchemy 使用 JOIN 语句作为父级在同一查询中来加载关系。 ‘subquery’ 类似 ‘joined’ ，但是 SQLAlchemy 会使用子查询。 ‘dynamic’ 在有多条数据的时候是特别有用的。不是直接加载这些数据，SQLAlchemy 会返回一个查询对象，在加载数据前您可以过滤（提取）它们。

lazy for backref

class Person(Base):
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    addresses = relationship(Address, backref=backref('person', lazy='joined'),
                                lazy='dynamic')

Many To One

One post has many tags and each tag belongs to many posts.

class Post(Base):
    id = Column(Integer, primary_key=True)
    title = Column(String(255))
    tags = relationship(Tag, secondary=PostTagLink, backref=backref('posts', lazy='dynamic'))

class Tag(Base):
    id = Column(Integer, primary_key=True)
    value = Column(String(100))
    

class PostTagLink(Base):
    tag_id = Column(Integer, primary_key=True)
    post_id = Column(Integer, primary_key=True)

One To One

Many To Many

We’ll make our application a blog application, where users can write BlogPost items, which have Keyword items associated with them.

create a association table

For a plain many-to-many, we need to create an un-mapped Table construct to serve as the association table. This looks like the following:

>>> from sqlalchemy import Table, Text
>>> # association table
>>> post_keywords = Table('post_keywords', Base.metadata,
...     Column('post_id', ForeignKey('posts.id'), primary_key=True),
...     Column('keyword_id', ForeignKey('keywords.id'), primary_key=True)
... )

Above, we can see declaring a Table directly is a little different than declaring a mapped class.

Next we define BlogPost and Keyword, using complementary relationship() constructs, each referring to the post_keywords table as an association table:

>>> class BlogPost(Base):
...     __tablename__ = 'posts'
...
...     id = Column(Integer, primary_key=True)
...     user_id = Column(Integer, ForeignKey('users.id'))
...     headline = Column(String(255), nullable=False)
...     body = Column(Text)
...
...     # many to many BlogPost<->Keyword
...     keywords = relationship('Keyword',
...                             secondary=post_keywords,
...                             back_populates='posts')
...
...     def __init__(self, headline, body, author):
...         self.author = author
...         self.headline = headline
...         self.body = body
...
...     def __repr__(self):
...         return "BlogPost(%r, %r, %r)" % (self.headline, self.body, self.author)


>>> class Keyword(Base):
...     __tablename__ = 'keywords'
...
...     id = Column(Integer, primary_key=True)
...     keyword = Column(String(50), nullable=False, unique=True)
...     posts = relationship('BlogPost',
...                          secondary=post_keywords,
...                          back_populates='keywords')
...
...     def __init__(self, keyword):
...         self.keyword = keyword

Above, the many-to-many relationship is BlogPost.keywords. The defining feature of a many-to-many relationship is the secondary keyword argument which references a Table object representing the association table. This table only contains columns which reference the two sides of the relationship; if it has any other columns, such as its own primary key, or foreign keys to other tables, SQLAlchemy requires a different usage pattern called the “association object”, described atAssociation Object.

We would also like our BlogPost class to have an author field. We will add this as another bidirectional relationship, except one issue we’ll have is that a single user might have lots of blog posts. When we access User.posts, we’d like to be able to filter results further so as not to load the entire collection. For this we use a setting accepted by relationship() called lazy='dynamic', which configures an alternate loader strategy on the attribute:

>>> BlogPost.author = relationship(User, back_populates="posts")
>>> User.posts = relationship(BlogPost, back_populates="author", lazy="dynamic")

association object

https://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#association-pattern

Loading

Recall earlier that we illustrated a lazy loading operation, when we accessed the User.addresses collection of a User and SQL was emitted. If you want to reduce the number of queries (dramatically, in many cases), we can apply an eager load to the query operation. SQLAlchemy offers three types of eager loading, two of which are automatic, and a third which involves custom criterion. All three are usually invoked via functions known as query options which give additional instructions to the Query on how we would like various attributes to be loaded, via the Query.options() method.

Subquery Load

In this case we’d like to indicate that User.addresses should load eagerly. A good choice for loading a set of objects as well as their related collections is the orm.subqueryload() option, which emits a second SELECT statement that fully loads the collections associated with the results just loaded.

>>> from sqlalchemy.orm import subqueryload
SQL>>> jack = session.query(User).\
...                 options(subqueryload(User.addresses)).\
...                 filter_by(name='jack').one()

Joined Load

The other automatic eager loading function is more well known and is called orm.joinedload(). This style of loading emits a JOIN, by default a LEFT OUTER JOIN, so that the lead object as well as the related object or collection is loaded in one step. We illustrate loading the same addresses collection in this way - note that even though the User.addresses collection on jack is actually populated right now, the query will emit the extra join regardless:

>>> from sqlalchemy.orm import joinedload

SQL>>> jack = session.query(User).\
...                        options(joinedload(User.addresses)).\
...                        filter_by(name='jack').one()

Explicit Join + Eagerload

A third style of eager loading is when we are constructing a JOIN explicitly in order to locate the primary rows, and would like to additionally apply the extra table to a related object or collection on the primary object. This feature is supplied via the orm.contains_eager() function, and is most typically useful for pre-loading the many-to-one object on a query that needs to filter on that same object. Below we illustrate loading an Address row as well as the related User object, filtering on the User named “jack” and using orm.contains_eager() to apply the “user” columns to the Address.user attribute:

>>> from sqlalchemy.orm import contains_eager
SQL>>> jacks_addresses = session.query(Address).\
...                             join(Address.user).\
...                             filter(User.name=='jack').\
...                             options(contains_eager(Address.user)).\
...                             all()

For more information on eager loading, including how to configure various forms of loading by default, see the section Relationship Loading Techniques.

Relationship

Import statements

from sqlalchemy import Table, Column, Integer, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

One To Many

user.addresses refers a collection of items represented by the address.

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    addresses = relationship('Address')

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'))

To establish a bidirectional relationship in one-to-many: user.addresses and address.user

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    addresses = relationship('Address', back_populates='user')

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'))
    user = relationship('User', back_populates='addresses')

Foreign Key put into the “many” object.

One To One

One To One is essentially a bidirectional relationship with a scalar attribute on both sides. To achieve this, the uselist flag indicates the placement of a scalar attribute instead of a collection on the “many” side of the relationship.

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    id_card = relationship('IDCard', back_populates='user', uselist=False)

class IDCard(Base):
    __tablename__ = 'id_card'
    id = Column(Integer, primary_key=True)
    seq = Column(String(255))
    user_id = Column(Integer, ForeignKey('user.id'))
    user = relationship('User', back_populates='id_card')

Many To Many

Many to Many adds an association table between two classes. The association table is indicated by the secondary argument to relationship(). Usually, the Table uses the MetaData object associated with the declarative base class, so that the ForeignKey directives can locate the remote tables with which to link

post_tag_link = Table('post_tag', Base.metadata,
    Column('post_id', Integer, ForeignKey('post.id')),
    Column('tag_id', Integer, ForeignKey('tag.id'))
)

class Post(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    title = Column(String(255))
    tags = relationship('Tag', secondary=post_tag_link)

class Tag(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(100))

For a bidirectional relationship, both sides of the relationship contain a collection.

post_tag_link = Table('post_tag', Base.metadata,
    Column('post_id', Integer, ForeignKey('post.id')),
    Column('tag_id', Integer, ForeignKey('tag.id'))
)

class Post(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    title = Column(String(255))
    tags = relationship('Tag', secondary=post_tag_link, back_populates='posts')

class Tag(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    posts = relationship('Post', secondary=post_tag_link, back_populates='tags')

https://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#deleting-rows-from-the-many-to-many-table