Python 3.x 连接数据库（pymysql 方式）

Python 3.x 连接数据库（pymysql 方式）

首先下载pymysql 模块可以通过 pip 安装pip3 install pymysql
出现Successfully installed pymysql-1.0.2 ，下载成功
1.MySQL先创建一个学生表，方便测试 pymysql 的功能

drop database if exists school;
create database school charset='utf8';
use school;
/*学生表*/
drop table if exists student;
create table student(
id int primary key auto_increment not null comment '学号',
name varchar(255) unique not null comment '姓名',
sex char(1) comment '0:男 1:女',
age int not null comment '年龄'
)engine=innodb auto_increment=100000 default charset=gbk;

insert into student(name,sex,age) values
('张三','男',18),
('李四','女',20),
('王五','男',19),
('赵六','女',21);
select*from student;

2.python

# 导入pymysql模块
import pymysql

# 连接到database（mysql数据库）
con = pymysql.Connect(
    host='localhost',
    port=3306,
    user='root',
    passwd='root',
    db='school',  # 库名
    charset='utf8'
)


# 插入数据
def insert():
    sql = "insert into student (name, sex, age) VALUES ('雷军', '男', 22)"
    cursor.execute(sql)
    con.commit()  # 提交
    print('添加成功！')


# 修改数据
def update():
    sql = "update student set name= '%s' where id=%d"
    data = ('猪八戒', 100003)
    cursor.execute(sql % data)
    con.commit()
    print('修改成功！')


# 查询数据
def select():
    sql = "select * from student where name='%s'"
    data = '猪八戒'
    cursor.execute(sql % data)
    print('查询成功！')
    print(cursor.fetchall())


# 删除数据
def delete():
    sql = "delete from student where id= %d "
    data = 100004
    cursor.execute(sql % data)
    con.commit()
    print('删除成功！')


if __name__ == '__main__':

    while True:
        print('*****************')
        print("1.插入数据")
        print("2.修改数据")
        print("3.查询数据")
        print("4.删除数据")
        n = input("请选择：")
        print(n)
        # 获取游标
        cursor = con.cursor()
        if n == '1':
            insert()
        elif n == '2':
            update()
        elif n == '3':
            select()
        elif n == '4':
            delete()
        else:
            print('输入有误！')
        # 关闭连接
        cursor.close()
        con.close()
        print('*****************')