def getCompanyCodes(): industryIDs = getIndustryIDs() companiesSQLQuery = """SELECT c.code || co.suffix as ycode from company c join country co on c.country_id = co.id where c.industry_id = %s; """ # list of lists, each interior list comtains company codes for that id companyByID = [] for row in industryIDs: industryID = row['id'] idList = [industryID] companies = db.selectQuery(companiesSQLQuery, idList) companyList = [] for company in companies: companyList.append(company['ycode']) companyByID.append(companyList) log.info("industry %s has %s companies" % (industryID, len(companyList))) return companyByID
def populateHistoric(): companiesSQLQuery = """SELECT c.code || co.suffix as ycode from company c join country co on c.country_id = co.id; """ companies = db.selectQuery(companiesSQLQuery, []) #print companies companyList = [] for company in companies: companyList.append(company[0]) for ycode in companyList: historicData.getHistoricInfo(ycode)
def populateCompanies(file): log.info("populating COMPANY table, getting data from %s" % file) with open(file, "r") as f: reader = csv.reader(f) companies = [ x for x in reader ] for company in companies: companyName = company[0] companyCode = company[1] # get code for query industry = company[2] industryAsList = [industry] sqlQuery = "SELECT ID FROM INDUSTRY WHERE NAME = %s;" industryID = db.selectQuery(sqlQuery, industryAsList) industryID = industryID[0]['id'] insertSQL = "INSERT INTO COMPANY (CODE, NAME, INDUSTRY_ID) VALUES (%s, %s, %s);" try: log.debug("trying to populate company with value %s" % companyName) db.insertSingle(insertSQL, [companyCode, companyName, industryID]) except: log.error("unable to insert %s into company" % companyName)