一个通用的脚本，处理MySQL WorkBench导出表的JSON数据进SQLITE3，创建的是FTS4的虚拟表
# -*- coding:utf-8 -*-
import json
import sqlite3
import os, sys def join_key_by_dict(dict_info):
result = ""
for item in dict_info.keys():
result += item + ','
result = result.rstrip(',')
return result def join_value_by_dict(dict_info):
result = ""
for item in dict_info.keys():
if isinstance(dict_info[item], str):
result += "'" + dict_info[item].replace("'", "''") + "',"
else:
result += str(dict_info[item]) + ","
# result += dict_info[item] + ','
result = result.rstrip(',')
return result def create_tbl_by_json(json_file, tbl_name, db_conn):
# 打开文件，
json_handle = open(json_file, "r")
# 读取第一行，转换成json数据
line = json_handle.readline()
json_handle.close() # 清除左边的[，右边的,
line = line.lstrip('[').rstrip('\r\n ,') json_line = json.loads(line)
# 获取到所有key，构建表的创建命令
create_tbl_str = "create virtual table %s USING fts4( %s );" % (tbl_name, join_key_by_dict(json_line)) # 打开光标
cur = db_conn.cursor()
cur.execute(create_tbl_str)
db_conn.commit()
cur.close()
print('create %s table success[%s]' % (tbl_name, json_file) ) def insert_record_by_json(json_file, tbl_name, db_conn):
# 打开文件，
json_handle = open(json_file, "r")
# 读取第一行，转换成json数据
cur = db_conn.cursor()
count = 0
for line in json_handle:
json_line = json.loads(line.lstrip('[').rstrip('\r\n ,]'))
# 获取到所有key，构建表的创建命令
key_str = join_key_by_dict(json_line)
val_str = join_value_by_dict(json_line)
# 组装命令并执行
insert_record_str = "INSERT INTO %s (%s) VALUES(%s);" % (tbl_name, key_str, val_str)
cur.execute(insert_record_str)
count += 1
db_conn.commit()
cur.close()
json_handle.close()
print('insert record finish, count: %s' % count ) def convert_json_to_db(json_file, db_file):
# 检查json_file是否存在
if not os.path.exists(json_file):
print('file not exist: %s' % json_file)
return # 打开数据库连接
db_conn = sqlite3.connect(db_file) tbl_name, _ = os.path.splitext(os.path.basename(json_file))
# 开始创建表
create_tbl_by_json(json_file, tbl_name, db_conn) # 开始插入记录
insert_record_by_json(json_file, tbl_name, db_conn) # 关闭数据库
db_conn.close()
print('Operation done successfully') if __name__ == '__main__':
if len(sys.argv) != 3:
print('Usage: python %s json_file db_file' % os.path.basename(__file__))
exit(1)
json_file = sys.argv[1]
db_file = sys.argv[2]
convert_json_to_db(json_file, db_file)
一个通用的脚本，处理MySQL WorkBench导出表的JSON数据进SQLITE3，创建的是通用表
# -*- coding:utf-8 -*-
import json
import sqlite3
import os, sys def join_key_by_dict(dict_info):
result = ""
for item in dict_info.keys():
result += item + ','
result = result.rstrip(',')
return result def join_value_by_dict(dict_info):
result = ""
for item in dict_info.keys():
if isinstance(dict_info[item], str):
result += "'" + dict_info[item].replace("'", "''") + "',"
else:
result += str(dict_info[item]) + ","
# result += dict_info[item] + ','
result = result.rstrip(',')
return result def join_keyvalue_by_dict(dict_info):
result = ""
for item in dict_info.keys():
if isinstance(dict_info[item], int):
result += str(item) + " INTEGER,"
else:
result += str(item) + " TEXT,"
# result += dict_info[item] + ','
result = result.rstrip(',')
return result def create_tbl_by_json(json_file, tbl_name, db_conn):
# 打开文件，
json_handle = open(json_file, "r")
# 读取第一行，转换成json数据
line = json_handle.readline()
json_handle.close() # 清除左边的[，右边的,
line = line.lstrip('[').rstrip('\r\n ,') json_line = json.loads(line)
# 获取到所有key，构建表的创建命令
create_tbl_str = "create table %s (%s);" % (tbl_name, join_keyvalue_by_dict(json_line)) # 打开光标
cur = db_conn.cursor()
cur.execute(create_tbl_str)
db_conn.commit()
cur.close()
print('create %s table success[%s]' % (tbl_name, json_file) ) def insert_record_by_json(json_file, tbl_name, db_conn):
# 打开文件，
json_handle = open(json_file, "r")
# 读取第一行，转换成json数据
cur = db_conn.cursor()
count = 0
for line in json_handle:
json_line = json.loads(line.lstrip('[').rstrip('\r\n ,]'))
# 获取到所有key，构建表的创建命令
key_str = join_key_by_dict(json_line)
val_str = join_value_by_dict(json_line)
# 组装命令并执行
insert_record_str = "INSERT INTO %s (%s) VALUES(%s);" % (tbl_name, key_str, val_str)
cur.execute(insert_record_str)
count += 1
db_conn.commit()
cur.close()
json_handle.close()
print('insert record finish, count: %s' % count ) def convert_json_to_db(json_file, db_file):
# 检查json_file是否存在
if not os.path.exists(json_file):
print('file not exist: %s' % json_file)
return # 打开数据库连接
db_conn = sqlite3.connect(db_file) tbl_name, _ = os.path.splitext(os.path.basename(json_file))
# 开始创建表
create_tbl_by_json(json_file, tbl_name, db_conn) # 开始插入记录
insert_record_by_json(json_file, tbl_name, db_conn) # 关闭数据库
db_conn.close()
print('Operation done successfully') if __name__ == '__main__':
if len(sys.argv) != 3:
print('Usage: python %s json_file db_file' % os.path.basename(__file__))
exit(1)
json_file = sys.argv[1]
db_file = sys.argv[2]
convert_json_to_db(json_file, db_file)
一个通用的脚本，处理MySQL WorkBench导出表的JSON数据进SQLITE3，输出的是可以执行的SQL语句
# -*- coding:utf-8 -*-
import json
import os, sys def join_key_by_dict(dict_info):
result = ""
for item in dict_info.keys():
result += item + ','
result = result.rstrip(',')
return result def join_value_by_dict(dict_info):
result = ""
for item in dict_info.keys():
if isinstance(dict_info[item], str):
result += "'" + dict_info[item].replace("'", "''") + "',"
else:
result += str(dict_info[item]) + ","
# result += dict_info[item] + ','
result = result.rstrip(',')
return result def create_tbl_by_json(json_file, tbl_name, sql_file):
# 打开文件，
json_handle = open(json_file, "r")
# 读取第一行，转换成json数据
line = json_handle.readline()
json_handle.close() # 清除左边的[，右边的,
line = line.lstrip('[').rstrip('\r\n ,') json_line = json.loads(line)
# 获取到所有key，构建表的创建命令
create_tbl_str = 'create virtual table %s USING fts4( %s );\n' % (tbl_name, join_key_by_dict(json_line))
sql_file.write(create_tbl_str) # 打开光标
#print('create %s table success[%s]' % (tbl_name, json_file) ) def insert_record_by_json(json_file, tbl_name, sql_file):
# 打开文件，
json_handle = open(json_file, "r")
# 读取第一行，转换成json数据
count = 0
for line in json_handle:
json_line = json.loads(line.lstrip('[').rstrip('\r\n ,]'))
# 获取到所有key，构建表的创建命令
key_str = join_key_by_dict(json_line)
val_str = join_value_by_dict(json_line)
# 组装命令并执行
insert_record_str = "INSERT INTO %s (%s) VALUES(%s);\n" % (tbl_name, key_str, val_str)
sql_file.write(insert_record_str)
count += 1
json_handle.close()
#print('insert record finish, count: %s' % count ) def convert_json_to_sql(json_file, sql_file):
# 检查json_file是否存在
if not os.path.exists(json_file):
print('file not exist: %s' % json_file)
return # 检查sql_file是否存在
if os.path.exists(sql_file):
print('file is exist: %s, will overwrite it.' % sql_file) # 打开文件
sql_handle = open(sql_file, "w") tbl_name, _ = os.path.splitext(os.path.basename(json_file))
# 开始创建表
create_tbl_by_json(json_file, tbl_name, sql_handle) # 开始插入记录
insert_record_by_json(json_file, tbl_name, sql_handle) # 关闭文件
sql_handle.close()
print('Operation done successfully') if __name__ == '__main__':
if len(sys.argv) != 3:
print('Usage: python %s json_file db_file' % os.path.basename(__file__))
exit(1)
json_file = sys.argv[1]
sql_file = sys.argv[2]
convert_json_to_sql(json_file, sql_file)