使用pymysql和SQLAlchemy两种方式让Python对MySQL数据进行操作

常规方式-pymysql

1.安装包

此处我使用的是pipenv虚拟环境，故命令如下

pipenv install pymysql

2.配置连接

这里需要注意charset编码一定不要带-，比如utf-8

    connect = pymysql.Connect(
        host='localhost',
        port=3306,
        user='root',
        passwd='666666',
        db='race_flask',
        charset='utf8' 
    )

3.获取连接、游标

    def start_conn(self):
        cursor = self.connect.cursor()
        return self.connect,cursor

4.增删改查

这里需要注意，对数据库数据进行操作时，需要调用commit()方法进行提交

    # 插入数据
    def insert_db(self,cursor,*args):
        sql = 'insert into test_student(sid,name,sex) values ("%s","%s","%s")'
        cursor.execute(sql % args)
        cursor.commit()

    # 删除数据
    def del_data(self,cursor,*args):
        sql = 'delete from test_student where "%s" = "%s" '
        cursor.execute(sql % args)

    # 修改数据
    def updata_db(cursor,*arg):
        sql = 'update test_student set name="%s",sex="%s" where id = 1 '
        cursor.execute(sql % arg)
        cursor.commit()

    # 查询全部
    def query_all(self,cursor):
        sql = 'select * from test_student'
        cursor.execute(sql)
        return cursor.fetchall()

    # 查询数据
    def query_data(self,cursor,* args):
        print(args)
        sql = 'select * from  test_student where "%s" = "%s" '
        cursor.execute(sql % args)
        return cursor.fetchall()

5.关闭游标、连接

    def close_connn(self,cursor,connect):
        cursor.close()
        connect.close()

6.完整代码如下

# -*- coding:utf-8 -*-
# Created by ZhaoWen on 2020/10/17

import pymysql

class common_sql():
    connect = pymysql.Connect(
        host='localhost',
        port=3306,
        user='root',
        passwd='666666',
        db='race_flask',
        charset='utf8' 
    )

    # 连接数据库 获取连接 游标
    def start_conn(self):
        cursor = self.connect.cursor()
        return self.connect,cursor


    # 插入数据
    def insert_db(self,cursor,*args):
        sql = 'insert into test_student(sid,name,sex) values ("%s","%s","%s")'
        cursor.execute(sql % args)
        cursor.commit()

    # 删除数据
    def del_data(self,cursor,*args):
        sql = 'delete from test_student where "%s" = "%s" '
        cursor.execute(sql % args)

    # 修改数据
    def updata_db(cursor,*arg):
        sql = 'update test_student set name="%s",sex="%s" where id = 1 '
        cursor.execute(sql % arg
        cursor.commit()

    # 查询全部
    def query_all(self,cursor):
        sql = 'select * from test_student'
        cursor.execute(sql)
        return cursor.fetchall()

    # 查询数据
    def query_data(self,cursor,* args):
        print(args)
        sql = 'select * from  test_student where "%s" = "%s" '
        cursor.execute(sql % args)
        return cursor.fetchall()

    # 关闭连接
    def close_connn(self,cursor,connect):
        cursor.close()
        connect.close()

ORM映射方式-SQLAlchemy

1.安装包

pipenv isntall sqlalchemy

2.创建引擎、获取数据库连接

     engine = create_engine('mysql://root:666666@localhost:3306/race_flask?charset=utf8')
     connect = engine.connect()

3.创建declarative_base实例、并创建模型类

     Base = declarative_base()

     class student(Base):
         __tablename__ = 'test_student'
         sid = Column(Integer, primary_key=True)
         name = Column(String(20))
         sex = Column(String(4))

4.创建session持久会话对象

     # 创建持久化session 这个对象可以和数据库进行交互
     Session = sessionmaker(bind=engine)
     session = Session()

5.增删改查

sqlalchemy对数据库中数据进行修改时，都是通过操作对象的方式进行

     # 增加
     stu = student(sid='2', name='李四', sex='男')
     session.add(stu)
     session.commit()

        ---

     # 修改 通过对象更新数据
     stu = session.query(student).filter(student.name=='张三').first()
     stu.sex = '女'
     session.commit()

        ------

     # 查询
     students = session.query(student).all()
     for stu in students:
         print(stu)
     stu_name = session.query(student.name).all()
     print(stu_name)
        
     # 删除
     stu = session.query(student).filter(student.sex == '男').first()
     session.delete(stu)
     session.commit()

6.完整代码

# -*- coding:utf-8 -*-
# Created by ZhaoWen on 2020/10/17

from sqlalchemy import create_engine
from sqlalchemy import Table,Column,Integer,String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

class sqlalchemy_sql():
    engine = create_engine('mysql://root:666666@localhost:3306/race_flask?charset=utf8')
    connect = engine.connect()
    Base = declarative_base()
    Session = sessionmaker(bind=engine)
    session = Session()

    # 增加
    def insert_data(self):
        stu = student(sid='2', name='李四', sex='男')
        self.session.add(stu)
        self.session.commit()

    # 删除
    def del_Data(self):
        stu = self.session.query(student).filter(student.sex == '男').first()
        self.session.delete(stu)
        self.session.commit()

    # 修改 通过对象更新数据
    def update_Data(self):
        stu = self.session.query(student).filter(student.name == '张三').first()
        stu.sex = '女'
        self.session.commit()

    # 查询
    def query_data(self):
        students = self.session.query(student).all()
        for stu in students:
            print(stu)
        stu_name = self.session.query(student.name).all()
        return stu_name