You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
65 lines
1.6 KiB
Python
65 lines
1.6 KiB
Python
|
|
|
|
import mysql.connector
|
|
import pandas as pd
|
|
import matplotlib.pyplot as plt
|
|
from datetime import datetime
|
|
|
|
# Database connection
|
|
try:
|
|
cloud_conn = mysql.connector.connect(
|
|
host='106.227.91.181',
|
|
database='huaerdames_cloud',
|
|
port=3306,
|
|
user='root',
|
|
password='cuixing@HuaerdaMySQL'
|
|
)
|
|
|
|
# Query to get net_weight by month
|
|
query = """
|
|
SELECT
|
|
DATE_FORMAT(create_time, '%Y-%m') as month,
|
|
SUM(net_weight) as total_weight
|
|
FROM wms_ingredients_log
|
|
WHERE is_deleted = 0
|
|
AND net_weight IS NOT NULL
|
|
AND create_time IS NOT NULL
|
|
GROUP BY DATE_FORMAT(create_time, '%Y-%m')
|
|
ORDER BY month
|
|
"""
|
|
|
|
# Execute query and load data into DataFrame
|
|
df = pd.read_sql(query, cloud_conn)
|
|
|
|
# Close database connection
|
|
cloud_conn.close()
|
|
|
|
# Convert month to datetime for better plotting
|
|
df['month'] = pd.to_datetime(df['month'])
|
|
|
|
# Create the plot
|
|
plt.figure(figsize=(12, 6))
|
|
plt.plot(df['month'], df['total_weight'], marker='o')
|
|
|
|
# Customize the plot
|
|
plt.title('Monthly Net Weight Statistics', fontsize=14)
|
|
plt.xlabel('Month', fontsize=12)
|
|
plt.ylabel('Total Net Weight', fontsize=12)
|
|
plt.grid(True)
|
|
|
|
# Rotate x-axis labels for better readability
|
|
plt.xticks(rotation=45)
|
|
|
|
# Adjust layout to prevent label cutoff
|
|
plt.tight_layout()
|
|
|
|
# Save the plot
|
|
plt.savefig('monthly_weight_plot.png')
|
|
|
|
# Show the plot
|
|
plt.show()
|
|
|
|
except mysql.connector.Error as err:
|
|
print(f"Database error: {err}")
|
|
except Exception as e:
|
|
print(f"Error: {e}") |