def load_aos():
'''
Puts all the Area Offices into a sql table
'''
try:
sql.has_table('AOS', con=cnx)
print "Area office data already loaded."
except ValueError:
sql.to_sql(aos, name = 'AOS', con=cnx)
print "Area office data successfully loaded."
def load_emp():
'''
Puts all of the employment data into a sql table
'''
try:
sql.has_table('allemp', con=cnx)
print "Employment data already loaded."
except ValueError:
sql.to_sql(view_emp, name = 'allemp', con=cnx)
print "Employment data successfully loaded."
def load_rates():
'''
Puts all the DAWF rates into a sql table
'''
try:
sql.has_table('DAWF', con=cnx)
print "Injury rate data already loaded."
except ValueError:
sql.to_sql(DAWF_rate, name = 'DAWF', con=cnx)
print "Injury rate data successfully loaded."
def test_legacy_write_frame(self):
"""Test legacy write frame name.
Assume that functionality is already tested above so just do quick check that it basically works"""
sql.write_frame(
self.test_frame1, 'test_frame_legacy', self.conn, flavor='sqlite')
self.assertTrue(
sql.has_table('test_frame_legacy', self.conn, flavor='sqlite'), 'Table not written to DB')
def test_to_sql_fail(self):
sql.to_sql(self.test_frame1, 'test_frame2',
self.conn, flavor='sqlite', if_exists='fail')
self.assertTrue(
sql.has_table('test_frame2', self.conn, flavor='sqlite'), 'Table not written to DB')
self.assertRaises(ValueError, sql.to_sql, self.test_frame1,
'test_frame2', self.conn, flavor='sqlite', if_exists='fail')
def get_profit_data_quater():
temp_year = start
while(temp_year != end):
for temp_quater in [1,2,3,4]:
item = str(temp_year) + "-" + str(temp_quater)
if not sql.has_table(item, engine, flavor="mysql"):
print item + "begins"
data_total_raw[item] = ts.get_profit_data(temp_year, temp_quater)
print "\n"
if any(data_total_raw[item]): data_total_raw[item].to_sql(item, engine)
temp_year += 1
def get_profit_data_quater():
temp_year = start
while (temp_year != end):
for temp_quater in [1, 2, 3, 4]:
item = str(temp_year) + "-" + str(temp_quater)
if not sql.has_table(item, engine, flavor="mysql"):
print item + "begins"
data_total_raw[item] = ts.get_profit_data(
temp_year, temp_quater)
print "\n"
if any(data_total_raw[item]):
data_total_raw[item].to_sql(item, engine)
temp_year += 1
def test_to_sql_replace(self):
sql.to_sql(self.test_frame1, 'test_frame3',
self.conn, flavor='sqlite', if_exists='fail')
# Add to table again
sql.to_sql(self.test_frame1, 'test_frame3',
self.conn, flavor='sqlite', if_exists='replace')
self.assertTrue(
sql.has_table('test_frame3', self.conn, flavor='sqlite'), 'Table not written to DB')
num_entries = len(self.test_frame1)
num_rows = self._count_rows('test_frame3')
self.assertEqual(
num_rows, num_entries, "not the same number of rows as entries")
def importData():
#Start Time
start = datetime(2010,1,1)
end = datetime.date(datetime.now())
data = DataReader(sp500constituents[0], "yahoo", start, end)
en = enumerate(sp500constituents)
[i for i, x in en if x=='WFMI']
sp500constituents[200:len(sp500constituents)]
problems = []
dataImportProblems = []
for series in sp500constituents[485:len(sp500constituents)]:
print series
try:
data = DataReader(series, "yahoo", start, end)
data = data.reset_index()
except:
print "Can't read {}".format(series)
dataImportProblems.append(series)
continue
con = sqlite3.connect("/home/phcostello/Documents/Data/FinanceData.sqlite")
try:
psql.write_frame( data, series, con)
con.commit()
except:
print "Problems with {}".format(series)
problems.append(series)
finally:
con.close()
#changing tables to have date formats so RODBC driver recognizes
#Should check that this is occuring above.
con = sqlite3.connect("/home/phcostello/Documents/Data/FinanceData.sqlite")
for tb in sp500constituents:
if psql.has_table(tb, con):
sqltxt = "SELECT * FROM {}".format(tb)
#print sqltxt
data = psql.read_frame(sqltxt, con)
sqlDropTxt = 'DROP TABLE "main"."{}"'.format(tb)
#print sqlDropTxt
psql.execute(sqlDropTxt, con)
con.commit()
psql.write_frame( data, tb, con)
con.commit()
con.close()
def test_to_sql(self):
sql.to_sql(self.test_frame1, 'test_frame1', self.conn, flavor='sqlite')
self.assertTrue(
sql.has_table('test_frame1', self.conn, flavor='sqlite'),
'Table not written to DB')
def test_to_sql(self):
sql.to_sql(self.test_frame1, 'test_frame1', self.conn, flavor='sqlite')
self.assertTrue(
sql.has_table('test_frame1', self.conn, flavor='sqlite'), 'Table not written to DB')
