Python Growth path 13

One: SqlAlchemy ORM

Orm:object relational Mapping Object Relational mapping is a program technology that enables the conversion of data between different types of systems in an object-oriented programming language

SQLAlchemy is an ORM framework in the Python programming language that builds on database APIs and uses relational object mappings for database operations, in short: converting objects to SQL and then executing SQL using the data API and getting execution results:

Built on SQL AQI supremacy, no need to write native SQL statements, is to encapsulate objects and turn them into native statements

Dialect is used to communicate with the data API, invoking different database APIs depending on the configuration file, enabling operations on the database, such as:

Mysql-python  #适用于python 2 using the Mysql-python module    mysql+mysqldb://<user>:<password>@
1. Create a table using SQLAlchemy + pymysql:
Import pymysqlfrom sqlalchemy import Create_engine, Table, Column, Integer, String, MetaData, foreignkeymetadata = Metadat A () #创建user和color两个表, metadata encapsulates the statement user = Table (' user ', metadata,    Column (' id ', Integer, primary_key=true),    Column (' Name ', String ()), color = Table (' Color ', metadata,     column (' id ', Integer, primary_key=true),    column (' Name ', String ()) engine = Create_engine ("Mysql+pymysql://root:[email protected] @localhost: 3306/test", Max_ overflow=5) #连接到数据库, maximum connection pool of 5 metadata.create_all (engine) #创建数据库
2. Add Data:
Import pymysqlfrom sqlalchemy import Create_engine, Table, Column, Integer, String, MetaData, foreignkeymetadata = Metadat A () user = Table (' user ', metadata,    column (' id ', Integer, primary_key=true),    column (' name ', String),) color = Table (' Color ', metadata,    column (' id ', Integer, primary_key=true),    column (' name ', String),) engine = Create_engine ("Mysql+pymysql://root:[email protected" @localhost: 3306/test ", max_overflow=5) conn = Engine.connect () sql = User.insert (). VALUES (Id=1,name= ' Wu ') #id可以不写, self-increment conn.execute (SQL) Conn.close ()
Database validation:
3. Delete data:
#/usr/bin/env  python#-*-coding:utf-8-*-import pymysqlfrom sqlalchemy import create_engine, Table, Column, Integer , String, MetaData, foreignkeymetadata = MetaData () user = Table (' user ', MetaData,    Column (' id ', Integer, primary_key= True),    Column (' name ', String ()), color = Table (' Color ', metadata,    Column (' id ', Integer, Primary_key=true),    Column (' name ', String),) engine = Create_engine ("Mysql+pymysql://root:[email protected] @localhost: 3306/ Test ", max_overflow=5) conn = Engine.connect () SQL1 = User.delete (). WHERE (user.c.id = = 1) #匹配id为1sql2 = User.delete (). WHERE (User.c.name = = ' li ') #匹配条件name为liconn. Execute (SQL1) #执行语句conn. Execute (SQL2) conn.close ()
4, change the data:
Import pymysqlfrom sqlalchemy import Create_engine, Table, Column, Integer, String, MetaData, foreignkeymetadata = Metadat A () user = Table (' user ', metadata,    column (' id ', Integer, primary_key=true),    column (' name ', String),) color = Table (' Color ', metadata,    column (' id ', Integer, primary_key=true),    column (' name ', String),) engine = Create_engine ("Mysql+pymysql://root:[email protected" @localhost: 3306/test ", max_overflow=5) conn = Engine.connect () #sql1 = User.insert (). VALUES (name= "Zhang") #添加数据sql1 = User.update (). WHERE (User.c.name = = ' Zhang '). VALUES (name= ' Jack ' ) #匹配套件并改数据conn. Execute (SQL1) conn.close ()
5, check the data:
#/usr/bin/env  python#-*-coding:utf-8-*-import pymysqlfrom sqlalchemy import create_engine, select,table, Column, Integer, String, MetaData, foreignkeymetadata = MetaData () user = Table (' user ', MetaData,    Column (' id ', Integer, primary_key=true),    Column (' name ', String ()), color = Table (' Color ', metadata,    Column (' id ', Integer, primary_key=true),    Column (' name ', String ()), engine = Create_engine ("Mysql+pymysql://root:[email protected]@ Localhost:3306/test ", max_overflow=5) conn = Engine.connect () sql = select ([user,]) res = conn.execute (SQL) print ( Res.fetchall ()) Conn.close ()
The actual use of the method introduced:
To create a table and insert data:
 #/usr/bin/env python#-*-coding:utf-8-*-from sqlalchemy import Create_enginefrom sqlalchemy.ext.declarative Import declarative_basefrom sqlalchemy import Column, Integer, stringfrom sqlalchemy.orm import sessionmakerbase = Declar Ative_base () #生成一个SqlORM base class engine = Create_engine ("mysql+pymysql://root:[email protected] @localhost: 3306/test ", Echo=true) class Host (Base): __tablename__ = ' hosts ' id = Column (integer,primary_key=true,autoincrement=true) Ho  stname = Column (String (unique=true,nullable=false), ip_addr = Column (string), Unique=true,nullable=false) port = Column (integer,default=22) Base.metadata.create_all (engine) #创建所有表结构if __name__ = = ' __main__ ': sessioncls = Sessionma Ker (bind=engine) #创建与数据库的会话session class, note that this is a class that is returned to the session, not an instance session = Sessioncls () H1 = Host (Hostname= ' lo Calhost ', ip_addr= ' 127.0.0.1 ') H2 = Host (hostname= ' Centos ', ip_addr= ' 192.168.10.254 ', port=2021) Session.add_all ([H1, H2]) Session.commit () #提交 
To query and change the data:
#/usr/bin/env python#-*-coding:utf-8-*-from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative Import Dec Larative_basefrom sqlalchemy Import Column, Integer, stringfrom sqlalchemy.orm import sessionmakerbase = Declarative_bas E () #生成一个SqlORM base class engine = Create_engine ("mysql+pymysql://root:[email protected] @localhost: 3306/test", echo= True) class Host (Base): __tablename__ = ' hosts ' id = Column (integer,primary_key=true,autoincrement=true) hostname = Column (string, unique=true,nullable=false) ip_addr = Column (string (+), unique=true,nullable=false) port = Colu Mn (integer,default=22) Base.metadata.create_all (engine) #创建所有表结构if __name__ = = ' __main__ ': sessioncls = Sessionmaker ( Bind=engine) #创建与数据库的会话session class, note that this is a class that is returned to the session, not an instance session = Sessioncls () H1 = Host (hostname= ' Localh    Ost ', ip_addr= ' 127.0.0.1 ') H2 = Host (hostname= ' Centos ', ip_addr= ' 192.168.10.254 ', port=2021) Session.add_all ([H1,H2]) #session. Rollback () #h3 = Host (hosTname= ' ubuntu2 ', ip_addr= ' 192.168.2.244 ', port=20000) #session. Add (H3) #session. Add_all ([H1,H2]) #h2. hostname = ' U Buntu_test ' #只要没提交, there is no problem with the modification at this time res = Session.query (Host). Filter (Host.hostname.in_ ([' ubuntu2 ', ' localhost ']). First () #  All, first one, last #res. hostname = "Test hostname" #将查到的数据改值 session.delete (res) #删除查询到的数据 Session.commit () #提交
ORM Foreign Key Association Many-to-many: A group can contain multiple hosts, a host can also be in multiple groups, that is, a host can be in more than one group, as follows:
Class Parent (Base):  #父类    __tablename__ = ' parent '    id = Column (Integer, primary_key=true)    children = Relationship ("child") class child (Base):  #子类    __tablename__ = ' child '  #定义表名称    id = Column (Integer, primary_key=true)  #id的值类型    parent_id = Column (Integer, ForeignKey (' parent.id '))  #关联父类的 ' Parent.id to complete the foreign key association with the parent class
Test
SQLAlchemy cannot add new fields to existing tables, so you can either recreate the table or create it yourself using native SQL statements, or you can use SQLAlchemy's three-way tool, where we removed the previous table and rebuilt it:
Mysql> drop tables  hosts; Query OK, 0 rows affected (0.22 sec)
Pyhton Code:
#/usr/bin/env python#-*-coding:utf-8-*-from sqlalchemy Import Create_engine,foreignkeyfrom Sqlalchemy.ext.declarative Import declarative_basefrom sqlalchemy import Column, Integer, Stringfrom sqlalchemy.orm Import sessionmakerbase = Declarative_base () #生成一个SqlORM base class engine = Create_engine ("Mysql+pymysql://root:[email Protected] @localhost: 3306/test ", Echo=true) class Host (Base): __tablename__ = ' hosts ' id = Column (integer,primary_key= TRUE,AUTOINCREMENT=TRUE) hostname = column (string (+), unique=true,nullable=false) ip_addr = Column (string), Uniqu E=true,nullable=false) port = column (integer,default=22) group_id = Column (Integer, ForeignKey (' Group.id ')) class Gro Up (Base): __tablename__ = ' group ' id = column (integer,primary_key=true) name = Column (String), Unique=true,null Able=false) child_id = Column (Integer)
Base.metadata.create_all (engine) #创建所有表结构

if __name__ = = ' __main__ ': sessioncls = Sessionmaker (bind=engine) #创建与数据库的会话session class, notice that this is a class that is returned to the session, not an instance Session = Sessioncls () session.commit () #提交
Verify:
Mysql> desc hosts;+----------+--------------+------+-----+---------+----------------+| Field    | Type         | Null | Key | Default | Extra          |+----------+--------------+------+-----+---------+----------------+| ID       | int      | NO   | PRI | NULL    | auto_increment | | hostname | varchar |  NO   | UNI | NULL    |                | | ip_addr  | varchar (128) | NO   | UNI | NULL    |                | | port     | int      | YES  |     | NULL    |                | | group_id | int (one)      | YES  | MUL | NULL    |                
The value of the key #group_id line is Mul
To view the group table:
Mysql> desc test.group;+----------+-------------+------+-----+---------+----------------+| Field    | Type        | Null | Key | Default | Extra          |+----------+-------------+------+-----+---------+----------------+| ID       | int     | NO   | PRI | NULL    | auto_increment | | name     | varchar (64) | NO   | UNI | NULL    |                | | child_id | int (one)     | YES  |     | NULL    |                | +----------+-------------+------+-----+---------+----------------+3 rows in Set (0.01 sec)
To create the associated data:
#/usr/bin/env python#-*-coding:utf-8-*-from sqlalchemy Import Create_engine,foreignkeyfrom Sqlalchemy.ext.declarative Import declarative_basefrom sqlalchemy import Column, Integer, Stringfrom sqlalchemy.orm Import sessionmakerbase = Declarative_base () #生成一个SqlORM base class engine = Create_engine ("mysql+pymysql://root:[email  Protected] @localhost: 3306/test ", Echo=true) class Host (Base): __tablename__ = ' hosts ' id = Column (integer,primary_key= TRUE,AUTOINCREMENT=TRUE) hostname = column (string (+), unique=true,nullable=false) ip_addr = Column (string), Uniqu E=true,nullable=false) port = column (integer,default=22) group_id = Column (Integer, ForeignKey (' Group.id ')) class Gro Up (Base): __tablename__ = ' group ' id = column (integer,primary_key=true) name = Column (String), Unique=true,null  Able=false) child_id = Column (Integer) Base.metadata.create_all (engine) #创建所有表结构if __name__ = = ' __main__ ': sessioncls = Sessionmaker (bind=engine) #创建与数据库的会话session class, notice that this returns toSession is a class, not an instance session = Sessioncls () g3 = Group (name= ' G3 ') Session.add_all ([G3,]) H1 = Host (hostname= ' l Ocalhost ', ip_addr= ' 1.1.1.1 ', group_id=g3.id) #由于数据库还没有g3的内容, so this association is unsuccessful Session.add_all ([H1,]) Session.commit () #提交
To change the associated data:
#/usr/bin/env python#-*-coding:utf-8-*-from sqlalchemy Import Create_engine,foreignkeyfrom Sqlalchemy.ext.declarative Import declarative_basefrom sqlalchemy import Column, Integer, Stringfrom sqlalchemy.orm Import sessionmakerbase = Declarative_base () #生成一个SqlORM base class engine = Create_engine ("mysql+pymysql://root:[email  Protected] @localhost: 3306/test ", Echo=true) class Host (Base): __tablename__ = ' hosts ' id = Column (integer,primary_key= TRUE,AUTOINCREMENT=TRUE) hostname = column (string (+), unique=true,nullable=false) ip_addr = Column (string), Uniqu E=true,nullable=false) port = column (integer,default=22) group_id = Column (Integer, ForeignKey (' Group.id ')) class Gro Up (Base): __tablename__ = ' group ' id = column (integer,primary_key=true) name = Column (String), Unique=true,null  Able=false) child_id = Column (Integer) Base.metadata.create_all (engine) #创建所有表结构if __name__ = = ' __main__ ': sessioncls = Sessionmaker (bind=engine) #创建与数据库的会话session class, notice that this returns toThe session is a class, not an instance session = Sessioncls () g3 = Session.query (Group). Filter (group.name== ' G3 '). First () #查到一个已经存在的组 h = session.query (Host). Filter (host.hostname== ' localhost '). Update ({' group_id ': g3.id}) #查到一个主机的信息并更新其group_ ID is the ID of the group that was found in the previous step, which completes the management of the foreign Key Session.commit () #提交
Python Growth path 13