import pymysql
import xlwt
# 创建mysql连接
connect = pymysql.connect(
host='127.0.0.1',
db="lingyun",
user='root',
passwd='2418870649',
port=3307,
charset='utf8',
use_unicode=True)
# 通过cursor执行增删查改
# 带字段名输出
cursor = connect.cursor(cursor=pymysql.cursors.DictCursor)
# 仅数据
cursor_name = connect.cursor()
# 获取所有表名
tables_name_sql = "SHOW TABLES"
# 执行sql(获取数据库表名)
cursor_name.execute(tables_name_sql)
# 获取数据
result_name = cursor_name.fetchall()
for name in result_name:
# sql
select_sql = "SELECT COLUMN_NAME 列名,DATA_TYPE 字段类型, CHARACTER_MAXIMUM_LENGTH 长度, IS_NULLABLE 是否为空, COLUMN_COMMENT 描述 FROM INFORMATION_SCHEMA. COLUMNS WHERE table_schema = 'lingyun' AND table_name = '%s'" % (name[0])
# 执行sql语句(获取表结构)
cursor.execute(select_sql)
# 获取数据
result = cursor.fetchall()
# 创建一个Workbook对象,相当于创建了一个Excel文件
workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet('sheet', cell_overwrite_ok=True)
# 输出表头
i = 0
for item in result[0]:
worksheet.write(0, i, item)
i += 1
# 输出数据
s = 1
for index in range(len(result)):
t = 0
for item in result[index]:
worksheet.write(s, t, result[index][item])
t += 1
s += 1
workbook.save(name[0]+'.xls')
# 关闭游标
cursor.close()
# 关闭连接
connect.close()
Python生成数据库中所有表的的字典(execl)
于 2022-05-19 10:14:49 首次发布