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_userTrue#对于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.dirtyIdentitySet([<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 sessionFalse

---

查询

#通过User类查询>>> for instance in session.query(User).order_by(User.id): ...     print instance.name, instance.fullnameed Ed Joneswendy Wendy Williamsmary Mary Contraryfred Fred Flinstone

#通过User类的列组合查询，返回Tuple>>> for name, fullname in session.query(User.name, User.fullname): ...     print name, fullnameed Ed Joneswendy Wendy Williamsmary Mary Contraryfred 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 nameed

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

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 concernquery.filter(User.name.is_(None))

• IS NOT NULL:

query.filter(User.name != None)# alternatively, if pep8/linters are a concernquery.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() callsquery.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 eMultiple rows were found for one()>>> from sqlalchemy.orm.exc import NoResultFound>>> try: ...     user = query.filter(User.id == 99).one()... except NoResultFound, e:...     print eNo 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 textSQL>>> for user in session.query(User).\...             filter(text("id<224")).\...             order_by(text("id")).all(): ...     print user.nameedwendymaryfred

• 还可以使用占位符和参数，以及使用原生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 funcSQL>>> 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