数据库数据导出

一、使用Python导出MySQL数据到Excel

  一）使用pymysql+xlwt

import pymysql
import xlwt
import time

TODAY = time.strftime('%Y%m%d')
## 自定义文件写入的目录和文件名
xlsx_name = '/app/data/mysql-export/' + '用户设备信息' + TODAY + '.xls'
#print(xlsx_name)

conn=pymysql.connect(
    host="$host",
    port=3306,
    db="db1",
    user="admin",
    passwd="$password",
    charset="utf8"
)
cursor=conn.cursor()

workbook=xlwt.Workbook(encoding='utf-8')
booksheet=workbook.add_sheet('Sheet1',cell_overwrite_ok=True)
# 设定表头
#list_table_head = []
list_table_head = ['user_id','email','设备唯一指纹','ip','客户端类型','app版本号','mac','手机厂商','系统版本','设备更换时间']
for i in range(len(list_table_head)):
    booksheet.write(0,i, list_table_head[i])
# 导出的Excel文件内容错乱（Excel有自动处理逻辑（比如数字超过15位）），使用concat处理（在数据前增加特殊字符，使其变为字符串）
sql="""
select
concat("'",user_id) as user_id,
email,concat("'",deviceId) as '设备唯一指纹',
ip,clientType as '客户端类型',
appVersion as 'app版本号',mac as 'mac地址',
model as '手机厂商',osv as '系统版本',concat("'",create_time) as '设备更换时间'
from bigdata.user_device_detail where user_id > 0
"""
cursor=conn.cursor()
cursor.execute(sql)
DATA=cursor.fetchall()
conn.close()
# 因为表头占一行，所以数据从第二行开始写入
for i,row in enumerate(DATA):
    for j,col in enumerate(row):
        booksheet.write(i+1,j,col)
workbook.save(xlsx_name)

二、统计数据库实例中的信息

  一）统计数据库实例中的库表数据量

mysql -uadmin -h ${host} -p${password}-e "SELECT
  TABLE_SCHEMA as 库名,
  TABLE_NAME as 表名,
  sum(DATA_LENGTH)  as 数据空间字节数,
  sum(INDEX_LENGTH) as 索引字节数,
  TABLE_ROWS as 数据行数
FROM TABLES
where TABLE_SCHEMA in ('database1','database2','database3')
group by TABLE_SCHEMA,TABLE_NAME" information_schema > table_size.txt