value = None # print value # raw_input() success, result = myspot.sql_2mysql(value, sqltype, sqllen, format='iso') if not success: print "WARNING: for %s failed to parse %s as %s" % ( fname, value, sqltype) # print result clean_values.append(result) # CHANGED: 2012-12-21 - row index added if tab_serial does not already exist # ... helps b/c index_table expects to find tab_serial if tab_serial_missing: clean_values.append(index + 1) stmt_out = myspot.sql_prepare_insert( table_out, target_fields_found, clean_values) # print stmt_out try: cursor_out.execute(stmt_out) except Exception, e: print stmt_out print repr(e) # print cursor_out.cursor.info() # if index == 0: # break # now write sourceFile/timestamp data updatetime = myspot.sql_get_updatetime(source_db, table_in) stmt = "ALTER TABLE %s ADD COLUMN sourceFile CHAR(64)" % table_out
str(i) for i in sql_dict.keys() ]), "', '".join([str(i) for i in sql_dict.values()])) # print stmt cursor.execute(stmt) index += 1 if index % 100 == 0: # NOTE: 2012-12-29 - ending print with comma stops newline print ".", # ============================================================= # = Now create indexed _ix suffix table and insert clean rows = # ============================================================= success = quick_mysql_tab(cursor, fdict, c_tab, field_names) for index, row in enumerate(rows_clean): stmt = sql_prepare_insert(c_tab, field_names, row) # print stmt cursor.execute(stmt) if index % 100 == 0: # NOTE: 2012-12-29 - ending print with comma stops newline print ".", # ========================================== # = Pen-ultimately! add a primary key to the table = # ========================================== stmt = "ALTER TABLE %s ADD PRIMARY KEY (%s)" % (c_tab, ', '.join(pkeys)) try: # print stmt cursor.execute(stmt) except: print "ERROR: Failed to create primary key for %s" % c_tab
"', '".join([str(i) for i in sql_dict.values()])) # print stmt cursor.execute(stmt) index += 1 if index % 100 == 0: # NOTE: 2012-12-29 - ending print with comma stops newline print ".", # ============================================================= # = Now create indexed _ix suffix table and insert clean rows = # ============================================================= success = quick_mysql_tab(cursor, fdict, c_tab, field_names) for index, row in enumerate(rows_clean): stmt = sql_prepare_insert(c_tab, field_names, row) # print stmt cursor.execute(stmt) if index % 100 == 0: # NOTE: 2012-12-29 - ending print with comma stops newline print ".", # ========================================== # = Pen-ultimately! add a primary key to the table = # ========================================== stmt = "ALTER TABLE %s ADD PRIMARY KEY (%s)" % (c_tab, ', '.join(pkeys)) try: # print stmt cursor.execute(stmt) except: print "ERROR: Failed to create primary key for %s" % c_tab
success, result = myspot.sql_2mysql(value, sqltype, sqllen, format='iso') if not success: print "WARNING: for %s failed to parse %s as %s" % (fname, value, sqltype) # print result clean_values.append(result) # CHANGED: 2012-12-21 - row index added if tab_serial does not already exist # ... helps b/c index_table expects to find tab_serial if tab_serial_missing: clean_values.append(index + 1) stmt_out = myspot.sql_prepare_insert(table_out, target_fields_found, clean_values) # print stmt_out try: cursor_out.execute(stmt_out) except Exception, e: print stmt_out print repr(e) # print cursor_out.cursor.info() # if index == 0: # break # now write sourceFile/timestamp data updatetime = myspot.sql_get_updatetime(source_db, table_in) stmt = "ALTER TABLE %s ADD COLUMN sourceFile CHAR(64)" % table_out cursor_out.execute(stmt)
# print insert_fields insert_fields.extend(metadata_fields) # Create SQL table # TODO: 2012-08-18 - need a different create table statement if labelled fields requested success = quick_mysql_tab(cursor_out, fdict, tab_name, insert_fields) # success = quick_mysql_tab(cursor, fdict, tab_name, insert_fields, labelled = True) for rowkey, row in rows_dict.items(): insert_dict = row_to_insert_dict(row, insert_fields, result=mysql_output_lvl) fv = insert_dict.items() insert_fields, values = tuple(zip(*fv)) stmt = sql_prepare_insert(tab_name, insert_fields, values) # print stmt cursor_out.execute(stmt) if debug: sys.exit(1) # Finally add a primary key to the table stmt = "ALTER TABLE %s ADD PRIMARY KEY (%s)" % (tab_name, ', '.join(pkeys)) cursor_out.execute(stmt) # Run post-flight SQL script if 'postflight' in tspec and tspec['postflight'] is not None: sql_multistmt(cursor, tspec['postflight']) # ==================== # = Pickle the data =
insert_fields = fields # print insert_fields insert_fields.extend(metadata_fields) # Create SQL table # TODO: 2012-08-18 - need a different create table statement if labelled fields requested success = quick_mysql_tab(cursor_out, fdict, tab_name, insert_fields) # success = quick_mysql_tab(cursor, fdict, tab_name, insert_fields, labelled = True) for rowkey, row in rows_dict.items(): insert_dict = row_to_insert_dict( row, insert_fields, result=mysql_output_lvl) fv = insert_dict.items() insert_fields, values = tuple(zip(*fv)) stmt = sql_prepare_insert(tab_name, insert_fields, values) # print stmt cursor_out.execute(stmt) if debug: sys.exit(1) # Finally add a primary key to the table stmt = "ALTER TABLE %s ADD PRIMARY KEY (%s)" % (tab_name, ', '.join(pkeys)) cursor_out.execute(stmt) # Run post-flight SQL script if 'postflight' in tspec and tspec['postflight'] is not None: sql_multistmt(cursor, tspec['postflight']) # ==================== # = Pickle the data =