def save(self, table_name, msg, path):
con_str = f"mysql+mysqldb://{self._bt_info['USER_ID']}:{self._bt_info['NORM_PWD']}"\
f"@localhost/{self._bt_info['DB_NAME']}"
engine = create_engine(con_str)
type_dict = {
'grp': VARCHAR(10),
'code': VARCHAR(10),
'date': DATE(),
'prev_price': INTEGER(),
'price': INTEGER(),
'captured': FLOAT()
}
for day in self._result.columns[6:]:
type_dict[day] = FLOAT()
# Making DB from self._result_data
self._result.to_sql(name=table_name,
con=engine,
chunksize=1000,
index_label='idx',
dtype=type_dict)
with engine.connect() as con:
con.execute(f"ALTER TABLE {table_name} ADD PRIMARY KEY (idx);")
con.execute(f"ALTER TABLE {table_name} MODIFY idx INTEGER;")
date = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
with open(path + 'backtest.log', 'a') as f:
f.write(f"[{date}] {table_name} : {msg}\n")
def test_column_datatype_to_string(self):
test_cases = (
(DATE(), "DATE"),
(VARCHAR(length=255), "VARCHAR(255)"),
(
VARCHAR(length=255, charset="latin1", collation="utf8mb4_general_ci"),
"VARCHAR(255)",
),
(NVARCHAR(length=128), "NATIONAL VARCHAR(128)"),
(TEXT(), "TEXT"),
)
for original, expected in test_cases:
actual = MySQLEngineSpec.column_datatype_to_string(
original, mysql.dialect()
)
self.assertEqual(actual, expected)
class Paper(Base):
__tablename__ = 'paper'
id = Column(INTEGER(unsigned=True),
primary_key=True,
autoincrement=True,
nullable=False)
id_scp = Column(BIGINT(unsigned=True), nullable=False, unique=True)
eid = Column(VARCHAR(45), nullable=False, unique=True)
title = Column(VARCHAR(512), nullable=False)
type = Column(VARCHAR(2), nullable=False)
type_description = Column(VARCHAR(45), nullable=True)
abstract = Column(TEXT(), nullable=True)
total_author = Column(SMALLINT(unsigned=True), nullable=False)
open_access = Column(BOOLEAN(create_constraint=True,
name='open_access_check'),
nullable=False)
cited_cnt = Column(SMALLINT(unsigned=True), nullable=True)
url = Column(VARCHAR(256), nullable=False, unique=True)
article_no = Column(VARCHAR(45), nullable=True)
date = Column(DATE(), nullable=False)
fund_id = Column(BIGINT(unsigned=True),
ForeignKey('fund.id'),
nullable=True)
source_id = Column(INTEGER(unsigned=True),
ForeignKey('source.id'),
nullable=True)
doi = Column(VARCHAR(256), nullable=True, unique=True)
volume = Column(VARCHAR(45), nullable=True)
issue = Column(VARCHAR(45), nullable=True)
page_range = Column(VARCHAR(45), nullable=True)
retrieval_time = Column(DATETIME(), nullable=False)
create_time = Column(DATETIME(),
nullable=False,
server_default=text('CURRENT_TIMESTAMP'))
update_time = Column(
DATETIME(),
nullable=False,
server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
# Relationships
fund = relationship('Fund', back_populates='papers')
source = relationship('Source', back_populates='papers')
keywords = relationship('Keyword',
secondary=Paper_Keyword,
back_populates='papers')
authors = relationship('Paper_Author', back_populates='paper')
def __init__(
self,
id_scp,
eid,
title,
type,
total_author,
open_access,
cited_cnt,
url,
date,
retrieval_time,
type_description=None,
abstract=None,
article_no=None,
fund_id=None,
source_id=None,
doi=None,
volume=None,
issue=None,
page_range=None,
create_time=None,
update_time=None,
):
self.id_scp = id_scp
self.eid = eid
self.title = title
self.type = type
self.type_description = type_description
self.abstract = abstract
self.total_author = total_author
self.open_access = open_access
self.cited_cnt = cited_cnt
self.url = url
self.article_no = article_no
self.fund_id = fund_id
self.source_id = source_id
self.doi = doi
self.volume = volume
self.issue = issue
self.date = date
self.page_range = page_range
self.retrieval_time = retrieval_time
self.create_time = create_time
self.update_time = update_time
def __repr__(self):
max_len = 50
if len(self.title) <= max_len:
return f'{self.id_scp}: {self.title}; DOI: {self.doi}'
return f'{self.id_scp}: {self.title[:max_len-3]}...; DOI: {self.doi}'
def get_year(self):
return self.date.year
from sqlalchemy.sql import select
from db import metadata
from sqlalchemy import func, text, UniqueConstraint
from sqlalchemy import Table, Column, String, TIMESTAMP
from sqlalchemy.dialects.mysql import BIGINT, DATE, DECIMAL, TEXT, VARCHAR
yahoo_historical = Table(
'yahoo_historical', metadata,
Column('id', BIGINT(20), primary_key=True, autoincrement=True),
Column('Date', DATE(), nullable=False, index=True),
Column('Symbol', String(32), nullable=False, index=True),
Column('Open', DECIMAL(20, 8), nullable=False),
Column('High', DECIMAL(20, 8), nullable=False),
Column('Low', DECIMAL(20, 8), nullable=False),
Column('Close', DECIMAL(20, 8), nullable=False),
Column('Adj Close', DECIMAL(20, 8), nullable=False, key='adj_close'),
Column('Volume', DECIMAL(20, 8), nullable=False),
Column('3day_before_change',
DECIMAL(12, 4),
nullable=False,
key='_3day_before_change'),
Column('created_at', TIMESTAMP, nullable=False, server_default=func.now()),
Column(
'updated_at',
TIMESTAMP,
nullable=False,
server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
index=True), UniqueConstraint('Symbol',
'Date',
name='symbol_date_uniq'))