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的问题而不是导出的问题，如果有必要可以需求做相应的修改
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()
