The SQLAlchemy of ORM Framework

I. Introduction to SQLAlchemy

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.

Second, the preliminary application of sqlalchemy---table creation

1. Creation and deletion of single table

#!/usr/bin/env python
#-*-Coding:utf-8-*-
#Author: Ye

#导入模块
From sqlalchemy.ext.declarative import declarative_base
From SQLAlchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
From Sqlalchemy.orm import Sessionmaker, relationship
From SQLAlchemy import Create_engine
Import time

#创建继承类Base (the prescribed wording)
Base = Declarative_base ()

#创建类, and inherit the parent class base
Class User (Base):
#创建表, the value after "__talbename__" represents the database table name
__tablename__ = ' user '
#创建数据库表的列字段
id = Column (integer,primary_key=true,autoincrement=true)
Name = Column (String (32))
Age = Column (Integer)

#创建索引
__table_args__ = (
UniqueConstraint (' id ', ' name ', name= ' uix_id_name '),
Index (' Uix_id_name ', ' name ')
)

Def init_db ():
#创建引擎, and on this basis through the Pymysql plug-in, connect to the database. Create a database connection pool at the same time, set to 5
Engine = Create_engine ("Mysql+pymysql://root:[email Protected]:3306/day15?charset=utf8", max_overflow=5)

#找到所有继承Base这个类的子类, and create all of the database tables defined in the child class
Base.metadata.create_all (Engine)


Def drop_db ():
# Create the engine, and on this basis through the Pymysql plug-in, connect to the database. Create a database connection pool at the same time, set to 5
Engine = Create_engine ("Mysql+pymysql://root:[email Protected]:3306/day15?charset=utf8", max_overflow=5)

# Find all subclasses of the class inheriting base and delete all database tables defined in the subclass
Base.metadata.drop_all (Engine)

#执行函数, create a database table
init_db ()
Time.sleep (1)
Print ("database table creation succeeded!") ")

#执行函数, delete database tables
drop_db ()
Time.sleep (1)
Print ("Database table deleted successfully!") ")

2, a pair of multi-

 #!/usr/bin/env python 
 #-*-coding:utf-8-*-
 #Author: ye 
 
 #导入模块 
 from sqlalchemy.ext.declarative Import Declarative_base 
 from SQLAlchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 
 from Sqlalchemy.orm import Sessionmaker, relationship 
 from sqlalchemy import create_engine 
 Import time 
 
 # Create an inherited class base (prescribed notation) 
 base = Declarative_base () 
 
 Class Favor (base): 
 __tablename__ = ' Favor ' 
 nid = Column (Integer, Primary_key=true) #设置为主键 
 caption = Column (String (), default= ' Red ', unique=true) #默认值为red, set to Unique constraint 
 
 
 Class Person (Base): 
 __tablename__ = ' person ' 
 Nid = Column (Integer, primary_key=true) #设置为主键 
 NA Me = Column (String (p), Index=true, nullable=true) #可以为空 
 favor_id = column (Integer, ForeignKey ("Favor.nid")) #设置外键, p Erson.favor.id = Favor.id 

#创建引擎, and on this basis through the Pymysql plug-in, connect to the database. Create a database connection pool at the same time, set to 5
Engine = Create_engine ("Mysql+pymysql://root:[email Protected]:3306/day15?charset=utf8", max_overflow=5)

#找到所有继承Base这个类的子类, and create all of the database tables defined in the child class
Base.metadata.create_all (Engine)

3, many-to-many

#!/usr/bin/env python
#-*-Coding:utf-8-*-
#Author: Ye

#导入模块
From sqlalchemy.ext.declarative import declarative_base
From SQLAlchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
From Sqlalchemy.orm import Sessionmaker, relationship
From SQLAlchemy import Create_engine
Import time

#创建继承类Base (the prescribed wording)
Base = Declarative_base ()

# Many-to-many
Class Group (Base):
__tablename__ = ' Group '
id = Column (Integer, Primary_key=true)
Name = Column (String (), Unique=true, Nullable=false)

Class Server (Base):
__tablename__ = ' server '

id = Column (Integer, Primary_key=true, Autoincrement=true)
hostname = Column (String (unique=true), Nullable=false)
Port = Column (Integer, default=22)


Class Servertogroup (Base):
__tablename__ = ' Servertogroup '
Nid = Column (Integer, Primary_key=true, Autoincrement=true)
server_id = Column (Integer, ForeignKey (' server.id ')) #设置外键 servertogroup.server_id = server.id
group_id = Column (Integer, ForeignKey (' group.id ')) #设置外键 servertogroup.group_id = group.id


Def init_db ():
# Create the engine, and on this basis through the Pymysql plug-in, connect to the database. Create a database connection pool at the same time, set to 5
Engine = Create_engine ("Mysql+pymysql://root:[email Protected]:3306/day15?charset=utf8", max_overflow=5)
# Find all the subclasses of the class inheriting base and create all the database tables defined in the subclass
Base.metadata.create_all (Engine)


Def drop_db ():
# Create the engine, and on this basis through the Pymysql plug-in, connect to the database. Create a database connection pool at the same time, set to 5
Engine = Create_engine ("Mysql+pymysql://root:[email Protected]:3306/day15?charset=utf8", max_overflow=5)

# Find all subclasses of the class inheriting base and delete all database tables defined in the subclass
Base.metadata.drop_all (Engine)

# Execute function, create database table
init_db ()
Time.sleep (1)
Print ("database table creation succeeded!") ")

#执行函数, delete database tables
drop_db ()
Time.sleep (1)
Print ("Database table deleted successfully!") ")

Third, further application of sqlalchemy---table operation

1. Add Data

#!/usr/bin/env python
#-*-Coding:utf-8-*-
#Author: Ye

#导入模块
From sqlalchemy.ext.declarative import declarative_base
From SQLAlchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
From Sqlalchemy.orm import Sessionmaker, relationship
From SQLAlchemy import Create_engine

#创建继承类Base (the prescribed wording)
Base = Declarative_base ()

#创建类, and inherit the parent class base
Class User (Base):
#创建表, the value after "__talbename__" represents the database table name
__tablename__ = ' user '
#创建数据库表的列字段
id = Column (integer,primary_key=true,autoincrement=true)
Name = Column (String (32))
Age = Column (Integer)

#创建引擎, and on this basis through the Pymysql plug-in, connect to the database. Create a database connection pool at the same time, set to 5
Engine = Create_engine ("Mysql+pymysql://root:[email Protected]:3306/day15?charset=utf8", max_overflow=5)

#找到所有继承Base这个类的子类, and create all of the database tables defined in the child class
#Base. Metadata.create_all (Engine)

#创建session会话
Session = Sessionmaker (bind = engine)
Session = Session ()

#实例化类对象 and added to the session
obj = User (name = "Alex1", age = +) #添加单条数据
Session.add (obj)

#添加多条数据
Session.add_all ([
User (name = "Alex2", age = 100),
User (name = "Alex3", age = 90),
])

#提交数据
Session.commit ()

2. Delete data

#!/usr/bin/env python
#-*-Coding:utf-8-*-
#Author: Ye

#导入模块
From sqlalchemy.ext.declarative import declarative_base
From SQLAlchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
From Sqlalchemy.orm import Sessionmaker, relationship
From SQLAlchemy import Create_engine

#创建继承类Base (the prescribed wording)
Base = Declarative_base ()

#创建类, and inherit the parent class base
Class User (Base):
#创建表, the value after "__talbename__" represents the database table name
__tablename__ = ' user '
#创建数据库表的列字段
id = Column (integer,primary_key=true,autoincrement=true)
Name = Column (String (32))
Age = Column (Integer)


#创建引擎, and on this basis through the Pymysql plug-in, connect to the database. Create a database connection pool at the same time, set to 5
Engine = Create_engine ("Mysql+pymysql://root:[email Protected]:3306/day15?charset=utf8", max_overflow=5)


#创建session会话
Session = Sessionmaker (bind = engine)
Session = Session ()

#删除数据, the class name is behind query, and the filter is followed by the Where condition
Session.query (User). Filter (User.ID > 3). Delete ()

#提交数据
Session.commit ()

3. Modify the data

#!/usr/bin/env python
#-*-Coding:utf-8-*-
#Author: Ye

#导入模块
From sqlalchemy.ext.declarative import declarative_base
From SQLAlchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
From Sqlalchemy.orm import Sessionmaker, relationship
From SQLAlchemy import Create_engine

#创建继承类Base (the prescribed wording)
Base = Declarative_base ()

#创建类, and inherit the parent class base
Class User (Base):
#创建表, the value after "__talbename__" represents the database table name
__tablename__ = ' user '
#创建数据库表的列字段
id = Column (integer,primary_key=true,autoincrement=true)
Name = Column (String (32))
Age = Column (Integer)


#创建引擎, and on this basis through the Pymysql plug-in, connect to the database. Create a database connection pool at the same time, set to 5
Engine = Create_engine ("Mysql+pymysql://root:[email Protected]:3306/day15?charset=utf8", max_overflow=5)


#创建session会话
Session = Sessionmaker (bind = engine)
Session = Session ()

#修改数据, after query is the class name, filter followed by the Where condition, update followed by the Modified field and field contents
Session.query (User). Filter (User.ID > 2). Update ({User.age:User.age + 10},synchronize_session= "Evaluate")

#提交数据
Session.commit ()

4. Query data

#!/usr/bin/env python
#-*-Coding:utf-8-*-
#Author: Ye

#导入模块
From sqlalchemy.ext.declarative import declarative_base
From SQLAlchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
From Sqlalchemy.orm import Sessionmaker, relationship
From SQLAlchemy import Create_engine

#创建继承类Base (the prescribed wording)
Base = Declarative_base ()

#创建类, and inherit the parent class base
Class User (Base):
#创建表, the value after "__talbename__" represents the database table name
__tablename__ = ' user '
#创建数据库表的列字段
id = Column (integer,primary_key=true,autoincrement=true)
Name = Column (String (32))
Age = Column (Integer)

#创建引擎, and on this basis through the Pymysql plug-in, connect to the database. Create a database connection pool at the same time, set to 5
Engine = Create_engine ("Mysql+pymysql://root:[email Protected]:3306/day15?charset=utf8", max_overflow=5)

#创建session会话
Session = Sessionmaker (bind = engine)
Session = Session ()

#查询数据, the class name is behind query
Result_sql = Session.query (User) #结果是生成的sql语句
Print ("Result_sql:", Result_sql) #SELECT user.id as user_id, user.name as user_name, user.age as user_age from user

Result_obj = Session.query (User). All () #查询出来的是符合条件的User对象
Print ("Result_obj:", Result_obj) #[<__main__. User object at 0x00000004e0a6ea58> <__main__. User object at 0x00000004e0a6e9e8> <__main__. User object at 0x00000004e0a6efd0>]

#将结果打印
For result in Result_obj:
Print ("user_id:", result.id)
Print ("user_name:", Result.name)

#提交数据
Session.commit ()

Note: Other ways to query:filter_by Internal Call filter

Session = Session.query (User.Name, User.age). All () #查询user表中name, age column

session.query (User). filter_by (name= ' Alex '). All ()#查询user表中name是alex的所有行

session.query (User) filter_by (name= ' Alex '). First ()#查询user表中name是alex的第一行数据

5. Other query conditions

# conditional ret = session.query (users). filter_by (name= ' Alex '). All () ret = session.query (users). Filter (Users.id > 1, Users.name = = ' Eric '). All () ret = Session.query (Users). Filter (Users.id.between (1, 3), Users.name = = "Eric"). All () # Equivalent to Users.id >1 and Users.id < 3 and Users.name = = Ericret = Session.query (Users). Filter (Users.id.in_ ([1,3,4])). All ( ret = session.query (users). Filter (~users.id.in_ ([1,3,4]). All () #~ represents non-ret = session.query (users). Filter (Users.id.in _ (Session.query (users.id). Filter_by (Name= ' Eric ')). All () #相当于select * from the Users where users.id in (SELECT * from ...) From SQLAlchemy import and_, Or_ret = Session.query (Users). Filter (And_ (Users.id > 3, Users.name = = "Eric"). All () ret = Session.query (Users). Filter (Or_ (Users.id < 2, Users.name = = ' Eric '). All () ret = session.query (users). Filter (#        The and connection in the innermost brackets is first done, and then the outer brackets are connected or_ (Users.id < 2, and_ (users.name = = ' Eric ', Users.id > 3), Users.extra = "")). All () # wildcard character ret = Session.query (Users). Filter (USers.name.like (' e% ')). All () #模糊查询ret = Session.query (Users). Filter (~users.name.like (' e% ')). All () # LIMIT ret = Session.query (users) [1:2]# sort ret = session.query (users). Order_by (Users.name.desc ()). All () ret = session.query (users) . Order_by (Users.name.desc (), USERS.ID.ASC ()). All () # groups from sqlalchemy.sql import Funcret = Session.query (Users). Group _by (Users.extra). All () ret = Session.query (Func.max (users.id), #最大值 func.sum (users.id), #求和 func.min (users.id)). Gro Up_by (Users.name). All () #最小值ret = Session.query (Func.max (users.id), Func.sum (users.id), Func.min (Users.id)). Group _by (Users.name). have (Func.min (users.id) >2). All () # even table ret = Session.query (Users, Favor). Filter (Users.id = = Favor.nid). All () ret = session.query (person). Join (Favor). All () ret = session.query (person). Join (Favor, isouter=true). All () # combination q1 = session.query (users.name). Filter (Users.id > 2) q2 = Session.query (favor.caption). Filter (Favor.nid < 2) ret = q1.union (Q2). All () #组合并去重q1 = Session.query (users.name). Filter (UserS.id > 2) q2 = Session.query (favor.caption). Filter (Favor.nid < 2) ret = Q1.union_all (Q2). All () #组合不去重 

6. Multi-Table Query

Application of A.relationship

#!/usr/bin/env python
#-*-Coding:utf-8-*-
#Author: Ye

#导入模块
From sqlalchemy.ext.declarative import declarative_base
From SQLAlchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
From Sqlalchemy.orm import Sessionmaker, relationship
From SQLAlchemy import Create_engine
Import time

#创建继承类Base (the prescribed wording)
Base = Declarative_base ()

# Many-to-many
Class Department (Base):
__tablename__ = ' Department '
id = Column (Integer, Primary_key=true)
Depart_name = Column (String (unique=true), Nullable=false)

Class Person (Base):
__tablename__ = ' person '

id = Column (Integer, Primary_key=true, Autoincrement=true)
user_name = Column (String (unique=true), Nullable=false)
user_depart_id = Column (Integer, ForeignKey (' department.id ')) #通过此外键进行关联

#创建两表之间的连接
Depart = Relationship ("Department", backref= "pers")

# Create the engine, and on this basis through the Pymysql plug-in, connect to the database. Create a database connection pool at the same time, set to 5
Engine = Create_engine ("Mysql+pymysql://root:[email Protected]:3306/day15?charset=utf8", max_overflow=5)

#Base. Metadata.create_all (Engine)

#创建session会话
Session = Sessionmaker (bind = engine)
Session = Session ()

Result_obj = Session.query (person). All ()

For result in Result_obj:
Print ("Person.id:", result.id)
Print ("Person.user_name:", Result.user_name)
Print ("Depart_name:", Result.depart.depart_name)

Application of B.session.add

#创建session会话
Session = Sessionmaker (bind = engine)
Session = Session ()

#通过session在多表中插入数据
Session.add (Person (id=10,user_name= ' SB ', Depart=department (id=9,depart_name= "Hunan")))
Session.commit ()

ORM Framework's SQLAlchemy