Al trabajar con Bases de Datos es común importar y exportar datos, bien para hacer backups o para compartir datos entre distintos entornos.

Una buena forma de hacerlo es mediante archivos de texto, especialmente si son motores diferentes o sistemas no visibles entre si por red.

Tener a mano un conjunto de funciones que faciliten el trabajo y permitan adaptarnos a los requisitos de forma rápida es fundamental para ahorrar tiempo y esfuerzo.

Vamos a ver un ejemplo completo de importación de datos desde archivo a una base de datos MySQL, utilizamos sentencias SQL estandar por lo que utilizando el conector adecuado funcionará con cualquier otro motor de BD.

Hemos visto con anterioridad MySQL desde Python, trabajando con MySQLdb y Archivos de texto con Python, los conceptos son los mismos. Vamos a utilizar MySQLdb así que échale un vistazo a las entradas antes de seguir si no tienes instalado MySQLdb o crees que necesitas repasar algún concepto.

Antecedentes

Antes de continuar, este método es cómodo y permite tener un control de cada operación al detalle, trabajando si es preciso con cada registro y permite monitorizar y registrar cada operación, sin embargo, no es el más rápido ni adecuado para grandes ficheros. Si el volumen de datos a importar es elevado o la trazabilidad de la operación no es necesaria, lo idóneo es utilizar el comando «LOAD DATA INFILE» del propio MySQL. Mira la entrada MySQL y Python, Importar grandes ficheros .txt con LOAD DATA INFILE

Vamos a partir de un archivo de texto que contendrá los datos a importar, cada linea es un registro, los campos se separan con el carácter pipe «|».

ID|PRC|USRID|FCH|LID|PGS|PUL|TL
1|1|48|2018-08-27 08:01:58.777000000|2|210|1|7
2|1|40|2018-08-27 08:03:34.680000000|1|400|1|7
3|1|45|2018-08-27 08:03:38.470000000|3|401|1|7
4|1|57|2018-08-27 08:05:22.467000000|5|400|1|7
5|1|59|2018-08-27 08:05:34.390000000|4|426|1|7
6|1|48|2018-08-27 08:05:44.580000000|6|400|1|7
...

El destino será la tabla CTPLX:

'id', 'int(10) unsigned', 'NO', 'PRI', NULL, ''
'prc', 'int(10) unsigned', 'NO', '', NULL, ''
'usrid', 'int(10) unsigned', 'NO', '', NULL, ''
'fch', 'datetime', 'YES', '', 'CURRENT_TIMESTAMP', ''
'lid', 'int(10) unsigned', 'NO', '', NULL, ''
'pgs', 'int(10) unsigned', 'NO', '', NULL, ''
'pul', 'int(10) unsigned', 'NO', '', NULL, ''
'tl', 'int(10) unsigned', 'NO', '', NULL, ''

La sentencia CREATE para crear la tabla:

CREATE TABLE CTPLX (
	id int unsigned not null, 
	prc int unsigned not null, 
	usrid int unsigned not null, 
	fch datetime NULL DEFAULT CURRENT_TIMESTAMP,
	lid int unsigned not null, 
	pgs int unsigned not null, 
	pul int unsigned not null, 
	tl int unsigned not null,
	constraint CTPL1 primary key (id)
);

Listo, tenemos lo necesario, sin más preámbulos, vamos al código

El código

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Sat Sep 29 21:26:01 2018
 
@author: altaruru and dge
"""
#declara objeto global connection a la BD mysql
import ncmfx.ncmdbclass
 
cnn=ncmdbclass.mysqlbase()
 
def getheader(slinea, separador):
    sflds=""    
    alinea=slinea.split(separador) # spliteamos elementos, devuelve lista
    for elemento in alinea:
        sflds=sflds + elemento + ", "                
    n=len(sflds)-2 # quitamos ultima ","
    if(n>0):
        sflds=sflds[0:n] # linea preparada        
        return True, sflds    
    # en cualquier otro  devuelve False
    return False, ""
    
def txt2sql(path, separador, tabla, colsnames, colsdef):    
    sname1, sext = splitfilename(path)
    spathlog = sname1 + "_log1.log"
    spathsql = sname1 + ".sql"
    # abre archivo log para escritura
    flog = open (spathlog,"w")
    flog.write("txt2sql(%s, %s, %s, %s, %s)\nINIT...\n" % (path, separador, tabla, colsnames, colsdef))
    # abre archivo SQL para escritura
    fsql = open (spathsql,'w')    
    # abre archivo origen para lectura
    f = open (path, "r")
    bflds=False
    count=0
    acolsdef=colsdef.split(",")
    ncolsdef=len(acolsdef)    
    bRet=True #por defecto a true, puede que no entre en el siguiente if
    if(colsnames!=""): # si la info de cabecera se pasa por parámetro la prepara                
        bRet, sflds=getheader(colsnames, separador)        
        if(bRet):
            smsg="OK # HEADER (%s)\n" % (colsnames)
            flog.write(smsg)
            bflds=True
        else:
            smsg="ERROR # Bad Header (%s)\n" % (colsnames)
            flog.write(smsg)        
        
    if(bRet):
        while(True):
            # hay que leer las lineas de una en una o se desborda con archivos grandes
            # la prueba se hace con archivo de 900Mb
            try:
                slinea=f.readline()
                if not(slinea): # si no lee mas lineas, se sale
                    print("EOF")
                    break
                else:                        
                    slinea=slinea.rstrip('\n') # quitamos el salto de linea
                    alinea=slinea.split(separador) # spliteamos elementos, devuelve lista
                    ncols=len(alinea) # contamos columnas/elementos
                    blineaOK=False               
                    # si el numero de elementos no se corresponde con el esperado ignora linea
                    if(ncols==ncolsdef):            
                        if(count==0 and colsnames==""):                    
                            # linea de cabecera
                            bflds, sflds = getheader(slinea, separador)
                            if(bflds):
                                blineaOK=True
                            else:
                                bRet=False
                        else:                
                            if (bflds):
                                # si no hay campos definidos no construye la sentencia
                                # preparamos sentencia INSERT                
                                stmp=""
                                eidx=0 # indice del elemento
                                for elemento in alinea:
                                    # hay que contemplar si el tipo de dato para la composición de la sentencia SQL
                                    # si es cadena o fecha '
                                    # cuidado con los decimales
                                    if not (elemento):
                                        stmp=stmp + "NULL, "
                                    else:
                                        if(acolsdef[eidx]=="N"):
                                            stmp=stmp + elemento + ", "
                                        else:
                                            stmp=stmp + "'" + elemento + "', "
                                        
                                    eidx+=1
                                n=len(stmp)-2 # quitamos ultima ","
                                if(n>0):
                                    stmp=stmp[0:n] # linea preparada
                                ssql="INSERT INTO %s (%s) VALUES (%s);" % (tabla, sflds, stmp)
                                fsql.write(ssql + "\n")
                                #print(ssql)
                                smsg="OK # (%s)\n" % (slinea)
                                flog.write(smsg)                    
                                #print("  txt > %d '%s'" % (ncols, slinea))
                                blineaOK=True # si todo es correcto marca linea OK
                            else:
                                smsg="ERROR # campos no definidos (%s)" % (slinea)
                                flog.write(smsg)
                                print(smsg)

            except Exception as e:
                smsg="*** Error: '%s'" % (e)            
                flog.write(smsg + "\n")
            except:
                flog.write("*** Error desconocido\n")
                
            # incrementa contador de lineas
            count+=1
                
            if not (blineaOK):
                smsg="ERROR # campos no definidos (%s)\n" % (slinea)
                flog.write(smsg)
            
            if(count>10 or not bRet):
            #if(not bRet):
                break
            
        # end while ----
        
        smsg="END\n total lineas: %d\n" % count
        flog.write(smsg)
        
    # endif bRet
        
    f.close()
    fsql.close()
    flog.close()
    return bRet
 
def splitfilename(filename):
    sname=""
    sext=""
    i=filename.rfind(".")
    if(i!=0):
        n=len(filename)
        j=n-i-1
        sname=filename[0:i]
        sext=filename[-j:]    
    return sname, sext
 
def txtsqlexec(svr, bd, usr, pwd, pathsql):
    # conecta a bd y ejecutar sentencias el archivo txt
    sname1, sext = splitfilename(pathsql)
    spathlog = sname1 + "_log2.log"    
    # abre archivo log para escritura
    flog = open (spathlog,'w')
    flog.write("txtsqlexec(%s, %s, %s, %s)\nINIT...\n" % (bd, usr, "*", pathsql))        
    cnn.Connect(svr, usr, pwd, bd)
    if(cnn.getState()==1):
        flog.write("OK # DB Connected!\n")
        # ok, conectado a la bd, abrimos el archivo con sentencias SQL
        f = open (pathsql, "r")
        # recorremos las lineas y se ejecutan
        while(True):
            # hay que leer las lineas de una en una o se desborda con archivos grandes
            # la prueba se hace con archivo de 900Mb
            try:
                slinea=f.readline()
                if not(slinea): # si no lee mas lineas, se sale
                    print("EOF")
                    break
                else:        
                    slinea=slinea.rstrip('\n') # quitamos el salto de linea
                    bexec, result = cnn.Exec(slinea, True)
                    if not(bexec):
                        sErrMsg=cnn.getError()
                        flog.write(sErrMsg + " >" + slinea + "\n")
            except Exception as e:
                smsg="*** Error: '%s'" % (e)            
                flog.write(smsg + "\n")
            except:
                flog.write("*** Error desconocido\n")            
        # end while
        f.close()
        flog.write("Sentencias ejecutadas\n")
        cnn.Close()
        flog.write("Desconecta de BD\n")
        flog.write("END\n")
    else:
        flog.write("ERROR # not Connected!\n")
                   
    flog.close()
 
bret1=txt2sql("/home/altaruru/pysrc/dat/CTLP.txt", "|", "CTPLX", "", "N,N,N,D,N,N,N,N") 
if(bret1):
    txtsqlexec("Servidor", "EsquemaBD", "usuariobd", "*pwdusuariobd*", "/home/altaruru/pysrc/dat/CTLPX.sql")

 

Ok, quizas es demasiado soltarlo ahí sin más.

El código está comentado y creo es bastante intuitivo.

Tenemos dos bloques:

  • Por un lado la función txt2sql que lee el archivo de texto y crea un .sql con sentencias INSERT.
  • El segundo bloque, la función txtsqlexec, lee el .sql y ejecuta el contenido del mismo.

función txt2sql

Lee archivo de texto con una estructura de linea por registro y campos separados por un caracter, en este caso «|» y crea un archivo .sql con sentencias INSERT.

Parámetros de la función
  • path del archivo
  • separador de campos
  • nombre de tabla destino
  • nombre de las columnas, si no vienen en la primera linea del .txt
  • cadena con los tipo de campo separados por coma «,»
    • ejemplo: «N,N,N,D,N,N,N,N»
    • N – Number, S – String, D – Date
Ejemplo de llamada a la función

txt2sql(«/home/altaruru/pysrc/dat/CTLP.txt», «|», «CTPLX», «», «N,N,N,D,N,N,N,N»)

Salida

Tras la ejecución tendremos el archivo CTLPX.sql:

INSERT INTO CTPLX (ID, PRC, USRID, FCH, LID, PGS, PUL, TL) VALUES (1, 1, 48, '2018-08-27 08:01:58.777000000', 2, 210, 1, 7);
INSERT INTO CTPLX (ID, PRC, USRID, FCH, LID, PGS, PUL, TL) VALUES (2, 1, 40, '2018-08-27 08:03:34.680000000', 1, 400, 1, 7);
INSERT INTO CTPLX (ID, PRC, USRID, FCH, LID, PGS, PUL, TL) VALUES (3, 1, 45, '2018-08-27 08:03:38.470000000', 3, 401, 1, 7);
INSERT INTO CTPLX (ID, PRC, USRID, FCH, LID, PGS, PUL, TL) VALUES (4, 1, 57, '2018-08-27 08:05:22.467000000', 5, 400, 1, 7);
INSERT INTO CTPLX (ID, PRC, USRID, FCH, LID, PGS, PUL, TL) VALUES (5, 1, 59, '2018-08-27 08:05:34.390000000', 4, 426, 1, 7);
INSERT INTO CTPLX (ID, PRC, USRID, FCH, LID, PGS, PUL, TL) VALUES (6, 1, 48, '2018-08-27 08:05:44.580000000', 6, 400, 1, 7);
INSERT INTO CTPLX (ID, PRC, USRID, FCH, LID, PGS, PUL, TL) VALUES (7, 1, 45, '2018-08-27 08:06:43.510000000', 9, 400, 1, 7);
INSERT INTO CTPLX (ID, PRC, USRID, FCH, LID, PGS, PUL, TL) VALUES (8, 1, 40, '2018-08-27 08:06:53.870000000', 8, 400, 1, 7);
...

En caso de errores estos quedarán registrados en CTLPX_log1.log

Si todo es correcto la función devuelve True

Función txtsqlexec

Esta función abre un archivo de texto, conecta a la BD y ejecuta las sentencias SQL que contiene.

Parámetros de la función

def txtsqlexec(svr, bd, usr, pwd, pathsql):

  • Nombre del Servidor MySQL
  • Base de Datos destino
  • Usuario MySQL
  • Contraseña MySQL
  • Path del archivo SQL a ejecutar

Si hay errores estos quedan registrados en CTLPX_log2.log

Ejemplo de llamada a la función

txtsqlexec(«Servidor», «EsquemaBD», «usuariobd», «*pwdusuariobd*», «/home/altaruru/pysrc/dat/CTLPX.sql»)

Ejecución

Una vez modificado el código con los parámetros de conexión a la BD y los path correctos queda ejecutar.

Si todo es correcto, se habrán generado los archivos .sql y .log así como los registros en la BD.

 

Si bien, no es la mejor forma de importar grandes archivos puede solucionarnos algún que otro caso.

De paso hemos visto, trabajo con cadenas, concatenar, split/división, lectura de archivos linea a linea, escritura de archivos e inserción en BD.

 

Quizás sea de vuestro interés MySQL y Python, Importar grandes ficheros .txt con LOAD DATA INFILE

Cualquier duda, no dudéis y preguntar.

Feliz día!

 

Deja tu comentario