为了做数据对象的版本控制，需要将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.
导出建表语句会根据表的数据情况编号自增列，这是mysqldump的问题而不是导出的问题，如果有必要可以需求做相应的修改
去掉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()