Insert data to Postgresql from MySQL

Question

I have a code as follows:

import pg
import MySQLdb
db_postgre = pg.connect(dbname=...,user=...,passwd=...,host=.., port=...)
db_mysql=MySQLdb.Connect(user=...,passwd=...,db=..., host=...)
cur = db_mysql.cursor(MySQLdb.cursors.DictCursor)
cur.execute ("""SELECT X,Y,Z FROM tab_a""")
data = crs.fetchall ()
for row in data :
 #INSERT THE ROW (X,Y,Z) TO POSTGRESQL TABLE.

The table in PostgreSQL (post_tab_a) is identical to the one in MySQL . meaning it also has X,Y,Z all types are TEXT.

Is it possible to perform insert directly from cur? What is the easiyest way to do this insert?

• Lukasz Szozda: So do you read data from one source, enter loop and insert row-by-row to another db? It would have poor performance.
• java: @lad2025 The loop isn't mandatory. I just want to read from one source and enter another. I need something to mimic this query insert into post_tab_a (x,y,z) select x,y,z from tab_a. The issue is that post_tab_a is in postgresql and tab_a is in MYSql
• Lukasz Szozda: Check: Is there a fast method for exporting a large table from mysql to postgresql?
• java: @lad2025 it won't help in my case. I can't do it with files. My table isn't large and I need to import records from it 4-5 times a day. after I import them I delete them from MySQL. I doubt it will be more than 100 rows per day. Performace is important but I can ignore it for an eaier solution. I'm just looking for a way to write the query as simple as possible.

Answer 1

Simply open another cursor for Postgre for the iterative inserts. There wouldn't be any named confusion as tables correspond to their cursor/connection objects:

import pg
import MySQLdb
# DB CONNECTIONS
db_postgre = pg.connect(dbname=...,user=...,passwd=...,host=.., port=...)
db_mysql = MySQLdb.Connect(user=...,passwd=...,db=..., host=...)
# CURSORS
mycur = db_mysql.cursor()
postcur = db_postgre.cursor()
mycur.execute("SELECT X,Y,Z FROM tab_a")
for row in mycur.fetchall():
 postcur.execute("INSERT INTO tab_a (X,Y,Z) VALUES (%s, %s, %s)", \
 (row['X'], row['Y'], row['Z']))
 db_postgre.commit()
# CLOSE CURSORS
postcur.close()
mycur.close()
# CLOSE CONNECTIONS
db_postgre.close()
db_mysql.close()