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 sqlalchemy orm

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

python sqlalchemy对oracle数据库的ORM反射

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

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

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

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

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

使用Python数据库ORM SQLAlchemy

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

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

本节内容 ORM介绍 sqlalchemy安装 sqlalchemy基本使用 多外键关联 多对多关系 表结构设计作业   一、ORM介绍如果写程序用pymysql和程序交互,那是不是要写原生sql语...
  • fgf00
  • fgf00
  • 2016-10-27 21:11
  • 5130

Python SQLAlchemy ORM示例

本文记录了使用SQLAlchemy实现常见的CRUD操作,后续会继续记录使用中踩到的坑和一些使用技巧。SQLAlchemy 在构建在 WSGI 规范上的Python Web 框架中得到了广泛应用,在我...

SQLAlchemy 笔记 ORM方式访问数据库

SQLAlchemy 笔记 ORM方式访问数据库山下狮子 发布于 1年前,共有 0 条评论原文链接下载安装看看安装成功了吗import sqlalchemy print sqlalchemy.__ve...

sqlalchemy orm使用指南

本教程只讲述sqlalchemy的ORM使用方式,手写sql的使用基本上和python-mysqldb没区别。 使用环境:部署了openstack的虚拟机。否则请自行安装sqlalchemy所需的各...

sqlalchemy系列(2) orm相关简易操作

sqlalchemy中的orm让我们可以很方便的采用类似python对象的方式去操作数据库,非常的方便shi'yi
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

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