The SQLAlchemy of Python

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.

The ORM (Object relational mapping) methodology is based on three core principles:

• Simple: Modeling data in the most basic form
• Communication: Database structure is documented in a language that anyone can understand
• Precision: Create a properly standardized structure based on a data model

Dialect is used to communicate with the data API, depending on the database API that is called by the configuration file, to perform operations on the database, such as: ' database type + database driver name://user name: password @ machine Address: Port number/database name‘

Mysql-python:mysql+mysqldb://<user>:<password>@
　　

 
  
 Basic Operations (1)connecting to a database: Create_engine () engine = Create_engine ("Mysql+pymysql://root:[email Protected]:3306/wang", max_overflow=5)
 
 

  
  
 
   
Create_engine () returns a database engine
 
   
Mysql+pymysql "Specify a connection using Pymysql"
 
   
Connect to database ' Wang ' using username ' root ' and password ' 123456 '
 
   
Max_overflow is the maximum number of connections
 
   
CharSet: Set the character set used when connecting CharSet = UTF8
 
   
The echo parameter, if true, displays each SQL statement executed, and the production environment closes after class
 
  
 
 
(2)fields and data types and how to do itIn the Sqlalchemy.scherma package there is a description of the database relationship, listing several commonly used:
 
 

  
  
 
   
Field: Column
 
   
Indexes: Index
 
   
Tables: Table
 
  
 
 
Data types are listed in the Sqlalchemy.types package, which are commonly used:
 
 

  
  
 
   
Binary: BIGINT
 
   
Boolean: Boolean
 
   
Character: Char
 
   
Variable character: VARCHAR
 
   
Date: Datatime
 
  
 
 
(3)CREATE TABLE StructureUse schema Type/sql Expression language/engine/connectionpooling/dialect for database operations. The engine uses the schema type to create a specific structure object, and then converts the object to an SQL statement via SQL Expression language, and then connects the database through ConnectionPooling. Then execute SQL through dialect and get the results.
#!/usr/bin/env python3#coding:utf8from sqlalchemy Import create_engine, Table, Column, Integer, String, MetaData, foreignkey# Creating a database connection engine = Create_engine ("Mysql+pymysql://root:[email Protected]:3306/wang", max_overflow=5) # Get meta Data MetaData = MetaData () # definition Table 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 ())    # Create a data table if the data table exists, then ignore Metadata.create_all (engine)
　　
inheritance and deletion and modification      Use Orm/schema type/sql Expression language/engine/connectionpooling/dialect All components to manipulate the database. Objects are created from the class, objects are converted to SQL, and then SQL is executed. The      query object can return the value of the iteration (iterator value), and then we can query through for. However, the all (), one (), and first () methods of the Query object will return a non-iterative value (non-iterator value), for example all () returns a list, and the primary () method restricts and returns the result set only as a scalar. (1) Create a database
#!/usr/bin/env python#-*-coding:utf-8-*-from sqlalchemy.ext.declarative import Declarative_basefrom SQLAlchemy Import Column, Integer, stringfrom sqlalchemy.orm import sessionmakerfrom sqlalchemy import create_engine# CREATE DATABASE engine = C Reate_engine ("Mysql+pymysql://root:[email Protected]:3306/wang", max_overflow=5) # generates a sqlorm base class base = Declarative_ Base () # Defines the table structure class User (base):    # table name    __tablename__ = ' users '    # definition ID, primary key unique,    id = Column (Integer, Primary_ key=true)    name = Column (String (50)) # finds all subclasses of base, generates corresponding data table information in the database according to the structure of the subclass Base.metadata.create_all (engine) # Create a session with the database sessions class, note that this is the class that is returned to the session, not the instance session = Sessionmaker (bind=engine) session = Session () # get session , and then add the object to the session# last commit and close. The session object can be considered the current database connection.
(2) Increase
#!/usr/bin/env python#-*-coding:utf-8-*-from sqlalchemy.ext.declarative import Declarative_basefrom SQLAlchemy Import Column, Integer, stringfrom sqlalchemy.orm import sessionmakerfrom sqlalchemy Import create_engineengine = Create_ Engine ("Mysql+pymysql://root:[email Protected]:3306/wang", max_overflow=5) base = Declarative_base () class User (base) :    __tablename__ = ' users '    id = column (Integer, primary_key=true)    name = Column (String (50)) Base.metadata.create_all (engine) session = Sessionmaker (bind=engine) session = Session () # # # # # # # # # #  Add ######### Define a field Zengjia = User (id=2, name= ' Sbliuyao ') # Add field Session.add (Zengjia) # Add multiple fields Session.add_all ([    User (id=3, name= ') Sbyao '),    User (id=4, name= ' Liuyao ')] # Commit The above operations, now just add in memory, write back to the database, you must do the commit operation Session.commit ()
(3) Delete
#!/usr/bin/env python#-*-coding:utf-8-*-from sqlalchemy.ext.declarative import Declarative_basefrom SQLAlchemy Import Column, Integer, stringfrom sqlalchemy.orm import sessionmakerfrom sqlalchemy Import create_engineengine = Create_ Engine ("Mysql+pymysql://root:[email Protected]:3306/wang", max_overflow=5) base = Declarative_base () class User (base) :    __tablename__ = ' users '    id = column (Integer, primary_key=true)    name = Column (String (50)) Base.metadata.create_all (engine) session = Sessionmaker (bind=engine) session = Session () ########### Delete ########### Delete the user table, field session.query (user) with ID greater than 2. Filter (User.ID > 2). Delete () Session.commit ()
(4) Modification
#!/usr/bin/env python#-*-coding:utf-8-*-from sqlalchemy.ext.declarative import Declarative_basefrom SQLAlchemy Import Column, Integer, stringfrom sqlalchemy.orm import sessionmakerfrom sqlalchemy Import create_engineengine = Create_ Engine ("Mysql+pymysql://root:[email Protected]:3306/wang", max_overflow=5) base = Declarative_base () class User (base) :    __tablename__ = ' users '    id = column (Integer, primary_key=true)    name = Column (String (50)) Base.metadata.create_all (engine) session = Sessionmaker (bind=engine) session = Session () # Field with ID equal to 2 in user table modified to id= 6session.query (User). Filter (User.ID = = 2). Update ({' id ': 6}) Session.commit ()
(5) Enquiry
# # # Query Method 1 ##### Query the field in the User table is the first data of Name=liuyao ret = Session.query (User). filter_by (name= ' Liuyao '). All () print (ret) # Output RET, this is an object memory address for I in Ret:print (i.id,i.name) # output RET Content # # Query Method 2 ##### Query the User table field is the first data of Name=liuyao ret = SESSION.Q     Uery (User) filter_by (name= ' Liuyao '). First () print (RET) # Outputs the result of the object's memory address print (ret.name) # Output The name of the result field print (ret.id) # Output ID Field # # Query 3 # # # # # # # # # # # # # # # # # Query the User table field is name Liuyao or mayun information printed ret = Session.query (User). Filter (User.name.in_ ([' Query (User.name.label (")). All () # The key here is the label method, which means changing the name field of the User to be called name_label,#, which is equivalent to executing: select Users.name as Name_label from Userprint (Ret,type (ret)) # # # Query Method 5 # # # # # # # # # Query the user table is sorted by id ret = session.query (user). order_by (User.ID). All () Print (ret) for I in Ret:print (i.name) # # # Query Way 6 # # # # # # # # # # # # # # # Query the user table type 0 to 3 fields with the ID of ret = session.query (User). order_by (user.id) [0 : 3]print (ret) for I in Ret:print (i.name) # # # Query Method 7 # ## # Creates a query, filter is the Where condition, and the last Call to one () returns a unique row, and if all () is called returns all rows user = Session.query (user). Filter (user.id== ' 5 '). One () Print (type user) # View user's type print (user.name) # View the Name property of an object
(6) Foreign Key Association
Since multiple tables of relational data can also be used to implement a one-to-many, many-to-many correlation with foreign keys, the ORM Framework can also provide one-to-many, many-to-many functions between two objects,
 
 

  
  
 
   
One-to-many (a user can have multiple address)
 
  
 
 
#!/usr/bin/env python3#coding:utf8# import required modules from SQLAlchemy import Create_engine,funcfrom sqlalchemy.ext.declarative Import declarative_basefrom sqlalchemy import Column, Integer, string,foreignkeyfrom sqlalchemy.orm import Sessionmaker , relationship# generates Sqlorm base class base = Declarative_base () # CREATE database connection engine = Create_engine ("mysql+pymysql://root:[email     Protected]:3306/wang ", max_overflow=5) # The goal is that a person can have more than one book, then a one-to-many relationship in the database class User (Base): # table name __tablename__ = ' User '     # ID Field id = column (string, primary_key=true) # Name Field name = Column (String (20)) # One-to-many: # content is not a table name but a defined table structure name Books = Relationship (' book ') class book (Base): # indicates __tablename__ = ' book ' # ID Field id = Column (String), p    Rimary_key=true) # Name Field name = Column (String (20)) # The Book table for the "many" side is associated to the user table by a foreign key: # ForeignKey is the ID field of the Foreign Key Association user table    user_id = Column (String), ForeignKey (' User.ID ') # creates the required table Base.metadata.create_all (engine) if __name__ = = ' __main__ ': # bind, build session SESSIONCLS = Sessionmaker (Bind=engine) session = SESSIONCLS () # Create User Liuyao = User (id= ' 1 ', name= ' Liuyao ') Ali = User (id= ' 2 ', Name= ' Ali ') # Add Field Session.add_all ([Liuyao,ali]) # Submit Session.commit () # Create White Deer original This book, designate who is the owner of whitedeer = books (id= ' 1 ', Nam    E= ' White_deer ', user_id = ' 1 ') # Create three volume this book, specify who is the owner of threebody = books (id= ' 2 ', name= ' three_body ', user_id = ' 2 ') # Add Field Session.add_all ([whitedeer,threebody]) # Submit Session.commit ()
　　

 
 

  
  
 
   
Many-to-many
 
  
 
 
Establish a two-way one-to-many relationship, "reverse" is a lot of people, specify an extra relationship () function, and connect two using the Relationship.back_populates parameter. In a nutshell, the relationship function is a convenient way to call SQLAlchemy to a relationship, and the Backref parameter provides a declaration of a reverse reference to the relationship. The Back_populates parameter was introduced to relationship in the latest version of SQLAlchemy.
#!/usr/bin/env python3#coding:utf8from sqlalchemy Import Column, Sequence, String, Integer, Foreignkeyfrom SQLAlchemy Import Create_engine # Imports Create connection Drivers from sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm Import Sessionmakerfrom sqlalchemy.orm Import relationship, backref# this URL can be resolved with Urlparse, where echo=true represents the execution of the SQL statement engine = Create_engine ("Mysql+pymysql://root:[email protected]:3306/wang", max_overflow=5) # generates the declarative base class, Later model inherits from this class base = Declarative_base () class Parent (base): __tablename__ = ' parent ' id = Column (Integer, primary_key=t Rue) name = Column (String (), unique=true,nullable=false) children = relationship ("Child", back_populates= "parent") c Lass Child (Base): __tablename__ = ' child ' id = column (Integer, primary_key=true) name = Column (String), unique= True,nullable=false) parent_id = Column (Integer, ForeignKey (' parent.id ')) parent = relationship ("parent", Back_popul Ates= "Children") Base.metadata.create_all (engine) # Create all table structures if__name__ = = ' __main__ ': sessioncls = Sessionmaker (bind=engine) # Create a session with the database sessions class, note that this is a class that is returned to the session, not a real Example session = Sessioncls () Mama = parent (id= ' 1 ', name= ' Mamaxx ') Baba = parent (id= ' 2 ', name= ' Babaoo ') session.add_a    ll ([Mama,baba]) Onesb = Child (id= ' 1 ', name= ' onesb ', parent_id= ' 2 ') TWOSB = Child (id= ' 2 ', name= ' TWOSB ', parent_id= ' 2 ') Session.add_all ([ONESB,TWOSB]) Session.commit ()
 
 

  
  

  
 Many-to-many three-table foreign key Association 
 
  
  
#!/usr/bin/env python3#coding:utf8from sqlalchemy Import Create_engine,func,tablefrom sqlalchemy.ext.declarative Import declarative_basefrom sqlalchemy import Column, Integer, string,foreignkeyfrom sqlalchemy.orm import Sessionmaker , relationshipbase = Declarative_base () # relationship Table Host2group = table (' Host_2_group ', Base.metadata, Column (' host_id ',              ForeignKey (' hosts.id '), primary_key=true), Column (' group_id ', ForeignKey (' group.id '), primary_key=true),    Engine = Create_engine ("Mysql+pymysql://root:[email protected]:3306/wang", Max_overflow=5) class Host (Base): __tablename__ = ' hosts ' id = column (integer,primary_key=true,autoincrement=true) hostname = column (String), Uniqu    E=true,nullable=false) ip_addr = Column (String (+), unique=true,nullable=false) port = column (integer,default=22) Groups = Relationship (' Group ', secondary= host2group, backref = ' host_list ') CLA SS Group (Base): __tablename__ = 'Group ' id = column (integer,primary_key=true) name = Column (String (unique=true,nullable=false)) Base.metadata.creat E_all (engine) # Create all table structures if __name__ = = ' __main__ ': sessioncls = Sessionmaker (bind=engine) session = Sessioncls () g 1 = Group (name= ' G1 ') g2 = Group (name= ' g2 ') G3 = Group (name= ' g3 ') G4 = Group (name= ' G4 ') Session.add_all ([G1,g2, G3,G4]) Session.commit ()
　　

 
 
The SQLAlchemy of Python