Bash脚本执行sql语句
本文介绍如果通过Bash脚本执行sql语句,并介绍如何根据xlsx生成sql语句。
一、使用Python根据Excel生成sql语句
- 安装相关的包
!pip install openpyxl
- 生成sql
我这边只用到两种xlsx内容格式,需要按照自己的excel内容格式修改生成sql语句代码
2.1 xlsx格式一:
1 | 90.5 | 1 |
---|---|---|
4 | 80.9 | 2 |
85 | 50.9 | 32 |
2.1 xlsx格式一:
第一列为表主键,第二列和第三列为表中要修改的属性值。
对应python代码为:
def generate_sql_update2(file_path):
wb = openpyxl.load_workbook(file_path)
sheet = wb.active
max_row = sheet.max_row
# sql存储列表
sql_statements = []
# 从第1行开始
for row in range(1, max_row + 1):
column1 = sheet.cell(row=row, column=1).value
column2 = sheet.cell(row=row, column=2).value
column3 = sheet.cell(row=row, column=3).value
sql = f"UPDATE table_name SET column2={column2},column3={column3} WHERE column1={column1};"
sql_statements.append(sql)
return sql_statements
2.2 xlsx格式二:
45 | 60 | 89 | |
---|---|---|---|
20 | 90.5 | 85.5 | 42.0 |
15 | 89.2 | 66.5 | 98.4 |
第一列和第一行为主键,两者联合确定一行数据,从第二行第二列到第n行第n列为要修改的数据。代对应代码为:
def generate_sql_update(file_path):
wb = openpyxl.load_workbook(file_path)
sheet = wb.active
max_row = sheet.max_row
max_col = sheet.max_column
# sql存储列表
sql_statements = []
# 从第二行开始
for row in range(2, max_row + 1):
id = sheet.cell(row=row, column=1).value
# 从第二列开始
for col in range(2, max_col + 1):
column1 = sheet.cell(row=1, column=col).value
score = sheet.cell(row=row, column=col).value
# 生成sql语句
sql = f"table_name SET score={score} WHERE id={id} AND column1 ={column1};"
sql_statements.append(sql)
return sql_statements
请修改table_name和column的名字。
2.3 完整代码:
import openpyxl
from sqlalchemy import create_engine
def generate_sql_update(file_path):
wb = openpyxl.load_workbook(file_path)
sheet = wb.active
max_row = sheet.max_row
max_col = sheet.max_column
# sql存储列表
sql_statements = []
# 从第二行开始
for row in range(2, max_row + 1):
id = sheet.cell(row=row, column=1).value
# 从第二列开始
for col in range(2, max_col + 1):
column1 = sheet.cell(row=1, column=col).value
score = sheet.cell(row=row, column=col).value
# 生成sql语句
sql = f"table_name SET score={score} WHERE id={id} AND column1 ={column1};"
sql_statements.append(sql)
return sql_statements
def generate_sql_update2(file_path):
wb = openpyxl.load_workbook(file_path)
sheet = wb.active
max_row = sheet.max_row
# sql存储列表
sql_statements = []
# 从第1行开始
for row in range(1, max_row + 1):
column1 = sheet.cell(row=row, column=1).value
column2 = sheet.cell(row=row, column=2).value
column3 = sheet.cell(row=row, column=3).value
sql = f"UPDATE table_name SET column2={column2},column3={column3} WHERE column1={column1};"
sql_statements.append(sql)
return sql_statements
def generate_sql_update3(file_path):
wb = openpyxl.load_workbook(file_path)
sheet = wb.active
max_row = sheet.max_row
# sql存储列表
sql_statements = []
# 从第1行开始
for row in range(1, max_row + 1):
id = sheet.cell(row=row, column=1).value
author_name = sheet.cell(row=row, column=2).value
author_chinese_name = sheet.cell(row=row, column=3).value
auther_email = sheet.cell(row=row, column=4).value
sql = f"insert into authors values ({id},'{author_name}', '{author_chinese_name}', '{auther_email}');"
sql_statements.append(sql)
return sql_statements
def write_sql_to_file(sql_queries, txt_path):
with open(txt_path, 'w') as file:
for query in sql_queries:
file.write(query + '\n')
if __name__ == '__main__':
# xlsx的路径,原始数据excel路径
file_path = 'C:/Users/Desktop/update.xlsx'
sql_queries = generate_sql_update(file_path)
txt_path='C:/Users/Desktop/update.txt'
write_sql_to_file(sql_queries,txt_path)
file_path为xlsx的路径,txt_path为生成的sql路径。
二、Bash脚本执行sql
sql_update.sh
#!/bin/bash
# MySQL连接信息
DB_PASSWORD="123456" # 替换为你的MySQL密码
# 待执行的SQL文件路径
SQL_FILE="C:/Users/Desktop/update.txt"
# 检查文件是否存在
if [ ! -f "$SQL_FILE" ]; then
echo "Er## 标题ror: SQL file $SQL_FILE not found."
exit 1
fi
# 连接并执行SQL语句
while IFS= read -r sql_statement
do
if [[ ! -z "$sql_statement" ]]; then
mysql -hlocalhost -uroot -p"$DB_PASSWORD" casiep -e "$sql_statement"
fi
done < "$SQL_FILE"
终端输入以下命令即可。
bash sql_update.sh