Basic Primer _python-modules and packages. In-depth SQLAlchemy sqlalchemy orm refactoring table?

Brief introduction:

Description: This module is primarily used to map a relational database table to a class of py, the row maps to an instance of the Py class, and the column maps to the properties of the Py instance, because it is compatible with many Db-api and extension, so can prioritize the data model, while ignoring the underlying DB-API switch, data migration is more convenient.

Quick installation:

Pip Install--upgrade SQLAlchemy

Define the structure:

#!/usr/bin/env python# -*- coding: utf-8 -*-"" "## authors: limanman#  oschina: http://xmdevops.blog.51cto.com/# purpose:# "" "#  Description:  Import public module from datetime  Import datetimefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy  import  (create_engine, metadata, table, column, integer, string,  datetime, numeric,                         ForeignKey, PrimaryKeyConstraint,  Foreignkeyconstraint, uniqueconstraint) #  Description:  Import other modules from constants import  username, password, hostname, hostport, database, charsset, alldburibase =  declarative_base () class cookies (base):    __tablename__ =  ' Cookies '     id =&nbsp Column (Integer (),  primary_key=true, autoincrement=true)     cookie_name =  column (String (),  index=true)     cookie_recipe_url = column (String ( 255))     cookie_sku = column (String)     quantity =  column (Integer ())     unit_cost = column (Numeric (12, 2))

Description: SQLAlchemy ORM and SQLAlchemy core are slightly different, users only focus on the upper data object definition, before we need to define the metadata container object to let the table object reference it, and in the ORM only need to define the table class inherits from the Declarative_ Base instance bases are automatically associated with table objects and metadata objects without having to focus on the underlying implementation.

Note: To define an ORM table class, you need to satisfy at least four conditions, must inherit from the Declarative_base instance, must contain a __tablename__ class attribute, the value is the table name in the corresponding database, must contain at least one or more column properties, Must have at least one or more columns as the primary key

Index constraints:

Class Cookies (Base): __tablename__ = ' cookies ' __table_args__ = (primarykeyconstraint (' ID ')) id = Column (Integer ( ), autoincrement=true) cookie_name = Column (String (), index=true) cookie_recipe_url = Column (string (255)) Cooki E_sku = column (String) quantity = Column (Integer ()) unit_cost = Column (Numeric (12, 2))

Description: Table-level constraints/indexes can be defined directly in the SQLAlchemy core, and can be easily implemented in SQLAlchemy ORM, by setting the __table_args__ class property of the table class to set multiple indexes and constraints, which can be a sequence object, Primarykeyconstraint, ForeignKeyConstraint, UniqueConstraint usage and sqlalchemy core are the same ~

Relational mappings:

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/89/DC/wKiom1gf9criMzD-AABr6KNEdwA673.png "title=" Clipboard.png "alt=" Wkiom1gf9crimzd-aabr6knedwa673.png "/>

Note: For example, a user can produce multiple orders, so create a foreign key user_id associated users table in the Orders table, and each order can contain more than one commodity, and can contain more than one final price, so the Line_ Create foreign key order_id associated orders in items, and because each item has specific details, create a foreign key cookie_id associated cookies in Line_items

#!/usr/bin/env python# -*- coding: utf-8 -*-"" "## authors: limanman#  oschina: http://xmdevops.blog.51cto.com/# purpose:# "" "#  Description:  Import public module from datetime  import datetimefrom sqlalchemy.orm import relationship, backreffrom  sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import  (Create_ Engine, column, integer, string, datetime, numeric, foreignkey) #  Description:  Importing other modules from constants import username, password, hostname, hostport,  Database, charsset, alldburibase = declarative_base () class users (base):     __tablename__ =  ' users '     id = column (Integer (),  Primary_key=true, autoincrement=true)     customer_number = column (Integer () ,  autoincrement=true)   &NBsp; username = column (String),  unique=true, nullable=false      email_address = column (String (255),  nullable=false)     phone =  column (String),  nullable=false)     password = column (String (25),  nullable=false)     created_on = column (DateTime (),  default= DateTime.Now)     updated_on = column (DateTime (),  default=datetime.now,  onupdate=datetime.now) class orders (Base):    __tablename__ =  ' Orders '     id = column (Integer (),  primary_key=true, autoincrement=true)      user_id = column (Integer (),  foreignkey (' users.id '),  nullable=false)     user = relationship (' Users ',  backref=backref (' orders ',  order_by=id )) Class lineitems (Base):  &NBsp;  __tablename__ =  ' Line_items '     id = column (Integer (),  primary_key=true, autoincrement=true)     quantity = column (Integer ())     extended_cost = column (Numeric (12, 2))     order_id  = column (Integer (),  foreignkey (' orders.id '),  nullable=false)      Cookie_id = column (Integer (),  foreignkey (' cookies.id '),  nullable=false)      order = relationship (' Orders ',  backref=backref (' Line_items ',  order_by=id))      cookies = relationship (' Cookies ',  backref=backref (' Line_item ',  uselist= False)) class cookies (Base):    __tablename__ =  ' Cookies '      id = column (Integer (),  primary_key=true, autoincrement=true)      cookie_name = Column (String (),  index=true)     cookie_recipe_url = column (String (255))     cookie_sku = column (String)     quantity =  Column (Integer ())     unit_cost = column (Numeric (12, 2)) if __name__  ==  ' __main__ ':    #  first step:  Create engine Object     engine =  create_engine (alldburi, echo=true, pool_recycle=3600)     #  second step:  Create a metadata Object     base.metadata.create_all (bind=engine)

Description: As a simple demonstration of the SQLAlchemy ORM Common build process, unlike SQLAlchemy core, it forces relationship and Backref to increase the class attributes that are directed to the associated table class. The first parameter of relationship is the name of the table class to be associated, and the common Backref is created by Backref, the first parameter is the same as the property name that supports the reverse access of the other table, the remaining relationship and Backref parameters, As above uselist=false indicates that the reverse reference is One2One mode, more parameters can refer to the source code instance lib/site-packages/sqlalchemy/orm/relationships.py

This article is from the "Li-Yun Development Road" blog, please be sure to keep this source http://xmdevops.blog.51cto.com/11144840/1870170

Basic Primer _python-modules and packages. In-depth SQLAlchemy sqlalchemy orm refactoring table?