def write_trip_to_sql(self, cursor):
"""
Once a trip is completed, it is written to the sqlite3 database.
Parameters:
``c`` (sqlite3.Cursor): the sqlite3 connection's cursor to execute SQL queries
"""
sql = ("INSERT INTO %s \
(trip_id, vehicle_id, direction, route, start_location, \
end_location, start_time, end_time, duration) \
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)" % TABLE_ONE)
row = [str(self.trip_id), str(self.vehicle_id),
str(self.direction), str(self.route),
str(self.start_location), str(self.end_location),
str(self.start_time), str(self.end_time),
str(self.duration)]
cursor.execute(sql, row)
def write_count_to_sql(self, curr_trips, cursor):
"""
Takes the amount of current trips for each route and direction, then writes it to a sqlite3 database at the time that the count was checked.
Parameters:
``curr_trips`` (list): current trips housed in the Direction object
``c`` (sqlite3.Cursor): the sqlite3 connection's cursor to execute SQL queries
"""
row = {"Time": format(dt.now()),
"Count": len(curr_trips),
"Direction": self.direction_name,
"Route": self.route_name}
sql = ("INSERT INTO %s (datetime, count, direction, route) \
VALUES (?, ?, ?, ?)" % TABLE_TWO)
cursor.execute(sql,
[str(row['Time']), str(row['Count']),
str(row['Direction']), str(row['Route'])] )
import ast
from settings.settings import TABLE_ONE, TABLE_TWO
from trips_db import cursor as c
pd.set_option('display.width', 110)
pd.set_option('display.max_rows', 500)
pd.set_option('display.float_format', lambda x: '%.3f' % x)
# TODO: standardize durations.. eg. 1.5x average at this time
# TODO: for trips that are significantly less than their route length, remove them
if __name__ == '__main__':
# View what is in table one (ie. completed trips)
query = c.execute("SELECT * FROM %s" % TABLE_ONE).fetchall()
df = pd.DataFrame(data=query, columns=['ID', 'Trip_ID', 'Vehicle_ID', 'Direction', 'Route', 'Start location', 'End location', 'Start time', 'End time', 'Duration']).drop('ID', axis=1)
# Add a distance column
df['Start location'] = df['Start location'].apply(ast.literal_eval)
df['End location'] = df['End location'].apply(ast.literal_eval)
df['Distance (mi)'] = df.apply(lambda x: vincenty(x['Start location'], x['End location']).miles, axis=1)
# Truncate microseconds
for col in ['Start time', 'End time', 'Duration']:
df[col] = df[col].apply(lambda x: x.split('.')[0])
###########
### EDA ###
###########
