集结号捕鱼送分 当前位置:首页>集结号捕鱼送分>正文

集结号捕鱼送分

发布时间:2019-02-22

原标题:python可用ORM之Pony

python可用ORM之Pony


Pony是Python的一种ORM,它允许使用生成器表达式来构造查询,通过将生成器表达式的抽象语法树解析成SQL语句。它也有在线ER图编辑器可以帮助你创建Model。

示例分析

Pony语句:

select(p for p in Person if p.age > 20)

翻译成sql语句就是:

SELECT p.id, p.name, p.age, p.classtype, p.mentor, p.gpa, p.degree

FROM person p

WHERE p.classtype IN ("Student", "Professor", "Person")

AND p.age > 20

Pony语句:

select(c for c in Customer
         if sum(c.orders.price) > 1000)

翻译成sql语句就是:

SELECT "c"."id"
FROM "Customer" "c"
  LEFT JOIN "Order" "order-1"
    ON "c"."id" = "order-1"."customer"
GROUP BY "c"."id"
HAVING coalesce(SUM("order-1"."total_price"), 0) > 1000

安装Pony

pip install pony

使用Pony

#!/usr/bin/env python
#-*- coding:utf-8 -*-

import datetime
import pony.orm as pny
import sqlite3

# conn = sqlite3.connect("D:日常python学习PY2Pony学习music.sqlite")
# print conn

# database = pny.Database()
# database.bind("sqlite","music.sqlite",create_db=True)

# 路径建议写绝对路径。我这边开始写相对路径报错 unable to open database file
database = pny.Database("sqlite","D:日常python学习PY2Pony学习music.sqlite",create_db=True)

########################################################################
class Artist(database.Entity):
    """
    Pony ORM model of the Artist table
    """
    name = pny.Required(unicode)
    #被外键关联
    albums = pny.Set("Album")

########################################################################
class Album(database.Entity):
    """
    Pony ORM model of album table
    """
    #外键字段artlist,外键关联表Artist,Artist表必须写Set表示被外键关联
    #这个外键字段默认就是index=True,除非自己指定index=False才不会创建索引,索引名默认为[idx_表名__字段](artist)
    artist = pny.Required(Artist)
    title = pny.Required(unicode)
    release_date = pny.Required(datetime.date)
    publisher = pny.Required(unicode)
    media_type = pny.Required(unicode)

# turn on debug mode
pny.sql_debug(True)      # 显示debug信息(sql语句)

# map the models to the database
# and create the tables, if they don"t exist
database.generate_mapping(create_tables=True)       # 如果数据库表没有创建表

运行之后生成sqlite如下:

上述代码对应的sqlite语句是:

GET CONNECTION FROM THE LOCAL POOL
PRAGMA foreign_keys = false
BEGIN IMMEDIATE TRANSACTION
CREATE TABLE "Artist" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "name" TEXT NOT NULL
)
 
CREATE TABLE "Album" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "artist" INTEGER NOT NULL REFERENCES "Artist" ("id"),
  "title" TEXT NOT NULL,
  "release_date" DATE NOT NULL,
  "publisher" TEXT NOT NULL,
  "media_type" TEXT NOT NULL
)
 
CREATE INDEX "idx_album__artist" ON "Album" ("artist")
 
SELECT "Album"."id", "Album"."artist", "Album"."title", "Album"."release_date", "Album"."publisher", "Album"."media_type"
FROM "Album" "Album"
WHERE 0 = 1
 
SELECT "Artist"."id", "Artist"."name"
FROM "Artist" "Artist"
WHERE 0 = 1
 
COMMIT
PRAGMA foreign_keys = true
CLOSE CONNECTION

插入/增加数据

详情见:https://github.com/flowpig/daily_demos

#!/usr/bin/env python
#-*- coding:utf-8 -*-

import datetime
import pony.orm as pny
from models import Album, Artist
from database import PonyDatabase


# ----------------------------------------------------------------------
@pny.db_session
def add_data():
    """"""

    new_artist = Artist(name=u"Newsboys")
    bands = [u"MXPX", u"Kutless", u"Thousand Foot Krutch"]
    for band in bands:
        artist = Artist(name=band)

    album = Album(artist=new_artist,
                  title=u"Read All About It",
                  release_date=datetime.date(1988, 12, 01),
                  publisher=u"Refuge",
                  media_type=u"CD")

    albums = [{"artist": new_artist,
               "title": "Hell is for Wimps",
               "release_date": datetime.date(1990, 07, 31),
               "publisher": "Sparrow",
               "media_type": "CD"
               },
              {"artist": new_artist,
               "title": "Love Liberty Disco",
               "release_date": datetime.date(1999, 11, 16),
               "publisher": "Sparrow",
               "media_type": "CD"
               },
              {"artist": new_artist,
               "title": "Thrive",
               "release_date": datetime.date(2002, 03, 26),
               "publisher": "Sparrow",
               "media_type": "CD"}
              ]

    for album in albums:
        a = Album(**album)


if __name__ == "__main__":
    db = PonyDatabase()
    db.bind("sqlite", "D:日常python学习PY2Pony学习music.sqlite", create_db=True)
    db.generate_mapping(create_tables=True)


    add_data()

    # use db_session as a context manager
    with pny.db_session:
        a = Artist(name="Skillet")


"""
您会注意到我们需要使用一个装饰器db_session来处理数据库。 
它负责打开连接,提交数据并关闭连接。 你也可以把它作为一个上
下文管理器,with pny.db_session
"""

更新数据

#!/usr/bin/env python
#-*- coding:utf-8 -*-

import pony.orm as pny

from models import Artist, Album
from database import PonyDatabase

db = PonyDatabase()
db.bind("sqlite", "D:日常python学习PY2Pony学习music.sqlite", create_db=True)
db.generate_mapping(create_tables=True)


with pny.db_session:
    band = Artist.get(name="Newsboys")
    print band.name

    for record in band.albums:
        print record.title

    # update a record
    band_name = Artist.get(name="Kutless")
    band_name.name = "Beach Boys"
    
    #使用生成器形式查询
    """
    result = pny.select(i.name for i in Artist)
    result.show()
    
    结果:
    i.name              
    --------------------
    Newsboys            
    MXPX                
    Beach Boys          
    Thousand Foot Krutch
    Skillet             

    """

删除记录

import pony.orm as pny
 
from models import Artist
 
with pny.db_session:
    band = Artist.get(name="MXPX")
    band.delete()

Pony补充

可以连接的数据库:

##postgres

db.bind("postgres", user="", password="", host="", database="")

##sqlite         create_db:如果数据库不存在创建数据库文件

db.bind("sqlite", "filename", create_db=True)

##mysql

db.bind("mysql", host="", user="", passwd="", db="")

##Oracle

db.bind("oracle", "user/password@dsn")

Entity(实体)类似mvc里面的model

在创建实体实例之前,需要将实体映射到数据库表,生成映射后,可以通过实体查询数据库并创建新的实例。db.Entity自己定义新的实体必须从db.Entity继承

属性

class Customer(db.Entity):
    name = Required(str)
    picture = Optional(buffer)

sql_debug(True)  # 显示debug信息(sql语句)
db.generate_mapping(create_tables=True)  # 如果数据库表没有创建表

属性类型

  • Required
  • Optional
  • PrimaryKey
  • Set

Required and Optional

通常实体属性分为Required(必选)和Optional(可选)

PrimaryKey(主键)

默认每个实体都有一个主键,默认添加了id=PrimaryKey(int,auto=True)属性

class Product(db.Entity):
    name = Required(str, unique=True)
    price = Required(Decimal)
    description = Optional(str)
    
#等价于下面

class Product(db.Entity):
    id = PrimaryKey(int, auto=True)
    name = Required(str, unique=True)
    price = Required(Decimal)
    description = Optional(str)

Set

定义了一对一,一对多,多对多等数据结构

# 一对一
class User(db.Entity):
    name = Required(str)
    cart = Optional("Cart") #必须Optional-Required or Optional-Optional

class Cart(db.Entity):
    user = Required("User")
    
# 多对多
class Student(db.Entity):
    name = pny.Required(str)
    courses = pny.Set("Course")

class Course(db.Entity):
    name = pny.Required(str)
    semester = pny.Required(int)
    students = pny.Set(Student)
    pny.PrimaryKey(name, semester)      #联合主键

pny.sql_debug(True)      # 显示debug信息(sql语句)
db.generate_mapping(create_tables=True)     # 如果数据库表没有创建表
#-------------------------------------------------------
#一对多
class Artist(database.Entity):
    """
    Pony ORM model of the Artist table
    """
    name = pny.Required(unicode)
    #被外键关联
    albums = pny.Set("Album")

class Album(database.Entity):
    """
    Pony ORM model of album table
    """
    #外键字段artlist,外键关联表Artist,Artist表必须写Set表示被外键关联
    #这个外键字段默认就是index=True,除非自己指定index=False才不会创建索引,索引名默认为[idx_表名__字段](artist)
    artist = pny.Required(Artist)       #外键字段(数据库显示artist)
    title = pny.Required(unicode)
    release_date = pny.Required(datetime.date)
    publisher = pny.Required(unicode)
    media_type = pny.Required(unicode)

# Compositeindexes(复合索引)
class Example1(db.Entity):
    a = Required(str)
    b = Optional(int)
    composite_index(a, b)
    #也可以使用字符串composite_index(a, "b")

属性数据类型

格式为 :

属性名 = 属性类型(数据类型)

  • str
  • unicode
  • int
  • float
  • Decimal
  • datetime
  • date
  • time
  • timedelta
  • bool
  • buffer ---used for binary data in Python 2 and 3
  • bytes ---used for binary data in Python 3
  • LongStr ---used for large strings
  • LongUnicode ---used for large strings
  • UUID

    attr1 = Required(str)
    # 等价
    attr2 = Required(unicode)
    
    attr3 = Required(LongStr)
    # 等价
    attr4 = Required(LongUnicode)
    
    attr1 = Required(buffer) # Python 2 and 3
    
    attr2 = Required(bytes) # Python 3 only
    
    #字符串长度,不写默认为255
    name = Required(str,40)     #VARCHAR(40)
    
    #整数的大小,默认32bit
    attr1 = Required(int, size=8)   # 8 bit - TINYINT in MySQL
    attr2 = Required(int, size=16)  # 16 bit - SMALLINT in MySQL
    attr3 = Required(int, size=24)  # 24 bit - MEDIUMINT in MySQL
    attr4 = Required(int, size=32)  # 32 bit - INTEGER in MySQL
    attr5 = Required(int, size=64)  # 64 bit - BIGINT in MySQL
    
    #无符号整型
    attr1 = Required(int, size=8, unsigned=True) # TINYINT UNSIGNED in MySQL
    
    # 小数和精度
    price = Required(Decimal, 10, 2)        #DECIMAL(10,2)
    
    # 时间
    dt = Required(datetime,6)
    
    # 其它参数
    unique   是否唯一
    auto    是否自增
    default     默认值
    sql_default 
    created_at = Required(datetime, sql_default=’CURRENT_TIMESTAMP’)
    index   创建索引
    index="index_name"  指定索引名称
    lazy    延迟加载的属性加载对象
    cascade_delete      关联删除对象
    column      映射到数据库的列名
    columns Set(多对多列名)
    table   多对多中间表的表名字
    nullable    允许该列为空
    py_check    可以指定一个函数,检查数据是否合法和修改数据
    
    class Student(db.Entity): 
        name = Required(str) 
        gpa = Required(float, py_check=lambda val: val >= 0 and val <= 5)

实例操作

# 获取实例

p = Person.get(name="Person")   #返回单个实例,如同
Django ORM的get
#------------------------------
# 查询
persons = Person.select()
"""
select并没有连接数据库查询,只是返回一个Query object,调用persons[:]返回所有Person实例
"""

# limit
persons [1:5]

# show
persons.show()

# 生成器表达式查询,然后解析AST树的方式构造SQL语句

select(p for p in Person) 
#和Person.select()一样返回Query object

select((p.id, p.name) for p in Person)[:]

# 带where条件查询
select((p.id, p.name) for p in Person if p.age ==20)[:]

# 分组聚合查询
select((max(p.age)) for p in Person)[:] #[25]

max(p.age for p in Person) #25

select(p.age for p in Person).max() #25
#-----------------------------
# 修改实例
@db_session
def update_persons():
  p = Person.get(id=2)
  p.page = 1000
  commit()
  
# 删除
@db_session
def delete_persons():
    p = Person.get(id=2)
    p.delete()
    commit()

pony使用还可以使用游标操作(这样就可以写原生sql语句了)

result = db.execute("""select name from Artist""")
print result.fetchall()

类似Django ORM的save函数

before_insert()

Is called only for newly created objects before it is inserted into the database.

before_update()

Is called for entity instances before updating the instance in the database.

before_delete()

Is called before deletion the entity instance in the database.

after_insert()

Is called after the row is inserted into the database.

after_update()

Is called after the instance updated in the database.

after_delete()

Is called after the entity instance is deleted in the database.

例如:

class Message(db.Entity):
    title = Required(str)
    content = Required(str)

    def before_insert(self):
        print("Before insert! title=%s" % self.title)

参考资料:http://www.blog.pythonlibrary.org/2014/07/21/python-101-an-intro-to-pony-orm/

当前文章:http://radiokey.biz/content/18-10-08/15314/content_2463933569.htm

发布时间:2019-02-22 05:34:47

在线网页吃鸡游戏 捕鱼兑换现金手机版本 三张牌游戏炸金花下载 盗心魔幻扑克视频教程 扑克王怎么样 微乐棋牌游戏官网 品骏快递单号查询单号 打手机麻将赢钱神器 雷电游戏中心下载 天鸿游戏德州扑克

71415 18604 40759 27821 65403 1266097722 83083 33932

责任编辑:龙平安

随机推荐