pymysql操作MySQL数据库教程

卓学电子

已于 2023-06-05 16:46:45 修改

阅读量616

点赞数

分类专栏： python开发文章标签：数据库 mysql

于 2023-06-05 16:42:11 首次发布

本文链接：https://blog.csdn.net/weixin_42107504/article/details/131045039

版权

python开发专栏收录该内容

2 篇文章 0 订阅

订阅专栏

前言

使用python脚本操作MySQL前,有必要熟悉掌握以下几个知识点：

（1）了解MySQL，在自己本地电脑安装好MySQL；参考链接：https://blog.csdn.net/weixin_42107504/article/details/130814028

（2）安装一个操作数据库MySQL的客户端软件如Navicat Premium，学会基本操作，方便维护和管理数据库；参考链接：

Navicat Premium基本使用教程_卓学课堂的博客-CSDN博客

（3）学会搭建python开发环境，python脚本的基本使用；

gitee链接有相关下载包和说明文档：https://gitee.com/jobstudyit/NavicatPremium.git

1 准备MySQL客户端环境

这里使用MySQL客户端环境为Navicat Premium，如图所示，打开的是连接名为student_db的连接；

2 python使用pymysql操作MySQL

2.1 环境准备

本文介绍使用的是 Visual Studio Code环境，通过python使用pymysql操作MySQL;

首先要进行pymysql模块安装（后面介绍使用的是pymysql模块）：

pip install pymysql

也可以使用import mysql.connector模块，安装如下：

pip install mysql-connector-python

2.2 数据库常见的操作

我们可以通过python对数据库进行如下操作：

（1）创建不存在的数据库；

（2）查看所有存在的数据库（打印出来），可以设置LIMIT查询的数据量，条件；

（3）新建数据表；

（4）对所新建或者打开的数据表插入数据（单条或者批量插入）；

（5）对数据表的数据进行查询；

（6）where用来对数据进行筛选，输出指定数据，打印出指定数据；

（7）对数据进行排序；

（8）对数据表的数据进行删除操作；

（9）更改数据表中的数据；

（10）删除整个数据表；

3 pymysql操作MySQL

3.1 创建数据库

创建数据库使用 “CREATE DATABASE” 语句，以下创建一个名为 student_info 的数据库；

import pymysql
 
mydb = pymysql.connect(
    host="localhost",
    user="root",
    passwd="mqy10983"
)

mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE student_info")

对客户端进行刷新后（必须刷新，不然看不到），我们看到创建了名为“student_info”的数据库；

3.2 查看数据库

创建数据库后可使用 “SHOW DATABASES” 语句来查看数据库数据库是否创建成功，以及查看当前存在的数据库；

import pymysql
 
mydb = pymysql.connect(
    host="localhost",
    user="root",
    passwd="mqy10983"
)

mycursor = mydb.cursor()
mycursor.execute("SHOW DATABASES")
for x in mycursor:
  print(x)

当前存在的数据库对应的打印：

3.3 新建数据库表

通过“CREATE TABLE”新建一个名为stu_table的数据表

import pymysql
 
mydb = pymysql.connect(     #创建连接mysql
    host="localhost",       #主机名默认localhost
    user="root",            #用户名默认root
    passwd="mqy10983",      #按装MySQL时候设置的密码
    database="student_info" #所创建数据库的名字
)

mycursor = mydb.cursor()    #获取连接的cursor(游标对象)，才能进行各种操作
mycursor.execute("CREATE TABLE stu_table2(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT,score INT)")#设置id为主键（PRIMARY KEY
mycursor.execute("SHOW TABLES") 
for x in mycursor:
    print(x)

打印如下，这里创建了3个数据库表：

客户端 navicat刷新后显示情况如下：

3.4 插入数据

通过 “INSERT INTO” 语句进行数据插入操作

import pymysql
 
mydb = pymysql.connect(     #创建连接mysql
    host="localhost",       #主机名默认localhost
    user="root",            #用户名默认root
    passwd="mqy10983",      #按装MySQL时候设置的密码
    database="student_info" #所创建数据库的名字
)

mycursor = mydb.cursor()    #获取连接的cursor(游标对象)，才能进行各种操作
sql="INSERT INTO stu_table(id,name,age,score)VALUES(%s,%s,%s,%s)"  #插入信息数据
val=("1","zhangsan","18","100")                                    #插入行的值
mycursor.execute(sql,val)              
mydb.commit()                             #数据表有更新，必须使用该语句
print(mycursor.rowcount,"插入数据ok")      #rowcount获取插入数据对应行数

客户端 navicat刷新后显示情况如下：

3.5 批量插入数据

批量插入数据也是使用 “INSERT INTO” 语句，与单条信息插入不同之处：

【1】需要修改插入信息，以及要插入的批量数据值；

【2】mycursor.execute(sql,val)改成mycursor.executemany(sql,val)

批量插入数据执行的代码：

import pymysql
 
mydb = pymysql.connect(     #创建连接mysql
    host="localhost",       #主机名默认localhost
    user="root",            #用户名默认root
    passwd="mqy10983",      #按装MySQL时候设置的密码
    database="student_info" #所创建数据库的名字
)

mycursor = mydb.cursor()    #获取连接的cursor(游标对象)，才能进行各种操作
sql="INSERT INTO stu_table(name,age,score)VALUES(%s,%s,%s)"  #插入信息数据
val=[
     ("lisi","35","87"),   
     ("wagnwu","22","67"),   
     ("xiaoming","27","88"),   
     ("xiaopeng","32","75"),   
    ]                              
mycursor.executemany(sql,val)              
mydb.commit()                             #数据表有更新，必须使用该语句
print(mycursor.rowcount,"插入数据ok")      #rowcount获取插入数据对应行数

客户端 navicat刷新后显示情况，插入前：

客户端 navicat刷新后显示情况，插入后：

3.6 查询数据

使用“SELECT”语句进行数据查询:

import pymysql
 
mydb = pymysql.connect(     #创建连接mysql
    host="localhost",       #主机名默认localhost
    user="root",            #用户名默认root
    passwd="mqy10983",      #按装MySQL时候设置的密码
    database="student_info" #所创建数据库的名字
)

mycursor = mydb.cursor()    #获取连接的cursor(游标对象)，才能进行各种操作
mycursor.execute("SELECT * FROM stu_table")
myresult=mycursor.fetchall()#fetchall用来获取
for x in myresult:
    print(x)

查询到的数据打印如下：

修改对应语句只获取姓名和成绩：

mycursor.execute("SELECT name,score FROM stu_table")

打印如下：

3.7 数据筛选操作

使用语句“WHERE”对数据进行筛选操作，输出指定数据：

import pymysql
 
mydb = pymysql.connect(     #创建连接mysql
    host="localhost",       #主机名默认localhost
    user="root",            #用户名默认root
    passwd="mqy10983",      #按装MySQL时候设置的密码
    database="student_info" #所创建数据库的名字
)

mycursor = mydb.cursor()    #获取连接的cursor(游标对象)，才能进行各种操作
mycursor.execute("SELECT * FROM stu_table WHERE name='lisi'")   
myresult=mycursor.fetchall()
for x in myresult:
    print(x)

也可以通过占位符进行输出：

mycursor.execute("SELECT * FROM stu_table WHERE name=%s",("lisi",))

打印如下：

修改指定规定特殊的数据，如下名字name使用通配符，输出带有“xiao”的信息：

mycursor.execute("SELECT * FROM stu_table WHERE name LIKE '%xiao%'")

3.8 数据排序

使用 “ORDER BY ”语句进行数据排序:

import pymysql
 
mydb = pymysql.connect(     #创建连接mysql
    host="localhost",       #主机名默认localhost
    user="root",            #用户名默认root
    passwd="mqy10983",      #按装MySQL时候设置的密码
    database="student_info" #所创建数据库的名字
)

mycursor = mydb.cursor()    #获取连接的cursor(游标对象)，才能进行各种操作
sql = "SELECT * FROM stu_table ORDER BY score"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

对学生成绩排序后的打印如图所示：

3.9 查询条件设置

通过“LIMIT”语句限定只查询固定长度数据量

import pymysql
 
mydb = pymysql.connect(     #创建连接mysql
    host="localhost",       #主机名默认localhost
    user="root",            #用户名默认root
    passwd="mqy10983",      #按装MySQL时候设置的密码
    database="student_info" #所创建数据库的名字
)

mycursor = mydb.cursor()    #获取连接的cursor(游标对象)，才能进行各种操作
mycursor.execute("SELECT * FROM stu_table LIMIT 3")
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

打印如下：

可以指定起始位置，使用关键字是 OFFSET，如下从第（2+1）=第3条开始输出3条信息；（0为对应第一条信息，以此类推）

mycursor.execute("SELECT * FROM stu_table LIMIT 3 OFFSET 2")

打印如下

3.10 删除数据

使用“DELETE FROM” 语句删除数据，如下删除'wagnwu'对应的数据：

import pymysql
 
mydb = pymysql.connect(     #创建连接mysql
    host="localhost",       #主机名默认localhost
    user="root",            #用户名默认root
    passwd="mqy10983",      #按装MySQL时候设置的密码
    database="student_info" #所创建数据库的名字
)

mycursor = mydb.cursor()    #获取连接的cursor(游标对象)，才能进行各种操作
sql = "DELETE FROM stu_table WHERE name = 'wagnwu'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, " 条记录被删除")

客户端Navicat删除后看不到'wagnwu'的数据了：

可以通过 %s 占位符来转义来进行删除，结果是一致的；如下对"lisi"进行删除：

sql = "DELETE FROM stu_table WHERE name = %s"
name = ("lisi", )
mycursor.execute(sql, name) 
mydb.commit()
print(mycursor.rowcount, " 条被删除")

3.11 数据修改

通过“UPDATE”语句对数据进行修改：

import pymysql
 
mydb = pymysql.connect(     #创建连接mysql
    host="localhost",       #主机名默认localhost
    user="root",            #用户名默认root
    passwd="mqy10983",      #按装MySQL时候设置的密码
    database="student_info" #所创建数据库的名字
)

mycursor = mydb.cursor()    #获取连接的cursor(游标对象)，才能进行各种操作
sql = "UPDATE stu_table SET name = 'liming' WHERE name = 'xiaoming'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, " 条被修改")

客户端Navicat修改后的数据：

也可以通过占位符进行修改：

mycursor = mydb.cursor()    #获取连接的cursor(游标对象)，才能进行各种操作
sql = "UPDATE stu_table SET name = %s WHERE name = %s"
val = ("zhipeng", "xiaopeng")
mycursor.execute(sql, val)
mydb.commit() 
print(mycursor.rowcount, " 条被修改")

3.12 删除表

通过 “DROP TABLE” 语句对表进行删除，如下对stu_table2进行删除：

import pymysql
 
mydb = pymysql.connect(     #创建连接mysql
    host="localhost",       #主机名默认localhost
    user="root",            #用户名默认root
    passwd="mqy10983",      #按装MySQL时候设置的密码
    database="student_info" #所创建数据库的名字
)

mycursor = mydb.cursor()    #获取连接的cursor(游标对象)，才能进行各种操作
sql = "DROP TABLE IF EXISTS stu_table2" 
mycursor.execute(sql)

客户端Navicat删除前：

客户端Navicat 删除后：

4 pymysql和mysql.connector区别

（1）是不同模块，安装方式如下：

pymysql模块安装：

pip install pymysql

import mysql.connector模块安装：

pip install mysql-connector-python

（2）使用区别，对于上面所介绍的数据库基本操作，pymysql和mysql.connector区别在于创建连接使用的接口名字不同，其余都一样；

pymysql创建连接：

#pymysql模块使用
import pymysql
 
mydb = pymysql.connect(     #创建连接mysql
    host="localhost",       #主机名默认localhost
    user="root",            #用户名默认root
    passwd="mqy10983",      #按装MySQL时候设置的密码
    database="student_info" #所创建数据库的名字
)

mysql.connector创建连接：

#mysql.connector模块使用
import mysql.connector
 
mydb = mysql.connector.connect(     #创建连接mysql
    host="localhost",       #主机名默认localhost
    user="root",            #用户名默认root
    passwd="mqy10983",      #按装MySQL时候设置的密码
    database="student_info" #所创建数据库的名字
)