def go():
SH = Sqlite_Helper(pathToTable,'predictit')
SH.set_db()
SH.cursor.execute("SELECT datetime('now','localtime')")
DATE = SH.cursor.fetchall()[0][0]
print DATE
keys = ['CONTEST','QUESTION','BUY_YES', 'BUY_NO', 'SELL_YES','SELL_NO']
for tickersymbol in national + superTuesday + ['SCPRMRY16.DEM']:
try:
results = getContracts(tickersymbol)
rows = []
for result in results:
row = []
for key in keys:
row.append(cleanUp(result[key]))
row.append(DATE)
rows.append(row)
print row
del row
if rows:
SH.insert_rows(len(rows[0]),rows)
del results,rows
except Exception as e:
import traceback
print 'exception',tickersymbol,e,traceback.print_exc()
continue
SH.connection.close()
def get_station_vector(self,query,FCC_db,FCC_db_name):
SH = Sqlite_Helper(FCC_db,FCC_db_name)
conn,cursor = SH.set_db()
stations = {}
for callsign in query:
cursor.execute("SELECT * FROM {tn} WHERE callsign = '{callsign}'".\
format(tn = FCC_db_name,callsign = callsign))
results = cursor.fetchall()
if results:
results = results[0]
type = results['type'].encode('ASCII')
trans_lat = results['trans_lat']
trans_lon = results['trans_lon']
if type == 'FM':
reach_lats,reach_lons = Sqlite_Helper.get_reach_vector(self,results)
average_reach = Sqlite_Helper.get_average_reach(self,trans_lat,trans_lon,reach_lats,reach_lons)
else:
average_reach = 50
stations[results['callsign'].encode('ASCII')] = {'trans_lat':trans_lat,'trans_lon':trans_lon,'average reach':average_reach}
conn.close()
return stations
def __init__(self,query, FCC_db,FCC_db_name, db, db_name = "",vars = []):
super(Population_Query,self).__init__(db,db_name)
self.station_vector = self.get_station_vector(query,FCC_db,FCC_db_name)
self.conn,self.cursor = Sqlite_Helper.set_db(self)
self.search_hits = {}
self.population_dict = {}
self.db_name = db_name
self.vars = Sqlite_Helper.demographic_variables(self)
self.area_error = []
self.radius_error = []
self.census_tract_area = []
self.correct_area = []
def __init__(self,db, db_name, coordinates = (0.0,0.0), vars = []):
super(Population_Query,self).__init__(db,db_name)
self.conn,self.cursor = Sqlite_Helper.set_db(self)
assert(isinstance(coordinates,tuple))
self.coordinates = coordinates
self.search_hits = {}
self.population_dict = {}
self.db_name = db_name
self.vars = Sqlite_Helper.demographic_variables(self)
self.area_error = []
self.radius_error = []
self.census_tract_area = []
self.correct_area = []
def set_population_dict(self,demographics = []): if not self.search_hits: return [] if not demographics: demographics = Sqlite_Helper.demographic_variables(self) for demographic in demographics: total = 0 # iterate through tract tuples, add total population for tract in self.search_hits.itervalues(): geo_id = tract[0] population = tract[demographic] try: total += population except TypeError as e: try: population = float(population) except ValueError as v: raise v total += population self.population_dict[demographic] = str(total) return self.population_dict
def print_search_hits(self,csv,all_vars = False,sep = '|'):
dir = os.path.dirname(csv)
query_basename = 'query_' + os.path.basename(csv)
query_path = os.path.join(dir,query_basename)
q = open(query_path,'w')
if all_vars:
print_vars = Sqlite_Helper.all_variables(self)
else:
print_vars = self.vars
csv = open(csv,'w')
csv.write('tract' + sep + sep.join(print_vars) + '\n')
q.write('tract' + sep + sep.join(print_vars) + '\n')
query = '|'.join(['query','|'.join(['-']*4),str(self.coordinates[0]),
str(self.coordinates[1]),'|'.join(['0.0']*(len(print_vars)-7))]) + '\n'
csv.write(query)
q.write(query)
q.close()
for tract in self.search_hits:
row = self.search_hits[tract]
line = [tract]
for datum in self.search_hits[tract]:
line.append(str(datum))
csv.write(sep.join(line) + '\n')
def __init__(self,query,db,table_name,id_variable): super(Filter,self).__init__(db,table_name) self.query = query assert isinstance(self.query,list) self.conn,self.cursor = Sqlite_Helper.set_db(self) self.table_name = table_name self.vars = self.get_vars() self.id_variable = id_variable
def __init__(self,RC_db, RC_table_name,secondary_db = "",secondary_table_name = ""):
super(Query,self).__init__(RC_db,RC_table_name)
self.conn, self.cursor = Sqlite_Helper.set_db(self)
self.query_N = self.cursor.execute("SELECT count(*) FROM {tn}".format(tn = RC_table_name))
self.results_path = ""
self.description_path = ""
self.secondary_db = secondary_db
self.secondary_table_name = secondary_table_name
if os.path.exists(self.secondary_db):
self.allow_secondary_compare = True
else:
self.allow_secondary_compare = False
self.secondary_cursor = None
self.secondary_conn = None
def createSqliteTable(): HEADER = "CONTEST TEXT,QUESTION TEXT,BUY_YES INTEGER,BUY_NO INTEGER,SELL_YES INTEGER, SELL_NO INTEGER,DATE TEXT" SH = Sqlite_Helper(pathToTable,'predictit') SH.create(HEADER)