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
def get_data_from_excel():
reader = ExcelReader(excel_file)
rows = reader.get_data_from_sheet(sheet_name_or_idx)
return rows, reader.create_qry
