Python exports the table creation statement of the MySQL database to the file,

Python exports the table creation statement of the MySQL database to the file,

 

To control the version of Data Objects, You need to export the table structure in the MySQL database to a file for version management. After a try, you can completely export the table structure information in the database.

#-*-Coding: UTF-8-*-import osimport pymysqlclass 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.exe cute (SQL _string) list = cursor. fetchall () cursor. close () self. conn. close () return list tables t pymysql. error as e: print ("mysql execute error:", e) raise def execute_noquery (self, SQL _string): try: cursor = self. cursor cursor.exe cute (SQL _string) self. conn. commit () self. cursor. close () self. conn. close () using t pymysql. error as e: print ("mysql execute error:", e) raisedef main (): conn_dict = {'host': '2017. 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.exe cute_query (SQL _gettables) # create a new mysql_file_path = 'd if the path does not exist: \ mysqlscript 'If not OS. path. exists (mysql_file_path): OS. mkdir (mysql_file_path) mysqldump_commad_dict = {'dumpcommad ': 'mysqldump -- no-data', 'server': '2017. 0.0.1 ', 'user':' ******* ', 'Password':' ******* ', 'Port': 3306, 'db ': 'databse _ name'} if list: for row in list: print (row [0]) # Switch to the OS in the new folder. chdir (mysql_file_path) # table name dbtable = row [0] # file name exportfile = row [0] + '. SQL '# mysqldump command sqlfromat = "% s-h % s-u % s-p % s-P % s> % s" # generate the corresponding SQL statement SQL = (sqlfromat % (mysqldump_commad_dict ['dumpcommad '], comment ['server'], mysqldump_commad_dict ['user'], mysqldump_commad_dict ['Password'], comment ['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 ()

 

Database creation Test

create database test_databasecharset 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());

 

A warning is prompted during execution, but the final result is not affected.

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

The export table creation statement numbers the auto-incrementing columns Based on the table data. This is a problem of mysqldump, rather than an export problem. If necessary, modify the column accordingly.

 

 

 

Remove the remarks in the mysqldump export table structure

Import osfilepath = "D :\\ mysqlscript" # Switch to the OS In the new folder. 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 # re-write the extracted content to the file print (content) fp = open (file, 'w') fp. write (content) fp. close ()