Explains Pony usage in python ORM and pythonormpony

Explains Pony usage in python ORM and pythonormpony

Pony is an ORM of Python. It allows the use of generator expressions to construct queries and parses the abstract syntax tree of generator expressions into SQL statements. It also has an online ergraph editor to help you create a Model.

Sample Analysis

Pony statement:

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

The translated SQL statement is:

SELECT p.id, p.name, p.age, p.classtype, p.mentor, p.gpa, p.degreeFROM person pWHERE p.classtype IN ('Student', 'Professor', 'Person')AND p.age > 20

Pony statement:

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

The translated SQL statement is:

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

Install Pony

pip install pony

Use Pony

#! /Usr/bin/env python #-*-coding: UTF-8-*-import datetimeimport pony. orm as pnyimport sqlite3 # conn = sqlite3.connect ('d: \ daily python learning PY2 \ Pony learning \ music. sqlite ') # print conn # database = pny. database () # database. bind ("sqlite", "music. sqlite ", create_db = True) # absolute path is recommended. When I write a relative path, the following error occurs: unable to open database filedatabase = pny. database ("sqlite", "D: \ daily python learning PY2 \ Pony learning \ music. sqlite ", create_db = True) ######################################## ################################ class Artist (database. entity): "" Pony ORM model of the Artist table "" name = pny. required (unicode) # associate albums = pny by a foreign key. set ("Album ") ######################################## ################################ class Album (database. entity): "Pony ORM model of album table" "# foreign key field artlist, foreign key Association table Artist, in the Artist table, Set must be written to indicate that the table is associated with a foreign key. # The default value of this foreign key field is index = True. indexes are not created unless you specify index = False, the default index name is [idx _ TABLE name__ field] (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 modepny. SQL _debug (True) # display debug information (SQL statement) # map the models to the database # and create the tables, if they don't existdatabase. generate_mapping (create_tables = True) # If no table is created in the database table

Generate sqlite after running:

The sqlite statement corresponding to the above Code is:

GET CONNECTION FROM THE LOCAL POOLPRAGMA foreign_keys = falseBEGIN IMMEDIATE TRANSACTIONCREATE 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 COMMITPRAGMA foreign_keys = trueCLOSE CONNECTION

Insert/add data

Source Code address: https://github.com/flowpig/daily_demos

#! /Usr/bin/env python #-*-coding: UTF-8-*-import datetimeimport pony. orm as pnyfrom models import Album, Artistfrom database import PonyDatabase # -------------------------------------------------------------------- @ pny. db_sessiondef 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 = Al Bum (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": "S Parrow "," 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: \ daily python learning PY2 \ Pony learning \ music. sqlite ", create_db = True) db. generate_mapping (create_tables = True) add_data () # use db_session as a con Text manager with pny. db_session: a = Artist (name = "Skillet") ''' you will notice that we need a decorator db_session to process the database. It opens the connection, submits data, and closes the connection. You can also use it as a context manager, with pny. db_session '''

Update Data

#! /Usr/bin/env python #-*-coding: UTF-8-*-import pony. orm as pnyfrom models import Artist, Albumfrom database import PonyDatabasedb = PonyDatabase () db. bind ("sqlite", "D: \ daily python learning PY2 \ Pony learning \ 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" # Use the generator to query ''' result = pny. select (I. name for I in Artist) result. show () Result: I. name -------------------- Newsboys MXPX Beach Boys Thousand Foot Krutch Skillet '''

Delete record

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

Supplement Pony

Accessible databases:

# Define SDB. bind ('users', user = '', password ='', host = '', database ='') # sqlite create_db: Create a database file db if the database does not exist. bind ('sqlite ', 'filename', create_db = True) # mysqldb. bind ('mysql', host = '', user ='', passwd = '', db ='') # Oracledb. bind ('oracle ', 'user/password @ dsn ')

Entity (Entity) is similar to the model in mvc

Before creating an object instance, You need to map the object to a database table. After the ing is generated, You can query the database through the object and create a new instance. Db. Entity define new entities by yourself and must inherit from db. Entity

Attribute

Class Customer (db. entity): name = Required (str) picture = Optional (buffer) SQL _debug (True) # display debug information (SQL statement) db. generate_mapping (create_tables = True) # If no table is created in the database table

Attribute type

• Required
• Optional
• PrimaryKey
• Set
  

Required and Optional

Generally, entity attributes are divided into Required (Required) and Optional (Optional)

PrimaryKey (primary key)

By default, each object has a primary key. The id = PrimaryKey (int, auto = True) attribute is added by default.

Class Product (db. entity): name = Required (str, unique = True) price = Required (Decimal) description = Optional (str) # equivalent to the following class Product (db. entity): id = PrimaryKey (int, auto = True) name = Required (str, unique = True) price = Required (Decimal) description = Optional (str)

Set

Defines one-to-one, one-to-multiple, and many-to-many data structures

# One-to-one class User (db. entity): name = Required (str) cart = Optional ("Cart") # Required Optional-Required or Optional-Optionalclass Cart (db. entity): user = Required ("User") # many-to-many 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) # federated primary key pny. SQL _debug (True) # display debug information (SQL statement) db. generate_mapping (create_tables = True) # If no table is created for the database table # --------------------------------------------------- # One-to-multiple class Artist (database. entity): "" Pony ORM model of the Artist table "" name = pny. required (unicode) # associate albums = pny by a foreign key. set ("Album") class Album (database. entity): "Pony ORM model of album table" "# foreign key field artlist, foreign key Association table Artist, in the Artist table, Set must be written to indicate that the table is associated with a foreign key. # The default value of this foreign key field is index = True. indexes are not created unless you specify index = False, the default index name is [idx _ TABLE name__ field] (artist) artist = pny. required (Artist) # foreign key field (the database displays artist) title = pny. required (unicode) release_date = pny. required (datetime. date) publisher = pny. required (unicode) media_type = pny. required (unicode) # Compositeindexes (Composite Index) class Example1 (db. entity): a = Required (str) B = Optional (int) composite_index (a, B) # You can also use the string composite_index (a, 'B ')

Attribute Data Type

Format:

Property name = property type (data type)

• 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) # equivalent attr2 = Required (unicode) attr3 = Required (LongStr) # equivalent attr4 = Required (LongUnicode) attr1 = Required (buffer) # Python 2 and 3attr2 = Required (bytes) # Python 3 only # String Length. If this parameter is left blank, the default value is 255 name = Required (str, 40) # VARCHAR (40) # integer size, 2bitattr1 = Required (int, size = 8) #8 bit-TINYINT in MySQLattr2 = Required (int, size = 16) #16 bit-SMALLINT in MySQLattr3 = Required (int, size = 8, size = 24) #24 bit-MEDIUMINT in MySQLattr4 = Required (int, size = 32) #32 bit-INTEGER in MySQLattr5 = Required (int, size = 64) #64 bit-BIGINT in MySQL # unsigned integer attr1 = Required (int, size = 8, UNSIGNED = True) # TINYINT unsigned in MySQL # Decimal and precision price = Required (Decimal, 10, 2) # DECIMAL () # time dt = Required (datetime, 6) # other parameters unique whether unique auto default SQL _default created_at = Required (datetime, SQL _default = 'current _ timestamp ') index create index = 'index _ name' specify index name lazy delayed loading attribute loading object cascade_delete Association deletion object column ing to database column name columns Set (many-to-many column names) the table name nullable allows this column to be empty. py_check can specify a function to check whether the data is valid and modify the data class Student (db. entity): name = Required (str) gpa = Required (float, py_check = lambda val: val> = 0 and val <= 5)

Instance operations

# Obtain instance p = Person. get (name = "Person") # returns a single instance, just like the get # ---------------------------- of Django ORM # queries persons = Person. select () ''' select does not connect to the database for Query, but returns a Query object. Calls persons [:] to return all Person instances ''' # limitpersons [] # showpersons. show () # builder expression query, and then parse the AST tree to construct SQL statements select (p for p in Person) # and Person. returns Query objectselect (p. id, p. name) for p in Person) [:] # 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 # --------------------------- # modify the instance @ db_sessiondef update_persons (): p = Person. get (id = 2) p. page = 1000 commit () # Delete @ db_sessiondef delete_persons (): p = Person. get (id = 2) p. delete () commit ()

Pony can also be used for cursor operations (so that native SQL statements can be written)

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

Save functions similar to Django ORM

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.

For example:

class Message(db.Entity):  title = Required(str)  content = Required(str)  def before_insert(self):    print("Before insert! title=%s" % self.title)