Bash脚本执行sql语句

Bash脚本执行sql语句

本文介绍如果通过Bash脚本执行sql语句，并介绍如何根据xlsx生成sql语句。

一、使用Python根据Excel生成sql语句

1. 安装相关的包

!pip install openpyxl

2. 生成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