1

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 !

Improve this question

2 Answers 2

Reset to default
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'
Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

You way seems to be closer to a solution, but using #dbx=Connect() I have error File "postgres.py", line 14, in <module> db.execute(mysql); ts=db.fetchall(); tables=[] AttributeError: 'Connection' object has no attribute 'execute' ...may be any other ideas ?
@Serge : Looks like I forgot the cursor ... should work better now :-)
Yes :) we go closer...now it actually tries to do something but now I see error on File "postgres.py", line 20, in <module> DC.execute(psql); DB.commit() psycopg2.ProgrammingError: table "categories" does not exist What is could be ?
@Serge : This one I know ! As you are migrating tables from MySQL to PostgreSQL, at beginning they do not exist. You must replace line 19 psql='drop table %s'%(table) with psql='drop table if exists %s'%(table)
...and it works ! but till next error...now it looks like create table categories (id int8,name varchar(255),alternates mediumtext) 0 Traceback (most recent call last): File "postgres.py", line 20, in <module> DC.execute(psql); DB.commit() psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block ...I regret we can't catch the author of this script to ask him what he meant writing this code ! ...from one point of view it could be a good instrument of import MySQL>PostgreSQL - but now I've no any idea what else to try...
|
0

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

Improve this answer

3 Comments

You're right, I've installed python-magic for magic - but I have no any idea or hint how to create Connect under python-magic...this is why I try to use a direct MySql connection
I don't think this was the case. python-magic is for something else. Make your own module and wrap the connection to the database into a method Connect()
I wish I could if I could know :) how to write a Python module...by the way - when I was trying without python-magic - it even did not start anything...so python-magic definitely has a matter here. Another question how to write this "Connect" so that magic could import the one

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.