public
Last active

Python script for migrate database from postgres to mysql

  • Download Gist
pg2mysql.py
Python
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83
#! /usr/bin/env python
import os
import psycopg2
import MySQLdb
 
PG = {
'name' : 'moodle_rvt_sociales',
'user' : 'diegueus9',
'pass' : 'fuseki',
'host' : 'localhost',
}
 
MYSQL = {
'db': 'moodle_rvt_sociales',
'user': 'root',
'passwd': 'fuseki',
'host': 'localhost',
}
 
TABLES_LIST = [
'pais',
'tutor_rvt',
'tipo_area',
'administradores',
'ahijado',
'contactos',
'departamento',
'desempena',
'ejerce_dp',
'ejerce_en',
'ejerce_tv',
'grupo_interes',
'institucion',
'lista_contactos_grupo',
'municipio',
'promedio',
'promedio_categoria',
'rol_es_mti',
'tabla_auxiliar_para_borrar_los_que_nunca',
'tipo_area',
]
 
def create_pg_connection():
string = "dbname={name} user={user} password={pass}".format(**PG)
conn = psycopg2.connect(string)
cur = conn.cursor()
return cur
 
def generate_tsv(table):
file_ = open('/tmp/{0}.tsv'.format(table), 'w')
kwargs = {
'file': file_,
'table': table,
}
cursor = create_pg_connection()
cursor.copy_to(**kwargs)
cursor.close()
 
def import_tsv(table):
MYSQL.update({'table': table})
mysql_command = 'mysqlimport --local --compress --user={user} --password={passwd} --verbose --host={host} {db} /tmp/{table}.tsv'.format(**MYSQL)
os.system(mysql_command)
 
def create_mysql_connection():
conn = MySQLdb.connect(**MYSQL)
cursor = conn.cursor()
return cursor
 
def get_mysql_tables():
cursor = create_mysql_connection()
cursor.execute(""" SHOW TABLES """)
result = cursor.fetchall()
for table in result:
yield table[0]
 
 
if __name__=="__main__":
TABLES_LIST = []
for table in get_mysql_tables():
TABLES_LIST.append(table)
for table in TABLES_LIST:
generate_tsv(table)
import_tsv(table)

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.