Insert query result from one table into another table -- Python

Question

I have a requirement to connect to two Databases's and copy data over from source DB(Postgres) to Target DB (MySQL)

I am getting an error

TypeError: can only concatenate str (not "list") to str

When I try to generate an insert query when copying data from source to target

I am able to connect to the databases successfully, so that's not an issue

def query_sql(connection, query):
    cursor = connection.cursor()
    cursor.execute(query)
    return cursor.fetchall()

def insert_to_target_table(data):

    insert_sql = "INSERT INTO " + schema + "." + target_table + " SELECT * FROM " + data
    execute_query(db_connection, insert_sql)

full_data = query_sql(postgres_connection, "SELECT * from    "+schema+"."+src_table+";")

insert_to_target_table(full_data)

I know the full_data is returned as a list and that's giving the error Is there any other way by converting type or using pandas that this can be achieved?

Answer 1

I have also recently worked on same thing, I can help you with this code of mine, I edit it a bit for you.It will help you to copy your data from postgres database to mysql using python language

import psycopg2
import mysql.connector

# PostgreSQL connection parameters
postgres_conn = psycopg2.connect(
    host='postgres_host',
    port=5432,
    database='postgres_db',
    user='postgres_user',
    password='postgres_password'
)

# MySQL connection parameters
mysql_conn = mysql.connector.connect(
    host='mysql_host',
    database='mysql_db',
    user='mysql_user',
    password='mysql_password'
)

# Define source and target tables
src_table = 'source_table'
target_table = 'target_table'

# Retrieve data from PostgreSQL
cursor = postgres_conn.cursor()
cursor.execute(f"SELECT * FROM {src_table}")
data = cursor.fetchall()

# Insert data into MySQL
cursor = mysql_conn.cursor()
for row in data:
    cursor.execute(f"INSERT INTO {target_table} VALUES ({', '.join(['%s']*len(row))})", row)
mysql_conn.commit()

# Close connections
cursor.close()
postgres_conn.close()
mysql_conn.close()

To transfer data between the two databases, this code uses psycopg2 for PostgreSQL and mysql-connector-python for MySQL. Your actual database connection information and table names should be used in place of the placeholder values.

Hope it works :)