ORM Framework/sqlachemy

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

1. Installation

PIP3 Install SQLAlchemy

Sqlachemy itself is unable to operate the database, must rely on third-party plug-ins such as Pymysql, dialect used to communicate with the data API, depending on the configuration file call different database APIs, so as to achieve the operation of the database:

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

2.ORM function Use

2.1 Creating a table

#导入模块from sqlalchemy.ext.declarative Import declarative_basefrom sqlalchemy import Column,integer,string,foreignkey, UniqueConstraint, indexfrom sqlalchemy.orm import sessionmaker,relationshipfrom sqlalchemy import create_enginebase= Declarative_base () #继承Base类 # Create form Class Usertype (base): __tablename__= ' usertype ' # #新建表名称 Id=column (Integer,prima ry_key=true,autoincrement=true) # #设置表列名, type, primary key, self-increment title=column (String (+), nullable=true,index=true) # #设置表列名, type, Can be empty, index class User (Base): __tablename__= ' users ' # #新建表名称 id = Column (Integer, Primary_key=true, AutoIncrement =true) name = Column (string (+), nullable=true, index=true) email = column (string (+), unique=true) # #设置表列名, class          Type, unique index user_type_id = Column (Integer, ForeignKey (' usertype.id ')) #设置表列名, type, foreign key # __table_args__ = (　　       # UniqueConstraint (' id ', ' name ', name= ' Uix_id_name '), # Index (' ix_n_ex ', ' name ', ' email ',), #) User_type=relationship (' usertype ', backref= 'Xxoo ') # #设置relationship, associate two classes, query the user table, query to the # usertype table, trigger query Usertype table is the property of the Usertype table when iterating through the results # setting backref for reverse lookup Request Def create_db (): Base.metadata.create_all (Engine) def drop_db (): Base.metadata.create_all (engine) Engine=create_ Engine (' Mysql+pymysql://root:[email protected]:3306/db1?charset=utf8 ', max_overflow=5) #使用第三方模块连接数据库, # Set maximum number of connections Session=sessionmaker (bind=engine) #生成一个会话session =session () create_db () #创建表格

2.2 Operation Table

Increase

# #创建一个obj1 =usertype (title= ") #实例化对象session. Add (obj1) # #创建多个objs =[usertype (title= ' Egon '), Usertype (title= ' Eric '), Usertype (title= ' aldx ')] Session.add_all (OBJS)

By deleting

Session.query (Usertype.id,usertype.title). Filter (usertype.id>2). Delete ()

Change

# The following two methods are based on the original operation of the value Session.query (usertype.id,usertype.title). Filter (usertype.id>0). Update ({usertype.title: Usertype.title+ ' x '},synchronize_session= ' False ') # #字符串操作session. Query (Users). Filter (Users.id > 2). Update ({"num ": Users.num + 1}, synchronize_session=" Evaluate ") # #数字类型操作

Check

Print (Session.query (usertype)) User_type_list=session.query (usertype). All () # #user_type_list是一个列表user_type_list = Session.query (usertype). Filter (USERTYPE.ID>2) # #filter是过滤 # This statement is equivalent to the WHERE condition statement for row in user_type_list:     print ( Row.id)

Other

# 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 () ret = Session.query (Users). Filter (Users.id.in_ ([1,3,4]). All () ret = session.query (users). Filter (~users.id.in_ ([1,3,4]) ). All () ret = Session.query (Users). Filter (Users.id.in_ (Session.query (users.id). Filter_by (Name= ' Eric ')). SQLAlchemy import and_, Or_ret = Session.query (Users). Filter (And_ (Users.id > 3, Users.name = = ' Eric ')). All () ret = Sessi        On.query (Users). Filter (Or_ (Users.id < 2, Users.name = = ' Eric '). All () ret = session.query (users). Filter (Or_ ( Users.id < 2, and_ (users.name = = ' Eric ', Users.id > 3), Users.extra! = "")). All () # wildcard character ret = Sessio N.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 () # Group from sqlalchemy.sql Import Funcret = Session.query (Users). group_by (Users.extra). All () ret = Session.query (Func.max (U sers.id), Func.sum (users.id), Func.min (Users.id)). Group_by (Users.name). All () ret = Session.query (Func.max (Users.i D), Func.sum (users.id), Func.min (Users.id)). Group_by (Users.name). have (Func.min (users.id) >2). All () # even table ret = ses Sion.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 () # Combo 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 ()

　　

ORM Framework/sqlachemy