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