Python 为线上mysql数据库添加数据
程序员文章站
2022-04-15 16:30:39
...
场景:最近经常需要把一些功能的模块的数据迁移到服务器数据库中,弄来弄去比较麻烦,于是用所谓的胶水语言写python写一个脚本,来帮助完成工作,话不多说,直接上菜
import pymysql
import re
def open_database(host, user, password, db_name):
db = pymysql.connect(host, user, password, db_name)
return db
def open_database2(host, port, user, password, db_name, encoding):
db = pymysql.connect(host=host, port=port, user=user,
passwd=password, db=db_name, charset=encoding)
return db
def show_table(db):
cu = db.cursor()
cu.execute("SHOW TABLES")
tables = cu.fetchall()
print(tables)
def add_batch(sql_list,db=None):
if db is not None and type(sql_list).__name__=='list':
cur = db.cursor()
for sql in sql_list:
try:
cur.execute(sql)
db.commit()
except:
cur.rollback()
db.close()
if db is None and type(sql_list).__name__=='dict':
for key in sql_list.keys():
db = open_database("127.0.0.1","root","123456",key)
cur = db.cursor()
for sql in sql_list[key]:
try:
cur.execute(sql)
db.commit()
except:
cur.rollback()
db.close()
def read_file(url):
sql_map = {}
sql_list=[]
file = open(url, encoding='utf-8')
database = ''
count = 0
while 1:
line = file.readline()
if re.match("^\\s+$", line) is not None:
continue
if re.match("^/\\*.*\\*/$", line) is not None:
continue
if re.match("^/\\*.*\\*/;$", line) is not None:
continue
if re.match("^CREATE.*;$", line) is not None:
continue
if re.match("^USE.*;$", line) is not None:
if count!=0:
sql_map[database]=sql_list
database = line.split("`")[1]
count=count+1
sql_list =[]
continue
if not line:
sql_map[database]=sql_list
break
sql_list.append("" + line.strip())
if count == 1:
sql_map[database]=sql_list
print(database)
return sql_map
if __name__ == '__main__':
re = read_file("C:\\Users\\hasee\\Desktop\\client.sql")
add_batch(re)
下面是sql文件截图