python3 使用 pymysql 链接数据库操作

pymysql 数据库链接

如果没有安装 PyMySQL ，参考安装文档。
PyMySQL 安装文档

前期建库准备

create user citizenwang identified by 'yourpassword';

create database python;

grant all privileges on *.* to 'citizenwang'@'%' identified by 'yourpassword';

blush privileges;

连接数据库 connect

connet 方法返回一个数据库链接对象 <class 'pymysql.connections.Connection'>

方法1：直接使用 connect 方法

connection = pymysql.connect(host='localhost', port=3306, user='user', password='passwd', db='db', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)

• charset 字符集，一般是 utf8
• port 端口， 默认 3306
• cursorclass 建议省略

方法2：使用字典 或 函数连接数据库

def connect_mysql():
    db_config = {
        'host':'127.0.0.1',
        'port':3306,
        'user':'db_user',
        'password':'db_user_passwd',
        'db':'database_name',
        'charset':'utf8mb4'
    }

    try:
        cms = pymysql.connect(**db_config)
    except Exception as e:
        print(e)
    return cms

connect_mysql()

该方法替代 pymysql.connect 的好处，是在数据库配置有修改的情况下，比如修改了端口（最为常见）、密码等等，那么只需要更改这个字典，或者函数即可，而不需去修改每一个 pymysql.connect 方法。

数据库链接对象的方法

import pymysql

def connect_mysql():
    db_config = {
        'host':'127.0.0.1',
        'port':3306,
        'user':'citizenwang',
        'password':'yourpassword',
        'db':'python',
        'charset':'utf8mb4'
    }
    try:
        cms = pymysql.connect(**db_config)
    except Exception as e:
        print(e)
    return cms

db = connect_mysql()
print(dir(db))
print(type(db))

['autocommit', 'begin', 'character_set_name', 'charset',  'close', 'commit', 'connect', 'connect_timeout', 'cursor', 'cursorclass', 'db', 'get_autocommit', 'get_host_info', 'get_proto_info', 'get_server_info', 'host', 'host_info', 'init_command', 'insert_id', 'kill', 'literal', 'open', 'password']

<class 'pymysql.connections.Connection'>

• 比较常用的 数据库链接对象方法有 cursor

数据库对象操作

import pymysql

def connect_mysql():                # 创建一个包含connect方法参数的函数
    db_config = {
        'host':'127.0.0.1',
        'port':3306,
        'user':'citizenwang',
        'password':'yourpassword',
        'db':'python',
        'charset':'utf8mb4'        # charset 可以只写 utf8，注意不是 utf-8
    }
    try:
        cms = pymysql.connect(**db_config)   # 创建一个 pymysql 链接对象，并赋值给 变量 cms
    except Exception as e:
        print(e)
    return cms

if __name__ == '__main__':
    sql = 'create table test(id int not null); insert into test(id) values(1000);'  # 定义一个需要执行的 sql 语句
    db = connect_mysql()   # 使用 connetc_mysql() 函数创建一个数据库链接对象
    cus = db.cursor()      # 使用数据库对象的 cursor() 方法创建一个游标对象 <class 'pymysql.cursors.Cursor'>
    print(dir(db))
    print(dir(cus))
    try:
        cus.execute('drop table if exists test')  # execute 方法执行 sql，如果 test 表存在，删除
        cus.execute(sql)   # execute 执行定义的 sql 变量语句
        cus.close()  # 关闭数据库游标对象
        db.commit()  # 提交到数据库执行，注意此处是 数据库链接对象的 commit 方法，不是游标的方法
    except Exception as e:
        db.rollback()   # 如果发生错误，先执行回滚操作
        raise e         # 如果发生错误，raise 错误并退出
    finally:
        cus.close()  # 无论是否有误，都关闭数据库链接

• except Exception 内的函数，rollback 操作，一定在 raise e 之前。

数据库查看是否成功

use python;
show tables;
select * from test;

游标 cursor

db = pymysql.connect(config)  # 创建 数据库链接对象
cus = db.cursor     # 创建 游标对象
print(dir(cus))     # 查看游标的方法

游标常用方法：

cus.cursor()   创建游标对象
cus.close()  关闭游标对象

cus.fetchone()  得到结果集的下一行
cus.fetchall()   得到结果集剩下的所有行
cus.fetchmany()

cus.execute()   执行一个数据库命令
cus.executemany(sql, args)  
# sql 必须是字符串类型
# args 是一个集合

示例：

import pymysql

def connect_mysql():                # 创建一个包含connect方法参数的函数
    db_config = {
        'host':'127.0.0.1',
        'port':3306,
        'user':'citizenwang',
        'password':'yourpassword',
        'db':'python',
        'charset':'utf8mb4'        # charset 可以只写 utf8，注意不是 utf-8
    }
    try:
        cms = pymysql.connect(**db_config)   # 创建一个 pymysql 链接对象，并赋值给 变量 cms
    except Exception as e:
        print(e)
    return cms

if __name__ == '__main__':
    number = []
    for i in range(1,100):
        number.append(i)                              # 创建一个包含 1 到 99 的列表
    insert_sql = 'insert into test(id) value(%s);'    # 执行插入语句，将 number 插入列表
    select_sql = 'select * from test;'                # 选择所有的表内容
    db = connect_mysql()                              # 创建一个 PyMySQL 数据库链接对象
    cus = db.cursor()                                 # 创建一个游标对象
    try:
        cus.execute('drop table if exists test; create table test(id int not null);')   # 执行语句，如果存在删除，并创建
        cus.executemany(insert_sql, number)           # executemany(arg, para) 必须两个参数，第一个是 sql 语句，第二个是参数
        cus.execute(select_sql)                       # execute(arg) 方法，执行

        result1 = cus.fetchone()                      # fetchone()，选取一行内容输出
        print('result1：', result1)

        result2 = cus.fetchmany(4)                    # fetchmany(arg) 指定选取的行数
        print('result2：', result2)

        result3 = cus.fetchall()                      # fetchall() 从当前游标开始，读取所有行
        print('result3：', result3)

        cus.close()                                   # 关闭游标
        db.commit()                                   # 提交数据库，如果没有这个操作，插入的数据就不会成功
    except Exception as e:
        db.rollback()
        raise e
    finally:
        cus.close()

示例结果：

result1： (1,)
result2： ((2,), (3,), (4,), (5,))
result3： ((6,), (7,), (8,), (9,))