一、pycharm安装pymysql包

第一步点击“File”——点击“Settings”

第二步点击“Python Interpreter”——点击“+”

?第三步搜索“pymysql”——点击“Install Package”?,下载成功即可

?二、使用Python的三方库PyMySQL来访问MySQL数据库

1、准备数据库

drop database if exists sxs;
create database sxs default charset utf8;

use sxs;

drop table if exists s_student;


create table s_student
(
dnum   int not null comment '学号',
dname varchar(10) not null comment '姓名',
dclass  varchar(20) not null comment '班级',
primary key (dnum)
);

insert into s_student values 
	(100, 'candy', '1班'),
	(101, 'jeny', '3班'),
	(102, 'marry', '2班'),
	(103, 'jane', '2班');

2、使用pymysql增加数据

import pymysql
def main():
    num = int(input('学号: '))
    name = input('名字: ')
    class1 = input('班级: ')
    # 1. 创建数据库连接对象
    con = pymysql.connect(host='localhost', port=3306,
                          database='sxs', charset='utf8',
                          user='root', password='root111')
    try:
        # 2. 通过连接对象获取游标
        with con.cursor() as cursor:
            # 3. 通过游标执行SQL并获得执行结果
            result = cursor.execute(
                'insert into s_student values (%s, %s, %s)',
                (num, name, class1)
            )
        if result == 1:
            print('添加成功!')
        # 4. 操作成功提交事务
        con.commit()
    finally:
        # 5. 关闭连接释放资源
        con.close()

if __name__ == '__main__':
    main()

结果展示：

3、使用pymysql删除数据

import pymysql
def main():
    num = int(input('学号: '))
    con = pymysql.connect(host='localhost', port=3306,
                          database='sxs', charset='utf8',
                          user='root', password='root111',
                          autocommit=True)
    try:
        with con.cursor() as cursor:
            result = cursor.execute(
                'delete from s_student where dnum=%s',
                (num, )
            )
        if result == 1:
            print('删除成功!')
    finally:
        con.close()

if __name__ == '__main__':
    main()

?结果展示

4、使用pymysql查询数据

import pymysql
from pymysql.cursors import DictCursor

def main():
    con = pymysql.connect(host='localhost', port=3306,
                          database='sxs', charset='utf8',
                          user='root', password='root111')
    try:
        with con.cursor(cursor=DictCursor) as cursor:
            cursor.execute('select dnum as num, dname as name, dclass as class1 from s_student')
            results = cursor.fetchall()
            print(results)
            print('学号\t姓名\t\t班级')
            for dept in results:
                print(dept['num'], end='\t')
                print(dept['name'], end='\t')
                print(dept['class1'])
    finally:
        con.close()

if __name__ == '__main__':
    main()

?结果展示：

5、使用pymysql修改数据

import pymysql
def main():
    num = int(input('学号: '))
    name = input('名字: ')
    class1 = input('班级: ')
    con = pymysql.connect(host='localhost', port=3306,
                          database='sxs', charset='utf8',
                          user='root', password='root111',
                          autocommit=True)
    try:
        with con.cursor() as cursor:
            result = cursor.execute(
                'update s_student set dname=%s, dclass=%s where dnum=%s',
                (name, class1, num)
            )
        if result == 1:
            print('更新成功!')
    finally:
        con.close()

if __name__ == '__main__':
    main()

?结果展示：