Python进行MySQL数据抽取与CSV文件生成

大博士.J

已于 2025-03-11 16:23:33 修改

阅读量535

点赞数 7

文章标签： python mysql 开发语言

于 2025-03-11 16:22:49 首次发布

本文链接：https://blog.youkuaiyun.com/qq_24396737/article/details/146182238

版权

引言

在数据分析领域，经常需要从数据库中提取数据并将其转换为易于处理的格式。本文将详细介绍如何使用Python脚本连接到MySQL数据库、抽取特定表的数据，并将这些数据保存为CSV文件。这个过程包括设置数据库连接、编写SQL查询语句、执行查询以及将结果写入CSV文件。

准备工作

首先，确保你的环境中安装了必要的库：

pandas：用于数据操作和分析。
numpy：支持大规模多维数组和矩阵运算。
pymysql：用于与MySQL数据库交互。

你可以通过以下命令来安装这些依赖：

pip install pandas numpy pymysql

数据抽取流程

配置数据库连接
在开始之前，你需要准备好数据库的连接信息，如主机地址、端口、用户名、密码及数据库名等。这部分信息通常由数据库管理员提供。
编写函数抽取数据
使用get_mysql_data函数根据指定的表名、列名、WHERE条件以及LIMIT值来构造SQL查询语句，并执行该语句以获取数据。
保存数据至CSV
一旦从数据库中成功获取了数据，接下来就是利用save_to_csv函数将这些数据转换成DataFrame格式，并保存为CSV文件。
数据处理与输出
最后，我们定义了一个结构化的字典row来存储每一行数据的信息，并遍历t_user_data和test_data两个列表，填充row字典并将每个条目添加到out_data列表中。最后，调用save_to_csv函数将处理后的数据保存为CSV文件。

完整代码

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import pandas as pd
import numpy as np
import pymysql

# MySQL 数据库连接配置 host=数据库地址   port=数据库端口  user=数据库名  password=数据库密码 database=要连接的库名  charset=字符集utf-8
db_config = {
    'host': '1.1.1.5',
    'port': 3306,
    'user': 'root',
    'password': '123456',
    'database': 'test',
    'charset': 'utf8mb4',
}

# 创建 MySQL 连接对象
conn = pymysql.connect(**db_config)
cursor = conn.cursor()


# 获取数据
def get_mysql_data(table, columns, where_clause, limit):
    try:
        # 将列名列表转换为逗号分隔的字符串
        cols_str = ', '.join(columns)
        sql = f'SELECT {cols_str} FROM {table} '
        # 添加WHERE子句（如果有）
        if where_clause:
            sql += f" WHERE {where_clause}"

        # 如果指定了限制条件，则添加LIMIT子句
        if limit is not None:
            sql += f" LIMIT {limit}"

        print("sql语句:  " + sql)
        cursor.execute(sql)

        # 获取所有查询结果
        rows = cursor.fetchall()

        return rows
    except Exception as e:
        print('存储到MySQL出错: ', e)
        return None


# 将数据存储csv
def save_to_csv(filename, data, columns):
    try:
        # 将数据转换成 DataFrame
        df = pd.DataFrame(data, columns=columns)
        # 将 DataFrame 保存为 CSV 文件
        df.to_csv(filename, index=False, encoding='utf-8-sig')
        print(f"\n数据已保存到 {filename} \n")
        return df.to_csv(index=False, encoding='utf-8-sig')
    except Exception as e:
        print('保存到CSV出错: ', e)


# 处理并输出
def out_main():
    # 获取t_user
    t_user_table = 't_user'
    t_user_columns = ['id', 'username', 'password']
    t_user_where_clause = None
    t_user_limit = None
    t_user_data = get_mysql_data(t_user_table, t_user_columns, t_user_where_clause, t_user_limit)
    print(f'表1数据总数：{len(t_user_data)}')

    # 获取test
    test_table = 'test'
    test_columns = ['id', 'name', 'create_time', 'update_time', 'is_deleted']
    test_where_clause = None
    test_page_limit = None
    test_data = get_mysql_data(test_table, test_columns, test_where_clause, test_page_limit)
    print(f'表2数据总数：{len(test_data)}')

    # 定义结构
    row = {
        'user_id': '',
        'user_username': '',
        'user_password': '',
        'test_id': '',
        'test_name': '',
        'test_create_time': '',
        'test_update_time': '',
        'test_is_deleted': ''
    }
    out_data = []

    # 遍历 t_user_data 并填充 out_row
    for user_row in t_user_data:
        for test_row in test_data:
            row['user_id'] = user_row[0]
            row['user_username'] = user_row[1]
            row['user_password'] = user_row[2]
            row['test_id'] = test_row[0]
            row['test_name'] = test_row[1]
            row['test_create_time'] = test_row[2]
            row['test_update_time'] = test_row[3]
            row['test_is_deleted'] = test_row[4]
            out_data.append(row)

    # 存储csv
    if out_data:
        # 完成输出
        print(f'输出结构:\n' + save_to_csv(f'out_data.csv', out_data, row.copy()))


# 在 main 函数开始时连接数据库
if __name__ == '__main__':
    out_main()
    # 关闭数据库连接
    cursor.close()
    conn.close()