知识周汇 | MySQL增删改查与Python连接_pymysql增删改查-优快云博客

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 自增主键
    name VARCHAR(50) NOT NULL,         -- 姓名，不能为空
    age INT,                           -- 年龄
    email VARCHAR(100) UNIQUE          -- 邮箱，唯一
);

1.2 插入数据

使用 INSERT INTO 语句向表中插入数据。

插入单条数据：

INSERT INTO users (name, age, email)
VALUES ('Alice', 25, 'alice@example.com');

插入多条数据：

INSERT INTO users (name, age, email)
VALUES 
    ('Bob', 22, 'bob@example.com'),
    ('Charlie', 28, 'charlie@example.com');

1.3 查询数据

使用 SELECT 语句查询数据。

查询所有数据：

SELECT * FROM users;

查询特定字段：

SELECT name, email FROM users;

带条件查询：

SELECT * FROM users WHERE age > 25;

排序查询：

SELECT * FROM users ORDER BY age DESC;  -- 按年龄降序

分页查询：

SELECT * FROM users LIMIT 2 OFFSET 1;  -- 跳过 1 条，返回 2 条

1.4 更新数据

使用 UPDATE 语句更新数据。

更新单条数据：

UPDATE users
SET age = 26
WHERE name = 'Alice';

更新多条数据：

UPDATE users
SET age = age + 1  -- 年龄加 1
WHERE age < 25;

1.5 删除数据

使用 DELETE 语句删除数据。

删除特定数据：

DELETE FROM users
WHERE name = 'Bob';

删除所有数据：

DELETE FROM users;  -- 清空表

1.6 删除表

使用 DROP TABLE 语句删除表。

DROP TABLE users;  -- 删除表及其数据

1.7 完整示例

以下是一个完整的示例，包括创建表、插入数据、查询、更新和删除操作：

-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    email VARCHAR(100) UNIQUE
);

-- 插入数据
INSERT INTO users (name, age, email)
VALUES 
    ('Alice', 25, 'alice@example.com'),
    ('Bob', 22, 'bob@example.com'),
    ('Charlie', 28, 'charlie@example.com');

-- 查询数据
SELECT * FROM users;

-- 更新数据
UPDATE users
SET age = 26
WHERE name = 'Alice';

-- 删除数据
DELETE FROM users
WHERE name = 'Bob';

-- 删除表
DROP TABLE users;

2 Mysql与python连接

ysql-connector-python 是 MySQL 官方提供的 Python 驱动程序，用于在 Python 程序中连接和操作 MySQL 数据库。以下是安装方式

 pip install mysql-connector-python -i https://mirrors.aliyun.com/pypi/simple

Python与MySQL的连接，实质上是借助Python的数据库驱动来执行SQL语句，进而管理和操作数据库中的数据，以下是一个示例

2.0代码示例

# coding=utf-8


import mysql.connector.pooling

# 本地数据库
__config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "XXXXX",
    "database": "test"
}

try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **__config,
        pool_size=10
    )
except Exception as e:
    print(e)


class UserDao():
    def select_infro_from_user(self):
        sql = "SELECT id ,name,age,email FROM users;"

        print(sql)
        try:
            con = pool.get_connection()
            cursor = con.cursor()
            cursor.execute(sql)
            result = cursor.fetchall()
            return result
        except Exception as e:
            if "con" in dir():
                con.rollback()
        finally:
            if "con" in dir():
                con.close()


result = UserDao().select_infro_from_user()
print(result)

上述代码逻辑结构

导入模块: 导入MySQL连接池模块。
配置数据库连接: 定义数据库连接的配置信息。
创建连接池: 使用配置信息创建MySQL连接池。
定义 UserDao 类: 封装与用户相关的数据库操作。
定义 select_infro_from_user 方法: 查询users表中的用户信息。
调用方法并打印结果: 调用UserDao类的方法，获取查询结果并打印。

2.1 导入模块

import mysql.connector.pooling

作用: 导入MySQL连接池模块。
解释: mysql.connector.pooling 是 mysql-connector-python 库中的一个模块，用于创建和管理数据库连接池。连接池可以提高数据库操作的效率，避免频繁创建和关闭连接。

2.2 数据库连接配置

# 本地数据库
__config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "1901xxx",
    "database": "test"
}

作用: 定义数据库连接的配置信息。
解释:
- "host": "localhost": 数据库服务器地址，这里是本地主机。
- "port": 3306: 数据库端口号，MySQL默认端口是3306。
- "user": "root": 数据库用户名，这里是root。
- "password": "1901xxx": 数据库密码。
- "database": "test": 要连接的数据库名称，这里是test。

2.3 创建连接池

try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **__config,
        pool_size=10
    )
except Exception as e:
    print(e)

作用: 创建MySQL连接池。
解释:
- **__config: 将__config字典解包为关键字参数，传递给MySQLConnectionPool。
- pool_size=10: 设置连接池的大小为10，即最多可以同时存在10个数据库连接。
- try-except块: 捕获创建连接池时可能发生的异常，并打印错误信息。

2.4 定义 `UserDao` 类

class UserDao():

作用: 定义一个名为UserDao的类。
解释: 这个类封装了与用户相关的数据库操作。

2.5 定义 `select_infro_from_user` 方法

def select_infro_from_user(self):
    sql = "SELECT id ,name,age,email FROM users;"

    print(sql)
    try:
        con = pool.get_connection()
        cursor = con.cursor()
        cursor.execute(sql)
        result = cursor.fetchall()
        return result
    except Exception as e:
        if "con" in dir():
            con.rollback()
    finally:
        if "con" in dir():
            con.close()

作用: 从users表中查询用户信息。
解释:
- sql = "SELECT id ,name,age,email FROM users;": 定义SQL查询语句，查询users表中的id、name、age和email字段。
- print(sql): 打印SQL语句，便于调试。
- try-except-finally块: 捕获并处理数据库操作中的异常。
  - con = pool.get_connection(): 从连接池中获取一个数据库连接。
  - cursor = con.cursor(): 创建一个游标对象，用于执行SQL语句。
  - cursor.execute(sql): 执行SQL查询语句。
  - result = cursor.fetchall(): 获取查询结果的所有行。
  - return result: 返回查询结果。
  - except Exception as e:: 捕获异常并回滚事务。
  - finally:: 无论是否发生异常，最后都会关闭数据库连接。

2.6 调用 `UserDao` 类的方法

result = UserDao().select_infro_from_user()
print(result)

作用: 调用UserDao类的select_infro_from_user方法，获取查询结果并打印。
解释:
- result = UserDao().select_infro_from_user(): 创建一个UserDao对象，并调用其select_infro_from_user方法，将查询结果存储在result变量中。
- print(result): 打印查询结果，便于查看。

3 总结与思考

MySQL 是数据存储和管理的核心工具，熟练掌握其增删改查操作是数据分析的基础。通过 Python 连接 MySQL，可以实现自动化数据访问和处理，极大地提高数据分析的效率。