Python基础 - PyMySQL基本使用

1. pip配置清华的数据源

​ 在C:\Users\用户目录下创建pip目录，并添加配置文件pip.ini。

[global]
index-url = https://pypi.tuna.tsinghua.edu.cn/simple
[install]
trusted-host = https://pypi.tuna.tsinghua.edu.cn

​ pip list查看已安装的Python第三方库，使用pip install pymsql安装PyMySQL库。

2. 创建mysql表

CREATE TABLE `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `email` varchar(255) COLLATE utf8_bin NOT NULL,
    `password` varchar(255) COLLATE utf8_bin NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
AUTO_INCREMENT=1;

3. PyMySQL CRUD实例

# 导入游标
import pymysql.cursors

# 指定主机、用户名、密码连接到数据库，使用DictCursor
conn = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             database='db',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

with conn:
    with conn.cursor() as cursor:
        # 新增一条记录
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('tom@python.org', 'huge-secret'))

    # 连接默认不会自动提交修改，需要手动提交
    conn.commit()

    # 获取最新的那条数据的ID
    # print("最新的数据的ID:", cursor.lastrowid)
    with conn.cursor() as cursor:
        # 读一条记录
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('tom@python.org',))
        result = cursor.fetchone()
        print(result)

输出结果：

{'id': 1, 'password': 'huge-secret'}

4. 创建数据表

import pymysql

conn = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             database='db',
                             charset='utf8mb4')
# 得到可以执行SQL语句的游标对象，执行完毕返回的结果集默认以元组显示
cursor = conn.cursor()
# 定义要执行的sql
sql = """CREATE TABLE `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `email` varchar(255) COLLATE utf8_bin NOT NULL,
    `password` varchar(255) COLLATE utf8_bin NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
AUTO_INCREMENT=1;
"""
cursor.execute(sql)
cursor.close()
conn.close()