Python script to import mysql to postgresql

Question

With reference to Import MySQL dump to PostgreSQL database. An unknown developer has offered there to use the following script to import MySQL database to PostgreSQL

import MySQLdb
#from magic import Connect #Private mysql connect information - I COMMENTED THIS LINE to use direct connection
db = MySQLdb.connect(host="localhost", # your host, usually localhost
                     user="USER", # your username
                      passwd="PASS", # your password
                      db="w3i") # name of the data base
import psycopg2

dbx=Connect()
DB=psycopg2.connect("dbname='w3i'")
DC=DB.cursor()

mysql='''show tables from w3i'''
dbx.execute(mysql); ts=dbx.fetchall(); tables=[]
for table in ts: tables.append(table[0])
for table in tables:
    mysql='''describe w3i.%s'''%(table)
    dbx.execute(mysql); rows=dbx.fetchall()
    psql='drop table %s'%(table)
    DC.execute(psql); DB.commit()

    psql='create table %s ('%(table)
    for row in rows:
        name=row[0]; type=row[1]
        if 'int' in type: type='int8'
        if 'blob' in type: type='bytea'
        if 'datetime' in type: type='timestamptz'
        psql+='%s %s,'%(name,type)
    psql=psql.strip(',')+')'
    print psql
    try: DC.execute(psql); DB.commit()
    except: pass

    msql='''select * from w3i.%s'''%(table)
    dbx.execute(msql); rows=dbx.fetchall()
    n=len(rows); print n; t=n
    if n==0: continue #skip if no data

    cols=len(rows[0])
    for row in rows:
        ps=', '.join(['%s']*cols)
        psql='''insert into %s values(%s)'''%(table, ps)
        DC.execute(psql,(row))
        n=n-1
        if n%1000==1: DB.commit(); print n,t,t-n
    DB.commit()

As you can see - I changed line 2 to direct connection with MySQL

But now I have the following error

python postgres.py
Traceback (most recent call last):
  File "postgres.py", line 9, in <module>
    dbx=Connect()
NameError: name 'Connect' is not defined

Thanks in advance for a hint how to solve it !

Answer 1

EDIT : I forgot the cursor ...

EDIT2 : original script did not correctly process fields of TINYTEXT, MEDIUMTEXT or LONGTEXT type => added a conversion to PostgreSQL TEXT type

EDIT3 : the original script did not process ENUM fields, choked on non 7 bits characters, and had a wrong error management

You commented out line 2 where Connect was defined, but you left line 9 where Connect() is used untouched, so the error.

As you now explicitely connect to MySQL, you should replace dbx = Connect() with :

dbx = db.cursor()

It should now give (with the conversion of TEXT types line 28):

import MySQLdb
#from magic import Connect #Private mysql connect information - I COMMENTED THIS LINE to use direct connection
db = MySQLdb.connect(host="localhost", # your host, usually localhost
                     user="USER", # your username
                      passwd="PASS", # your password
                      db="w3i") # name of the data base
import psycopg2

# set client_encoding if different that PostgreSQL database default
encoding = 'Latin1'

dbx=db.cursor()
DB=psycopg2.connect("dbname='w3i'")
DC=DB.cursor()
DC.execute("set client_encoding = " + encoding)

mysql='''show tables from w3i'''
dbx.execute(mysql); ts=dbx.fetchall(); tables=[]
for table in ts: tables.append(table[0])
for table in tables:
    mysql='''describe w3i.%s'''%(table)
    dbx.execute(mysql); rows=dbx.fetchall()
    psql='drop table %s'%(table)
    DC.execute(psql); DB.commit()

    psql='create table %s ('%(table)
    for row in rows:
        name=row[0]; type=row[1]
        if 'int' in type: type='int8'
        if 'blob' in type: type='bytea'
        if 'datetime' in type: type='timestamptz'
        if 'text' in type: type='text'
        if 'enum' in type:
            type = 'varchar'
            print ("warning : conversion of enum to varchar %s(%s)" % (table, name))
        psql+='%s %s,'%(name,type)
    psql=psql.strip(',')+')'
    print psql
    try: DC.execute(psql); DB.commit()
    except Exception as e:
        print e
        DB.rollback()

Above script convert enum to VARCHAR. If you have only one enum type you can try to create it PostgreSQL side :

DC.execute("DROP TYPE IF EXISTS enumtyp CASCADE")
DC.execute("CREATE TYPE enumtyp AS ENUM( ... )"

where enumtyp is the name of the type and ... is the list of (textual) values (don't forget to add an empty value if the field can be empty in MySQL)

Then you replace enum with enumtyp by replacing line type = 'varchar' with :

        if 'enum' in type:
            type = 'enumtyp'

Answer 2

as reported in the answer you cite:

from magic import Connect #Private mysql connect information

Connect() is (I assume) a method feeding parameters to connect to a specific db.

You thus have either to implement on your own this magic module, with references to your specific parameters, or to specify which connection you want to setup, namely MySQLdb.connect(...) or psycopg2.connect(...)