def select_what(todos_los_campos):
'''
Genera los campos del select
'''
columnas = columnas_utiles(todos_los_campos)
print ', '.join([ t['tipo'] for t in columnas ])
cols = []
for c in columnas:
if c['tipo'] == 'ntext':
# http://pymssql.sourceforge.net/faq.php
# http://msdn.microsoft.com/en-us/library/ms187928.aspx
cols.append('CAST(%s AS VARCHAR)' % c['nombre'])
elif c['tipo'] == 'bit':
#cols.append('CAST(%s AS INT)' % c['nombre'])
cols.append(("%s = CONVERT(VARCHAR(5), CASE %s "
"WHEN 1 THEN 'true' ELSE 'false' END)")%
(c['nombre'], c['nombre']))
else:
cols.append(c['nombre'])
return ', '.join(cols)
def copia_datos(conexion_origen, conexion_destino, tablas, limpiar_destino = True, chunk_commit = 100):
'''
Copia datos entre conexiones
'''
conexion_destino.set_client_encoding('LATIN1')
cur_orig = conexion_origen.cursor()
cur_dest = conexion_destino.cursor()
#from ipdb import set_trace; set_trace()
cant_tablas, actual = len(tablas), 0
for tabla, campos in TABLAS.iteritems():
if tabla.lower() not in ('attachments', 'documentos'):
continue
print "Tabla", tabla
cur_orig.execute('SELECT COUNT(*) FROM %s' % tabla)
total_origen = cur_orig.fetchone()[0]
sql_source = 'SELECT %s FROM %s' % (select_what(campos), tabla)
print "Query: ", sql_source
cur_orig.execute(sql_source)
if limpiar_destino:
cur_dest.execute('DELETE FROM %s' % tabla)
conexion_destino.commit()
cant = 0
col_utils = columnas_utiles(campos)
cant_filas = len(col_utils)
values_dest = ','.join(['%s' for i in xrange(cant_filas)])
sql_dest = "INSERT INTO %s (%s) VALUES (%s);" % (tabla,
','.join(columnas_insert(col_utils)),
values_dest)
while True:
datos = cur_orig.fetchmany(chunk_commit)
if not datos:
break
for fila in datos:
try:
fila = adaptar_binarios(col_utils, fila)
cur_dest.execute( sql_dest, fila)
except psycopg2.DataError, e:
if e.pgcode == '22P02':
print "Datos Invalidos"
print fila
conexion_destino.rollback()
else:
#from ipdb import set_trace; set_trace()
#print "Error en la fila", fila
raise
cant += len(datos)
if (cant % chunk_commit == 0):
#print "Commit destino"
conexion_destino.commit()
print cant
print cant
conexion_destino.commit()
actual += 1
print "Exito en tabla %d de %d (%.2f%%)" % (actual, cant_tablas,
float(actual) / cant_tablas * 100)
