SQLAlchemy使用

SQLAlchemy

原文地址：Object Relational Tutorial

获得当前版本

sqlalchemy.__version__

Connecting

from sqlalchemy import create_engine
engine = create_engine('',echo=True)

Declare a Mapping

定义基类

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

定义Model类

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

在数据库中创建表

>>> Base.metadata.create_all(engine)

Create an Instance of Mapped Class

user = User(name='ed', fullname='Ed Jones', password='abc')

Creating a Session

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

或则先创建session类，再绑定engine

Session = sessionmaker()
Session.configure(bind=engine)

实例化

session = Session()

Adding and Updating Object

session.add(user)   # user状态为pending，如果query查询user，就会flush到数据库，commit也会flush到数据库
session.add_all([user1, user2,...])
session.new  # 获得所有pending的对象
# 修改
user.password = 'zyx'
session.dirty  # 获得未提交的脏数据
# 提交数据
session.commit()

Rolling Back

session.rollback()  # 事务回滚

---

Querying

session.query(User).order_by(User.id)  # Talle
query(User.name, User.fullname)        # Column
>>> for row in session.query(User, User.name).all():
...    print(row.User, row.name)       # namedtuple
>>> for row in session.query(User.name.label('name_label')).all():
...    print(row.name_label)           # 单独的column设置label

>>> from sqlalchemy.orm import aliased   
>>> user_alias = aliased(User, name='user_alias')  # 设置别名
>>> for row in session.query(user_alias, user_alias.name).all():
...    print(row.user_alias)

LIMIT and OFFSET

session.query(User).order_by(User.id)[1:3]

WHERE

session.query(User.name).filter_by(fullname='Ed Jones')  # 关键字传参
session.query(User.name).filter(User.fullname=='Ed Jones')

Common Filter Operators:

   * equels: User.name == 'ed'
   * not equels: User.name != 'ed
   * LIKE: User.name.like('%ed%')
   * ILIKE: User.name.ilike('%ed%')    # 忽略大小写
   * IN: User.name.in_(['ed', 'wendy'])
        `User.name.in_(session.query(User.name).filter(User.name.like('%ed%')))`
   * NOT IN: ~User.name.in_(['ed', 'wendy'])
   * IS NULL: User.name == None
   * IS NOT NULL: User.name != None
   * AND: 
    	`from sqlalchemy import and_`
        `and_(User.name == 'ed', User.fullname=='Ed Jones')`
        或者：
        query.filter(User.name == 'ed', User.fullname == 'Ed Jones')
        query.filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')
   * OR: from sqlalchemy import or_
        or_(User.name == 'ed', User.name == 'wendy')
   * MATCH: User.name.match('wendy')    # 有些数据库不支持，如SQLite

Returning List and Scalars

all()
first()
one() only one, 两个异常，MultipleResultsFound, NoResultFound
one_or_none() 一个异常，MultipleResultsFound，没有返回None
scalar() 调用one(), 成功后返回行的第一列，多为id

Using Textual SQLAlchemy 使用原生sql语句

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

使用parameters，colon:做占位符

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

使用columns对应TextClause到ORM column

>>> stmt = text("SELECT name, id, fullname, password "
...             "FROM users where name=:name")
>>> stmt = stmt.columns(User.name, User.id, User.fullname, User.password)
SQL>>> session.query(User).from_statement(stmt).params(name='ed').all()

Counting

count()   # 返回query的行数，并不适合使用
>>> from sqlalchemy import func
>>> 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')]   # 使用聚合函数
# SELECT count(*) FROM table
session.query(func.count('*')).select_from(User).scalar()
session.query(func.count(User.id)).scalar()  # 使用id(唯一)省略,select_from

Building a Relationship

>>> from sqlalchemy.orm import relationship
>>> 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")

更多Basic Relationship Patterns

Querying with Joins

query = session.query(User, Address).select_from(Address).join(User)

select_from定义从哪一张表开始join，默认是从query中最左边的开始join

query.join(Address, User.id==Address.user_id)  

指定ON条件，不指定默认使用外键

Using Aliases

>>> 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)
jack jack@google.com j25@yahoo.com

一张表被join两次，使用别名，获得有两个email地址分别为jack@google.com和j25@yahoo.com的user

Using Subqueries

Using EXISTS

Common Relationship Operators

==
!=
== None
contains
any
has
with_parent

Deleting

session.delete(item)
session.close() -> ROLLBACK

Cascade (ORM level)

addresses = relationship("Address", back_populates='user', cascade="all, delete-orphan")

Many To Many Relationship

User.posts = relationship(BlogPost, back_populates=“author”, lazy=“dynamic”)
懒加载