Traspasar datos entre bases de datos diferentes con Python

En este post vamos a ver cómo traspasar información de una base de datos a otra, aunque solo la estructura y el contenido de las tablas.

Si bien existen en el mercado diferentes aplicaciones que realizan este trabajo, podemos de una manera rápida y sencilla crear un script en Python que realice esta tarea.

Evidentemente, si tenemos conocimientos de ETL, con SQLServer y paquetes DTS ó Integration Services, dependiendo de la versión, se pueden hacer este tipo de operaciones. Pero, ¿y si no tenemos este tipo de herramientas? ¿Y si estamos en plataformas no Windows? Aquí vamos a utilizar Python como "pegamento", esto es, una navaja suiza, para poder traspasar datos. ¿Ventajas? En unas pocas líneas de texto vamos a crear una solución potente y sencilla de traspaso de información.

Como fuente de origen vamos a utilizar una base de datos Firebird, que es un sistema gestor de bases de datos gratuita. Nunca he trabajado con este sistema, y solo lo conocía de oídas, al hablar de Delphi. Sin embargo, en unas dos o tres horas podemos hacernos mas ó menos una vista bastante general de cómo funciona. No es objetivo de este post el explicar las ventajas e inconvenientes de Firebird, pero solo decir que es una opción bastante interesante para aplicar a entornos de producción.

El destino será una base de datos en MySQL, de la que he hablado tanto en este blog.

¿Qué es lo que vamos a hacer? Vamos a crear varios scripts, que lo que van a realizar es:

1. Conectar con una base de datos Firebird, y obtener los nombres de las tablas de dicha base de datos. A continuación generamos un fichero script SQL con los CREATE TABLE de las tablas obtenidas. Puesto que una tabla no se puede crear sin al menos un campo, crearemos un campo TESTIGO, que luego podrá ser eliminado. Decir que Firebird tiene un módulo para Python, denominado KInterbasDB. A continuación obtener todas las columnas de todas las tablas. Aquí hay que tener cuidado, ya que los tipos de datos de Firebird no son los mismos que los tipos de datos de MySQL. Es más, FireBird utiliza en muchas ocasiones DOMINIOS, para identificar tipos de datos en los campos, que es incompatible con MySQL (está en el estándar ANSI SQL92). De esta manera nos curamos en salud. Este script Python genera 2 ficheros script SQL. El anteriormente dicho y otro con los ALTER TABLE para incluir los campos que nos faltan.
2. Bien, tenemos las estructuras en ficheros SQL. ¿Qué nos falta? Evidentemente los datos. Para ello nos servimos de un segundo script de Python, que lo que hace es obtener los datos de cada una de las tablas de Firebird (mediante una conexión) y guardarlas en un fichero de script SQL con un formato INSERT INTO.
3. Finalmente tenemos 3 ficheros script SQL (creación de tablas, inserción de campos en dichas tablas e inserción de datos). Ahora podemos hacer dos cosas, o bien cargar en MySQL estos ficheros script SQL y lanzarnos con una herramienta llamada Query Browser ó crearnos una lanzadera en Python, que haga el trabajo. Vamos a realizar esta última operación como ejemplo, para ver que se puede hacer.
   

Empezamos, a continuacións se presenta el script Python de obtención de datos de la base de datos origen, que está en una Firebird:

# -*- coding: cp1252 -*-
import kinterbasdb
import os

# Fichero de creación de tablas y adición de campos.
fichero1 = 'crear_tablas.sql'
fichero2 = 'add_campos.sql'

# Ruta de conexión.
rutaAcceso = 'localhost:C:\basedatos_origen\bd_origen_firebird.gdb'

# Intentamos conectar con FireBird.
try:
  conexion= kinterbasdb.connect(dsn=rutaAcceso,\
  user='sysdba', password='123456',dialect=3, charset='DOS437')
  conectado = True
  print "Conectado!"
except:
  print "No he podido conectar con ", rutaAcceso

# Estamos conectados, a trabajar!
if conectado:
  # Obtenemos todas las tablas.
  cadenaSQL = '''
  select rdb$relation_name
  from rdb$relations
  where rdb$view_blr is null
  and (rdb$system_flag is null or rdb$system_flag = 0);
  '''
  # Inicializamos cursor.
  cursor = conexion.cursor()
  print "Recuperando nombres de tablas....!"
  # Ejecutamos cursor.
  cursor.execute(cadenaSQL)
  # Guardamos los nombres de las tablas en una lista.
  tablas = []
  for fila in cursor:
        tablas.append(fila[0])
  print "OK!"
  # Cadena que obtiene tablas con campos y tipos de datos.
  cadenaSQL = '''
  select rf.rdb$relation_name, rf.rdb$field_name,
  rf.rdb$field_source,
  f.rdb$field_type,     t.rdb$type_name,
  f.rdb$field_sub_type, f.rdb$character_length, f.rdb$field_scale,
  f.rdb$field_length, st.rdb$type_name as
  rdb$sub_type_name,
  case f.rdb$field_type
  when 7 then 'smallint'
  when 8 then 'integer'
  when 16 then 'bigint'
  when 9 then 'quad'
  when 10 then 'float'
  when 11 then 'd_float'
  when 17 then 'boolean'
  when 27 then 'double'
  when 12 then 'datetime'
  when 13 then 'time'
  when 35 then 'timestamp'
  when 261 then 'blob'
  when 37 then 'varchar'
  when 14 then 'char'
  when 40 then 'cstring'
  when 45 then 'blob_id'
  end as "ActualType",
  case f.rdb$field_type
  when 7 then
  case f.rdb$field_sub_type
  when 1 then 'numeric'
  when 2 then 'decimal'
  end
  when 8 then
  case f.rdb$field_sub_type
  when 1 then 'numeric'
  when 2 then 'decimal'
  end
  when 16 then
  case f.rdb$field_sub_type
  when 1 then 'numeric'
  when 2 then 'decimal'
  else 'bigint'
  end
  when 14 then
  case f.rdb$field_sub_type
  when 0 then 'unspecified'
  when 1 then 'binary'
  when 3 then 'acl'
  else
  case
  when f.rdb$field_sub_type is null then 'unspecified'
  end
  end
  when 37 then
  case f.rdb$field_sub_type
  when 0 then 'unspecified'
  when 1 then 'text'
  when 3 then 'acl'
  else
  case
  when f.rdb$field_sub_type is null then 'unspecified'
  end
  end
  when 261 then
  case f.rdb$field_sub_type
  when 0 then 'unspecified'
  when 1 then 'text'
  when 2 then 'blr'
  when 3 then 'acl'
  when 4 then 'reserved'
  when 5 then 'encoded-meta-data'
  when 6 then 'irregular-finished-multi-db-tx'
  when 7 then 'transactional_description'
  when 8 then 'external_file_description'
  end
  end as "ActualSubType"
  from rdb$relation_fields rf
  join rdb$fields f
  on f.rdb$field_name = rf.rdb$field_source
  left join rdb$types t
  on t.rdb$type = f.rdb$field_type
  and t.rdb$field_name = 'RDB$FIELD_TYPE'
  left join rdb$types st
  on st.rdb$type = f.rdb$field_sub_type
  and st.rdb$field_name = 'RDB$FIELD_SUB_TYPE'
  where rf.rdb$view_context is null
  and (rf.rdb$system_flag is null or rf.rdb$system_flag = 0)
  order by
  rf.rdb$relation_name,
  rf.rdb$field_position ;
  '''
  # Inicializamos cursor.
  cursor.close()
  cursor = conexion.cursor()
  # Info.
  print "Recuperando información de campos de tablas!"
  # Ejecutamos cursor.
  cursor.execute(cadenaSQL)
  # Guardamos los nombres de las tablas en una lista.
  campos_tablas = []
  # Posiciones: 0 nombre tabla, 1 nombre campo, 2 long. campo, 10 tipo dato.
  for fila in cursor:
        campos_tablas.append((fila[0],fila[1],fila[8],fila[10]))
  # Info.
  print "OK!"
  print "Guardando información en ficheros SQL!"
  # Creamos fichero.
  f = open(os.path.realpath(fichero1), "w")
  # Creamos los create table.
  for tabla in tablas:
        cadenaSQL = 'CREATE TABLE ' + str(tabla).strip() +' ( campo_testigo char(1) ) ;'
        f.write(cadenaSQL+"\n")
  # Cerramos fichero.
  f.close()
  # Creamos fichero.
  f2 = open(os.path.realpath(fichero2), "w")
  # Creamos alter table.
  for campo in campos_tablas:
        if str(campo[3]).strip() == 'datetime' or \
        str(campo[3]).strip() == 'timestamp' or \
        str(campo[3]).strip() == 'double' or \
        str(campo[3]).strip() == 'time':
              cadenaSQL =  'ALTER TABLE ' + str(campo[0]).strip() + ' ADD ' + str(campo[1]).strip() + \
              ' ' + str(campo[3]).strip() + ' ;'
        else:
              cadenaSQL =  'ALTER TABLE ' + str(campo[0]).strip() + ' ADD ' + str(campo[1]).strip() + \
              ' ' + str(campo[3]).strip() + '(' + str(campo[2]).strip()+ ') ;'
        f2.write(cadenaSQL+"\n")
  # Cerramos fichero.
  f2.close()
  # Info.
  print "Terminada exportación de datos!"
  # Cerramos conexiones de cursor y base de datos.
  print "Cerrando conexiones!"
  cursor.close()
  conexion.close()

Observaciones:

Este script genera dos ficheros script SQL, crear_tablas.SQL y add_campos.SQL que contienen sentencias SQL, CREATE TABLE y ALTER TABLE. Se puede ver que para obtener información de Firebird he tenido que utilizar unos SELECT que atacan a tablas del sistema. Estas sentencias las he tenido que buscar en Internet con San Google y modificarlas levemente, para poder obtener los datos que necesitaba. Darse cuenta en el SELECT que busca los campos de las tablas que hay que hacer una correspondencia, mas o menos acertada entre tipos de datos. ¿Qué significa esto? Pues que si queremos que el destino no sea MySQL sino otro sistema gestor bastará con cambiar ahí las correspondencias. Por ejemplo, en SQL Server de Microsoft un entero es int (no integer).

En el fichero crear_tablas.SQL tendremos en cada línea información como esta:

CREATE TABLE ejemplo1 campo_testigo char(1) ;

En el fichero add_campos.SQL tendremos en cada línea información como esta:

ALTER TABLE ejemplo1 ADD campo1 integer(4) ;
ALTER TABLE ejemplo1 ADD campo2 double ;

Continuamos. En este segundo script Python, como hemos dicho anteriormente, obtenemos la información de las tablas. Dicha información se guardará en un fichero script SQL con un formato de INSERT INTO. Veámoslo a continuación:

# -*- coding: utf-8 -*-
import kinterbasdb
import os

def reemplazar_caracteres(cadena):
  cadena = str(cadena)
  # Reemplazamos \ por /.
  cadena = cadena.replace('\\','/')
  # Quitamos comillas dobles.
  cadena = cadena.replace('"','')
  return cadena

# Info.
print "Carga de datos en el sistema cliente"

# Fichero de carga de datos mediante insert.
fichero1 = 'insert.sql'

# Ruta de conexión del origen.
rutaAcceso = 'localhost:C:\basedatos_origen\bd_origen_firebird.gdb'

# Inicializamos testigo.
conectado = False

# Intentamos conectar con FireBird.
try:
  conexion= kinterbasdb.connect(dsn=rutaAcceso,\
  user='sysdba', password='123456',dialect=3, charset='DOS437')
  conectado = True
  print "Conectado con FireBird!"
except:
  print "No he podido conectar con ", rutaAcceso

# Estamos conectados, a trabajar!
if conectado:
  # Obtenemos todas las tablas.
  cadenaSQL = '''
  select rdb$relation_name
  from rdb$relations
  where rdb$view_blr is null
  and (rdb$system_flag is null or rdb$system_flag = 0);
  '''
  # Inicializamos cursor.
  cursor = conexion.cursor()
  print "Recuperando nombres de tablas....!"
  # Ejecutamos cursor.
  cursor.execute(cadenaSQL)
  # Guardamos los nombres de las tablas en una list
  tablas = []
  for fila in cursor:
        tablas.append(fila[0])
  print "OK!"
  # Creamos fichero de script en donde se guardarán los insert.
  f3 = open(os.path.realpath(fichero1), "w")
  # Info.
  print "Generando sentencias INSERT para volcado de datos."
  # Vamos obteniendo información por cada tabla para generar sentencias SQL INSERT.
  for tabla in tablas:
        # info.
        print "Formando los INSERT para tabla ", tabla
        # Tabla actual a tratar.
        tabla_actual = tabla
        # Info.
        print "Recuperando información... esta operación puede tardar..."
        # Obtenemos todas las filas de la tabla.
        sentenciaSelect = "select * from "
        cursor.execute(sentenciaSelect + " " + tabla_actual)
        aux = cursor.fetchall()
        # Info.
        print "Formando sentencias INSERT y guardándolas en fichero script... un momento..."
        # Para cada fila, vamos obteniendo la información para construir las INSERT.
        for i in aux:
              cadenaSQL = 'INSERT INTO ' + tabla_actual + ' VALUES('
              aux2 = ''
              for j in i:
                    # Si nos encontramos con un registro muy grande podría interesar obviarlo. OJO!!!!!
                    if len(str(j)) > 2000:
                          j = "DATOS NO CARGADOS"
                          print "Cadena demasiada larga, no se guardará!"
                    if aux2 == '':
                          j = reemplazar_caracteres(j)              
                          aux2 = 'null' if j == 'None' else '"' + str(j) + '"'
                    else:
                          j = reemplazar_caracteres(j)                       
                          k = 'null' if str(j) == 'None' else '"' + str(j) + '"'
                          aux2 = str(aux2) + ', ' + k
              cadenaSQL = str(cadenaSQL) + str(aux2) + ') ;' 
              f3.write(cadenaSQL+"\n")
  # Cerramos fichero.
  f3.close()
  # Cerramos conexiones de cursor y base de datos.
  print "Cerrando conexiones de FireBird!"
  cursor.close()
  conexion.close()

Observaciones:

Vemos que este script Python genera un script SQL llamado insert.SQL. Este fichero tendrá, por cada línea la siguiente información:

INSERT INTO nombre_tabla VALUES('DATO 1','DATO 2',....,'DATO N') ;

El lector tiene que apreciar lo sencillo que es hacer herramientas potentes con unas pocas líneas de código.

Ya tenemos los 3 ficheros script SQL listos para ser ejecutados en el Query Browser de MySQL. Y si no queremos hacerlo pues nos montamos una lanzadera y listo. Voy a escribir el código para una lanzadera, que puede servir para estos tres scripts SQL creados. Aquí lo presento para insert.SQL, aunque evidentemente sirve para los tres, con los cambios apropiados (que son triviales y dejo al lector para no extender más este artículo). Aquí está el último script en Python, la lanzadera:

# -*- coding: utf-8 -*-
import MySQLdb
import os

# Info.
print "Lanzadera de sentencias SQL."

# Fichero a lanzar.
fichero1 = 'insert.sql'
fichero_error = 'error_lanzadera.sql'

# Base de datos.
base_de_datos = 'bd_destino_MySQL'

# Inicializamos testigo.
conectado = False

# Intentamos conectar con sistema gestor (MySQL).
try:
  conexionMySQL = MySQLdb.Connect("localhost", "root","1234",base_de_datos)
  cursorMySQL = conexionMySQL.cursor()
  conectado = True
  print "Conectado con MySQL!"
except:
  print "No he podido conectar con base de datos MySQL: ", base_de_datos

# Estamos conectados, a trabajar!
if conectado:
  # Abrimos fichero de sentencias SQL.
  f = open(os.path.realpath(fichero1),'r')
  # Abrimos fichero de posibles errores.
  f1 = open(os.path.realpath(fichero_error),'w')
  # Ejecutamos sentencia SQL, por cada línea.
  while True:
        # Línea de fichero.
        cadenaSQL = f.readline()
        if not cadenaSQL:
              print "TERMINÉ DE LANZAR SENTENCIAS SQL. SALIENDO DEL SCRIPT DE PYTHON!!!"
              break
        try:
              # Lanzamos Sentencia.
              cursorMySQL.execute(cadenaSQL)
              # Confirmamos escritura.
              conexionMySQL.commit()
        except:
              # Hubo un error, se informa, hay que parar.
              conexionMySQL.rollback()
              # info.
              print "Hubo un error en la instrucción: ", cadenaSQL
              print "Guardando fallo en fichero!"
              f1.write(cadenaSQL + "\n")
  # Cerramos fichero.
  f.close()
  f1.close()
  # Cerramos conexiones de cursor y base de datos.
  print "Cerrando conexiones de MySQL!"
  cursorMySQL.close()
  conexionMySQL.close()

Observaciones:

Este script lanza un script SQL dado. Además genera un fichero con las posibles sentencias SQL que han fallado. Darse cuenta que aunque haya sentencias que fallen, el script Python sigue ejecutándose. De este modo en el fichero de errores tendremos únicamente las que han fallado, de modo que lo tendremos todo controlado, y sabremos lo que ha funcionado y lo que no. En el fichero de errores se guardan las sentencias enteras.

CONCLUSIONES:

No es difícil crear con Python herramientas que nos saquen de más de un apuro. En este caso crear un trasvase de información entre bases de datos de diferentes fabricantes. Cabría destacar que se podría eliminar el campo testigo que se ha utilizado, mediante el correspondiente script SQL (generado en Python), que se deja al lector para su desarrollo. Cuestiones negativas son las codificaciones en las que se encuentren las bases de datos, ya que nos podemos llevar una sorpresa desagradable si se encuentran con caracteres extraños (el eterno problema de las codificaciones en Python, algo que sigue sin gustarme).
De todas formas este tipo de operaciones se pueden realizar con herramientas que están en el mercado, y muy potentes. Aunque si no las tienes a mano, y necesitas salir del apuro... Python está ahí para algo.

Y es que Python aquí, funciona como un verdadero pegamento.