Python操作MySQL数据库(pymysql)

Python操作MySQL数据库（pymysql）

通过 pymysql库来实现的

首先安装 pymysql

pip install pymysql

下面是源代码,使用 class类的构造方法初始化项目

import pymysql

class datas():
    # 配置MySQL的连接
    def __init__(self):
        '''配置数据库'''
        host = '127.0.0.1'
        port = 3306
        usr = 'root'     # 用户
        pwd = '000000'   # 密码
        database = 'test_db'  #设置数据库
        self.data_connect = pymysql.connect(host=host, port=port, database=database, user=usr, password=pwd,charset='utf8')
        if self.data_connect:
            print('连接成功')
        else:
            print("数据库连接失败！！")

    def into_data(self,arr_data):
        """插入数据"""
        cur = self.data_connect.cursor()  # 创建游标，目的执行sql语句
        sql = f'insert into emp(id, workno, name, gender, age, idcard, workaddress, entrydate) value (%s,%s,%s,%s,%s,%s,%s,%s)'
        cur.execute(sql,arr_data)  # 插入数据
        self.data_connect.commit()  # 提交数据
        cur.close() # 关闭游标

    def search_all(self):
        '''查看所有数据'''
        cur = self.data_connect.cursor()  # 创建游标，目的执行sql语句
        sql = 'select * from emp'
        cur.execute(sql)  # 插入数据
        data = cur.fetchall()
        self.data_connect.commit()  # 提交数据
        cur.close() # 关闭游标
        return data
    def search_value(self,arr_data):
        """搜索数据"""
        cur = self.data_connect.cursor()  # 创建游标，目的执行sql语句
        sql = 'select * from emp where %s like "%%%s%%"' % arr_data
        cur.execute(sql)
        data = cur.fetchall()
        self.data_connect.commit()  # 提交数据
        cur.close()  # 关闭游标
        return data
    def delete_sql(self,emp_id):
        '''删除数据库内容，通过id删除'''
        sql = 'delete from emp where id = %s' % emp_id
        cur = self.data_connect.cursor()  # 创建游标，目的执行sql语句
        delete_bool = cur.execute(sql)
        self.data_connect.commit()  # 提交数据
        cur.close() # 关闭游标
        return delete_bool  # 0 删除失败，1 删除成功

    def update_id(self,emp_id,arr_data):
        '''修改数据,通过id修改'''
        # arr_data = arr_data + (emp_id)
        sql = f'update emp set workno = "{arr_data[0]}",name = "{arr_data[1]}",gender = "{arr_data[2]}",age = {arr_data[3]},idcard = "{arr_data[4]}",workaddress = "{arr_data[5]}",entrydate = "{arr_data[6]}" where id = {emp_id}'
        cur = self.data_connect.cursor()  # 创建游标，目的执行sql语句
        delete_bool = cur.execute(sql)
        self.data_connect.commit()  # 提交数据
        cur.close() # 关闭游标
        return delete_bool  # 0 删除失败，1 删除成功

if __name__ == '__main__':
    t = datas()
    # t.into_data([1, '00001', '柳岩666', '女', 20, '123456789012345678', '北京', '2000-01-01']) # 插入数据
    # t.search_all()  # 搜索所有
    # t.search_value(('name', '张')) # 搜索内容,参数1 类型,参数二 内容
    # t.delete_sql(2)  # 删除数据,通过id删除
    # t.update_id(4,['0002','tike','男',30,'123656789012345678','邯郸','2005-04-11'])   # 修改数据 参数一是id,参数二是数组类型的内容
    for i in t.search_all():
        print(i)

下面是本文中使用的 MySQL 代码, 想测试的小伙伴可以直接复制.

# 创建数据库 test_db
create database if not exists test_db;

# 删除表  if exists 尝试删除
drop table if exists emp;
# 创建 emp 表
create table emp(
id int comment '编号',
workno varchar(10) comment '工号',
name varchar(10) comment '姓名',
gender char(1) comment '性别',
age tinyint unsigned comment '年龄',
idcard char(18) comment '身份证号',
workaddress varchar(50) comment '工作地址',
entrydate date comment '入职时间'
)comment '员工表';
# 插入数据
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (1, '00001', '柳岩666', '女', 20, '123456789012345678', '北京', '2000-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (2, '00002', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (3, '00003', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (4, '00004', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (5, '00005', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (6, '00006', '杨逍', '男', 28, '12345678931234567X', '北京', '2006-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (7, '00007', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (8, '00008', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (9, '00009', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (10, '00010', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (11, '00011', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (12, '00012', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (13, '00013', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (14, '00014', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (15, '00015', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (16, '00016', '周芷若', '女', 18, null, '北京', '2012-06-01');