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