一、使用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
