欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Python 为线上mysql数据库添加数据

程序员文章站 2022-04-15 16:30:39
...

场景:最近经常需要把一些功能的模块的数据迁移到服务器数据库中,弄来弄去比较麻烦,于是用所谓的胶水语言写python写一个脚本,来帮助完成工作,话不多说,直接上菜
Python 为线上mysql数据库添加数据

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文件截图
Python 为线上mysql数据库添加数据