Python - sqlalchemy Orm

爪子又疼了，懒得写了

#!/usr/bin/env python3# -*- coding: utf-8 -*-from sqlalchemy import Column, String, Integer, ForeignKey,create_enginefrom sqlalchemy.ext.declarative import declarative_baseBase = declarative_base()class UrlModel(Base):    __tablename__ = 'urls'    id = Column(Integer, primary_key=True)    realUrl = Column(String,unique=True)    # relativeUrl = Column(String,default="")    # 用来存储img的dir    title = Column(Integer,default="Img")    # 下载状态    state=Column('state',Integer,default=0)    fileName=Column(String)    def __init__(self,realUrl,title="imgs"):          self.realUrl=realUrl        self.title=title        splitPath = realUrl.split('/')        self.fileName = splitPath.pop()    # 类似java toSring    def __repr__(self):        return "%s(%r,%r,%d,%r,%r,%r)"%(self.__class__.__name__,self.id,self.realUrl,self.fileName,self.title,self.state)def initialSession(filePath):    engine = create_engine('sqlite:///'+filePath, echo=True)     from sqlalchemy.orm import sessionmaker    # Construct a sessionmaker object    session = sessionmaker()    # Bind the sessionmaker to engine    session.configure(bind=engine)    # Create all the tables in the database which are    # defined by Base's subclasses such as User    Base.metadata.create_all(engine)    return session()if __name__ == '__main__':      s=initialSession("wanimal.db")    surl="http://www.baidu.com";    # SELECT urls.id AS urls_id, urls."realUrl" AS "urls_realUrl", urls.title AS urls_title, urls."fileName" AS "urls_fileName" FROM urls WHERE urls.id = ?    # 2015-07-06 20:56:29,983 INFO sqlalchemy.engine.base.Engine (1,)    # 不要用one,找不到数据会报 NoResultFound    urlModel=s.query(UrlModel).filter(UrlModel.realUrl==surl).first()    if not urlModel:        urlModel=UrlModel(surl)        s.add(urlModel)        s.commit()    urlModel.state=1;    # 重复添加一个对象无效，自动转换为更新    s.add(urlModel)    s.commit()    urlModel.state=2;    # 更新    # 2015-07-06 20:56:29,985 INFO sqlalchemy.engine.base.Engine UPDATE urls SET state=? WHERE urls.id = ?    # 2015-07-06 20:56:29,985 INFO sqlalchemy.engine.base.Engine (2, 1)    # 2015-07-06 20:56:29,987 INFO sqlalchemy.engine.base.Engine COMMIT    s.add(urlModel);    s.commit()    # 2015-07-06 21:01:05,559 INFO sqlalchemy.engine.base.Engine DELETE FROM urls WHERE urls.id = ?    # 2015-07-06 21:01:05,559 INFO sqlalchemy.engine.base.Engine (1,)    # 2015-07-06 21:01:05,561 INFO sqlalchemy.engine.base.Engine COMMIT    s.delete(urlModel)    s.commit()

Query

Common Filter OperatorsHere’s a rundown of some of the most common operators used in filter():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:query.filter(User.name.match('wendy'))Notematch() uses a database-specific MATCH or CONTAINS function; its behavior will vary by backend and is not available on some backends such as SQLite.

Returning Lists and Scalars

A number of methods on Query immediately issue SQL and return a value containing loaded database results. Here’s a brief tour:all() returns a list:>>> 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() applies a limit of one and returns the first result as a scalar:SQL>>> query.first()<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>one(), fully fetches all rows, and if not exactly one object identity or composite row is present in the result, raises an error. With multiple rows found:SQL>>> from sqlalchemy.orm.exc import MultipleResultsFound>>> try:...     user = query.one()... except MultipleResultsFound, e:...     print eMultiple rows were found for one()With no rows found:SQL>>> from sqlalchemy.orm.exc import NoResultFound>>> try:...     user = query.filter(User.id == 99).one()... except NoResultFound, e:...     print eNo row was found for one()The one() method is great for systems that expect to handle “no items found” versus “multiple items found” differently; such as a RESTful web service, which may want to raise a “404 not found” when no results are found, but raise an application error when multiple results are found.scalar() invokes the one() method, and upon success returns the first column of the row:>>> query = session.query(User.id).filter(User.name == 'ed').\...    order_by(User.id)SQL>>> query.scalar()7来源： <http://docs.sqlalchemy.org/en/rel_1_0/orm/tutorial.html>