sqlalchemy入门记录
前言
发现翻译全文时间比较久，所以先整个简单的使用说明吧
安装SQLAlchemy
pip install sqlalchemy
查看SQLAlchemy版本
In [1]: import sqlalchemy
In [2]: sqlalchemy.__version__
Out[2]: '1.0.14'
连接数据库
create_engine(数据库://用户名:密码(没有密码则为空)@主机名:端口/数据库名',echo =True)
from sqlalchemy import create_engine
engine = create_engine('mysql://root:@localhost:3306/sqlalchemy', echo=True)
ORM
基类
In [10]: from sqlalchemy.ext.declarative import declarative_base
In [11]: Base = declarative_base()
类
In [12]: from sqlalchemy import Column, Integer, String
In [13]: class User(Base):
....: __tablename__ = 'users'
....: id = Column(Integer, primary_key=True)
....: name = Column(String(255))
....: password = Column(String(255))
创建表
In [15]: Base.metadata.create_all(engine)
2016-08-31 15:12:56,639 INFO sqlalchemy.engine.base.Engine DESCRIBE `users`
2016-08-31 15:12:56,639 INFO sqlalchemy.engine.base.Engine ()
2016-08-31 15:12:56,649 INFO sqlalchemy.engine.base.Engine ROLLBACK
2016-08-31 15:12:56,651 INFO sqlalchemy.engine.base.Engine
CREATE TABLE users (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(255),
password VARCHAR(255),
PRIMARY KEY (id)
)
2016-08-31 15:12:56,652 INFO sqlalchemy.engine.base.Engine ()
2016-08-31 15:12:56,684 INFO sqlalchemy.engine.base.Engine COMMIT
查看是否创建成功
In [17]: engine.table_names()
2016-08-31 15:14:00,572 INFO sqlalchemy.engine.base.Engine SHOW FULL TABLES FROM `sqlalchemy`
2016-08-31 15:14:00,572 INFO sqlalchemy.engine.base.Engine ()
Out[17]: [u'users']
使用Session
初始化一个Session对象
In [18]: from sqlalchemy.orm import sessionmaker
In [19]: Session = sessionmaker(bind=engine)
In [23]: session = Session()
或者
In [20]: Session = sessionmaker()
In [21]: Session.configure(bind=engine)
In [23]: session = Session()
添加／更新对象
In [22]: user_1 = User(id=1, name='wang', password='123')
In [24]: session.add(user_1)
In [26]: user_2 = User(id=2, name='qian', password='234')
In [27]: user_3 = User(id=3, name='sun', password='345')
In [28]: session.add_all([user_2, user_3])
In [29]: session.dirty
Out[29]: IdentitySet([])
In [30]: session.new
Out[30]: IdentitySet([<__main__.User object at 0x7fc6a805ad50>, <__main__.User object at 0x7fc6a805a910>, <__main__.User object at 0x7fc6a805ac90>])
In [31]: user_1.id
Out[31]: 1
In [32]: session.commit()
2016-08-31 15:26:17,798 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2016-08-31 15:26:17,799 INFO sqlalchemy.engine.base.Engine INSERT INTO users (id, name, password) VALUES (%s, %s, %s)
2016-08-31 15:26:17,800 INFO sqlalchemy.engine.base.Engine ((1, 'wang', '123'), (2, 'qian', '234'), (3, 'sun', '345'))
2016-08-31 15:26:17,802 INFO sqlalchemy.engine.base.Engine COMMIT
In [33]: user_1.password = '1234'
In [34]: session.dirty
Out[34]: IdentitySet([<__main__.User object at 0x7fc6a805a910>])
回滚（做了修改但是还未提交）
In [33]: user_1.password = '1234'
In [34]: session.dirty
Out[34]: IdentitySet([<__main__.User object at 0x7fc6a805a910>])
In [37]: session.rollback()
In [38]: session.dirty
Out[38]: IdentitySet([])
查询
In [39]: session.query(User).filter_by(name='wang')
Out[39]: <sqlalchemy.orm.query.Query at 0x7fc6a801bfd0>
In [40]: session.query(User).filter_by(name='wang').first()
2016-08-31 15:36:07,500 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2016-08-31 15:36:07,500 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.password AS users_password
FROM users
WHERE users.name = %s
LIMIT %s
2016-08-31 15:36:07,501 INFO sqlalchemy.engine.base.Engine ('wang', 1)
Out[40]: <__main__.User at 0x7fc6a805a910>
# all()
In [41]: session.query(User).filter_by(password='123').all()
2016-08-31 15:36:32,016 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.password AS users_password
FROM users
WHERE users.password = %s
2016-08-31 15:36:32,016 INFO sqlalchemy.engine.base.Engine ('123',)
Out[41]: [<__main__.User at 0x7fc6a805a910>]
In [43]: for instance in session.query(User).order_by(User.id):
print instance.id, instance.name
....:
2016-08-31 15:37:51,553 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.password AS users_password
FROM users ORDER BY users.id
2016-08-31 15:37:51,554 INFO sqlalchemy.engine.base.Engine ()
1 wang
2 qian
3 sun
# filter_by
In [52]: for name in session.query(User.name).filter_by(password='123'):
print name
....:
2016-08-31 15:47:44,062 INFO sqlalchemy.engine.base.Engine SELECT users.name AS users_name
FROM users
WHERE users.password = %s
2016-08-31 15:47:44,062 INFO sqlalchemy.engine.base.Engine ('123',)
('wang',)
('qian',)
# order_by
In [54]: for user in session.query(User).order_by(User.id)[1:3]:
print user.name
....:
2016-08-31 15:51:18,657 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.password AS users_password
FROM users ORDER BY users.id
LIMIT %s, %s
2016-08-31 15:51:18,658 INFO sqlalchemy.engine.base.Engine (1, 2)
qian
sun
查询的时候通用过滤符号
# equal
query.filter(User.name == 'ed')
# not equal
query.filter(User.name != 'ed')
# like
query.filter(User.name.like('%ed%'))
# in
query.filter(User.name.in_(['ed', 'wendy', 'jack']))
# not in
query.filter(~User.name.in_(['ed', 'wendy', 'jack']))
# add
# 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 --Make sure you use or_() and not the Python or operator!
from sqlalchemy import or_
query.filter(or_(User.name == 'ed', User.name == 'wendy'))
# match
query.filter(User.name.match('wendy'))
返回list或者scalar
all() 列表
first() scalar
one() one_or_none() scalar()
