python-MySQL驱动之mysql-connector

mysql-connector

mysql-connector 是 MySQL 官方提供的驱动器。

安装驱动

Ubuntu22

sudo apt install python3-mysql.connector

多操作系统

python -m pip install mysql-connector-python3

验证安装

python中,导入模块，不提示错误即为安装成功

import mysql.connector

数据库操作

创建连接后，sql操作命令与在mysql中操作一致,因为下文中常用的

mycursor = mydb.cursor()

为创建一个游标对象mycursor，用于数据库的连接，并且可以执行SQL的命令（查询、插入、更新、删除等）

创建数据库连接

用于连接数据库

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",       # 数据库主机地址
    user="yourusername",    # 数据库用户名
    passwd="yourpassword"   # 数据库密码
)

创建数据库

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",       # 数据库主机地址
    user="yourusername",    # 数据库用户名
    passwd="yourpassword"   # 数据库密码
)
mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE cavia_db")    # 创建一个名为cavia_db的数据库

打开数据库

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",       # 数据库主机地址
    user="yourusername",    # 数据库用户名
    passwd="yourpassword",   # 数据库密码
    database="cavia_db"        # 要打开的数据库名
)

查看数据库里的数据表

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",       # 数据库主机地址
    user="yourusername",    # 数据库用户名
    passwd="yourpassword",   # 数据库密码
    database="cavia_db"        # 要打开的数据库名
)
mycursor = mydb.cursor()
mycursor.execute("SHOW TABLES")
for x in mycursor:
    print(x)

创建/删除数据表

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",       # 数据库主机地址
    user="yourusername",    # 数据库用户名
    passwd="yourpassword",   # 数据库密码
    database="cavia_db"        # 要打开的数据库名
)
mycursor = mydb.cursor()
mycursor.execute(
"CREATE TABLE cavia_test 
(id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
age VARCHAR(255))")
#创建名为caiva_test的数据表，添加主键，定义两列name和age

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",       # 数据库主机地址
    user="yourusername",    # 数据库用户名
    passwd="yourpassword",   # 数据库密码
    database="cavia_db"        # 要打开的数据库名
)
mycursor = mydb.cursor()
sql = "DROP TABLE IF EXISTS cavia_test"
#IF EXISTS 关键字是用于判断表是否存在，只有在存在的情况才删除
mycursor.execute(sql)

数据表中插入(批量)数据

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",       # 数据库主机地址
    user="yourusername",    # 数据库用户名
    passwd="yourpassword"   # 数据库密码
    database="cavia_db"        # 要打开的数据库名
)
mycursor = mydb.cursor()
sql = "INSERT INTO cavia_test (name, age) VALUES (%s, %s)"
val = [
("Cavia", "30"),
("Kaer", "35"),
("MMM", "25"),
("Mulan","18")
mycursor.execute(sql, val)
mydb.commit()    # 数据表内容有更新，必须使用到该语句
print(mycursor.rowcount, "条记录插入成功。")    #输出变动数量（4），记录插入成功

回滚操作

在一个事务中执行多个 SQL 操作时，如果其中某个操作出现错误或失败，需要进行回滚操作。回滚操作可以撤销已经执行的 SQL 操作，使数据库回到事务开始之前的状态。
通过回滚操作，可以确保所有的 SQL 操作要么全部成功提交，要么全部被撤销，从而维护了数据的一致性和可靠性。

例如可以将插入数据的操作中的

mycursor.execute(sql, val)
mydb.commit()    # 数据表内容有更新，必须使用到该语句
print(mycursor.rowcount, "条记录插入成功。")    #输出变动数量（4），记录插入成功

替换为

try:
   # 执行sql语句
   mycursor.execute(sql)
   # 执行sql语句
   mydb.commit()
   print(mycursor.rowcount, "条记录插入成功。")    #输出变动数量（4），记录插入成功
except:
   # 发生错误时回滚
   mydb.rollback()

查询所有数据

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",       # 数据库主机地址
    user="yourusername",    # 数据库用户名
    passwd="yourpassword"   # 数据库密码
    database="cavia_db"        # 要打开的数据库名
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM cavia_test")
myresult = mycursor.fetchall()     # fetchall() 获取所有记录
for x in myresult:
  print(x)

其余查询方法，如查询具体字段、where条件、通配符等。参见MySQL语句

数据排序

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",       # 数据库主机地址
    user="yourusername",    # 数据库用户名
    passwd="yourpassword"   # 数据库密码
    database="cavia_db"        # 要打开的数据库名
)
mycursor = mydb.cursor()
sql = "SELECT * FROM cavia_test ORDER BY name"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

默认的排序方式为升序，关键字为 ASC，如果要设置降序排序，可以设置关键字 DESC

sql = “SELECT * FROM cavia_test ORDER BY name DESC”

Limit

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",       # 数据库主机地址
    user="yourusername",    # 数据库用户名
    passwd="yourpassword"   # 数据库密码
    database="cavia_db"        # 要打开的数据库名
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM cavia_test LIMIT 3 OFFSET 1")
#从第二条查询，查询3条
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

查询/删除/更新指定数据

为了防止数据库查询发生 SQL 注入的攻击，我们可以使用 %s 占位符来转义查询/删除/更新的条件

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",       # 数据库主机地址
    user="yourusername",    # 数据库用户名
    passwd="yourpassword"   # 数据库密码
    database="cavia_db"        # 要打开的数据库名
)
mycursor = mydb.cursor()
sql = "SELECT * FROM cavia_test WHERE name = %s"
na = ("Cavia", )
mycursor.execute(sql,na)
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",       # 数据库主机地址
    user="yourusername",    # 数据库用户名
    passwd="yourpassword"   # 数据库密码
    database="cavia_db"        # 要打开的数据库名
)
mycursor = mydb.cursor()
sql = "DELETE FROM cavia_test WHERE name = %s"
na = ("Cavia", )
mycursor.execute(sql,na)
mydb.commit()    # 数据表内容有更新，必须使用到该语句
print(mycursor.rowcount, "条记录删除成功。")

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",       # 数据库主机地址
    user="yourusername",    # 数据库用户名
    passwd="yourpassword"   # 数据库密码
    database="cavia_db"        # 要打开的数据库名
)
mycursor = mydb.cursor()
sql = "UPDATE cavia_test SET age = %s  WHERE name = %s"
val = ("18", "Cavia")
mycursor.execute(sql,val)
mydb.commit()    # 数据表内容有更新，必须使用到该语句
print(mycursor.rowcount, "条记录修改成功。")