SQLAlchemy代码总结(1)

SQLAlchemy代码总结(1)

本文抽自官方文档，用来记忆，具体请看官方文档，因为这里只有代码，没有什么说明。

版本检查

>>> import sqlalchemy
>>> sqlalchemy.__version__
1.0.8

---

连接
engine在第一次使用时建立DBAPI与数据库的连接

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

---

* 创建Declarative 用来描述表和类与表的关系*

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

---

创建映射类
至少需要__tablename__属性和一个是主键的列

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

---

创建模式

• 查看table对象

>>> User.__table__ 
Table('users', MetaData(bind=None),
            Column('id', Integer(), table=<users>, primary_key=True, nullable=False),
            Column('name', String(), table=<users>),
            Column('fullname', String(), table=<users>),
            Column('password', String(), table=<users>), schema=None)

• 使用MetaData来创建数据表

>>> Base.metadata.create_all(engine) 

---

创建映射类实例
SqlAlchemy会自动帮我们给没有赋值的属性赋值为None，虽然自动提供了下面的构造器，我们可以写自己的来覆盖默认的

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

---

创建Session

• 在当前有Engine对象时创建Session工厂

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

• 在当前没有Engine对象时创建Session工厂

>>> Session = sessionmaker()
>>> Session.configure(bind=engine)  # once engine is available

• 使用Session工厂创建Session

session = Session() #在第一次对session进行使用时才会进行连接。

---

添加新对象

• 添加单个对象

>>> ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
#添加，这个操作是pendding的，会在需要的时候执行到数据库
>>> session.add(ed_user)

#查询操作flush之前的pendding，所以可以查到刚插入的结果
>>> our_user = session.query(User).filter_by(name='ed').first() 
>>> our_user
<User(name='ed', fullname='Ed Jones', password='edspassword')>

#Session可以辨别出我们刚进行插入操作的对象就是我们查询的对象，所以会相等
>>> ed_user is our_user
True

#对于Primary Key相同的总是返回的对象

• 添加多个对象

>>> session.add_all([
...     User(name='wendy', fullname='Wendy Williams', password='foobar'),
...     User(name='mary', fullname='Mary Contrary', password='xxg527'),
...     User(name='fred', fullname='Fred Flinstone', password='blah')])

#更改其中的某条数据
>>> ed_user.password = 'f8s7ccs'

#Session对象可以发现你的更改
>>> session.dirty
IdentitySet([<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>])

#目前有三个pendding数据
>>> session.new  
IdentitySet([<User(name='wendy', fullname='Wendy Williams', password='foobar')>,
<User(name='mary', fullname='Mary Contrary', password='xxg527')>,
<User(name='fred', fullname='Fred Flinstone', password='blah')>])

#告诉Session立刻提交
>>> session.commit()
#提交后Session甬到的连接回到连接池了，接下来的操作会获取一个新的连接池

#提交的数据中一些数据也会被更新
>>> ed_user.id 
1

---

回滚

#更改一些数据
>>> ed_user.name = 'Edwardo'

#添加一条新数据
>>> fake_user = User(name='fakeuser', fullname='Invalid', password='12345')
>>> session.add(fake_user)

#查询已经可以看到结果
>>> session.query(User).filter(User.name.in_(['Edwardo', 'fakeuser'])).all() 
[<User(name='Edwardo', fullname='Ed Jones', password='f8s7ccs')>, <User(user='fakeuser', fullname='Invalid', password='12345')>]

#回滚，一切恢复了
>>> session.rollback()
>>> ed_user.name 
u'ed'
>>> fake_user in session
False

---

查询

#通过User类查询
>>> 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 Flinstone

#通过User类的列组合查询，返回Tuple
>>> for name, fullname in session.query(User.name, User.fullname): 
...     print name, fullname
ed Ed Jones
wendy Wendy Williams
mary Mary Contrary
fred Fred Flinstone

#混合查询，返回Tuple
>>> for row in session.query(User, User.name).all(): 
...    print row.User, row.name
<User(name='ed', fullname='Ed Jones', password='f8s7ccs')> ed
<User(name='wendy', fullname='Wendy Williams', password='foobar')> wendy
<User(name='mary', fullname='Mary Contrary', password='xxg527')> mary
<User(name='fred', fullname='Fred Flinstone', password='blah')> fred

#制定列别名
>>> from sqlalchemy.orm import aliased
>>> user_alias = aliased(User, name='user_alias')

SQL>>> for row in session.query(user_alias, user_alias.name).all(): 
...    print row.user_alias
<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>
<User(name='wendy', fullname='Wendy Williams', password='foobar')>
<User(name='mary', fullname='Mary Contrary', password='xxg527')>
<User(name='fred', fullname='Fred Flinstone', password='blah')>

#LIMIT 和 OFFSET语句用切片表示
>>> for u in session.query(User).order_by(User.id)[1:3]: 
...    print u
<User(name='wendy', fullname='Wendy Williams', password='foobar')>
<User(name='mary', fullname='Mary Contrary', password='xxg527')>

#使用filter_by()来实现where语句，关键字参数
>>> for name, in session.query(User.name).\
...             filter_by(fullname='Ed Jones'): 
...    print name
ed

#使用filter()来实现where语句，应用Python操作符和映射类属性
>>> for name, in session.query(User.name).\
...             filter(User.fullname=='Ed Jones'): 
...    print name
ed

Query结果可以链式调用，这将返回一个新的应用了指定规则的Query对象，链式filter相当于and
>>> for user in session.query(User).\
...          filter(User.name=='ed').\
...          filter(User.fullname=='Ed Jones'): 
...    print user
<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>

---

常用的过滤操作符

• equals:

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

• not equals:

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

• LIKE:

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

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

• OR:

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

• MATCH:

#match要求数据库需要有MATCH或者CONTAINS函数
query.filter(User.name.match('wendy'))

---

query返回的List和标量

• all()返回列表

>>> 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()返回第一个结果

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

• one()如果只有一个结果则返回该结果，如果有多个结果则抛出MultipleResultsFount异常，如果没有结果则抛出NoResultFound异常

>>> from sqlalchemy.orm.exc import MultipleResultsFound
>>> try: 
...     user = query.one()
... except MultipleResultsFound, e:
...     print e
Multiple rows were found for one()

>>> from sqlalchemy.orm.exc import NoResultFound
>>> try: 
...     user = query.filter(User.id == 99).one()
... except NoResultFound, e:
...     print e
No row was found for one()

• scalar() 其调用one()方法，根据是否成功返回第一行第一列

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

---

使用字面上的SQL语句

• 可以使用text包裹sql语句

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

• 还可以使用占位符和参数，以及使用原生SQL查询语句

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

>>> session.query(User).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all()
[<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>]

---

计数

以下计数的SQL原理是做了一次子查询，如果想要避免子查询，可以使用func.count()方法

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

使用func.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')]

使用func.count()方法表示SQL语句中的count(*)

#因为必须要提供表信息，所以要增加select_from方法，与select count(*) from table呼应
>>> session.query(func.count('*')).select_from(User).scalar() 
4
#因为提供了主键lie，所以表可以推断出来。
>>> session.query(func.count(User.id)).scalar() 
4