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.
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:
Second, internal processingUsing 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 functionUse 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 tableTable 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 ()
obj = Users(name="alex0", extra=‘sb‘)session.add(obj)session.add_all([ Users(name="alex1", extra=‘sb‘), Users(name="alex2", extra=‘sb‘),])session.commit()
session.query(Users).filter(Users.id > 2).delete()session.commit()
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()
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()
# 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