print len(talias_dict)
print talias_dict
sys.exit(1)
# first check there are no identical field names
ttemp =[f['fname'] for f in fdict]
if len(ttemp) > len(set(ttemp)):
print "ERROR: Duplicate field names found in dictionary"
sys.exit(1)
else:
fdict_lookup = {}
for f in fdict:
fdict_lookup[f['fname'].lower()] = f
# Get fields names from existing table
field_list = myspot.sql_get_cols(source_db, table_in)
field_list = [f.lower() for f in field_list]
print field_list
source_fields, target_fields = [], []
# for field in field_list:
# if field not in talias_dict:
# print "WARNING: %s not in field dictionary - will not be imported" % field
# continue
# fname = talias_dict[field]
# fspec = fdict_lookup[fname]
# source_fields.append(field)
# target_fields.append(fname)
# print field, fname
t_tab = args.target_table
o_tab = args.target_table + args.suffix_original
c_tab = args.target_table + args.suffix_index
print "\nOK: Running index_table.py for %s\n" % o_tab
cursor, connection = sql_connect(args.target_db, connection=True)
tdict = get_yaml_dict('table', local_dict=True)
fdict = get_yaml_dict('field', local_dict=True)
cdict = get_yaml_dict('corrections', local_dict=True)
tspec = get_tspec(tdict, t_tab)
pkeys = tspec['pkey']
# if there are excel sheet, row references then grab these as well
field_names = sql_get_cols(args.target_db, o_tab)
if 'modifiedat' in field_names:
field_names.remove('modifiedat')
excel_source = False
if 'excel_sheet' in field_names and 'excel_row' in field_names:
excel_source = True
# stmt = """ SELECT excel_sheet, excel_row FROM %s """ % (o_tab)
# cursor.execute(stmt)
# row_keys = cursor.fetchall()
# row_keys = ["sheet %s row %d" % (i[0], i[1]) for i in row_keys]
# else:
# assume sql_source
# CHANGED: 2012-09-07 - adds a unique row index to all tables
stmt = "ALTER TABLE %s DROP COLUMN tab_serial" % o_tab
t_tab = args.target_table
o_tab = args.target_table + args.suffix_original
c_tab = args.target_table + args.suffix_index
print "\nOK: Running index_table.py for %s\n" % o_tab
cursor, connection = sql_connect(args.target_db, connection=True)
tdict = get_yaml_dict('table', local_dict=True)
fdict = get_yaml_dict('field', local_dict=True)
cdict = get_yaml_dict('corrections', local_dict=True)
tspec = get_tspec(tdict, t_tab)
pkeys = tspec['pkey']
# if there are excel sheet, row references then grab these as well
field_names = sql_get_cols(args.target_db, o_tab)
if 'modifiedat' in field_names:
field_names.remove('modifiedat')
excel_source = False
if 'excel_sheet' in field_names and 'excel_row' in field_names:
excel_source = True
# stmt = """ SELECT excel_sheet, excel_row FROM %s """ % (o_tab)
# cursor.execute(stmt)
# row_keys = cursor.fetchall()
# row_keys = ["sheet %s row %d" % (i[0], i[1]) for i in row_keys]
# else:
# assume sql_source
# CHANGED: 2012-09-07 - adds a unique row index to all tables
stmt = "ALTER TABLE %s DROP COLUMN tab_serial" % o_tab
print len(talias_dict)
print talias_dict
sys.exit(1)
# first check there are no identical field names
ttemp = [f['fname'] for f in fdict]
if len(ttemp) > len(set(ttemp)):
print "ERROR: Duplicate field names found in dictionary"
sys.exit(1)
else:
fdict_lookup = {}
for f in fdict:
fdict_lookup[f['fname'].lower()] = f
# Get fields names from existing table
field_list = myspot.sql_get_cols(source_db, table_in)
field_list = [f.lower() for f in field_list]
print field_list
source_fields, target_fields = [], []
# for field in field_list:
# if field not in talias_dict:
# print "WARNING: %s not in field dictionary - will not be imported" % field
# continue
# fname = talias_dict[field]
# fspec = fdict_lookup[fname]
# source_fields.append(field)
# target_fields.append(fname)
# print field, fname
sh_dict.write(i + tab_offset, 3, tab_label)
next_offset = i + tab_offset
tab_offset = next_offset + 1
dvr_book.save('../sitecomms/outgoing/' + dvr_filename)
# ========================
# = Add key errors sheet =
# ========================
kerror_headings_original = ['validation_msg', 'key_fields', 'key_values', 'missing_fields']
columns_to_skip = ['modifiedat', 'sourceFileTimeStamp']
columns_to_redact = ['dob', 'namef', 'namel', 'idnhs', 'idpcode']
cursor = sql_connect('spotid')
for tab, tab_name in source_tables.items():
kerror_fields = sql_get_cols('spotid', 'keys_dvr')
stmt = """SELECT %s FROM keys_dvr
WHERE locate('%s', key_values)
AND sql_table = '%s' """ % (
', '.join(kerror_fields), sitecode, tab + '_import')
cursor.execute(stmt)
kerror_rows = cursor.fetchall()
# skip on if no key errors
if not kerror_rows:
continue
# create dictionary of kerror vals
krows = []
for kerror_row in kerror_rows:
kdict = dict(zip(kerror_fields, kerror_row))
krows.append(kdict)
tab_offset = next_offset + 1
dvr_book.save('../sitecomms/outgoing/' + dvr_filename)
# ========================
# = Add key errors sheet =
# ========================
kerror_headings_original = [
'validation_msg', 'key_fields', 'key_values', 'missing_fields'
]
columns_to_skip = ['modifiedat', 'sourceFileTimeStamp']
columns_to_redact = ['dob', 'namef', 'namel', 'idnhs', 'idpcode']
cursor = sql_connect('spotid')
for tab, tab_name in source_tables.items():
kerror_fields = sql_get_cols('spotid', 'keys_dvr')
stmt = """SELECT %s FROM keys_dvr
WHERE locate('%s', key_values)
AND sql_table = '%s' """ % (', '.join(kerror_fields),
sitecode, tab + '_import')
cursor.execute(stmt)
kerror_rows = cursor.fetchall()
# skip on if no key errors
if not kerror_rows:
continue
# create dictionary of kerror vals
krows = []
for kerror_row in kerror_rows:
kdict = dict(zip(kerror_fields, kerror_row))
krows.append(kdict)