# 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
