Python导出MySQL数据库中表的建表语句到文件

时间:2021-05-03 08:20:56

 

为了做数据对象的版本控制,需要将MySQL数据库中的表结构导出成文件进行版本化管理,试写了一下,可以完整导出数据库中的表结构信息

# -*- coding: utf-8 -*-
import os
import pymysql


class DBTool:

conn
= None
cursor
= None

def __init__(self,conn_dict):
self.conn
= pymysql.connect(host=conn_dict['host'],
port
=conn_dict['port'],
user
=conn_dict['user'],
passwd
=conn_dict['password'],
db
=conn_dict['db'],
charset
=conn_dict['charset'])
self.cursor
= self.conn.cursor()


def execute_query(self, sql_string):
try:
cursor
=self.cursor
cursor.execute(sql_string)
list
= cursor.fetchall()
cursor.close()
self.conn.close()
return list
except pymysql.Error as e:
print("mysql execute error:", e)
raise

def execute_noquery(self, sql_string):
try:
cursor
= self.cursor
cursor.execute(sql_string)
self.conn.commit()
self.cursor.close()
self.conn.close()
except pymysql.Error as e:
print("mysql execute error:", e)
raise

def main():
conn_dict
= {'host': '127.0.0.1', 'port': 3306, 'user': '******', 'password': '******', 'db': 'test', 'charset': 'utf8'}
conn
= DBTool(conn_dict)
sql_gettables
= "select table_name from information_schema.`TABLES` WHERE TABLE_SCHEMA = 'databas_name';"
list
= conn.execute_query(sql_gettables)

# 文件目标路径,如果不存在,新建一个
mysql_file_path = 'D:\mysqlscript'
if not os.path.exists(mysql_file_path):
os.mkdir(mysql_file_path)

mysqldump_commad_dict
= {'dumpcommad': 'mysqldump --no-data ', 'server': '127.0.0.1', 'user': '******',
'password': '******', 'port': 3306, 'db': 'databse_name'}

if list:
for row in list:
print(row[0])
# 切换到新建的文件夹中
os.chdir(mysql_file_path)
#表名
dbtable = row[0]
#文件名
exportfile = row[0] + '.sql'
# mysqldump 命令
sqlfromat = "%s -h%s -u%s -p%s -P%s %s %s >%s"
# 生成相应的sql语句
sql = (sqlfromat % (mysqldump_commad_dict['dumpcommad'],
mysqldump_commad_dict[
'server'],
mysqldump_commad_dict[
'user'],
mysqldump_commad_dict[
'password'],
mysqldump_commad_dict[
'port'],
mysqldump_commad_dict[
'db'],
dbtable,
exportfile))
print(sql)
result
= os.system(sql)
if result:
print('export ok')
else:
print('export fail')

if __name__ == '__main__':
main()

 

建库测试

create database test_database
charset utf8mb4 collate utf8mb4_bin;

use test_database;


create table table_a
(
id
int auto_increment not null,
name
varchar(100) unique,
create_date
datetime,
primary key pk_id(id),
index idx_create_date(create_date)
);

insert into table_a(name,create_date) values ('aaaaaa',now());
insert into table_a(name,create_date) values ('bbbbbb',now());

create table table_b
(
id
int auto_increment not null,
name
varchar(100) unique,
create_date
datetime,
primary key pk_id(id),
index idx_create_date(create_date)
);
insert into table_b(name,create_date) values ('aaaaaa',now());
insert into table_b(name,create_date) values ('bbbbbb',now());

 

执行的时候会提示一个警告,但是不影响最终的结果

mysqldump: [Warning] Using a password on the command line interface can be insecure.

Python导出MySQL数据库中表的建表语句到文件

导出建表语句会根据表的数据情况编号自增列,这是mysqldump的问题而不是导出的问题,如果有必要可以需求做相应的修改

Python导出MySQL数据库中表的建表语句到文件

 

 

 

去掉mysqldump导出表结构中备注信息

import os

filepath
= "D:\\mysqlscript"
# 切换到新建的文件夹中
os.chdir(filepath)
pathDir
= os.listdir(filepath)
for file in pathDir:
lines
= open(file, "r")
content
= "use ***;"
content
= content + "\n"
for line in lines:
print(line)
if not (str(line).startswith("--") or str(line).startswith("/*") ):
if(line!="\n" and str(line).startswith(") ENGINE")):
content
= content +"\n"+ ")"
else:
content
= content + line
#将提炼后的内容重新写入文件
print(content)
fp
= open(file, 'w')
fp.write(content)
fp.close()