Python copy table from one database to another

by

Copying a Table from One Database to Another in Python

In order to copy a table from one database to another using Python, you can follow these steps:

  1. Establish connections to both databases
  2. Create a cursor to execute SQL queries
  3. Retrieve the table data from the source database
  4. Create the same table in the destination database
  5. Insert the retrieved data into the new table
  6. Close the connections

Example:

Let’s assume we have two databases:

  • Source Database: example_db1
  • Destination Database: example_db2

We will copy a table called users from the source database to the destination database.


import mysql.connector

# Establish connection to the source database
source_conn = mysql.connector.connect(
    host="localhost",
    user="source_user",
    password="source_password",
    database="example_db1"
)

# Establish connection to the destination database
destination_conn = mysql.connector.connect(
    host="localhost",
    user="destination_user",
    password="destination_password",
    database="example_db2"
)

# Create cursor for executing SQL queries
source_cursor = source_conn.cursor()
destination_cursor = destination_conn.cursor()

# Retrieve the table data from the source database
source_cursor.execute("SELECT * FROM users")
table_data = source_cursor.fetchall()

# Create the same table in the destination database
destination_cursor.execute("CREATE TABLE IF NOT EXISTS users (id INT, name VARCHAR(255))")

# Insert the retrieved data into the new table
destination_cursor.executemany("INSERT INTO users (id, name) VALUES (%s, %s)", table_data)

# Commit the changes
destination_conn.commit()

# Close the connections
source_cursor.close()
destination_cursor.close()
source_conn.close()
destination_conn.close()

In this example, we are using MySQL Connector/Python library to establish connections and execute SQL queries. You may need to install this library using pip (pip install mysql-connector-python) if you haven’t already.

The code first establishes connections to both the source and destination databases using the appropriate credentials. Then, it creates cursors for executing queries on each database.

We retrieve the data from the “users” table in the source database using the execute() method of the source cursor. Next, we create the same table structure in the destination database using the CREATE TABLE SQL query.

Finally, we insert the retrieved data into the new table in the destination database using the executemany() method. We pass in the SQL query and the table data retrieved from the source database.

After the changes are committed using commit(), we close the cursors and connections to release resources.

Leave a comment