Python连接MySQL统计数据量并导出Excel

Python实现MySQL数据统计及numpy统计函数

在数据分析与数据库管理中，经常需要统计MySQL数据库中各个表的数据量，并将结果导出为Excel表格以便查看。本文将介绍如何使用Python的pymysql库连接MySQL数据库，遍历指定数据库下的所有表并统计数据条数。同时，利用numpy库提供的统计函数（如np.mean、np.sum等）处理提取的数据。最后，通过xlwt库将统计结果导出为Excel文件，实现数据库数据量的自动化统计与报表生成。

1. 环境准备

在开始之前，请确保已安装以下Python库：

• pymysql：用于连接MySQL数据库。
• xlwt：用于生成Excel文件。
• numpy：用于数据统计处理。

可以通过以下命令安装这些库：

pip install pymysql xlwt numpy

2. 连接MySQL数据库

首先，我们需要使用pymysql库连接到MySQL数据库。以下是一个简单的连接示例：

import pymysql

db = pymysql.connect(
    host='192.168.1.74',
    user='root',
    password='123456',
    database='xx1'
)

连接成功后，我们可以创建一个游标对象来执行SQL查询。

3. 统计数据库表数据量

接下来，我们需要查询指定数据库下的所有表，并统计每个表的数据条数。以下是一个示例代码：

cursor = db.cursor()
sql = "SELECT TABLE_SCHEMA AS 'database', TABLE_NAME AS table_name FROM information_schema.TABLES WHERE TABLE_SCHEMA IN ('my1', 'my2', 't1', 'xx1');"
cursor.execute(sql)
res1 = cursor.fetchall()

for row in res1:
    database = row[0]
    table = row[1]
    c1 = row[0] + '.' + row[1]
    c2 = 'SELECT COUNT(*) FROM %s' % c1
    try:
        cursor.execute(c2)
        res2 = cursor.fetchall()
        for num in res2:
            count = num[0]
            print(f"Database: {database}, Table: {table}, Count: {count}")
    except Exception as e:
        print(f"Error: {e}")

这段代码首先查询指定数据库下的所有表，然后对每个表执行`COUNT(*)`查询，统计数据条数。

4. 使用numpy进行数据统计

在获取到每个表的数据条数后，我们可以使用numpy库进行进一步的数据统计。以下是一些常用的numpy统计函数：

• np.mean：计算所有元素的平均值。
• np.sum：计算所有元素的和。
• np.max：计算所有元素的最大值。
• np.min：计算所有元素的最小值。
• np.std：计算所有元素的标准差。
• np.var：计算所有元素的方差。
• np.argmax：返回最大值下标索引。
• np.argmin：返回最小值下标索引。
• np.cumsum：返回元素累加和数组。
• np.cumprod：返回元素累乘积数组。

以下是一个使用numpy统计数据条数的示例：

import numpy as np

counts = [100, 200, 300, 400]
mean_count = np.mean(counts)
sum_count = np.sum(counts)
max_count = np.max(counts)
min_count = np.min(counts)
std_count = np.std(counts)
var_count = np.var(counts)

print(f"Mean: {mean_count}, Sum: {sum_count}, Max: {max_count}, Min: {min_count}, Std: {std_count}, Var: {var_count}")

5. 导出统计结果到Excel

最后，我们将统计结果导出到Excel文件中。以下是一个使用xlwt库生成Excel文件的示例：

import xlwt

excel = xlwt.Workbook(encoding='utf-8')
sheet = excel.add_sheet('Mysql数据库')
sheet.write(0, 0, '库名')
sheet.write(0, 1, '表名')
sheet.write(0, 2, '数据条数')

i = 1
for row in res1:
    database = row[0]
    table = row[1]
    c1 = row[0] + '.' + row[1]
    c2 = 'SELECT COUNT(*) FROM %s' % c1
    try:
        cursor.execute(c2)
        res2 = cursor.fetchall()
        for num in res2:
            count = num[0]
            sheet.write(i, 0, database)
            sheet.write(i, 1, table)
            sheet.write(i, 2, count)
            i += 1
    except Exception as e:
        print(f"Error: {e}")

excel.save('C:\\Users\\user\\DeskTop\\mysql.xls')
db.close()

这段代码首先创建一个Excel工作簿，然后遍历之前获取的数据库表数据，将每个表的库名、表名和数据条数写入Excel文件。最后，保存Excel文件并关闭数据库连接。

6. 完整代码示例

以下是完整的Python代码示例，包括连接MySQL数据库、统计数据量、使用numpy进行数据统计以及导出结果到Excel：

import pymysql
import xlwt
import numpy as np

# 连接MySQL数据库
db = pymysql.connect(
    host='192.168.1.74',
    user='root',
    password='123456',
    database='xx1'
)
cursor = db.cursor()

# 查询指定数据库下的所有表
sql = "SELECT TABLE_SCHEMA AS 'database', TABLE_NAME AS table_name FROM information_schema.TABLES WHERE TABLE_SCHEMA IN ('my1', 'my2', 't1', 'xx1');"
cursor.execute(sql)
res1 = cursor.fetchall()

# 创建Excel工作簿
excel = xlwt.Workbook(encoding='utf-8')
sheet = excel.add_sheet('Mysql数据库')
sheet.write(0, 0, '库名')
sheet.write(0, 1, '表名')
sheet.write(0, 2, '数据条数')

i = 1
counts = []
for row in res1:
    database = row[0]
    table = row[1]
    c1 = row[0] + '.' + row[1]
    c2 = 'SELECT COUNT(*) FROM %s' % c1
    try:
        cursor.execute(c2)
        res2 = cursor.fetchall()
        for num in res2:
            count = num[0]
            sheet.write(i, 0, database)
            sheet.write(i, 1, table)
            sheet.write(i, 2, count)
            counts.append(count)
            i += 1
    except Exception as e:
        print(f"Error: {e}")

# 使用numpy进行数据统计
if counts:
    mean_count = np.mean(counts)
    sum_count = np.sum(counts)
    max_count = np.max(counts)
    min_count = np.min(counts)
    std_count = np.std(counts)
    var_count = np.var(counts)
    print(f"Mean: {mean_count}, Sum: {sum_count}, Max: {max_count}, Min: {min_count}, Std: {std_count}, Var: {var_count}")

# 保存Excel文件
excel.save('C:\\Users\\user\\DeskTop\\mysql.xls')
db.close()

7. 总结

本文介绍了如何使用Python的pymysql库连接MySQL数据库，遍历指定数据库下的所有表并统计数据条数。同时，利用numpy库提供的统计函数（如np.mean、np.sum等）处理提取的数据。最后，通过xlwt库将统计结果导出为Excel文件，实现数据库数据量的自动化统计与报表生成。

以上就是Python实现MySQL数据统计及numpy统计函数的详细内容，更多关于Python MySQL数据统计的资料请关注本站其它相关文章！