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

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()