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))
