def load_excel_to_sql(): """ The database is configured in config.json in the tempfiles directory """ # drop_create_tables() excelSheet = r'd:\$temp\Onion Lake SK wells.xlsx' # excelSheet = config.get_file_dir() + 'database new.xlsx' loader = Loader() loader.connect() loader.open_excel(excelSheet) loader.load_all_sheets() loader.close()
def test_run(self): #Testing loading an Excel spreadsheet into an sqlite3 database. # print ("Creating temporary database %s" % self.TEST_DATABASE) self.assertEqual(config.get_environment(),'unittest') # Distructive Tests must run in unittest enviornment dbu = DatabaseUtilities() dbc = DatabaseCreate() loader = Loader() dbu.delete_all_tables() dbc.create_all() dbi = config.get_database_instance() loader.connect() loader.open_excel(self.TEST_SPREADSHEET) shower = Shower() shower.connect() #Test that the worksheet has x number of tabs self.assertEqual(len(loader.wb.get_sheet_names()), 2) #Test that each tab has x number of columns self.assertEqual(len(loader.wb['Well'].columns), 12) self.assertEqual(len(loader.wb['Royalty Master'].columns), 11) #Test that each tab has x number of rows self.assertEqual(len(loader.wb['Well'].rows), 9) self.assertEqual(len(loader.wb['Royalty Master'].rows), 11) # print(dbi.get_table_names()) # self.assertEqual(len(dbi.get_table_names()), 0) # # #Test that we have x number of tables loader.load_all_sheets() # self.assertEqual(len(dbi.get_table_names()), 2) #test that each table has x number of columns self.assertEqual(len(shower.show_table('Well')), 8) self.assertEqual(len(shower.show_table('RoyaltyMaster')), 10) #test that each table has x number of row self.assertEqual(len(shower.show_columns('Well')), 12) self.assertEqual(len(shower.show_columns('RoyaltyMaster')), 11) #test column type self.assertEqual(shower.column_type('Well', 'ID'), 'integer') loader.commit()