def process(db, excel_file, dry_run): # Import excel file into temp table reader = ExcelReader(excel_file) rows = reader.get_data_from_sheet(0) tt_name = TempTable.create_from_data(db, rows, reader.create_qry) # Extract records in [ExternalData].dbo.tblGrowthSeries that matches InvestmentID in temp table imported above count = db.execute(import_growth_series_query(tt_name)) assert len(tt_name) == count # back up tblInvestmentGrowthSeries today = datetime.date.today() today = today.strftime('%Y%m%d') if not db.sp_columns('tblInvestmentGrowthSeries_{date}'.format(date=today)): # if table does not exist logger.info('Creating tblInvestmentGrowthSeries_{date}'.format(date=today)) db.execute('select * into tblInvestmentGrowthSeries_{date} from tblInvestmentGrowthSeries'.format(date=today)) # get investment id from [ExternalData].dbo.tblGrowthSeries investment_id = db.get_one_value('select top 1 code from [ExternalData].dbo.ImportGrowthSeries') logger.info(investment_id) logger.info('Before updating') logger.info(db.get_data('select top 1 * from tblInvestmentGrowthSeries where investmentid=? order by [date] desc' , investment_id)) count = db.execute(merge_to_tblInvestmentGrowthSeries_query()) logger.info("{} records updated in tblInvestmentGrowthSeries".format(count)) logger.info('After updating') logger.info(db.get_data('select top 1 * from tblInvestmentGrowthSeries where investmentid=? order by [date] desc' , investment_id)) if not dry_run: logger.info('Commit changes') db.commit() else: logger.info('All changes did not commit')
def get_data_from_excel(): reader = ExcelReader(excel_file) rows = reader.get_data_from_sheet(sheet_name_or_idx) return rows, reader.create_qry