'''Get column headers and data types using messytables'''

table = open(path[0]+CSV, 'rb')

# Creates a set of tables as file object, although it'll just be one

tableset = messytables.CSVTableSet(table)

rowset = tableset.tables[0] # get first and only table as iterator

# guesses header names and offset of header, returns headers as list

offset, headers = messytables.headers_guess(rowset.sample)

print "Here is the offset", str(offset), "\nHere are the headers:\n"\

, str(headers) # test

# establish headers in table

rowset.register_processor(messytables.headers_processor(headers))

# begin iterator at content, rather than header

rowset.register_processor(messytables.offset_processor(offset + 1))

# guess column types, return as list

types = messytables.type_guess(rowset.sample, strict=True)

print "Here are the data types", str(types)

dtypedict = {} # empty dictionary to append columns and datatype needed

# for pandas csv to dataframe conversion

colcount = 0 # location to append datatypes to match columns in dict

for column in types:

dtypedict[headers[colcount]]=column

colcount+=1

'''Uses pandas to convert input csv to specified SQLite database and

table (sqldatabase, sqlouttable). Dtypedict is used to set the datatypes

of the columns. Columns are the header names.

'''

# connect to database

cnx = sqlite3.connect(path[0]+sqldatabase)

cnx.text_factory = str

df = pd.read_csv(path[0] + CSV, dtype = dtypedict, # dict of col data types

header=None, # no header, define col header manually later

low_memory=False) # otherwise annoying error may show

#drop first row, since it contains headers

df.drop(df.index[:1], inplace=True)

df.columns = columns # set columns collected from messytables

df.to_sql(name=sqlouttable, con=cnx)

CSV = "WaterTableData.csv" # input csv

sqldatabase = "WaterTable.sqlite" # output sqlite database

sqlouttable = "WaterTableData" # output sql table

columns, dtypedict = headersDataTypes(CSV)