<随便写>将数据库中所有表导出为CSV格式的文件

1.工作中遇到的问题，客户不懂数据库，所以他想将镜像文件中还原数据库全部导出为CSV文件（EXCEL）

import pymysql
import csv
import os

# 获取表名
def from_mysql_get_all_tables_name(databases):
    conn = pymysql.connect(
        host=host,
        user=user,
        password=password,
        database=database,
        charset="utf8")
    cursor = conn.cursor()
    sql = 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = "{}";'.format(databases)
    cursor.execute(sql.encode('utf-8'))
    data = cursor.fetchall()  # 获取查询的所有记录
    conn.close()
    return data


# 数据
def from_mysql_get_all_info(table_name):
    conn = pymysql.connect(
        host=host,
        user=user,
        password=password,
        database=database,
        charset="utf8")
    cursor = conn.cursor()
    sql = "select * from {}".format(table_name)
    cursor.execute(sql.encode('utf-8'))
    data = cursor.fetchall()  # 获取查询的所有记录
    conn.close()
    return data


def from_mysql_get_all_column_name(databases, table_name):
    conn = pymysql.connect(
        host=host,
        user=user,
        password=password,
        database=database,
        charset="utf8")
    cursor = conn.cursor()
    sql = "select COLUMN_NAME from information_schema.`COLUMNS`  where TABLE_SCHEMA = '{}' and TABLE_NAME = '{}'".format(
        databases, table_name)
    cursor.execute(sql.encode('utf-8'))
    data = cursor.fetchall()  # 获取查询的所有记录
    conn.close()
    return data


# 不加字段行
def write_csv(table_name, column_name, data):
    filename = 'D:/data/{}.csv'.format(table_name)  # 文件名和路径
    path = 'D:/data'
    if not os.path.exists(path):
        os.mkdir(path)
    with open(filename, mode='w', encoding='utf-8', newline='') as f:
        write = csv.writer(f, dialect='excel')
        for item in data:
            write.writerow(item)


# 加字段行
def column_write_csv(table_name, column_name, data):
    filename = 'D:/data/{}.csv'.format(table_name)  # 文件名和路径
    path = 'D:/data'
    header = []
    for row in column_name:
        colu = row[0]
        header.append(colu)
    if not os.path.exists(path):
        os.mkdir(path)
    with open(filename, mode='w', encoding='utf-8',newline='') as f:
        write = csv.writer(f, dialect='excel')
        write.writerow(header)
        for item in data:
            write.writerow(item)


def main():
    # 1.获取该数据库下所有表名
    tables_name = from_mysql_get_all_tables_name(database)
    for row in tables_name:
        # 表名
        table_name = row[0]
        # print(table_name)
        # 2.获取该表下的所有字段名
        column_name = from_mysql_get_all_column_name(database, table_name)
        # print(column_name)
        # 3.获取该表的所有数据
        data = from_mysql_get_all_info(table_name)
        # print(data)
        # 4.将数据写入CSV
        column_write_csv(table_name, column_name, data)


if __name__ == '__main__':
    # 0.填写数据库相关参数
    database = "mysql"
    host = "127.0.0.1"
    user = "root"
    password = "root"
    main()