Python导出MySQL数据库中表的建表语句到文件

# -*- coding: utf-8 -*-
import os
import pymysql
<p>class DatabaseManager:
connection = None
cursor = None</p><pre class="brush:php;toolbar:false;"><code>def __init__(self, connection_params):
    self.connection = pymysql.connect(
        host=connection_params['host'],
        port=connection_params['port'],
        user=connection_params['user'],
        passwd=connection_params['password'],
        db=connection_params['db'],
        charset=connection_params['charset']
    )
    self.cursor = self.connection.cursor()

def run_query(self, sql_string):
    try:
        with self.cursor as cursor:
            cursor.execute(sql_string)
            results = cursor.fetchall()
        self.connection.close()
        return results
    except pymysql.Error as error:
        print("MySQL query error:", error)
        raise

def run_non_query(self, sql_string):
    try:
        with self.cursor as cursor:
            cursor.execute(sql_string)
        self.connection.commit()
        self.connection.close()
    except pymysql.Error as error:
        print("MySQL non-query error:", error)
        raise

<code># 定义文件目标路径，如果路径不存在，则创建
export_path = 'D:\mysqlscript'
if not os.path.exists(export_path):
    os.mkdir(export_path)

mysqldump_params = {
    'dump_command': 'mysqldump --no-data ',
    'server': '127.0.0.1',
    'user': '******',
    'password': '******',
    'port': 3306,
    'db': 'database_name'
}

if tables:
    for table in tables:
        print(table[0])
        os.chdir(export_path)
        table_name = table[0]
        export_file = f"{table_name}.sql"
        sql_format = "%s -h%s -u%s -p%s -P%s %s %s >%s"
        sql_command = sql_format % (
            mysqldump_params['dump_command'],
            mysqldump_params['server'],
            mysqldump_params['user'],
            mysqldump_params['password'],
            mysqldump_params['port'],
            mysqldump_params['db'],
            table_name,
            export_file
        )
        print(sql_command)
        result = os.system(sql_command)
        if result == 0:
            print('导出成功')
        else:
            print('导出失败')</code>

CREATE DATABASE test_database CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;</p><p>USE test_database;</p><p>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)
);</p><p>INSERT INTO table_a (name, create_date) VALUES ('aaaaaa', NOW());
INSERT INTO table_a (name, create_date) VALUES ('bbbbbb', NOW());</p><p>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)
);</p><p>INSERT INTO table_b (name, create_date) VALUES ('aaaaaa', NOW());
INSERT INTO table_b (name, create_date) VALUES ('bbbbbb', NOW());

<code>mysqldump: [Warning] Using a password on the command line interface can be insecure.</code>

import os</p><p>export_path = "D:mysqlscript"
os.chdir(export_path)
files = os.listdir(export_path)
for file in files:
with open(file, "r") as f:
content = "USE **<em>;
"
for line in f:
if not (line.startswith("--") or line.startswith("/</em>")):
if line != "
" and line.startswith(") ENGINE"):
content += "
" + ")"
else:
content += line
print(content)
with open(file, 'w') as f:
f.write(content)