def get_all(cls) -> list:
"""
Get all stocks from database
"""
stocks = []
with pool_manager() as conn:
query = f"SELECT id, name, company_name, country, industry, sector, in_use FROM {cls._table};"
try:
conn.cursor.execute(query)
stocks = conn.cursor.fetchall()
except (psycopg2.DataError, psycopg2.ProgrammingError, TypeError):
message = "Could not get stocks"
logger.warning(message)
stocks = [
Stock(pk=pk,
name=name,
company_name=company_name,
country=country,
industry=industry,
sector=sector,
in_use=in_use) for pk, name, company_name, country,
industry, sector, in_use in stocks
]
return stocks
def get_data_for_last_day(cls, pk: int) -> list:
stock_data_for_last_day = []
datetime_now = datetime.now().strftime(DATETIME_PATTERN)
datetime_yesterday = (datetime.now() -
timedelta(days=1)).strftime(DATETIME_PATTERN)
with pool_manager() as conn:
query = """SELECT * FROM stocks_data
WHERE stock_id = %(stock_id)s
AND %(yesterday)s <= created_at AND created_at < %(today)s
ORDER BY created_at;"""
try:
conn.cursor.execute(
query, {
'stock_id': pk,
'yesterday': datetime_yesterday,
'today': datetime_now
})
stock_data_for_last_day = conn.cursor.fetchall()
except (psycopg2.DataError, psycopg2.ProgrammingError,
TypeError) as err:
logger.info(f"Error! {err}")
stock_data_for_last_day = [
StockData(pk=pk,
stock_id=stock_id,
price=price,
created_at=created_at)
for pk, stock_id, price, created_at in stock_data_for_last_day
]
return stock_data_for_last_day
def create(cls, stocks: list) -> object:
"""Creates a new record in Dashboard table
:param stocks: list of stock ids for specific dashboard
:return: instance of Dashboard
"""
stock_names = [stock.name for stock in stocks]
stock_names.sort()
stock_names = " ".join(stock_names)
dashboard_hash = generate_dashboard_hash(stock_names=stock_names)
# Check if dashboard already exists returning it
existing_dashboard = Dashboard.get_by_hash(dashboard_hash)
if existing_dashboard:
return existing_dashboard
with pool_manager() as conn:
dashboard_has_stocks_table = 'public.dashboard_has_stocks'
try:
insert_dashboard_query = f"""INSERT INTO {cls._table} (dashboard_hash)
VALUES (%(dashboard_hash)s)
RETURNING dashboard_hash;"""
conn.cursor.execute(insert_dashboard_query, {'dashboard_hash': dashboard_hash})
dashboard_hash = conn.cursor.fetchone()
dashboard_has_stocks_insert_data = [(stock.pk, dashboard_hash) for stock in stocks]
insert_dashboard_has_stocks_query = (f"INSERT INTO {dashboard_has_stocks_table} "
"(stock_id, dashboard_hash)"
" VALUES (%s, %s);")
conn.cursor.executemany(insert_dashboard_has_stocks_query, dashboard_has_stocks_insert_data)
return Dashboard(dashboard_hash=dashboard_hash)
except (psycopg2.ProgrammingError, psycopg2.DatabaseError) as error:
logger.info(f"Error Dashboard model create {error}")
def update(self, price=None, created_at=None):
"""
Changes values of the stock data to the given.
:param price: StockData price
:param created_at: StockData creation date, time
:return: updated StockData instance
"""
data_to_update = []
if price is not None:
data_to_update.append("price = %(price)s")
if created_at is not None:
data_to_update.append("created_at = %(created_at)s")
created_at = created_at.strftime("%Y-%m-%d %H:%M:%S")
query = f"""UPDATE {self._table} SET {', '.join(data_to_update)}
WHERE id = %(id)s
RETURNING id, stock_id, price, created_at;"""
with pool_manager() as conn:
try:
conn.cursor.execute(query, {
'price': price,
'created_at': created_at,
'id': self.pk
})
contents = conn.cursor.fetchone()
price = contents[2]
created_at = contents[3]
self.price = price
self.created_at = created_at
return True
except (DataError, ProgrammingError):
return False
def create(cls, stock_id: int, price: float, created_at: datetime):
"""
Create new stock data
:param stock_id: Stock id
:param price: StockData price
:param created_at: StockData creation date, time
:return: StockData instance
"""
with pool_manager() as conn:
query = f"""INSERT INTO {cls._table} (stock_id, price, created_at)
VALUES (%(stock_id)s, %(price)s, %(created_at)s)
RETURNING id, stock_id, price, created_at;"""
try:
conn.cursor.execute(
query, {
'stock_id': stock_id,
'price': price,
'created_at': created_at.strftime("%Y-%m-%d %H:%M:%S")
})
pk, stock_id, price, created_at = conn.cursor.fetchone()
return StockData(pk=pk,
stock_id=stock_id,
price=price,
created_at=created_at)
except (DataError, ProgrammingError):
return None
def update(self,
name: str = None,
company_name: str = None,
country: str = None,
industry: str = None,
sector: str = None,
in_use: bool = False) -> bool:
"""
Update an existing instance in database
:param name:
:param company_name:
:param country:
:param industry:
:param sector:
:param in_use:
:return: True if update was successful and False if not
"""
data_to_update = []
if name is not None:
data_to_update.append("name = %(name)s")
if company_name is not None:
data_to_update.append("company_name = %(company_name)s")
if country is not None:
data_to_update.append("country = %(country)s")
if industry is not None:
data_to_update.append("industry = %(industry)s")
if sector is not None:
data_to_update.append("sector = %(sector)s")
if in_use is not None:
data_to_update.append("in_use = %(in_use)s")
query = f"""UPDATE {self._table} SET {', '.join(data_to_update)}
WHERE id = %(pk)s RETURNING id, name, company_name, country, industry, sector, in_use; """
with pool_manager() as conn:
try:
conn.cursor.execute(
query, {
'name': name,
'company_name': company_name,
'country': country,
'industry': industry,
'sector': sector,
'pk': self.pk,
'in_use': in_use
})
pk, name, company_name, country, industry, sector, in_use = conn.cursor.fetchone(
) # pylint: disable=C0103, W0612
self.name = name
self.company_name = company_name
self.country = country
self.industry = industry
self.sector = sector
self.in_use = in_use
return True
except (psycopg2.DataError, psycopg2.ProgrammingError):
return False
def delete_by_hash(cls, dashboard_hash: str) -> bool:
"""
Deletes a Dashboard instance by its dashboard_hash.
"""
with pool_manager() as conn:
query = f"DELETE FROM {cls._table} WHERE dashboard_hash = %(dashboard_hash)s;"
try:
conn.cursor.execute(query, {'dashboard_hash': dashboard_hash})
return True
except(psycopg2.DataError, psycopg2.ProgrammingError) as error:
logger.info(f"Error Dashboard model delete_by_hash {error}")
def delete_by_id(cls, pk: int):
"""
Delete stock data with given pk
:param pk: StockData id
:return: True if instance was deleted, else False
"""
if not StockData.get_by_id(pk):
return False
with pool_manager() as conn:
query = f"DELETE FROM {cls._table} WHERE id = %(id)s "
try:
conn.cursor.execute(query, {'table': cls._table, 'id': pk})
return True
except (DataError, ProgrammingError):
return False
def get_by_hash(cls, dashboard_hash: str):
"""Getting a dashboard by its hash from Dashboard table
:return: instance of DashboardConfig model
"""
with pool_manager() as conn:
get_dashboard_id_query = f"""SELECT dashboard_hash FROM {cls._table}
WHERE dashboard_hash = %(dashboard_hash)s;"""
try:
conn.cursor.execute(get_dashboard_id_query, {'dashboard_hash': dashboard_hash})
dashboard_hash = conn.cursor.fetchone()[0]
if dashboard_hash:
return Dashboard(dashboard_hash=dashboard_hash)
except (psycopg2.ProgrammingError, psycopg2.DatabaseError, TypeError) as error:
logger.info(f"Error Dashboard model get_by_hash {error}")
def delete_by_id(cls, pk: int) -> bool: # pylint: disable=C0103
"""
Delete instance by id in database
:param pk: instance id in database
:return: True if delete was successful and False if not
"""
if not Stock.get_by_id(pk):
return False
with pool_manager() as conn:
query = f"DELETE FROM {cls._table} WHERE id = %(id)s;"
try:
conn.cursor.execute(query, {'id': pk})
return True
except (psycopg2.DataError, psycopg2.ProgrammingError):
return False
def update(self, dashboard_hash: str) -> bool:
"""
Updates an existing dashboard.
"""
if not dashboard_hash:
return False
with pool_manager() as conn:
query = f"""UPDATE {self._table} SET dashboard_hash = %(dashboard_hash)s
WHERE dashboard_hash = %(dashboard_hash)s
RETURNING dashboard_hash;"""
try:
conn.cursor.execute(query, {'dashboard': dashboard_hash})
dashboard_hash = conn.cursor.fetchone()
self.dashboard_hash = dashboard_hash
return True
except (psycopg2.ProgrammingError, psycopg2.DatabaseError) as error:
logger.info(f"Error Dashboard model update {error}")
def get_by_id(cls, pk: int):
"""
Get stock data with given pk.
:param pk: StockData id
:return: StockData instance with given pk
"""
with pool_manager() as conn:
query = f"SELECT * FROM {cls._table} WHERE id = %(id)s"
try:
conn.cursor.execute(query, {'id': pk})
pk, stock_id, price, created_at = conn.cursor.fetchone()
return StockData(pk=pk,
stock_id=stock_id,
price=price,
created_at=created_at)
except (DataError, ProgrammingError, TypeError):
return None
def get_data_for_time_period(self, datetime_from: datetime,
datetime_to: datetime) -> list:
"""
Return list of stock datas for current stock in specified period of time
:param datetime_from: start time of period to get stock data
:param datetime_to: end time of period to get stock data
:return: list of StockData
"""
stock_data_for_time_period = []
datetime_from_str, datetime_to_str = datetime_from.strftime(
DATETIME_PATTERN), datetime_to.strftime(DATETIME_PATTERN)
with pool_manager() as conn:
query = "SELECT id, stock_id, price, created_at FROM stocks_data " \
"WHERE stock_id = %(stock_id)s " \
"AND %(datetime_from)s <= created_at AND created_at < %(datetime_to)s " \
"ORDER BY created_at;"
try:
conn.cursor.execute(
query, {
'stock_id': self.pk,
'datetime_from': datetime_from_str,
'datetime_to': datetime_to_str
})
stock_data_for_time_period = conn.cursor.fetchall()
except (psycopg2.DataError, psycopg2.ProgrammingError, TypeError):
message = f"Could not get stock data for pk={self.pk}, datetime_from={datetime_from_str}, " \
f"datetime_to={datetime_to_str}"
logger.warning(message)
stock_data_for_time_period = [
StockData(pk=pk,
stock_id=stock_id,
price=price,
created_at=created_at) for pk, stock_id, price,
created_at in stock_data_for_time_period
]
if Stock._are_gaps_in_data(datetime_from, datetime_to,
stock_data_for_time_period):
self._fill_gaps_in_data(datetime_from, datetime_to,
stock_data_for_time_period)
return stock_data_for_time_period
def create(cls,
name: str,
company_name: str,
country: str,
industry: str,
sector: str,
in_use: bool = None) -> Stock:
"""
Create a new instance in database
:param name: short name of company stocks
:param company_name: name of company
:param country:
:param industry:
:param sector:
:return: instance
"""
with pool_manager() as conn:
query = f"""INSERT INTO {cls._table} (name, company_name, country, industry, sector)
VALUES (%(name)s, %(company_name)s)
RETURNING id, name, company_name, country, industry, sector, in_use;"""
try:
conn.cursor.execute(
query, {
'name': name,
'company_name': company_name,
'country': country,
'industry': industry,
'sector': sector
})
pk, name, company_name, country, industry, sector, in_use = conn.cursor.fetchone(
) # pylint: disable=C0103
return Stock(pk=pk,
name=name,
company_name=company_name,
country=country,
industry=industry,
sector=sector,
in_use=in_use)
except (psycopg2.DataError, psycopg2.ProgrammingError):
message = f"Could not create Stock with name={name}, company_name={company_name}"
logger.warning(message)
def get_stock_by_ids(cls, stock_ids):
stock_ids = tuple(stock_ids)
select_stocks_query = f"""SELECT id, name, company_name, country, industry, sector, in_use FROM {cls._table}
WHERE id IN %(stock_ids)s"""
with pool_manager() as conn:
try:
conn.cursor.execute(select_stocks_query,
{'stock_ids': stock_ids})
stocks = conn.cursor.fetchall()
stocks = [
Stock(pk=stock[0],
name=stock[1],
company_name=stock[2],
country=stock[3],
industry=stock[4],
sector=stock[5],
in_use=stock[6]) for stock in stocks
]
return stocks
except (psycopg2.DataError, psycopg2.ProgrammingError, TypeError):
message = "Could not get stocks"
logger.warning(message)
def get_stocks(self):
dashboard_has_stocks_table = 'public.dashboard_has_stocks'
stocks_table = 'public.stocks'
with pool_manager() as conn:
get_stocks_id_query = f"""SELECT {dashboard_has_stocks_table}.stock_id,{stocks_table}.name,
{stocks_table}.company_name, {stocks_table}.country,
{stocks_table}.industry, {stocks_table}.sector, {stocks_table}.in_use
FROM {dashboard_has_stocks_table} INNER JOIN {stocks_table}
ON {dashboard_has_stocks_table}.stock_id = {stocks_table}.id
WHERE {dashboard_has_stocks_table}.dashboard_hash = %(dashboard_hash)s;"""
try:
conn.cursor.execute(get_stocks_id_query, {'dashboard_hash': self.dashboard_hash})
list_of_stocks = conn.cursor.fetchall()
list_of_stocks = [Stock(pk=stock[0],
name=stock[1],
company_name=stock[2],
country=stock[3],
industry=stock[4],
sector=stock[5],
in_use=stock[6])
for stock in list_of_stocks]
return list_of_stocks
except (psycopg2.ProgrammingError, psycopg2.DatabaseError, TypeError) as error:
logger.info(f"Error Dashboard model get_stocks {error}")
def get_by_id(cls, pk: int) -> Stock: # pylint: disable=C0103
"""
Get instance from database by id
:param pk: instance id in database
:return: instance from database
"""
with pool_manager() as conn:
query = f"SELECT id, name, company_name, country, industry, sector, in_use FROM {cls._table} WHERE id = %(id)s"
try:
conn.cursor.execute(query, {'id': pk})
pk, name, company_name, country, industry, sector, in_use = conn.cursor.fetchone(
)
return Stock(pk=pk,
name=name,
company_name=company_name,
country=country,
industry=industry,
sector=sector,
in_use=in_use)
except (psycopg2.DataError, psycopg2.ProgrammingError, TypeError):
message = f"Could not get stock by pk={pk}"
logger.warning(message)
