Basic Primer _python-modules and packages. Deep SQLAlchemy column-level constraints vs. table-level constraints?

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

To create an engine:

1. Before connecting to the database, the engine needs to be created as an interface to execute SQL, in fact the bottom layer through pool (connection pool) and dialect (translator) to convert the mapped PY statement to the corresponding DB-API supported native SQL statements to execute, so write PY does not care about the backend database

2. Pool will only be established if the connection is dependent and passive call, otherwise the creation engine will only create the dialect translator by default, and simply say that the connection created by engine and back-end database is always inert and passively created.

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/89/BD/wKioL1gbU3aDmPJdAABcUHRg_lc191.png "title=" Clipboard.png "alt=" Wkiol1gbu3admpjdaabcuhrg_lc191.png "/>

# connection string

#!/usr/bin/env python# -*- coding: utf-8 -*-"" "## authors: limanman#  oschina: http://xmdevops.blog.51cto.com/# purpose:# "" "#  Description:  Import public module From sqlalchemy  import create_engine#  Description:  Import other modules if __name__ ==  ' __main__ ':      "" "    sqlite:///data.db                           :  Relative to current directory lookup     sqlite:////var/run/data.db                  :  find     sqlite:///c under Absolute/var/run :\\users\\administrator\data.db :  Absolute C:\\users\\administrator under Search      "" "     engine = create_engine (' sqlite:///data.db ',  echo=true)      #  note:  to avoid garbled charseT=utf8 Force Settings     engine = create_engine (' mysql://username:[email protected]: hostport/'                              ' Database?charset=utf8 ',  echo=true,  pool_recycle=3600)

Note: As an example of two commonly used databases, when creating an engine, the parameter echo=true is the printing process, the default MySQL connection is idle for more than 8 hours will automatically shut down, pool_recycle=3600 shorten the time to 1 hours, the connection pool will automatically clean up the idle connection

Trace

Note: To go to the SQLAlchemy package __init__.py file, you can see that the default import is a. Sql/.types/.schema with core support, so we can use the code directly through the From SQLAlchemy import ...,..., ... Import, and go to the corresponding definition with very detailed usage scenarios and use case demos.

. types	Array,bigint,binary,blob,boolean,biginteger,binary,boolean,char,clob,date,datetime,decimal,date,datetime,enum, Float,float,int,integer,integer,interval,json,largebinary,nchar,nvarchar,numeric,numeric,pickletype,real, Smallint,smallinteger,string,text,time,timestamp,text,time,typedecorator,unicode,unicodetext,varbinary,varchar ,
. Schema	Checkconstraint,column,columndefault,constraint,defaultclause,fetchedvalue,foreignkey,foreignkeyconstraint, INDEX,METADATA,PASSIVEDEFAULT,PRIMARYKEYCONSTRAINT,SEQUENCE,TABLE,THREADLOCALMETADATA,UNIQUECONSTRAINT,DDL, Blank_schema

# Meta Data storage

Description: Metadata is primarily used to store data that describes various properties of table-related data in order to provide fast access to the database structure, so you first need to initialize a metadata object before defining the table structure

#!/usr/bin/env python#-*-coding:utf-8-*-"" "# # authors:limanman# oschina:http://xmdevops.blog.51cto.com/# Purpose:# "" "# Description: Import public module from sqlalchemy import metadata# Description: Import other modules if __name__ = = ' __main__ ': MetaData = MetaData ()

# Defining Table Objects

Description: By invoking the . Table under Schema Instantiates a Table object, the first parameter acts as the corresponding table name in the database, the second parameter acts as the metadata object for the record table-related property, and the other parameter as the Column object in the table structure. The Column object is also called by the. Schema to instantiate a Table object, the first parameter as the corresponding column name in the table, the second parameter as the column type, and other parameters as special column flags

#!/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 import create_engine, metadata, table,  column, integer, string, datetime, numeric#  Instructions:  Import other modules from constants  import username, password, hostname, hostport, database, charsset,  alldburiif __name__ ==  ' __main__ ':    #  first step:  Create engine Object      engine = create_engine (alldburi, echo=true, pool_recycle=3600)      #  Second Step:  Create metadata Object     metadata = metadata ()      #  Step three:  Create a Table object     users = table (          ' users ',  &NBsp;metadata,        column (' id ',  integer (),  primary_key= True, autoincrement=true),         column (' name ',  String (32),  nullable=true, unique=false, default= ' anonymous '),         column (' Mail ',  string (+),  nullable=false, unique=true, index=true),         column (' Salary ',  numeric (12, 2),  nullable=true, unique =false),         column (' Update ',  datetime (),  default= datetime.now, onupdate=datetime.now, index=true)     )     #   Fourth step:  using the engine to create the table     metadata.create_all (engine)

Note: As is the general procedure for building a table, for different column types, you can add different extension parameters, Primary_key=true is expressed as whether the primary key, Autoincrement=true indicates whether the key ID is automatically growing, nullable= True indicates whether it is empty, unique=false indicates whether it is unique, index=true indicates whether the index is indexed to speed up the query, Default=datetime.now represents the default value, onupdate= DateTime.Now indicates that any one of the column value updates will trigger the reset of this column

Note: As above is a regular table-building procedure, column initialization creates the constraint of the columns level through primary_key=true/unique=true, and can only constrain the single column, and will not be constrained because you set the same constraint on two columns, each constraint will only constrain its own value

# index KEY constraints

Description: Keys and constraints to ensure that our data meets certain requirements before being stored in the database, often with index index/primarykeyconstraint PRIMARY KEY constraint/uniqueconstraint UNIQUE constraint/ ForeignKeyConstraint FOREIGN KEY constraint

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/89/BD/wKioL1gbU5ryWuIOAABr6KNEdwA568.png "title=" Clipboard.png "alt=" Wkiol1gbu5rywuioaabr6knedwa568.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 import  (create_engine, metadata, table,  column, integer, string, datetime, numeric, column,                          index, primarykeyconstraint, uniqueconstraint, foreignkeyconstraint) #  Description:  Importing other modules from constants import username, password, hostname, hostport,  database, charsset, alldburiif __name__ ==  ' __main__ ':    #  The first step:  creating the Engine object     engine = create_engine (Alldburi, echo=true, pool_ recycle=3600)    &NBsp;#  The second step:  create the Metadata Object     metadata = metadata ()     #   Step three:  create a Table object     users = table (          ' users ',  metadata,        column (' id ',  integer () ,  autoincrement=true),         column (' Customer_number ',  Integer (),  autoincrement=true),         column (' username ',  String ( nullable=false),         column (' email_address ',  String (255),  nullable=false),         column (' phone ',  String ( ,  nullable=false),         column (' Password ',  string (25),  nullable=false),         column (' created_on ',  DateTime (),  default=datetime.now),         column (' updated_on ',  datetime (),  default= Datetime.now, onupdate=datetime.now),         primarykeyconstraint (' id '),         uniqueconstraint (' username '),    )     orders = table (         ' orders ',  metadata,        column (' id ',  integer (),  autoincrement =true),         column (' user_id ',  integer (),  nullable=False),         primarykeyconstraint (' id '),         foreignkeyconstraint (             [' user_id '],            [' users.id '],         )     )     line_items = table (          ' Line_items ',  metadata,        column (' ID ',  integer (),  autoincrement=true),         column (' Quantity ',  integer ()),         column (' Extended_cost ',  Numeric (12,  2)),         column (' order_id ',  integer (),  nullable= False),         column (' cookie_id ',  integer (),  nullable=False ),         primarykeyconstraint (' id '),         foreignkeyconstraint (             [' order_id '],            [' orders.id '],         ),         foreignkeyconstraint (             [' cookie_id '],             [' cookies.id '],        ),     )     cookies = table (          ' Cookies ',  metadata,        column (' id ',  integer (),  autoincrement=true),         column (' Cookie_name ',  String (50 )),         column (' Cookie_recipe_url ',  string (255)),         column (' Cookie_sku ',  string ()),         column (' Quantity ',  integer ()),         column (' Unit_ Cost ',  numeric (12,  2)),         index (none,  ' cookie_name '),         primarykeyconstraint (' id '),    )     #  Fourth Step :  using the engine to create the table     metadata.create_all

Note: As is the general procedure for building a table, index keys and constraints can be added not only by the corresponding column class initialization parameters in the construction of the table, but also by the corresponding table class initialization parameters, but also through the table instance after creating the table Append_constraint () method to dynamically add, very flexible

Extension: The first parameter of index is a normal index name, and the index name of Primarykeyconstraint/uniqueconstraint/foreignkeyconstraint is specified by name, and the constraint created is based on the table level. Supports simultaneous multiple constraints

Note: The first two parameters of the ForeignKeyConstraint must be a sequence, and the element within a sequence must be a column name that already exists, if you want to create a table-level constraint (while creating constraints on multiple columns), the default second parameter must be the inner element table name of the table. Otherwise, there's no difference between creating a single and column-level constraint.

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

Basic Primer _python-modules and packages. Deep SQLAlchemy column-level constraints vs. table-level constraints?