SqlAlchemy是Python下的一个成熟的ORM框架。下面我们对他的使用做一个简略的介绍。
0.安装
如果有pip，使用pip安装，更便捷。pip install sqlalchemy
也可以下载包安装。python setup.py install
1.连接配置
>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///:memory:', echo=True)
MySql的连库字符串：mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
2.模型配置
>>> from sqlalchemy.ext.declarative import declarative_base
>>> Base = declarative_base()
>>> 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) 创建一个模型的实例：
>>> ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
>>> ed_user.name
'ed'
>>> ed_user.password
'edspassword'
>>> str(ed_user.id)
'None'
3.数据查询
>>> from sqlalchemy.orm import sessionmaker
#两种建立session的方法：
>>> Session = sessionmaker(bind=engine)
#或者
>>> Session = sessionmaker()
>>> Session.configure(bind=engine) # once engine is available
3.1 普通查询
>>>session.query(user_alias, user_alias.name).all()/one()/first()
>>>session.query(user_alias, user_alias.name).filter(User.fullname=='Ed Jones').filter(User.age [>,<,==,>=,<==]25).all()
>>>query.filter(User.name == 'ed')
>>>query.filter(User.name != 'ed')
>>>query.filter(User.name.like('%ed%'))
>>>query.filter(User.name.in_(['ed', 'wendy', 'jack']))
>>>query.filter(~User.name.in_(['ed', 'wendy', 'jack']))
>>>query.filter(User.name == None)
>>>query.filter(User.name.is_(None))
>>>query.filter(User.name != None)
>>>query.filter(User.name.isnot(None))
>>>from sqlalchemy import and_
>>>query.filter(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')
>>>from sqlalchemy import or_
>>>query.filter(or_(User.name == 'ed', User.name == 'wendy'))
>>>query.filter(User.name.match('wendy'))
3.2关联查询
用到时候再补上。
4.更改提交