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()
Esempio n. 2
0
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()