场景：最近经常需要把一些功能的模块的数据迁移到服务器数据库中，弄来弄去比较麻烦，于是用所谓的胶水语言写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文件截图
