$ egrep '^class ' ~/.pyenv/versions/3.6.0/lib/python3.6/site-packages/sqlalchemy/sql/sqltypes.py class _LookupExpressionAdapter(object): class Concatenable(object): class Indexable(object): class String(Concatenable, TypeEngine): class Text(String): class Unicode(String): class UnicodeText(Text): class Integer(_LookupExpressionAdapter, TypeEngine): class SmallInteger(Integer): class BigInteger(Integer): class Numeric(_LookupExpressionAdapter, TypeEngine): class Float(Numeric): class DateTime(_LookupExpressionAdapter, TypeEngine): class Date(_LookupExpressionAdapter, TypeEngine): class Time(_LookupExpressionAdapter, TypeEngine): class _Binary(TypeEngine): class LargeBinary(_Binary): class Binary(LargeBinary): class SchemaType(SchemaEventTarget): class Enum(Emulated, String, SchemaType): class PickleType(TypeDecorator): class Boolean(Emulated, TypeEngine, SchemaType): class _AbstractInterval(_LookupExpressionAdapter, TypeEngine): class Interval(Emulated, _AbstractInterval, TypeDecorator): class JSON(Indexable, TypeEngine): class ARRAY(SchemaEventTarget, Indexable, Concatenable, TypeEngine): class REAL(Float): class FLOAT(Float): class NUMERIC(Numeric): class DECIMAL(Numeric): class INTEGER(Integer): class SMALLINT(SmallInteger): class BIGINT(BigInteger): class TIMESTAMP(DateTime): class DATETIME(DateTime): class DATE(Date): class TIME(Time): class TEXT(Text): class CLOB(Text): class VARCHAR(String): class NVARCHAR(Unicode): class CHAR(String): class NCHAR(Unicode): class BLOB(LargeBinary): class BINARY(_Binary): class VARBINARY(_Binary): class BOOLEAN(Boolean): class NullType(TypeEngine): class MatchType(Boolean):
常见操作
我们来看看使用SQLAlchemy完成常见的操作，例如增删查改：
常见查询操作
SELECT * FROM user 应该这样写：
1 2
with get_session() as s: print(s.query(User).all())
SELECT * FROM user WHERE name='nick' 应该这样写：
1 2 3
with get_session() as s: print(s.query(User).filter_by(User.name='nick').all()) print(s.query(User).filter(User.name == 'nick').all()) # 这样写是等同效果的
SELECT * FROM user WHERE name='nick' LIMIT 1 应该这样写：
1 2
with get_session() as s: print(s.query(User).filter_by(User.name='nick').first())
SELECT * FROM user ORDER BY id DESC LIMIT 1 应该这样写：
1 2
with get_session() as s: print(s.query(User).order_by(User.id.desc()).first())
SELECT * FROM user ORDER BY id DESC LIMIT 1 OFFSET 20 应该这样写：
1 2
with get_session() as s: print(s.query(User).order_by(User.id.desc()).offset(20).first())
常见删除操作
DELETE FROM user 应该这样写：
1 2
with get_session() as s: s.query(User).delete()
DELETE FROM user WHERE name='nick'：
1 2
with get_session() as s: s.query(User).filter_by(User.name='nick').delete()
DELETE FROM user WHERE name='nick' LIMIT 1：
1 2
with get_session() as s: s.query(User).filter_by(User.name='nick').limit(1).delete()
更新语句
UPDATE user SET name='nick'：
1 2
with get_session() as s: s.query(User).update({'name': 'nick'})
UPDATE user SET name='nick' WHERE id=1：
1 2
with get_session() as s: s.query(User).filter_by(User.id=1).update({'name': 'nick'})
也可以通过更改实例的属性，然后提交：
1 2 3 4
with get_session() as s: user = s.query(User).filter_by(User.id=1).one() user.name = 'nick' s.commit()
插入语句
这个就简单了，实例化对象，然后 session.add，最后提交：
1 2 3 4
with get_session() as s: user = User() s.add(user) s.commit()
连表
SQLAlchemy 中可以直接使用join语句：
1 2
with get_session() as s: s.query(Customer).join(Invoice).filter(Invoice.amount == 8500)
可以是这么几种写法：
1 2 3 4
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
