skip to Main Content

I have created 2 databases called source_db and destination_db my source_db contains these things or i did these things while creating source_db

-- Create the Customers table in the source database
CREATE TABLE Customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255),
    address VARCHAR(255)
);

-- Insert sample data into the Customers table
INSERT INTO Customers (name, email, address)
VALUES
    ('John Doe', '[email protected]', '123 Main St'),
    ('Jane Smith', '[email protected]', '456 Oak Ave');

-- Create the Orders table in the source database
CREATE TABLE Orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES Customers(customer_id),
    product VARCHAR(255),
    quantity INT,
    price DECIMAL(10, 2)
);

-- Insert sample data into the Orders table
INSERT INTO Orders (customer_id, product, quantity, price)
VALUES
    (1, 'Product A', 2, 19.99),
    (1, 'Product B', 1, 9.99),
    (2, 'Product C', 3, 14.99);

and for destination_db

CREATE TABLE Customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(20),
    address_id INT
);

-- Create the Addresses table in the destination database
CREATE TABLE Addresses (
    address_id SERIAL PRIMARY KEY,
    street VARCHAR(255),
    city VARCHAR(255),
    state VARCHAR(255),
    country VARCHAR(255)
);

-- Create the Orders table in the destination database
CREATE TABLE Orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES Customers(customer_id),
    product VARCHAR(255),
    quantity INT,
    price DECIMAL(10, 2),
    order_date DATE,
    is_delivered BOOLEAN
);

now I wrote a python script to migrate the database but it seems that it’s not working

import psycopg2

# Source database
source_host = 'localhost'
destination_host = source_host
source_port = destination_port = '5432'
source_database = 'source_db'
destination_database = 'destination_db'
source_user = destination_user = 'postgres'
source_password = destination_password = 'mysecretpassword'


def migrate_data():
    # Connect to the source database
    source_conn = psycopg2.connect(
        host=source_host,
        port=source_port,
        database=source_database,
        user=source_user,
        password=source_password
    )

    # Connect to the destination database
    destination_conn = psycopg2.connect(
        host=destination_host,
        port=destination_port,
        database=destination_database,
        user=destination_user,
        password=destination_password
    )

    # Create a cursor for the source database
    source_cursor = source_conn.cursor()

    # Create a cursor for the destination database
    destination_cursor = destination_conn.cursor()

    try:
        # Retrieve the table names from the source database
        source_cursor.execute("SELECT table_name FROM information_schema.tables WHERE table_schema='public'")
        table_names = [row[0] for row in source_cursor]

        # Migrate each table from the source to the destination
        for table_name in table_names:
            # Retrieve the data from the source table
            source_cursor.execute(f"SELECT customer_id, email FROM {table_name}")
            records = source_cursor.fetchall()

            # Prepare the insert statement for the destination table
            destination_cursor.execute(
                f"SELECT column_name FROM information_schema.columns WHERE table_name='{table_name}'")
            destination_columns = [row[0] for row in destination_cursor]

            # Filter the destination columns based on source columns
            columns = [column for column in destination_columns if column in ['customer_id', 'address']]

            column_names = ', '.join(columns)
            placeholders = ', '.join(['%s'] * len(columns))

            insert_query = f"INSERT INTO {table_name} ({column_names}) VALUES ({placeholders})"

            # Insert the data into the destination table
            destination_cursor.executemany(insert_query, records)

        # Commit the changes to the destination database
        destination_conn.commit()

        print('Data migration completed successfully.')
    except (Exception, psycopg2.DatabaseError) as error:
        print('Error occurred during data migration:', error)
        destination_conn.rollback()
    finally:
        # Close the cursors and connections
        source_cursor.close()
        destination_cursor.close()
        source_conn.close()
        destination_conn.close()


migrate_data()

I am getting this error

nitesh@nitesh:~/Documents/databasemigrationspython$ /bin/python3 /home/nitesh/Documents/databasemigrationspython/migratedata.py
Error occurred during data migration: not all arguments converted during string formatting

I don’t to how to solve this please help.

2

Answers


  1. You have '%s' there, which python thinks is part of a formatted string.

    You can either escape it, using '%%s', or raw strings by adding an r before the first quote: r'%s'

    Login or Signup to reply.
  2. I think it is because you have different number of columns in destination database. I would recommend to manually write code to cater extra columns and then insert each record.

    Here is what you need to do:

    def migrate_data():
        try:
            // Connect to the databases
            // Retrieve the table names from the source database
    
            for each table_name in table_names
                // Retrieve the data from the source table
                // Prepare the insert statement for the destination table according to column
                // Insert the data into the destination table
    
        except:
            //handle exceptions
        finally:
            // Close the cursors and connections
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search