Python - sqlalchemy Orm

原创 2015年07月06日 21:04:00

爪子又疼了,懒得写了


#!/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>

Python的数据库ORM框架:SQLAlchemy

SQLAlchemy是Python编程语言下的一款开源软件。提供了SQL工具包及对象关系映射(ORM)工具,使用MIT许可证发行。 SQLAlchemy“采用简单的Python语言,为高效和高性能的...
  • joeyon
  • joeyon
  • 2014年12月18日 09:33
  • 697

Python数据库ORM SQLAlchemy 0.7学习笔记(4) 添加对象

1. 添加一个新对象 前面介绍了映射到实体表的映射类User,如果我们想将其持久化(Persist),那么就需要将这个由User类建立的对象实例添加到我们先前创建的Session会话实例中: ...

python里比较流行的ORM框架:sqlalchemy

安装http://docs.sqlalchemy.org1、安装#进入虚拟环境 #执行 ./python3 -m pip installimport sqlalchemyprint(sqlalchem...

python3 基础模块(数据库文件模块dbm、shelve、pickle,django,mysql,ORM框架——SQLAlchemy)

1.dbm模块 该模块式自带的,如果没有安装

使用Python数据库ORM SQLAlchemy

文章中的有些内容借鉴了 http://wangye.org/blog/archives/724/ http://www.zouyesheng.com/sqlalchemy.html#toc7  1>p...

Python的ORM框架SQLAlchemy使用入门(三)

信仰比理智更有才华创建表格| 插入记录| 查询from sqlalchemy import create_engine from sqlalchemy import MetaData, Column,...
  • WuLex
  • WuLex
  • 2016年09月24日 11:13
  • 574

Python的ORM框架SQLAlchemy使用入门(二)【连接MySql数据库】

众生皆苦,放下即自在。天地皆空,唯有人在其中苦。沧海桑田,人生苦短。天地之空,在于无欲,任由万物在其中自由变幻,它依然安之若泰。人生之苦,皆因无力回天却又耿耿于怀。人本是人,只有承认自己不是万能之主,...
  • WuLex
  • WuLex
  • 2016年09月20日 17:14
  • 775

python sqlalchemy orm

ORM 和 Core相比:ORM主要是关注用户定义的对象,而Core更关注的是Schema,创建一个metadata容器,然后声明一个表对象和该容器进行关联。在ORM中我们会定义一个类去继承一个父类:...

python sqlalchemy对oracle数据库的ORM反射

一直在想程序调DB的表操作能不能简单一些,以前用的java,感觉hibernate,jpa什么的太复杂了,简单做一点点东西,要你把框架先了解一遍,jar包下一堆,配置文件先纠结一顿,然后... 你的热...
  • WHACKW
  • WHACKW
  • 2015年06月23日 19:59
  • 618

python(十二)下:ORM框架SQLAlchemy使用学习

本节内容 ORM介绍 sqlalchemy安装 sqlalchemy基本使用 多外键关联 多对多关系 表结构设计作业   一、ORM介绍如果写程序用pymysql和程序交互,那是不是要写原生sql语...
  • fgf00
  • fgf00
  • 2016年10月27日 21:11
  • 6576
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Python - sqlalchemy Orm
举报原因:
原因补充:

(最多只允许输入30个字)