Pymysql and SQLAlchemy module operation MySQL Database

These two modules can be installed directly from PIP:

Open MySQL to create several tables:

CREATE TABLE ' student_age ' (    ' id ' int not NULL,    ' age ' int not null,    ' sex ' ENUM (' M ', ' F ') is not NULL,    PRIMARY KEY (' id ')); INSERT into Student_age (ID, age, Sex) VALUES (1, +, ' m '), (2, +, ' m '), (3, +, ' F '); CREATE TABLE ' student_name ' (    ' id ' int not null auto_increment,    ' name ' varchar (TEN) NOT null default ' ',    ' stu_i d ' int not NULL,    PRIMARY key (' id '),    FOREIGN key (' stu_id ') REFERENCES ' student_age ' (' id ')); INSERT into Student_ Name (name, stu_id) VALUES (' Jack ', 1), (' Eric ', 2), (' Alice ', 3);

　　

Pymysql Basic Connection Example:

Import pymysql# Build Pipeline conn = Pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ' 123456 ', db= ' test ') # Create cursors cursor = conn.cursor () effect_rows = Cursor.execute (' SELECT * from Student_name ') print (' rows[%d] '% effect_rows, Cursor.fetchall ()) #关闭连接conn. Close ()

Pymysql Insert data and things like the effect, you can implement rollback, self-increment ID occupancy, must be submitted before it takes effect:

Import Pymysql, time# Set database connection parameters host = ' localhost ' port = 3306user = ' root ' passwd = ' 123456 ' db = ' test ' #创建通道conn = pymysql.co Nnect (Host=host, Port=port, User=user, PASSWD=PASSWD, db=db) #创建游标cursor = Conn.cursor () #写sql语句sql = "INSERT into student _age (ID, age, Sex) VALUES (4, +, ' F ') "#执行sql语句effect_row = Cursor.execute (sql) #打印影响行print (effect_row) Time.sleep (30) # Submit Data Conn.commit () #关闭通道conn. Close ()

Create a new table for the ORM Operations database:

The Object Relational Mapping (English: (Object Relational Mapping, or O/RM, or O/R Mapping) is a program technology that enables the conversion of data between different types of systems in an object-oriented programming language  . In effect, it actually creates a "virtual object database" that can be used in a programming language. ' Import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import Declarative_basefrom SQLAlchemy import Column, Integer, stringengine = Create_engine ("Mysql+pymysql://root:[email protected]/test", Encoding= "Utf-8", echo=true) #生成orm基类Base = Declarative_base () class User (base):    #表名    __tablename__ = ' user '    id = column (Integer, primary_key=true)    name = Column (string ())    Password = column (string (64)) # Create a table structure Base.metadata.create_all (engine)

New data:

Import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import Declarative_basefrom SQLAlchemy import Column, Integer, stringfrom sqlalchemy.orm import sessionmaker# Create engineering engine = Create_engine ("mysql+ Pymysql://root:[email protected]/test ", encoding= ' Utf-8 ', echo=true) #创建基本处理类Base = Declarative_base () #class User ( Base):    #表名    __tablename__ = ' user '    id = column (Integer, primary_key=true)    name = Column (String (32))    password = Column (String)    #绑定要操作的数据库Session_class = Sessionmaker (bind=engine) #类似于创建游标Session = Session_ Class () Obj1 = User (id=1, name= ' Summer ', password= ' 111111 ') #加入会话任务列表session. Add (obj1) #此时数据还未真正写入print (Obj1.id, Obj1.name, Obj1.password) #写入数据库session. Commit ()

Querying and formatting output:

Import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import Declarative_basefrom SQLAlchemy import Column, Integer, stringfrom sqlalchemy.orm import sessionmakerfrom sqlalchemy import func# Create engineering engine = C Reate_engine ("Mysql+pymysql://root:[email protected]/test", encoding= ' utf8 ') #创建基本处理类Base = Declarative_base ( ) #class User (Base): #表名 __tablename__ = ' User ' id = column (Integer, primary_key=true) name = Column (String (32)  ) Password = Column (String) #格式化输出查询结果 def __repr__ (self): return "<%s name:%s>"% (Self.id, Self.name) #绑定要操作的数据库Session_class = Sessionmaker (bind=engine) #类似于创建游标Session = Session_class () #查询 # data = Session.que Ry (user). All () #查询全部 # condition Query data = session.query (user). Filter (user.id>1). First () #获取第一个 # Modify Data.name = ' Tom ' data = Ses Sion.query (User). Filter (user.id>1). First () #撤销操作 # session.rollback () # print (' after rollback ') # data = Session.query (User). Filter (user.id>1). First () #提交之后才会作用到数据Library, like the effect of MySQL transaction # session.commit () # data = Session.query (User). Filter (user.id>1). Filter (USER.ID&LT;4). All () # Gets the interval data # statistics = Session.query (User). Filter (User.name.in_ ([' Eric ', ' Alice ']). Count () #分组查询data = Session.query ( Func.count (User.Name), User.Name). group_by (User.Name). All () print (data) #没有重写__repr__之前的访问方式 # print (Data[0].id, Data[0].name, Data[0].password)

foreign key table creation:

"" "foreign key Table creation: Student table students curriculum days sign-in table records, Associated Students and curriculum" "" Import sqlalchemyfrom sqlalchemy import Create_enginefrom Sqlalchemy.ext.declarative Import declarative_basefrom sqlalchemy import Column, Integer, String, ForeignKey, enum# Create Engineering engine = Create_engine ("Mysql+pymysql://root:[email protected]/test", encoding= ' utf-8 ') #创建基类Base = Declarative_base () #创建表结构class Student (base): __tablename__ = ' students ' id = Column (Integer, Nullable=false, primary        _key=true) name = Column (String (ten), nullable=false) Sex = column (Enum (' F ', ' M ')) def __repr__ (self): Return "<id:%s name:%s>"% (Self.id, Self.name) class days (Base): __tablename__ = ' days ' id = Column (Integer, n Ullable=false, primary_key=true) content = Column (String (+), nullable=false) def __repr__ (self): return "<day_id:%s content:%s>"% (Self.id, Self.content) class Record (Base): __tablename__ = ' records ' id = colum N (Integer, Nullable=false, primary_key=true) stu_id = CoLumn (Integer, ForeignKey ("Students.id"), nullable=false) day_id = Column (Integer, ForeignKey ("Days.id"), nullable= False) status = Column (Enum ("Yes", "No") def __repr__ (self): return "<id:%s status:%s>"% (self.id , Self.status) #生成表Base. Metadata.create_all (Engine)

Data insertion for the foreign key table:

"" "foreign key Table creation: Student table students curriculum days sign-in table records, Associated Students and curriculum" "" Import sqlalchemyfrom sqlalchemy import Create_enginefrom Sqlalchemy.ext.declarative Import declarative_basefrom sqlalchemy import Column, Integer, String, ForeignKey, enum# Create Engineering engine = Create_engine ("Mysql+pymysql://root:[email protected]/test", encoding= ' utf-8 ') #创建基类Base = Declarative_base () #创建表结构class Student (base): __tablename__ = ' students ' id = Column (Integer, Nullable=false, primary        _key=true) name = Column (String (ten), nullable=false) Sex = column (Enum (' F ', ' M ')) def __repr__ (self): Return "<id:%s name:%s>"% (Self.id, Self.name) class days (Base): __tablename__ = ' days ' id = Column (Integer, n Ullable=false, primary_key=true) content = Column (String (+), nullable=false) def __repr__ (self): return "<day_id:%s content:%s>"% (Self.id, Self.content) class Record (Base): __tablename__ = ' records ' id = colum N (Integer, Nullable=false, primary_key=true) stu_id = CoLumn (Integer, ForeignKey ("Students.id"), nullable=false) day_id = Column (Integer, ForeignKey ("Days.id"), nullable= False) status = Column (Enum ("Yes", "No") def __repr__ (self): return "<id:%s status:%s>"% (self.id , Self.status) #生成表Base. Metadata.create_all (Engine)

Associated query for foreign key table:

"" "Foreign Key Association query" "" Import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_ Basefrom sqlalchemy.orm import sessionmaker, relationshipfrom sqlalchemy import Column, Integer, String, Enum, ForeignKey #创建工程engine = Create_engine ("Mysql+pymysql://root:[email protected]/test", encoding= ' utf-8 ') #创建基类Base = Declarative_base () #创建表结构class Students (base): __tablename__ = ' Students ' id = Column (Integer, Nullable=false, Primar        y_key=true) name = Column (String (ten), nullable=false) Sex = column (Enum (' F ', ' M ')) def __repr__ (self): Return "<id:%s name:%s>"% (Self.id, Self.name) class days (Base): __tablename__ = ' days ' id = Column (integ Er, nullable=false, primary_key=true) content = Column (String (+), nullable=false) def __repr__ (self): RE Turn "<id:%s content:%s>"% (Self.id, Self.content) class Records (Base): __tablename__ = ' Records ' id = Column (  Integer, Nullable=false, Primary_key=true)  stu_id = Column (Integer, ForeignKey ("Students.id"), nullable=false) day_id = Column (Integer, ForeignKey ("Days.id"), n    Ullable=false) status = Column (Enum ("Yes", "No") students = Relationship ("Students", backref= "Students_records") Days = Relationship (' Days ', backref= ' days_records ') def __repr__ (self): return "<name:%s content:%s status:% S> "% (Self.students.name, self.days.content, Self.status) #生成表Base. Metadata.create_all (Engine) #创建会话Session_ class = Sessionmaker (bind=engine) #创建游标session = Session_class () data = Session.query (Students). Filter (students.name== ' Eric '). First () print (data.students_records)

Two foreign keys are associated to the same table:

"" "Two foreign keys are associated to the same table" "" Import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative Import Declarative_basefrom sqlalchemy Import Column, String, Integer, foreignkeyfrom sqlalchemy.orm import relationshipengine = Create_engine ("Mysql+pymysql://root:[email protected]/test", encoding= ' utf-8 ') Base = Declarative_base () Class Address (Base): __tablename__ = ' address ' id = Column (Integer, Primary_key=true, nullable=false) province = Column (String (ten), nullable=true) City = Column (string), Nullable=true) class Users (Base): __tablename__ = ' Us ERs ' id = column (Integer, Primary_key=true, nullable=false) name = Column (String (ten), nullable=true) First_addr_i d = Column (integer, ForeignKey (' address.id ')) second_addr_id = Column (Integer, ForeignKey (' address.id ')) first_addr = Relationship ("Address", foreign_keys=[first_addr_id]) second_addr = relationship ("Address", Foreign_keys=[second_ ADDR_ID]) def __repr__ (self): return "&ltName%s address%s%s> "% (Self.name, self.first_addr.province, self.second_addr.province) Base.metadata.create_all (engine)

Write Data:

Write Data to Table "" "Import ex5_1from sqlalchemy.orm Import sessionmakersession_class = Sessionmaker (bind=ex5_1.engine) Session = Session_class () add1 = ex5_1.address (province= ' Henan ', city= ' nanyang ') Add2 = ex5_1.address (province= ' Hebei ', city= ' Handan ') add3 = ex5_1.address (province= ' Hunan ', city= ' Yueyang ') session.add_all ([Add1, ADD2, add3]) user1 = Ex5_1. Users (name= ' Eric ', FIRST_ADDR=ADD1, second_addr=add2) user2 = ex5_1.users (name= ' Alice ', FIRST_ADDR=ADD2, second_addr= ADD3) User3 = ex5_1.users (name= ' Peter ', FIRST_ADDR=ADD3, SECOND_ADDR=ADD1) Session.add_all ([User1, User2, User3]) Session.commit ()

Inquire:

Import Ex5_1data = Ex5_1.session.query (ex5_1.users). Filter (ex5_1.users.name== ' Eric '). First () print (data)

　　Many-to-many foreign keys:

#创建表import sqlalchemyfrom sqlalchemy Import create_enginefrom sqlalchemy.ext.declarative import Declarative_basefrom SQLAlchemy import Column, String, Integer, DATE, Table, foreignkeyfrom sqlalchemy.orm import relationshipengine = Create_e Ngine ("Mysql+pymysql://root:[email protected]/test", encoding= ' utf-8 ') Base = Declarative_base () book_m2m_ Author = Table (' Book_m2m_author ', base.metadata, column (' book_id ', Integer, ForeignKey (' books.id ')), column (' author_id ', Integer, ForeignKey (' authors.id '))) class Books (Base): __tablename__ = ' Books ' id = Column (Integer, Prima Ry_key=true, nullable=true) book_name = column (String), nullable=false) publish_time = column (DATE) #创建到author        Mapping of the S table, correlation table Book_m2m_author, callback field Books authors = relationship ("Authors", Secondary=book_m2m_author, backref= "books") def __repr__ (self): return "<book_name:%s>"% Self.book_name class Authors (Base): __tablename__ = ' A Uthors ' id = Column (Integer, Primary_keY=true, nullable=true) author_name = Column (String), Nullable=false) def __repr__ (self): return "<autho R_name:%s> "% Self.author_nameBase.metadata.create_all (engine)

To create the data:

#插入数据import ex6_1from sqlalchemy.orm Import sessionmaker# Create session Session_class = Sessionmaker (bind=ex6_1.engine) session = Session_class () B1 = Ex6_1.books (book_name= ' C + + Primer Plus ') b2 = ex6_1.books (book_name= ' Python ') B3 = Ex6_1.books (book_ Name= ' Java ') a1 = ex6_1.authors (Author_name= ' Eric ') a2 = ex6_1.authors (Author_name= ' Alice ') a3 = Ex6_1.authors (Author_ Name= ' James ') b1.authors = [a1, a2]b2.authors = [A2, a3]b3.authors = [A3, A1]session.add_all ([B1, B2, B3, A1, A2, A3]) Sessi On.commit ()

Associated Query and delete operations:

#查询数据import ex6_1from sqlalchemy.orm Import sessionmakersession_class = Sessionmaker (bind=ex6_1.engine) Session = Session_class () data = Session.query (ex6_1.books). Filter (ex6_1.books.book_name== ' Python '). First () print ("Book_name :%s author:%s "% (data, data.authors)) #删除数据时不用管book_m2m_author, SQLAlchemy will automatically delete # by book Delete Author # book_obj = Session.query (ex6_ 1.Books). Filter (ex6_1.books.book_name== ' Python '). First () # author_obj = Session.query (ex6_1.authors). Filter (Ex6_1. authors.author_name== ' James '). First () # Book_obj.authors.remove (author_obj) #删除作者时, will automatically delete the author's association data with all the books Session.query (ex6_1.authors). Filter (ex6_1.authors.author_name== ' Eric '). First () #从authors里面删除这个作者, and from Book_m2m_ Author inside all books Delete this author session.delete (data) #提交修改到数据库Session. Commit ()

　　

Pymysql and SQLAlchemy module operation MySQL Database