# CHANGED: 2012-12-21 - tab_serial appended if does not already exist # ... also see end of loop below tab_serial_missing = False if 'tab_serial' not in target_fields_found: tab_serial_missing = True target_fields_found.append('tab_serial') debug = False if debug: sys.exit(1) # myspot.cr_mysql_tab(target_db, table_dict_name, target_fields_found, # raw_flg = 0, # import_flg = 1, # replace_flg = 1) myspot.quick_mysql_tab(cursor_out, fdict, table_out, target_fields_found) stmt_in = "SELECT %s FROM %s" % (', '.join(source_fields), table_in) cursor_in.execute(stmt_in) rows = cursor_in.fetchall() for index, values in enumerate(rows): sfv = zip(source_fields, target_fields, list(values)) # print sfv clean_values = [] for source, fname, value in sfv: # print fname if fname == 'ALIAS_NOT_IN_FIELD_DICT': continue fspec = fdict_lookup[fname]
# print sql_dict stmt = "REPLACE INTO keys_dvr (%s) VALUES ('%s')" % (", ".join([ 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)
stmt = "REPLACE INTO keys_dvr (%s) VALUES ('%s')" % ( ", ".join([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)
# CHANGED: 2012-12-21 - tab_serial appended if does not already exist # ... also see end of loop below tab_serial_missing = False if 'tab_serial' not in target_fields_found: tab_serial_missing = True target_fields_found.append('tab_serial') debug = False if debug: sys.exit(1) # myspot.cr_mysql_tab(target_db, table_dict_name, target_fields_found, # raw_flg = 0, # import_flg = 1, # replace_flg = 1) myspot.quick_mysql_tab(cursor_out, fdict, table_out, target_fields_found) stmt_in = "SELECT %s FROM %s" % (', '.join(source_fields), table_in) cursor_in.execute(stmt_in) rows = cursor_in.fetchall() for index, values in enumerate(rows): sfv = zip(source_fields, target_fields, list(values)) # print sfv clean_values = [] for source, fname, value in sfv: # print fname if fname == 'ALIAS_NOT_IN_FIELD_DICT': continue fspec = fdict_lookup[fname] if 'sqltype' in fspec:
metadata_fields = [ '_valid_row', '_valid_allfields', '_count_missfields', '_count_unusualfields', '_count_impossfields', '_list_unusualchks', '_list_imposschks' ] # Prepare insert statement field list # fields = [i['field'] for i in rows_dict.values()[0]] 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)
cursor_out = cursor metadata_fields = [ '_valid_row', '_valid_allfields', '_count_missfields', '_count_unusualfields', '_count_impossfields', '_list_unusualchks', '_list_imposschks'] # Prepare insert statement field list # fields = [i['field'] for i in rows_dict.values()[0]] 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