CODEWITHSUNDEEP
pythonmysqlpostgresql
java
java

Reputation: 1214

Insert data to Postgresql from MySQL

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?

Upvotes: 0

Views: 2032

Answers (1)

Parfait
Parfait

Reputation: 107767

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

Upvotes: 1

Related Questions