def add_companies_to_portfolio(portfolio, list_company, db=None): """ Add list of company to portfolio :param portfolio: id or name of portfolio :type portfolio: int|string :param list_company: list contains symbol of company :type list_company: list[str] :param db: if we have already connexion in other function who cal this function :type db: DbConnection :return: nb added company to portfolio :rtype: int """ if not db or not isinstance(db, DbConnection): db = DbConnection(HOST, USER, PASSWORD, DATABASE) # check if id or name of portfolio, if name => get id if isinstance(portfolio, str): if not portfolio.isdigit(): id_portfolio = get_id_portfolio(portfolio) portfolio = id_portfolio[0].get('id_portfolio')[0] # check if portfolio number is valid if not portfolio: raise ValueError( "Need to portfolio valid to add company to portfolio. portfolio = %s" % portfolio) # add each company to portfolio query = """INSERT INTO company_portfolio (id_portfolio, symbol_company, is_activate_in_portfolio) VALUES (%(id_portfolio)s, %(symbol_company)s, 1) ON DUPLICATE KEY UPDATE is_activate_in_portfolio = 1""" nb_company_add = 0 for company in list_company: params = {'id_portfolio': portfolio, 'symbol_company': company} nb_company_add += db.modified_db(query, params) return nb_company_add
def insert_transaction_to_db(id_portfolio, symbol_company, quantity, value_current, transaction_date, id_simulation=None, db=None): """ Insert transaction link to portfolio :param id_portfolio: id of portfolio :type id_portfolio: int :param symbol_company: symbol of a company :type symbol_company: str :param quantity: :type quantity: int :param value_current: :type value_current: float :param transaction_date: :type transaction_date: datetime :param id_simulation: if transaction is during simulation :type id_simulation: int :param db: if we have already connexion in other function who cal this function :type db: DbConnection :return: number row affected :rtype: int """ if not id_portfolio: raise ValueError( "Need to id_portfolio valid to create transaction. id_portfolio = %s" % id_portfolio) if not symbol_company: raise ValueError( "Need to symbol_company valid to create transaction. symbol_company = %s" % symbol_company) if not db or type(db) is not DbConnection: db = DbConnection(HOST, USER, PASSWORD, DATABASE) query = """INSERT INTO transaction (id_portfolio, id_symbol, quantity, value_current, transaction_date, id_simulation) VALUES (%(id_portfolio)s, %(symbol_company)s, %(quantity)s, %(value_current)s, %(transaction_date)s, %(id_simulation)s) ON DUPLICATE KEY UPDATE id_symbol = %(symbol_company)s, quantity = %(quantity)s, value_current = %(value_current)s, transaction_date = %(transaction_date)s, id_simulation = %(id_simulation)s)""" params = { 'id_portfolio': id_portfolio, 'symbol_company': symbol_company, 'quantity': quantity, 'value_current': value_current, 'transaction_date': transaction_date, 'id_simulation': id_simulation } return db.modified_db(query, params)
def create_portfolio(name=None, db=None): """ Create new portfolio :param name: name of portfolio :type name: str :param db: if we have already connexion in other function who cal this function :type db: DbConnection :return: id of portfolio :rtype int """ if not db or type(db) is not DbConnection: db = DbConnection(HOST, USER, PASSWORD, DATABASE) if not name: query = """SELECT (CASE COUNT(id_portfolio) WHEN 0 THEN 1 ELSE COUNT(id_portfolio)+1 END) AS new_id FROM portfolio""" res = db.select_in_db(query) name = 'new_portfolio' + str(res[0][0]) query = """INSERT INTO portfolio (name) VALUES (%(name)s) ON DUPLICATE KEY UPDATE name = name""" db.modified_db(query, {'name': name}) # get id portfolio return get_id_portfolio(name, db)
def delete_companies_to_portfolio(portfolio, list_company, db=None): """ Delete list of company to portfolio :param portfolio: id or name of portfolio :type portfolio: int|string :param list_company: list contains symbol of company :type list_company: list[str] :param db: if we have already connexion in other function who cal this function :type db: DbConnection :return: nb added company to portfolio :rtype: int """ if not db or not isinstance(db, DbConnection): db = DbConnection(HOST, USER, PASSWORD, DATABASE) # check if id or name of portfolio, if name => get id if isinstance(portfolio, str): if not portfolio.isdigit(): id_portfolio = get_id_portfolio(portfolio) portfolio = id_portfolio[0].get('id_portfolio')[0] # check if portfolio number is valid if not portfolio: raise ValueError( "Need to portfolio valid to add company to portfolio. portfolio = %s" % portfolio) # delete each company to portfolio query = """UPDATE company_portfolio SET is_activate_in_portfolio = 0 WHERE id_portfolio = %(id_portfolio)s AND symbol_company IN (%(symbol_company)s)""" # put string of %s, ex: list_company = ['b', 'a', 'g', 'l'] => param_in_clause = %s, %s, %s, %s param_in_clause = ', '.join('%s' for i in list_company) query = query % { 'id_portfolio': portfolio, 'symbol_company': param_in_clause } return db.modified_db(query, tuple(list_company))