最全MySQL8.0实战教程 24 MySQL的pymysql操作

水深无声 2024-03-25 12:35 106阅读 0赞

#### 最全MySQL8.0实战教程 ####

#### 文章目录 ####

*   *   *  最全MySQL8.0实战教程
         *  24 MySQL的pymysql操作
         *   *   *  24.1.1 查询
                 *  24.1.2 增删改

[【黑马程序员MySQL知识精讲+mysql实战案例\_零基础mysql数据库入门到高级全套教程】 ][MySQL_mysql_mysql_]

#### 24 MySQL的pymysql操作 ####

###### 24.1.1 查询 ######

PyMySQL 是一个纯 Python 实现的 MySQL 客户端库，支持兼容 Python 3，用于代替 MySQLdb。

【先简单准备下数据】

drop database if exists mydb17_pymysql;
    create database if not exists mydb17_pymysql;
    
    use mydb17_pymysql;
    
    create table if not exists student
    (
        sid   int primary key auto_increment,
        sname varchar(20),
        age   int
    );
    
    insert into student
    values (null, '宋江', 30),
           (null, '武松', 28),
           (null, '林冲', 26);

![在这里插入图片描述][8530ca7791cf4c6bb1a0fd3355208fab.png_pic_center]

编写代码

import pymysql
    
    # 1. 获取MySQL连接
    conn = pymysql.connect(host='localhost',
                           port=3306,
                           user='root',
                           password='123456',
                           database='mydb17_pymysql',
                           charset='utf8')
    
    # 2. 获取游标
    cursor = conn.cursor()
    
    # 3. 执行SQL语句
    sql = "select * from student"
    
    row_count = cursor.execute(sql)
    print("SQL语句执行影响的行数%d" % row_count)
    
    # 4. 取出结果集中的数据
    for line in cursor.fetchall():
        print(line)
        
    # 5. 关闭游标
    cursor.close()
    
    # 6. 关闭连接
    conn.close()

运行结果

![在这里插入图片描述][edee8fdc4da9494bb95d09d424322f67.png_pic_center]

###### 24.1.2 增删改 ######

【增】

# 【插入数据】
    sql = "insert into student values(%s,%s,%s)"
    data = (4, '晁盖', 34)
    
    row_count = cursor.execute(sql, data)
    print("SQL语句执行影响的行数%d" % row_count)
    
    conn.commit() # 一定记得提交事务，不然不生效

![在这里插入图片描述][9763609eaf1e4d89a199e3c0c05f1aef.png_pic_center]

当前数据库student 表是这样的

![在这里插入图片描述][97da24f8209441de9a93f6b2575e2af2.png_pic_center]

代码运行没问题

查看数据库

![在这里插入图片描述][01f6b4afbd85469bb550fffa21d92ef2.png_pic_center]

【改】

sql = "update student set sname = %s where sid = %s"
    data = ('李逵', 4)

![在这里插入图片描述][c65027d0a85a43c8816abb2ab2e5174c.png_pic_center]

数据表情况

![在这里插入图片描述][a7c85ded8dfb4943a3d9ff86476b62ec.png_pic_center]

【删】

sql = "delete from student where sid = %s"
    data = (4)

![在这里插入图片描述][c2e575e1f6eb49378d93221b52b920d6.png_pic_center]

数据表情况

![在这里插入图片描述][7ffe995c818b460f9106925248a85a43.png_pic_center]

OK，完结撒花，感谢黑马！！

[MySQL_mysql_mysql_]: https://www.bilibili.com/video/BV1iF411z7Pu
[8530ca7791cf4c6bb1a0fd3355208fab.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/bb5e61337309405087e9d987b0e5e7ae.png
[edee8fdc4da9494bb95d09d424322f67.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/4b2662e420f843078bc03cdaeafa9480.png
[9763609eaf1e4d89a199e3c0c05f1aef.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/3f41857b5a32457b844e8dc445391a50.png
[97da24f8209441de9a93f6b2575e2af2.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/90b5383563dc4370b92903a8577d7a85.png
[01f6b4afbd85469bb550fffa21d92ef2.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/ecdde4a8971a40ecb0b9a62fefaf84b4.png
[c65027d0a85a43c8816abb2ab2e5174c.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/a3f3aa0333b64add9e41a33b69a7f27f.png
[a7c85ded8dfb4943a3d9ff86476b62ec.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/eb769e1396904e8eb916b63de792c7f9.png
[c2e575e1f6eb49378d93221b52b920d6.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/24830380186f47b9b03c7dd22711cc8b.png
[7ffe995c818b460f9106925248a85a43.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/8bf7b44f68974425b1b7b8fd7af36901.png