I am designing a Stock Currency application and for that, I created a database. I searched for my question here first but the answerer told me to ask the same question here.
flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
class Stock_Data(db.Model):
__tablename__ = 'stock_datas'
# Column names start with capital letter for convention to use data easier (for now)
id = db.Column(db.Integer(), primary_key=True)
Date = db.Column(db.DateTime, nullable=False)
Open = db.Column(db.Float(), nullable=False)
High = db.Column(db.Float(), nullable=False)
Low = db.Column(db.Float(), nullable=False)
Currency_Close = db.Column(db.Float(), nullable=False)
Volume = db.Column(db.Integer(), nullable=False)
# Foreign Key
stock_id = db.Column(db.Integer, db.ForeignKey('stocks.id'))
# Foreign Attribute To Reach
stock = db.relationship('Stock', backref='Stock_Data', primaryjoin='Stock_Data.stock_id==Stock.id', lazy=True)
class Stock(db.Model):
__tablename__ = 'stocks'
id = db.Column(db.Integer(), primary_key=True)
symbol = db.Column(db.String(10), unique=True)
name = db.Column(db.String(50), unique=True)
sector = db.Column(db.String(10), nullable=True)
currency = db.Column(db.String(3), nullable=False)
class Parity_Data(db.Model):
__tablename__ = 'parity_datas'
id = db.Column(db.Integer(), primary_key=True)
Parity_Close = db.Column(db.Float())
Date = db.Column(db.DateTime, nullable=False)
# Foreign Key
parity_id = db.Column(db.Integer, db.ForeignKey('parities.id'))
# Foreign Attribute To Reach
parity = db.relationship('Parity', backref='Parity_Data', primaryjoin='Parity_Data.parity_id==Parity.id', lazy=True)
class Parity(db.Model):
__tablename__ = 'parities'
id = db.Column(db.Integer(), primary_key=True)
parity_name = db.Column(db.String(8), unique=True)
I created this database design all classes are in separate files. Sotck is related to Stock_Data (1-N) and Parity is related to Parity_Data (1-N) as seen.
I am creating a connection between Stock and Parity by
string_stock = 'APPL'
stock = session.query(Stock).filter(Stock.symbol == str(string_stock).upper()).first()
stock_data = session.query(Stock_Data).filter(Stock_Data.stock_id == stock.id).all()
parity = session.query(Parity).filter(Parity.parity_name.endswith(stock.currency)).first()
parity_data = session.query(Parity_Data).filter(Parity_Data.parity_id==parity.id).all()
By this method, I can fetch all stock_data and parity_data separately. However, when I try to merge stock_data and parity_data I get two lists and cannot be merged. After that, I tried
data_joined_on_time = session.query(Stock_Data).join(Parity_Data, Stock_Data.Date == Parity_Data.Date).all()
dir(session.query(Stock_Data).join(Parity_Data, Stock_Data.Date == Parity_Data.Date).all()[0])
This method joins data but the attributes: ['Currency_Close', 'Date', 'High', 'Low', 'Open', 'Volume', 'class', 'delattr', 'dict', 'dir', 'doc', 'eq', 'format', 'ge', 'getattribute', 'gt', 'hash', 'init', 'init_subclass', 'le', 'lt', 'mapper', 'module', 'ne', 'new', 'reduce', 'reduce_ex', 'repr', 'setattr', 'sizeof', 'str', 'subclasshook', 'table', 'tablename', 'weakref', '_decl_class_registry', '_sa_class_manager', '_sa_instance_state', 'id', 'metadata', 'query', 'query_class', 'stock', 'stock_id']
There is nothing about Parity_Data part.
My question consists of two parts:
- I could not think of another way for my database design.Is my database design incorrect?
- Is there a way to merge those two (stock_data and parity_data) by SQLAlchemy? I would like to filter first and join after it.
If there is no way I will merge them on pandas dataframe but firstly I want to try it on SQLAlchemy.