Python操作sql，备份数据库

1、批量执行sql

import pymysql# 执行批量的 SQL 语句
def executeBatchSql(cursor, sqlStatements):for sql in sqlStatements:try:cursor.execute(sql)print('Executed SQL statement:', sql)except Exception as e:print('Error executing SQL statement:', e)# 创建数据库连接
connection = pymysql.connect(host='xx.xx.xx.xx',port=xx,user='root',password='xx',database='xx',autocommit=True  # 设置自动提交模式
)# 连接数据库
try:with connection.cursor() as cursor:print('Connected to database')# 执行批量的 SQL 语句sqlStatements = ["UPDATE conveyor_exception_time SET date = DATE_SUB(CURDATE(), INTERVAL 1 DAY); ","UPDATE detection_ng_analysis SET date = CURDATE(); ","UPDATE single_data_analysis SET date = CURDATE(); ","UPDATE area_data_analysis SET date = CURDATE(); ","UPDATE store_area SET status = FLOOR(RAND() * 8);",# "UPDATE area_data_analysis SET `date`=DATE_SUB(CURDATE(), INTERVAL 0 DAY);","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 1 DAY) WHERE id='17';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 1 DAY) WHERE id='18';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 1 DAY) WHERE id='19';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 1 DAY) WHERE id='20';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 2 DAY) WHERE id='21';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 2 DAY) WHERE id='22';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 2 DAY) WHERE id='23';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 2 DAY) WHERE id='24';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 3 DAY) WHERE id='25';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 3 DAY) WHERE id='26';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 3 DAY) WHERE id='27';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 3 DAY) WHERE id='28';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 4 DAY) WHERE id='5';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 4 DAY) WHERE id='6';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 4 DAY) WHERE id='7';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 4 DAY) WHERE id='8';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 5 DAY) WHERE id='13';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 5 DAY) WHERE id='14';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 5 DAY) WHERE id='15';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 5 DAY) WHERE id='16';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 6 DAY) WHERE id='9';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 6 DAY) WHERE id='10';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 6 DAY) WHERE id='11';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 6 DAY) WHERE id='12';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 7 DAY) WHERE id='1';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 7 DAY) WHERE id='2';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 7 DAY) WHERE id='3';","UPDATE stacker_analysis_history SET `date`=DATE_SUB(CURDATE(), INTERVAL 7 DAY) WHERE id='4';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 0 DAY) WHERE id='0493';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 0 DAY) WHERE id='04c5';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 0 DAY) WHERE id='07d3';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 0 DAY) WHERE id='0e75';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 1 DAY) WHERE id='2712';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 1 DAY) WHERE id='2bd0';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 1 DAY) WHERE id='2e5f';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 1 DAY) WHERE id='32e2';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 2 DAY) WHERE id='43b9';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 2 DAY) WHERE id='4476';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 2 DAY) WHERE id='4514';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 2 DAY) WHERE id='4bcb';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 3 DAY) WHERE id='5e97';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 3 DAY) WHERE id='6781';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 3 DAY) WHERE id='6ae8';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 3 DAY) WHERE id='81a8';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 4 DAY) WHERE id='8d4c';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 4 DAY) WHERE id='8e66';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 4 DAY) WHERE id='9c59';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 4 DAY) WHERE id='9f77';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 5 DAY) WHERE id='a771';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 5 DAY) WHERE id='a809';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 5 DAY) WHERE id='ad77';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 5 DAY) WHERE id='b560';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 6 DAY) WHERE id='be6f';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 6 DAY) WHERE id='c567';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 6 DAY) WHERE id='c8f0';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 6 DAY) WHERE id='cbec';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 7 DAY) WHERE id='d24d';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 7 DAY) WHERE id='d4e6';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 7 DAY) WHERE id='da7e';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 7 DAY) WHERE id='e627';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 0 DAY) WHERE id='022e';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 0 DAY) WHERE id='02a5';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 0 DAY) WHERE id='060a';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 0 DAY) WHERE id='1649';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 1 DAY) WHERE id='1888';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 1 DAY) WHERE id='1a1f';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 1 DAY) WHERE id='2c33';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 1 DAY) WHERE id='3f44';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 2 DAY) WHERE id='48b2';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 2 DAY) WHERE id='4c45';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 2 DAY) WHERE id='4ca3';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 2 DAY) WHERE id='6760';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 3 DAY) WHERE id='679d';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 3 DAY) WHERE id='7201';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 3 DAY) WHERE id='73cc';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 3 DAY) WHERE id='815c';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 4 DAY) WHERE id='894a';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 4 DAY) WHERE id='8a45';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 4 DAY) WHERE id='96fe';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 4 DAY) WHERE id='9843';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 5 DAY) WHERE id='a46e';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 5 DAY) WHERE id='a471';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 5 DAY) WHERE id='a552';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 5 DAY) WHERE id='b6af';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 6 DAY) WHERE id='c9cd';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 6 DAY) WHERE id='cf82';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 6 DAY) WHERE id='d201';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 6 DAY) WHERE id='daff';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 7 DAY) WHERE id='de2d';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 7 DAY) WHERE id='ec95';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 7 DAY) WHERE id='ee4c';","UPDATE `jeecg-boot`.area_data_analysis SET `date`= DATE_SUB(CURDATE(), INTERVAL 7 DAY) WHERE id='fe9b';",# "UPDATE area_data_analysis SET date = CURDATE() WHERE date = DATE_SUB(CURDATE(), INTERVAL 1 DAY);", # "UPDATE area_data_analysis SET date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) WHERE date = DATE_SUB(CURDATE(), INTERVAL 2 DAY);", # "UPDATE area_data_analysis SET date = DATE_SUB(CURDATE(), INTERVAL 2 DAY) WHERE date = DATE_SUB(CURDATE(), INTERVAL 3 DAY);", # "UPDATE area_data_analysis SET date = DATE_SUB(CURDATE(), INTERVAL 3 DAY) WHERE date = DATE_SUB(CURDATE(), INTERVAL 4 DAY);", # "UPDATE area_data_analysis SET date = DATE_SUB(CURDATE(), INTERVAL 4 DAY) WHERE date = DATE_SUB(CURDATE(), INTERVAL 5 DAY);", # "UPDATE area_data_analysis SET date = DATE_SUB(CURDATE(), INTERVAL 5 DAY) WHERE date = DATE_SUB(CURDATE(), INTERVAL 6 DAY);", # "UPDATE area_data_analysis SET date = DATE_SUB(CURDATE(), INTERVAL 6 DAY) WHERE date = DATE_SUB(CURDATE(), INTERVAL 7 DAY);", # "UPDATE area_data_analysis SET date = DATE_SUB(CURDATE(), INTERVAL 7 DAY) WHERE date = DATE_SUB(CURDATE(), INTERVAL 8 DAY);", # "UPDATE area_data_analysis SET `date`=DATE_SUB(CURDATE(), INTERVAL 2 DAY) WHERE operation_code = 'OP03' AND  date = DATE_SUB(CURDATE(), INTERVAL 2 DAY);",# "",]executeBatchSql(cursor, sqlStatements)finally:# 关闭数据库连接connection.close()print("\033[32;44;1mSQL执行完成, Mock数据初始化完成...\033[0m")

2、备份数据库

import subprocessdef backup_mysql_database(host, port, user, password, database, output_file):# 构建备份命令command = f"mysqldump --host={host} --port={port} --user={user} --password={password} {database} > {output_file}"try:# 执行备份命令print("数据库备份进行中，请稍等...")subprocess.run(command, shell=True, check=True)print("数据库备份成功！")except subprocess.CalledProcessError as e:print(f"数据库备份失败：{e}")# 调用备份函数
host='xx.xx.xx.xx'
port=xx
user='root'
password='xx'
database='xx'
backup_mysql_database(host, port, user, password, database, "./data/jeecg-boot.sql")

3、使用shell执行python脚本

#!/bin/bash# 切换到Python脚本所在的目录# 执行Python脚本
python sql.py# 添加等待命令，不让脚本关闭
read -p "Press any key to exit..."