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
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'
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: