Python3: mysql-connector 和 pymysql 模块 - MySQL 数据库操作

我不是女神ヾ 2021-09-21 23:10 742阅读 0赞

本文链接: [https://blog.csdn.net/xietansheng/article/details/115559204][https_blog.csdn.net_xietansheng_article_details_115559204]

[Python3 学习笔记（目录）][Python3]

> Python 连接 MySQL 数据库，可以使用 `mysql-connector` 或 `PyMySQL` 模块，使用其中一个即可。

# 1. mysql-connector #

## 1.1 安装 mysql-connector 驱动，连接数据库 ##

`mysql-connector`模块是 MySQL 官方提供的连接器，使用 pip 安装模块:

$ python3 -m pip install mysql-connector

> 也可以从 MySQL 官网下载安装: [https://www.mysql.com/cn/products/connector/][https_www.mysql.com_cn_products_connector]

连接数据库:

import mysql.connector
    
    db = mysql.connector.connect(
        host="localhost",       # 主机地址，端口默认 port="3306"
        user="username",        # 用户名
        passwd="password",      # 用户密码
    )
    
    print(db)   
    
    # db: <mysql.connector.connection_cext.CMySQLConnection object>

连接并使用指定数据库:

import mysql.connector
    
    db = mysql.connector.connect(
        host="localhost",
        user="username",
        passwd="password",
        database="db_name"      # 连接的数据库名称
    )

MySQL 8.0+ 版本使用的默认加密认证方式为`caching_sha2_password`，而不是 `mysql_native_password`，如果报错`Authentication plugin 'caching_sha2_password' is not supported`，需要再安装`mysql-connector-python`模块:

$ python3 -m pip install mysql-connector-python

安装后再运行上面的代码。

查询用户的加密方式:

## 1.2 获取 Cursor 执行 SQL 语句 ##

连接成功数据库后(`db = mysql.connector.connect(...)`)，通过`db.cursor()`获取游标对象(`CMySQLCursor`)，该游标对象中的方法以及执行 SQL 语句获取结果等方法与`sqlite3`模块中的`sqlite3.Cursor`类似。

参考: [Python3: sqlite3 模块 - SQLite3 数据库操作][Python3_ sqlite3 _ - SQLite3]

代码示例:

import mysql.connector
    
    # 连接数据库
    db = mysql.connector.connect(
        host="localhost",
        user="root",
        passwd="root1234",
    )
    
    # 开始事务
    db.start_transaction()
    
    # 获取游标对象: CMySQLCursor
    cursor = db.cursor()    
    
    # 以字典的方式返回数据行（默认为元祖方式）
    # cursor = db.cursor(dictionary=True) # cursor: CMySQLCursorDict
    
    # 删除 名称为 "test" 的数据库
    cursor.execute("drop database if exists test")
    # 创建 名称为 "test" 的数据库
    cursor.execute("create database if not exists test")
    # 使用 名称为 "test" 的数据库
    cursor.execute("use test")
    
    # 删除表
    cursor.execute("drop table if exists Person")
    
    # 创建表
    cursor.execute(""" create table if not exists Person( id int primary key auto_increment, name varchar(100) not null unique, age int ) """)
    
    # 插入数据（MySQL 使用 %s 作为占位符）
    cursor.execute("insert into Person(name, age) values(%s, %s)", ("Tom1", 25))
    cursor.executemany("insert into Person(name, age) values(%s, %s)",
                       [("Tom2", 26), ("Tom3", 27), ("Tom4", 28)])
    
    # 查询数据
    cursor.execute("select * from Person")
    print(cursor.column_names)      # ('id', 'name', 'age')
    print(cursor.description)       # (('id', None, ...), ('name', ...), ('age', ...))
    print(cursor.fetchall())        # [(1, 'Tom1', 25), (2, 'Tom2', 26), ...]
    
    # 更新数据
    cursor.execute("update Person set name=%s, age=%s where name=%s", ("TOM111", 18, "Tom1"))
    cursor.execute("select * from Person where name=%s", ("TOM111",))
    print(cursor.fetchone())        # (1, 'TOM111', 18)
    
    # 删除数据
    cursor.execute("delete from Person where id=%s", (3,))
    cursor.execute("select * from Person where age>=%s", (26,))
    print(cursor.fetchall())        # [(2, 'Tom2', 26), (4, 'Tom4', 28)]
    
    # 关闭游标
    cursor.close()
    
    # 提交事务
    db.commit()
    # 关闭数据库连接
    db.close()

SELECT 查询结果行默认使用元祖的方式返回数据，可以改为以字典的方式返回:

db = pymysql.connect(...)
    
    cursor = db.cursor(dictionary=True)     # cursor: CMySQLCursorDict
    
    cursor.execute("select * from Person")
    
    cursor.fetchone()       # {'id': 1, 'name': 'Tom1', 'age': 25}
    cursor.fetchall()       # [{'id': 2, 'name': 'Tom2', 'age': 26}, ...]

相关 MySQL 命令:

mysql> select user from mysql.user;     # 查询用户
    mysql> drop database db_name            # 删除数据库
    mysql> create database db_name          # 创建数据库
    mysql> show databases;                  # 查看数据库
    mysql> use db_name;                     # 使用数据库
    mysql> select database();               # 查看当前使用的数据库
    mysql> show tables;                     # 查看表
    mysql> desc table_name                  # 查看表结构

# 2. PyMySQL #

*  `PyMySQL`的 GitHub 仓库: [https://github.com/PyMySQL/PyMySQL][https_github.com_PyMySQL_PyMySQL]
 *  `PyMySQL`的在线文档: [https://pymysql.readthedocs.io/en/latest/][https_pymysql.readthedocs.io_en_latest]

安装`PyMySQL`模块:

$ python3 -m pip install PyMySQL

如果要使用`sha256_password`或`caching_sha2_password`进行身份验证，还需要安装其他依赖项:

$ python3 -m pip install PyMySQL[rsa]

如果要使用 MariaDB 的`ed25519`身份验证方法，还需要安装其他依赖项:

$ python3 -m pip install PyMySQL[ed25519]

连接数据库:

import pymysql
    
    db = pymysql.connect(
        host="localhost",                       # 主机地址, 端口默认 port=3306
        user="username",                        # 用户名
        passwd="password",                      # 用户密码
        database="db_name"                      # 连接后使用的数据库(可选)
    )
    
    print(db)       # db: <pymysql.connections.Connection object>

获取 Cursor，执行 SQL 语句:

import pymysql
    
    # 连接数据库
    db = pymysql.connect(...)
    
    # 开始事务
    db.begin()
    
    # 获取 cursor
    cursor = db.cursor()    # cursor: pymysql.cursors.Cursor
    
    # 执行 SQL 语句
    cursor.execute("insert ...%s, %s", (arg1, arg2,))
    cursor.execute("select ...")
    
    # 获取 SELECT 查询结果
    # cursor.fetchone()
    cursor.fetchall()       # [(value1, value2, ...), ...]
    
    # 关闭 cursor
    cursor.close()
    
    # 提交事务
    db.commit()
    # 关闭数据库连接
    db.close()

`Cursor`的操作与`mysql-connector`和`sqlite3`模块相似。

SELECT 查询结果行默认使用元祖的方式返回数据，可以改为以字典的方式返回:

db = pymysql.connect(
        ...,
        cursorclass=pymysql.cursors.DictCursor      # 使用字典游标
    )
    
    cursor = db.cursor()    # cursor: pymysql.cursors.DictCursor
    
    cursor.fetchone()       # {'id': 1, 'name': 'Tom1', 'age': 25}
    cursor.fetchall()       # [{'id': 2, 'name': 'Tom2', 'age': 26}, ...]

[https_blog.csdn.net_xietansheng_article_details_115559204]: https://blog.csdn.net/xietansheng/article/details/115559204
[Python3]: https://blog.csdn.net/xietansheng/article/details/109266600
[https_www.mysql.com_cn_products_connector]: https://www.mysql.com/cn/products/connector/
[Python3_ sqlite3 _ - SQLite3]: https://blog.csdn.net/xietansheng/article/details/115559160
[https_github.com_PyMySQL_PyMySQL]: https://github.com/PyMySQL/PyMySQL
[https_pymysql.readthedocs.io_en_latest]: https://pymysql.readthedocs.io/en/latest/