sqlalchemy学习笔记

注意

入门

连接数据库

连接SQLite数据库（内存模式）

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

echo:启用它，我们将看到所有生成的SQL
engine:一个实例Engine，标识数据库核心接口，主要用于链接数据库。

声明一个映射

声明一个映射，用于之后创建model

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

根据声明的映射创建Model

from sqlalchemy import Column, Integer, String
>>> 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)

tablename :映射到数据库的表名
注意：在sqlite和postgresql上string不用设置长度，其他的必须设置字段长度：string(30)

注册表格

注册所有的表到数据库（这一步会创建所有的表），这里我建议不要自动注册，乙方产生不可预料的问题。

Base.metadata.create_all(engine)

通过model创建实例，

>>> ed_user = User(name='ed', fullname='Ed Jones', nickname='edsnickname')
>>> ed_user.name
'ed'
>>> ed_user.nickname
'edsnickname'
>>> str(ed_user.id)
'None'

创建会话

ORM的数据库“句柄”是Session。需要创建一个Session作为工厂类，每个线程单独有一个会话操作类。（注意：异步操作可能会有不一样，这里还需要更多了解）

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

每当需要与数据库对话时，都实例化一个Session：

session = Session()

add和update对象

sqlalchemy在读取的时候，会首先从未保存的数据里面搜索。所以，及时数据没有commit，会被有限搜索，但是如果不commit最后数据库不会有数据
要坚持我们的User目标，我们add()也给我们的Session：

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

如果我们在数据库中查询，则将首先刷新所有待处理的信息，然后立即发出查询。
例如，下面我们创建一个新Query对象，该对象加载的实例User。我们通过user的name属性 “过滤” ed，并指出我们只希望完整行列表中的第一个结果。User返回一个实例，该实例与我们添加的实例等效：

>>> our_user = session.query(User).filter_by(name='ed').first() 
>>> our_user
<User(name='ed', fullname='Ed Jones', nickname='edsnickname')>

实际上，Session已经确定返回的行与其内部对象映射中已经表示的行是同一行，因此我们实际上取回了与刚添加的实例相同的实例：

>>> ed_user is our_user
True

这里使用的ORM概念称为身份映射：可确保Session对同一数据集进行操作的特定行上的所有操作 。
一旦具有特定主键的对象出现在中Session，该对象上的 所有SQL查询 Session将始终返回相同的Python对象；
如果尝试在会话中放置另一个具有相同主键的已经持久化的对象，它也会引发错误。

我们可以User使用一次添加更多对象 add_all()：

>>> session.add_all([
User(name='wendy', fullname='Wendy Williams', nickname='windy'),
User(name='mary', fullname='Mary Contrary', nickname='mary'),
User(name='fred', fullname='Fred Flintstone', nickname='freddy')])

另外，我们已经确定Ed的昵称不太好，所以让我们对其进行更改：

>>> ed_user.nickname = 'eddie'

>>> session.dirty
IdentitySet([<User(name='ed', fullname='Ed Jones', nickname='eddie')>])

此时有三个对象等待处理：

>>> session.new  
IdentitySet([<User(name='wendy', fullname='Wendy Williams', nickname='windy')>,
<User(name='mary', fullname='Mary Contrary', nickname='mary')>,
<User(name='fred', fullname='Fred Flintstone', nickname='freddy')>])

INSERT三个新语句User添加对象：

SQL>>> session.commit()

此操作会刷新所有的user实例，如果查看id，会发现已经多了id值。·

SQL>>> ed_user.id 
1

回滚

略
真男人从不会滚

查询

这一块和django最接近，只是没有manager概念

SQL>>> for instance in session.query(User).order_by(User.id):
...     print(instance.name, instance.fullname)
ed Ed Jones
wendy Wendy Williams
mary Mary Contrary
fred Fred Flintstone

这里有个需要注意的地方，如果要查询指定字段（我一般都提取整个对象。。），可以在query里面写对象.字段，多个就可以提取多个，返回的也是多个（元组形式），比如：

>>> for row in session.query(User, User.name).all():
...    print(row.User, row.name)
<User(name='ed', fullname='Ed Jones', nickname='eddie')> ed
<User(name='wendy', fullname='Wendy Williams', nickname='windy')> wendy
<User(name='mary', fullname='Mary Contrary', nickname='mary')> mary
<User(name='fred', fullname='Fred Flintstone', nickname='freddy')> fred

也可以使用aliased，不过这个不知道到底存在意义是什么。
结果过滤（fileter字段，）
排序(order_by)字段

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

通用过滤符：

==
!=
like:query.filter(User.name.like('%ed%'))(如果要不区分大小写用ilike)
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%'))
))

# use tuple_() for composite (multi-column) queries
from sqlalchemy import tuple_
query.filter(
    tuple_(User.name, User.nickname).in_([('ed', 'edsnickname'), ('wendy', 'windy')])
)

not in:query.filter(~User.name.in_(['ed', 'wendy', 'jack']))
is null 、is not null:

query.filter(User.name == None)#!=

# alternatively, if pep8/linters are a concern
query.filter(User.name.is_(None))#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')

or:

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

返回结果

all()返回列表
first()返回第一个，没有则报错
one()如果超过一个或没有则报错
one_or_none()超过一个报错
scalar()调用该one()方法，并在成功后返回该行的第一列，与first区别？？
count()返回计数

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

也可以在查询结果里面讲count加入进行组合查询

>>> 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')]

表关系

假设有两张表

from sqlalchemy import *
from sqlalchemy.orm import relationship
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)
class Address(Base):
    	__tablename__ = 'addresses'
		id = Column(Integer, primary_key=True)
		email_address = Column(String, nullable=False)
		user_id = Column(Integer, ForeignKey('users.id'))
		user = relationship("User", back_populates="addresses")
		def __repr__(self):
			return "<Address(email_address='%s')>" % self.email_address

>>> User.addresses = relationship("Address", order_by=Address.id, back_populates="user")

上面的类介绍了该ForeignKey构造，该构造是应用于该构造的指令，该构造Column指示应将此列中的值限制为指定的远程列中存在的值。在ForeignKey上述表示的是，在值addresses.user_id列应该被限制为这些值在users.id列中，即它的主键。
第二个指令称为relationship()，告诉ORM Address该类本身应User使用attribute 链接到该类Address.user。 relationship()使用两个表之间的外键关系来确定此链接的性质，确定它们之间Address.user是多对一的。附加relationship()指令位于 User属性下的映射类上User.addresses。一方面Address.user是指User实例，另一方面User.addresses是指列表 Address 实例。

关联查询

查询user和address的关联信息实例如下：

>>> 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)
<User(name='jack', fullname='Jack Bean', nickname='gjffdd')>
<Address(email_address='jack@google.com')>

也可以使用Query.join()语法实现（如果不熟悉语法可能会对join有点陌生，都是django后遗症…）

>>> session.query(User).join(Address).\
...         filter(Address.email_address=='jack@google.com').\
...         all()
[<User(name='jack', fullname='Jack Bean', nickname='gjffdd')>]

Query.join()知道如何连接User 和Address,因为它们之间只有一个外键。如果Query.join() 使用以下一种形式，则没有外键或外键更好地工作：

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

外键

略

公共关系运算符

这是所有建立在关系上的运算符-每个运算符都链接到其API文档，其中包括有关用法和行为的完整详细信息：

eq() （多对一“等于”比较）：query.filter(Address.user == someuser)
ne() （多对一“不等于”比较）：query.filter(Address.user != someuser)
IS NULL（多对一比较，也使用__eq__()）：query.filter(Address.user == None)
contains() （用于一对多收藏）：query.filter(User.addresses.contains(someaddress))
any() （用于收藏集）：

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

has() （用于标量引用）：query.filter(Address.user.has(name='ed'))
Query.with_parent() （用于任何关系）：````session.query(Address).with_parent(someuser, ‘addresses’)```

预先加载

如果考虑到需要在加载的数据中进行再次搜索，可以一次将所有结果加载完成

>>> from sqlalchemy.orm import selectinload
SQL>>> jack = session.query(User).options(selectinload(User.addresses)).\
...                 filter_by(name='jack').one()
>>> jack
<User(name='jack', fullname='Jack Bean', nickname='gjffdd')>

>>> jack.addresses
[<Address(email_address='jack@google.com')>, <Address(email_address='j25@yahoo.com')>]

删除

设置关联数据的删除方式
级联删除：

>>> class User(Base):
...     __tablename__ = 'users'
...
...     id = Column(Integer, primary_key=True)
...     name = Column(String)
...     fullname = Column(String)
...     nickname = Column(String)
...     addresses = relationship("Address", back_populates='user',
...                     cascade="all, delete, delete-orphan")
...
...     def __repr__(self):
...        return "<User(name='%s', fullname='%s', nickname='%s')>" % (
...                                self.name, self.fullname, self.nickname)
>>> class Address(Base):
...     __tablename__ = 'addresses'
...     id = Column(Integer, primary_key=True)
...     email_address = Column(String, nullable=False)
...     user_id = Column(Integer, ForeignKey('users.id'))
...     user = relationship("User", back_populates="addresses")
...     def __repr__(self):
...         return "<Address(email_address='%s')>" % self.email_address

注意：back_populates是关联对象调用自己的名字
cascade:删除方式：“all, delete, delete-orphan”

基本关系模式

一对多

一对多关系将一个外键放在引用父表的子表上。 relationship()然后在父项上指定，以引用由子项表示的项的集合：

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Child")

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))

一对一

一对一本质上是双向关系，双方都有标量属性。为此，该uselist标志指示标量属性的放置，而不是关系的“许多”侧上的集合。要将一对多转换为一对一：

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    child = relationship("Child", uselist=False, back_populates="parent")

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))
    parent = relationship("Parent", back_populates="child")

多对多关系

建立多对多关系，需要建立一个未映射的Table构造为关联表

>>> 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)
... )

示例：

 >>> 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,# 它引用Table表示关联表的对象。该表仅包含引用关系两侧的列。
...                             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

示例2

association_table = Table('association', Base.metadata,
    Column('left_id', Integer, ForeignKey('left.id')),
    Column('right_id', Integer, ForeignKey('right.id'))
)

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship(
        "Child",
        secondary=association_table,
        back_populates="parents")

class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)
    parents = relationship(
        "Parent",
        secondary=association_table,
        back_populates="children")

当使用backref参数代替relationship.back_populates时，backref将自动secondary为反向关系使用相同的参数：
示例3：

association_table = Table('association', Base.metadata,
    Column('left_id', Integer, ForeignKey('left.id')),
    Column('right_id', Integer, ForeignKey('right.id'))
)

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Child",
                    secondary=association_table,
                    backref="parents")

class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)

多对多数据删除

这是唯一的一个行为secondary参数relationship() 是，Table它在这里指定自动受INSERT和DELETE语句，对象添加或从集合中删除。有没有必要从该表中手动删除。从集合中删除记录的行为将在刷新时删除该行：

# row will be deleted from the "secondary" table
# automatically
myparent.children.remove(somechild)

级联

配置级联删除：
1、级联删除：addresses = relationship("Address", cascade="save-update, merge, delete")
2、子类不删除：addresses = relationship("Address")