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 subprocess
def 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脚本文章来源地址https://www.toymoban.com/news/detail-663694.html
#!/bin/bash
# 切换到Python脚本所在的目录
# 执行Python脚本
python sql.py
# 添加等待命令,不让脚本关闭
read -p "Press any key to exit..."
文章来源:https://www.toymoban.com/news/detail-663694.html
到了这里,关于Python操作sql,备份数据库的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!