def getData(county, month):
# get the path to the crashes database
# (pwd_path enables python to find files within this project)
pwd_path = os.path.abspath(os.path.dirname(__file__))
db_path = os.path.join(pwd_path, '../db/crashes.db')
# define a connection variable
conn = None
crash_list = None
try:
# connect to sqllite
conn = dbutil.connect(db_path)
# get all crashes from sqlite
crash_list = dbutil.get_crashes(conn, county, month)
except Error as e:
# if there's an error, show it
print(e)
finally:
# if a connection is defined, close it
if (conn):
conn.close()
# redirect to crashes to show the result
return crash_list
def load_property_in_entity(self):
try:
con = dbutil.connect()
cur = con.cursor(mdb.cursors.DictCursor)
cur.execute("select * from property_in_entity")
self.property_in_entity_list = list(cur.fetchall())
# print self.property_in_entity_list
for etype in self.entity_types_list:
# self.property_in_entity_key_eid[property_in_entity.get('entity_type_id')] = property_in_entity
prop_id_list = list()
prop_list = list()
for tpe in self.property_in_entity_list:
if etype.get("id") == tpe.get("entity_type_id"):
prop_id_list.append(tpe.get("property_id"))
cur.execute("select * from property where id=%s", tpe.get("property_id"))
prop = cur.fetchone()
prop_list.append(prop)
self.etype_props_dict[etype.get("id")] = prop_list
self.property_in_entity_key_eid[etype.get("id")] = prop_id_list
# print self.property_in_entity_key_eid
# print self.etype_props_dict
except:
raise
finally:
if con:
con.close()
def get_entities_by_type(self, entity_type_url):
try:
con = dbutil.connect()
cur = con.cursor(mdb.cursors.DictCursor)
# get the entity type
entity_type = self.entity_types_dict_key_url.get(entity_type_url)
# print entity_type
# get entity data
cur.execute("select * from entity_data where entity_type_id=%s", entity_type["id"])
entity_data = cur.fetchall()
entities = list()
# print '############################################'
for entity_instance in entity_data:
entity = dict(entity_instance)
entity["entity_type_url"] = entity_type.get("url")
# print entity
for entity_prop in self.etype_props_dict.get(entity_type.get("id")):
prop_data_table_name = "prop_" + entity_prop["label"] + "_data"
# print prop_data_table_name
prop_data_query = "select * from %s where entity_data_id=%s" % (
prop_data_table_name,
entity_instance.get("id"),
)
# print prop_data_query
cur.execute(prop_data_query)
entity_prop_value = cur.fetchone()
entity[entity_prop["label"]] = entity_prop_value.get("value")
entities.append(entity)
except mdb.Error, e:
raise
def main():
file_names = ['traffic_2019_NJ.csv']
# get absolute path present working directory
# (this is needed to find other files in this project, like CSV and DB)
pwd_path = os.path.abspath(os.path.dirname(__file__))
for file_name in file_names:
# get path to the source CSV file and read to dataframe
crash_path = os.path.join(pwd_path, f'../csvfiles/{file_name}')
crashes = pd.read_csv(crash_path)
# clean up column names to make downstream API and JS easier to work with
crashes.columns = ['source', 'severity', 'start_time', 'end_time',
'start_lat', 'start_lng', 'city', 'county', 'state',
'temperature_f', 'wind_chill_f', 'humidity', 'pressure_in',
'visibility_mi', 'wind_speed_mph', 'precipitation_in',
'weather_condition', 'civil_twilight', 'year', 'month']
# get path location for the sqlite DB file
db_path = os.path.join(pwd_path, '../db/crashes.db')
#initialize a connection object for SQL
conn = None
try:
# connect to sqlite db, creating it if necessary
conn = dbutil.connect(db_path)
# write the dataframe to the sqlite file, replacing any existing records
# (this setup will help scale the source dataset size as we get ready to "go live")
crashes.to_sql(dbutil.get_crash_table(), con=conn, if_exists='replace')
except Error as e:
# print any sqlite errors that occur
print(e)
finally:
# if we have an open connection object, close it
if (conn != None):
conn.close()
# report how many crashes are added
print(f'{crashes.count()} crashes added.')
def load_entity_types(self):
try:
con = dbutil.connect()
cur = con.cursor(mdb.cursors.DictCursor)
# get the entity type
cur.execute("select * from entity_type")
self.entity_types_list = list(cur.fetchall())
# print self.entity_types_list
for entity_type in self.entity_types_list:
self.entity_types_dict_key_id[entity_type.get("id")] = entity_type
self.entity_types_dict_key_url[entity_type.get("url")] = entity_type
# print self.entity_types_dict_url
# print self.entity_types_dict_id
except:
raise
finally:
if con:
con.close()
#!/usr/bin/env python
#
# This script updates the contents of the comments_element table.
# It's fugly, but a lot less painful than trying to use Django's
# fixtures system.
import os, sys
sys.path.append(os.path.dirname(__file__))
import dbutil
os.system("make -C ../../en ids")
conn = dbutil.connect()
c = conn.cursor()
c.execute(
'''load data local infile "../../en/all-ids.dat" replace
into table comments_element
fields terminated by "|"'''
)
print "Database updated"
def updateStation(station):
(conn, cur) = dbutil.connect()
cur.execute(
''' UPDATE station set updated = %s where station = %s ''', (datetime.now(), station))
conn.commit()
dbutil.disconnect(conn)
def updateStationState(station, state):
(conn, cur) = dbutil.connect()
cur.execute(
''' UPDATE station set state = %s where station = %s ''', (state, station))
conn.commit()
dbutil.disconnect(conn)
def dbInsert(d, table):
(conn, cur) = dbutil.connect()
stmt = convertDictToInsert(d, table)
cur.execute(stmt, d.values())
conn.commit()
dbutil.disconnect(conn)
url = '''http://data.fcc.gov/mediabureau/v01/tv/facility/search/%s.json''' % (
state)
req = urllib2.Request(url=url)
f = urllib2.urlopen(req)
page = json.loads(f.read())
facilities = [sl['facilityList'] if sl['searchType'] ==
'State' else None for sl in page['results']['searchList']]
facilities = [fac for fac in facilities if fac is not None][0]
for facility in facilities:
stations.append(facility['callSign'])
else:
(conn, cur) = dbutil.connect()
cur.execute(
''' SELECT station FROM station where updated < '2015-01-03' ''')
station_rows = cur.fetchall()
shuffle(station_rows)
stations = [item for sublist in station_rows for item in sublist]
for station in stations:
print station
url = '''https://stations.fcc.gov/station-profile/%s/rss/''' % (station)
req = urllib2.Request(url=url)
db = open('logs/db.txt', 'a')
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import os, sys
sys.path.append(os.path.dirname(__file__))
import dbutil
conn = dbutil.connect()
c = conn.cursor()
c.execute('''select submitter_name from comments_comment''')
reviewers = {}
mappings = {
u'alejandro "tab-lover" dubrovsky': u'Alejandro Dubrovsky',
u'alex hirzel <*****@*****.**>': u'Alex Hirzel',
u'anonymous coward': u'Anonymous',
u'arthur van leeuwen': u'Arthur van Leeuwen',
u'augustss': u'Lennart Augustsson',
u'ed t': u'Anonymous',
u'geogre moschovitis': u'George Moschovitis',
u'george m': u'George Moschovitis',
u'haskell newb': u'Anonymous',
u'j. pablo fernandez': u'J. Pablo Fernández',
u'kamal al-marhoobi': u'Kamal Al-Marhubi',
u'kevin w.': u'Kevin Watters',
u'max cantor (#haskell - mxc)': u'Max Cantor',
u'michael campbell': u'Michael Campbell',
u'mike btauwerman': u'Mike Brauwerman',
u'no credit necessary': u'Anonymous',
def updateStation(station):
(conn, cur) = dbutil.connect()
cur.execute(''' UPDATE station set updated = %s where station = %s ''',
(datetime.now(), station))
conn.commit()
dbutil.disconnect(conn)
def updateStationState(station, state):
(conn, cur) = dbutil.connect()
cur.execute(''' UPDATE station set state = %s where station = %s ''',
(state, station))
conn.commit()
dbutil.disconnect(conn)
def dbInsert(d, table):
(conn, cur) = dbutil.connect()
stmt = convertDictToInsert(d, table)
cur.execute(stmt, d.values())
conn.commit()
dbutil.disconnect(conn)
req = urllib2.Request(url=url)
f = urllib2.urlopen(req)
page = json.loads(f.read())
facilities = [
sl['facilityList'] if sl['searchType'] == 'State' else None
for sl in page['results']['searchList']
]
facilities = [fac for fac in facilities if fac is not None][0]
for facility in facilities:
stations.append(facility['callSign'])
else:
(conn, cur) = dbutil.connect()
cur.execute(
''' SELECT station FROM station where updated < '2015-01-03' ''')
station_rows = cur.fetchall()
shuffle(station_rows)
stations = [item for sublist in station_rows for item in sublist]
for station in stations:
print station
url = '''https://stations.fcc.gov/station-profile/%s/rss/''' % (station)
req = urllib2.Request(url=url)
db = open('logs/db.txt', 'a')
log = open('logs/station_dloader.log', 'a')