mysql导入和导出（备份），基于python

01 导出

• 全部导出（整个数据库）

def get(self, request):
    db_data_conf = DATABASES.get("default")
    db_host = db_data_conf.get("HOST")
    db_user = db_data_conf.get("USER")
    db_passwd = db_data_conf.get("PASSWORD")
    db_name = db_data_conf.get("NAME")
    db_backup_name = r"%s/apps/system/sql/back.sql" % BASE_DIR
    os.system("mysqldump -h%s -u%s -p%s %s > %s" % (db_host, db_user, db_passwd, db_name, db_backup_name))

注意：

​ 这种情况下，最后一个 'TABLES;' 后边的注释要全部删除，会导致导入出错。

• 导出单个表

def get(self, request):
    db_data_conf = DATABASES.get("default")
    db_host = db_data_conf.get("HOST")
    db_user = db_data_conf.get("USER")
    db_passwd = db_data_conf.get("PASSWORD")
    db_name = db_data_conf.get("NAME")
    db_backup_name = r"%s/apps/system/sql/back.sql" % BASE_DIR
    os.system("mysqldump -h%s -u%s -p%s %s %s > %s" % (db_host, db_user, db_passwd, db_name, db_name, db_backup_name))

• 指定表导出

def get(self, request):
    db_data_conf = DATABASES.get("default")
    db_host = db_data_conf.get("HOST")
    db_user = db_data_conf.get("USER")
    db_passwd = db_data_conf.get("PASSWORD")
    db_name = db_data_conf.get("NAME")
    db_backup_name = r"%s/apps/system/sql/back.sql" % BASE_DIR
    conn = pymysql.connect(host=db_host,
                           user=db_user,
                           password=db_passwd,
                           database=db_name)
    cursor = conn.cursor()
    showtables = 'show tables;'
    cursor.execute(showtables)
    data1 = cursor.fetchall()
    new_file = ""
    for x in data1:
        if not "log" in x[0] and "site_siteeditorconf" not in x[0]:
            os.system(
                "mysqldump -u%s -p%s -h%s idss %s > %s" % (db_user, db_passwd, db_host, x[0], db_backup_name))
            with open(db_backup_name, "rt", encoding="utf-8")as f:
                new_file += f.read()
    with open(db_backup_name, "wt", encoding="utf-8")as f:
        f.write(new_file)

02 导入

def post(self, request):
    db_backup_name = r"%s/apps/system/sql/up.sql" % BASE_DIR
    db_data_conf = DATABASES.get("default")
    db_host = db_data_conf.get("HOST")
    db_user = db_data_conf.get("USER")
    db_passwd = db_data_conf.get("PASSWORD")
    db_name = db_data_conf.get("NAME")
    os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.utf8'
    db = pymysql.connect(host=db_host, db=db_name, user=db_user, passwd=db_passwd)
    c = db.cursor()
    with open(db_backup_name, 'r') as f:
        sql_list = f.read().split(';')
    for sql_item in sql_list:
        c.execute(sql_item)
    c.close()
    db.commit()