stock/Chan241206/copy_db.py

import pymysql
from tqdm import tqdm

# Remote database configuration
remote_db_config = {
    'host': '192.168.31.111',
    'port': 3307,
    'user': 'root',
    'password': 'r6kEwqWU9!v3',
    'database': 'qmt_stocks_whole'
}

# Local database configuration
local_db_config = {
    'host': 'localhost',
    'port': 3307,
    'user': 'root',
    'password': 'r6kEwqWU9!v3',
    'database': 'qmt_stocks_whole'
}

def fetch_tables(cursor):
    cursor.execute("SHOW TABLES")
    return [table[0] for table in cursor.fetchall()]

def fetch_table_schema(cursor, table_name):
    cursor.execute(f"SHOW CREATE TABLE `{table_name}`")
    return cursor.fetchone()[1]

def fetch_table_data(cursor, table_name):
    cursor.execute(f"SELECT * FROM `{table_name}`")
    return cursor.fetchall(), cursor.description

def create_table(cursor, create_table_sql):
    cursor.execute(create_table_sql)

def insert_data(cursor, table_name, data, description):
    columns = ", ".join([desc[0] for desc in description])
    placeholders = ", ".join(["%s"] * len(description))
    insert_sql = f"INSERT INTO `{table_name}` ({columns}) VALUES ({placeholders})"
    cursor.executemany(insert_sql, data)

def drop_table_if_exists(cursor, table_name):
    cursor.execute(f"DROP TABLE IF EXISTS `{table_name}`")

def table_exists(cursor, table_name):
    cursor.execute(f"SHOW TABLES LIKE '{table_name}'")
    return cursor.fetchone() is not None

def main():
    # Connect to remote database
    remote_conn = pymysql.connect(**remote_db_config)
    remote_cursor = remote_conn.cursor()

    # Connect to local database
    local_conn = pymysql.connect(**local_db_config)
    local_cursor = local_conn.cursor()

    try:
        # Fetch all tables from remote database
        tables = fetch_tables(remote_cursor)
        for table in tqdm(tables, desc="Processing tables"):
            # Fetch table schema
            create_table_sql = fetch_table_schema(remote_cursor, table)
            # Check if table exists in local database
            if table_exists(local_cursor, table):
                # Drop table in local database if exists
                drop_table_if_exists(local_cursor, table)
            # Create table in local database
            create_table(local_cursor, create_table_sql)
            # Fetch table data
            data, description = fetch_table_data(remote_cursor, table)
            # Insert data into local database
            insert_data(local_cursor, table, data, description)
        local_conn.commit()
    finally:
        remote_cursor.close()
        remote_conn.close()
        local_cursor.close()
        local_conn.close()

if __name__ == "__main__":
    main()