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')