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?

So do you read data from one source, enter loop and insert row-by-row to another db? It would have poor performance. — Lukasz Szozda
– Lukasz Szozda, Commented Apr 10, 2016 at 11:48
@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 — java
– java, Commented Apr 10, 2016 at 11:52
Check: Is there a fast method for exporting a large table from mysql to postgresql? — Lukasz Szozda
– Lukasz Szozda, Commented Apr 10, 2016 at 11:54
@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. — java
– java, Commented Apr 10, 2016 at 12:22

Parfait · Accepted Answer · 2016-04-10 15:03:18Z

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()

answered Apr 10, 2016 at 15:03

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert data to Postgresql from MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related