Python operation MySQL (ii) ORM Chapter

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.

First, installation

pip3 install SQLAlchemy

SQLAlchemy itself cannot operate the database, it must have been pymsql and other third-party plug-ins, dialect used to communicate with the data API, depending on the configuration file to call different database APIs, so as to achieve the operation of the database, such as:

MySQL-Python    mysql+mysqldb://<user>:<password>@

Second, internal processing

Using Engine/connectionpooling/dialect for database operations, the Engine uses connectionpooling to connect to the database and then executes the SQL statement through dialect.

#!/usr/bin/env python# -*- coding:utf-8 -*-from sqlalchemy import create_engine# 处理中文# sqlalchemy设置编码字符集一定要在数据库访问的URL上增加charset=utf8，否则数据库的连接就不是utf8的编码格式engine = create_engine("mysql+pymysql://root:[email protected]:3306/t1?charset=utf8‘", max_overflow=5, echo=True)# 执行SQL# cur = engine.execute(#     "INSERT INTO hosts (host, color_id) VALUES (‘1.1.1.22‘, 3)"# )# 新插入行自增ID# cur.lastrowid# 执行SQL# cur = engine.execute(#     "INSERT INTO hosts (host, color_id) VALUES(%s, %s)",[(‘1.1.1.22‘, 3),(‘1.1.1.221‘, 3),]# )# 执行SQL# cur = engine.execute(#     "INSERT INTO hosts (host, color_id) VALUES (%(host)s, %(color_id)s)",#     host=‘1.1.1.99‘, color_id=3# )# 执行SQL# cur = engine.execute(‘select * from hosts‘)# 获取第一行数据# cur.fetchone()# 获取第n行数据# cur.fetchmany(3)# 获取所有数据

Third, the use of ORM function

Use Orm/schema type/sql Expression language/engine/connectionpooling/dialect All components to manipulate the data. Objects are created from the class, objects are converted to SQL, and SQL is executed.

1. Create a table

#!/usr/bin/env python#-*-coding:utf-8-*-from sqlalchemy.ext.declarative import Declarative_basefrom SQLAlchemy Import Column, Integer, String, ForeignKey, UniqueConstraint, indexfrom sqlalchemy.orm import Sessionmaker, Relationshipfrom sqlalchemy Import create_engineengine = Create_engine ("mysql+pymysql://root:[email protected") : 3306/t1 ", max_overflow=5) Base = Declarative_base () # Create single-table class Users (base): __tablename__ = ' Users ' id = Column (Inte GER, primary_key=true) name = Column (string ()) Extra = column (string ()) __table_args__ = (uniqueconstrain T (' id ', ' name ', name= ' uix_id_name '), Index (' Ix_id_name ', ' name ', ' Extra '), # One-to-many class Favor (Base): __tablena  me__ = ' favor ' nid = Column (Integer, primary_key=true) caption = column (String (), default= ' Red ', Unique=true) class Person (Base): __tablename__ = "person" nid = Column (Integer, primary_key=true) name = Column (String), index=t Rue, nullable=true) favor_id = Column (Integer, forEignkey ("Favor.nid")) # Many-to-many class group (Base): __tablename__ = ' Group ' id = Column (Integer, primary_key=true) name = Column (String, Unique=true, nullable=false) port = column (Integer, default=22) class Server (Base): __tablename_ _ = ' server ' id = column (Integer, primary_key=true, autoincrement=true) hostname = column (String (), Unique=true, n Ullable=false) class Servertogroup (Base): __tablename__ = ' servertogroup ' nid = Column (Integer, Primary_key=true, AUT oincrement=true) server_id = Column (Integer, ForeignKey (' server.id ')) group_id = Column (Integer, ForeignKey (' group.i d ')) def init_db (): Base.metadata.create_all (Engine) def drop_db (): Base.metadata.drop_all (engine) Note: Another way to set up an external check Foreig Nkeyconstraint ([' other_id '], [' othertable.other_id '])

2. Operation table

Table structure + database connection

#!/usr/bin/env python#-*-coding:utf-8-*-from sqlalchemy.ext.declarative import Declarative_basefrom SQLAlchemy Import Column, Integer, String, ForeignKey, UniqueConstraint, indexfrom sqlalchemy.orm import Sessionmaker, Relationshipfrom sqlalchemy Import create_engineengine = Create_engine ("mysql+pymysql://root:[email protected") : 3306/t1 ", max_overflow=5) Base = Declarative_base () # Create single-table class Users (base): __tablename__ = ' Users ' id = Column (Inte GER, primary_key=true) name = Column (string ()) Extra = column (string ()) __table_args__ = (uniqueconstrain T (' id ', ' name ', name= ' uix_id_name '), Index (' Ix_id_name ', ' name ', ' Extra '), Def __repr__ (self): retur N "%s-%s"% (Self.id, self.name) # One-to-many class Favor (Base): __tablename__ = ' Favor ' nid = Column (Integer, Primary_key=tru E) caption = Column (String (), default= ' Red ', unique=true) def __repr__ (self): return "%s-%s"% (Self.nid, SE Lf.caption) class Person (Base): __tablename__ = ' peRson ' nid = Column (Integer, primary_key=true) name = Column (String (+), Index=true, nullable=true) favor_id = Col Umn (Integer, ForeignKey ("Favor.nid")) # is independent of the build table structure and is only used for query convenience favor = relationship ("Favor", backref= ' pers ') # Many-to-many class Ser Vertogroup (Base): __tablename__ = ' servertogroup ' nid = Column (Integer, Primary_key=true, Autoincrement=true) ser ver_id = Column (Integer, ForeignKey (' server.id ')) group_id = Column (Integer, ForeignKey (' group.id ')) group = Relatio Nship ("Group", backref= ' s2g ') server = relationship ("Server", backref= ' s2g ') class Group (Base): __tablename__ = ' Grou P ' id = column (Integer, primary_key=true) name = Column (String (unique=true), nullable=false) port = column (in Teger, default=22) # group = relationship (' group ', secondary=servertogroup,backref= ' host_list ') class Server (Base): __ tablename__ = ' server ' id = column (Integer, primary_key=true, autoincrement=true) hostname = column (String), Uniq Ue=true, Nullable=false)Def init_db (): Base.metadata.create_all (Engine) def drop_db (): Base.metadata.drop_all (engine) Session = Sessionmaker (b Ind=engine) session = Session ()

• Increase

obj = Users(name="alex0", extra=‘sb‘)session.add(obj)session.add_all([    Users(name="alex1", extra=‘sb‘),    Users(name="alex2", extra=‘sb‘),])session.commit()

• By deleting

session.query(Users).filter(Users.id > 2).delete()session.commit()

• Change

session.query(Users).filter(Users.id > 2).update({"name" : "099"})session.query(Users).filter(Users.id > 2).update({Users.name: Users.name + "099"}, synchronize_session=False)session.query(Users).filter(Users.id > 2).update({"num": Users.num + 1}, synchronize_session="evaluate")session.commit()

• Check

ret = session.query(Users).all()ret = session.query(Users.name, Users.extra).all()ret = session.query(Users).filter_by(name=‘alex‘).all()ret = session.query(Users).filter_by(name=‘alex‘).first()ret = session.query(Users).filter(text("id<:value and name=:name")).params(value=224, name=‘fred‘).order_by(User.id).all()ret = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name=‘ed‘).all()

• Other operations

# 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 ()

Python operation MySQL (ii) ORM article