for line in f:
lat, lon, _, altitude, _, date, time = line.split(',')
date_time = date + ' ' + time
point_query = """INSERT INTO ge_point(tid, user_id, geom, altitude,
date_time, utc_offset) VALUES (""" + str(tid) + ", " + user + \
""", ST_SetSRID(ST_MakePoint(:lon, :lat), 4326), :altitude,
':date_time', 8)"""
point_query = point_query.replace(':lat', lat)
point_query = point_query.replace(':lon', lon)
point_query = point_query.replace(':date_time', date_time)
point_query = point_query.replace(':altitude', altitude)
db.execute(point_query)
count += 1
logger.log_dyn(Logger.INFO, 'Inserting user ' + user +
' (' + str(count) + ' points imported).')
tid += 1
if os.path.isfile(modes):
df = pd.read_csv(modes, sep='\t')
for index, row in df.iterrows():
start, end, mode = (row['Start Time'], row['End Time'],
row['Transportation Mode'])
update_query = "UPDATE ge_point SET transportation_id = " + \
":mode WHERE user_id = :user AND " + \
"date_time >= ':start' AND date_time <= ':end'"
update_query = update_query.replace(":mode", transp[mode])
update_query = update_query.replace(":user", user)
update_query = update_query.replace(":start", start)
update_query = update_query.replace(":end", end)
db.execute(update_query)
exit()
##########################################################
####################### OP HANDLE ########################
if (db.connect()):
logger.log(
Logger.INFO, "Succesfully connected to database \'" +
str(config['DATABASE']['NAME']) + "\'!")
else:
logger.log(
Logger.ERROR, "Failed connecting to database \'" +
str(config['DATABASE']['NAME']) + "\'!")
if OPERATION == 'create':
logger.log_dyn(
Logger.INFO, "Creating schema for database '" +
str(config['DATABASE']['NAME']) + "'... ")
db.execute(open(CREATE_SCHEMA_FILE, "r").read())
db.commit()
logger.log(
Logger.INFO, "Creating schema for database '" +
str(config['DATABASE']['NAME']) + "'... SUCCESS!")
for file in INSERT_FILES:
logger.log_dyn(Logger.INFO,
"Executing inserts for file '" + file + "'... ")
db.execute(open(file, "r").read())
db.commit()
logger.log(Logger.INFO,
"Executing inserts for file '" + file + "'... SUCCESS!")
