为了做数据对象的版本控制, 需要将 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()
来源: http://www.linuxidc.com/Linux/2018-03/151390.htm