def createTable(dbhandle,
error,
options,
rows=None,
headers=None,
first_column=None,
existing_tables=[]):
# create table by guessing column types from data type.
if rows:
map_column2type, ignored, max_values = CSV.GetMapColumn2Type(
rows, ignore_empty=options.ignore_empty, get_max_values=True)
if ignored:
E.info("ignored columns: %s" % str(ignored))
headers = map_column2type.keys()
headers.sort()
elif headers:
map_column2type = dict(zip(headers, [
None,
] * len(headers)))
ignored = 0
columns_to_ignore = set([x.lower() for x in options.ignore_columns])
columns_to_rename = dict(
[x.lower().split(":") for x in options.rename_columns])
take = []
# associate headers to field names
columns = []
present = {}
for header_index, h in enumerate(headers):
hh = h
if options.lowercase:
hh = string.lower(h)
if hh in columns_to_ignore:
continue
if hh in present:
if options.ignore_duplicates:
continue
else:
raise ValueError("duplicate column %s" % hh)
present[hh] = 1
take.append(h)
if map_column2type[h] == int:
max_value = max_values[h]
if max_value > 2147483647:
t = "BIGINT DEFAULT '0'"
elif max_value > 32767:
t = "INTEGER DEFAULT '0'"
else:
t = "SMALLINT DEFAULT '0'"
elif map_column2type[h] == float:
t = "FLOAT DEFAULT '0'"
else:
t = "TEXT"
# remove special characters from column names
if hh == "":
if first_column is not None and header_index == 0:
hh = first_column
else:
raise ValueError("column '%s' without header " % h)
hh = columns_to_rename.get(hh, hh)
hh = re.sub('''['"]''', "", hh)
hh = re.sub("[,;.:\-\+/ ()%?]", "_", hh)
if hh[0] in "0123456789":
hh = "_" + hh
columns.append("%s %s" % (hh, t))
# delete old table if it exists
while 1:
try:
cc = dbhandle.cursor()
cc.execute("DROP TABLE IF EXISTS '%s'" % options.tablename)
dbhandle.commit()
cc.close()
E.info("existing table %s deleted" % options.tablename)
except sqlite3.OperationalError, msg:
E.warn(msg)
time.sleep(5)
continue
except error, msg:
E.warn("could not delete existing table %s: %s" %
(options.tablename, str(msg)))
dbhandle.rollback()
if not options.retry:
raise error, msg
elif options.tablename in existing_tables:
# table exists, but drop did not work (e.g. database lock)
time.sleep(5)
continue
else:
# table might not have existed
break
