玖叶教程网

最近碰到一个遗留项目，没有使用mybatis。导致java工程中需要手写insert语句，然后替换变量。当遇到表中字段特别多的时候，就特别费时间。因此使用python写了一个脚本。一键生成sql的insert语句。

右侧是建表语句，左侧是python代码。控制台上是生成的inser语句。python代码如下：

#!/usr/bin/env python

from pathlib import Path
import re

basedir = Path(".")
filepath = basedir / "SQL.sql"
tableNameRegex = r'^(create)\s+(table)\s+(if)?\s+(not)?\s+(exists)?\s+'
filedNameRegex = r'(bigint|text|varchar)[A-Za-z0-9\(\)\s]*\,?#39;
endRegex = r'\)\s?\;+'

tables = {}
tableName = ""
fieldNames = []

f = open(filepath, "r", encoding='utf-8')
for line in f.readlines():
    matchTableName = re.match(tableNameRegex, line, re.M | re.I)
    if matchTableName:
        tableName = re.sub(tableNameRegex, "", line, flags=re.IGNORECASE)
        tableName = re.sub(r'\(', "", tableName).strip()
        continue
    matchFiledName = re.search(filedNameRegex, line, re.M | re.I)
    if matchFiledName:
        fieldName = re.sub(filedNameRegex, "", line, flags=re.IGNORECASE).strip()
        fieldNames.append(fieldName)
        continue
    matchEnd = re.match(endRegex, line, re.M | re.I)
    if matchEnd:
        tables[tableName] = fieldNames.copy()
        tableName = ""
        fieldNames.clear()
f.close()

def jointFields(fieldList):
    fieldListStr = ""
    for i in range(len(fieldList)):
        if i == len(fieldList) - 1 :
            fieldListStr = fieldListStr + fieldList[i]
        else:
            fieldListStr = fieldListStr + fieldList[i] + ", "
    return fieldListStr

for table in tables:
    size = len(tables[table])
    insert_sql= "insert into " + table + "(" + jointFields(tables[table]) + ") values ("
    for i in range(size) :
        if i == size -1:
            insert_sql = insert_sql + "?"
        else:
            insert_sql = insert_sql + "?,"
    insert_sql = insert_sql + ")"
    print(insert_sql)