Export the table creation statement in the MySQL database to a file using Python

Export the table creation statement in the MySQL database to a file using Python

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 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.exe cute (SQL _string)
List = cursor. fetchall ()
Cursor. close ()
Self. conn. close ()
Return list
Failed 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 ()
Failed t pymysql. Error as e:
Print ("mysql execute error:", e)
Raise

Def 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)

# File target path. If the path does not exist, create a new one.
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': '2017. 0.0.1', 'user ':'******',
'Password': '******', 'Port': 3306, 'db': 'databse _ name '}

If list:
For row in list:
Print (row [0])
# Switch to the new folder
OS. 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 '],
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 ()

Database creation Test

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 ('aaaaa', now ());
Insert into table_a (name, create_date) values ('bbbbbbbb ', 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 ('aaaaa', now ());
Insert into table_ B (name, create_date) values ('bbbbbbbb', 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 OS

Filepath = "D :\\ mysqlscript"
# Switch to the new folder
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
# Re-write the extracted content to the file
Print (content)
Fp = open (file, 'w ')
Fp. write (content)
Fp. close ()

This article permanently updates link: https://www.bkjia.com/Linux/2018-03/151390.htm