Python - sqlalchemy Orm

爪子又疼了，懒得写了

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from sqlalchemy import Column, String, Integer, ForeignKey,create_engine
from sqlalchemy.ext.declarative import declarative_base

Base = 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 Operators

Here’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'))
Note

match() 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>