SQLAlchemy - Object Relational Mapper
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()
:
-
query.filter(User.name == 'ed')
-
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, useColumnOperators.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%')) ))
-
query.filter(~User.name.in_(['ed', 'wendy', 'jack']))
-
query.filter(User.name == None) # alternatively, if pep8/linters are a concern query.filter(User.name.is_(None))
-
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 Pythonand
operator!
-
OR
:from sqlalchemy import or_ query.filter(or_(User.name == 'ed', User.name == 'wendy'))
Note
Make sure you use
or_()
and not the Pythonor
operator!
-
query.filter(User.name.match('wendy'))
Note
match()
uses a database-specificMATCH
orCONTAINS
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 likeone()
, except that if no results are found, it doesn’t raise an error; it just returnsNone
. Likeone()
, however, it does raise an error if multiple results are found. -
scalar()
invokes theone()
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 func
construct. 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 User
and 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 toColumn
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 theAddress
class itself should be linked to theUser
class, using the attributeAddress.user
.relationship()
uses the foreign key relationships between the two tables to determine the nature of this linkage, determining thatAddress.user
will be many to one. An additionalrelationship()
directive is placed on theUser
mapped class under the attributeUser.addresses
. In bothrelationship()
directives, the parameterrelationship.back_populates
is assigned to refer to the complementary attribute names; by doing so, eachrelationship()
can make intelligent decision about the same relationship as expressed in reverse; on one side,Address.user
refers to aUser
instance, and on the other side,User.addresses
refers to a list ofAddress
instances. Therelationship.back_populates
parameter is a newer version of a very common SQLAlchemy feature calledrelationship.backref
. Therelationship.backref
parameter hasn’t gone anywhere and will always remain available! Therelationship.back_populates
is the same thing, except a little more verbose and easier to manipulate. The two complementing relationshipsAddress.user
andUser.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')