def create_tables_if_not_existing(engine: Engine):
metadata = MetaData()
metadata.bind = engine
tablename_jira_issue_created = 'JiraIssueCreated'
tablename_jira_issue_updated = 'JiraIssueUpdated'
try:
if not (engine.has_table(tablename_jira_issue_created)
and engine.has_table(tablename_jira_issue_updated)):
issue_created = Table(
tablename_jira_issue_created, metadata,
Column('issue', String(32), primary_key=True, nullable=False),
Column('created', TIMESTAMP, nullable=False))
issue_updated = Table(
tablename_jira_issue_updated, metadata,
Column('id',
BIGINT(),
Sequence('id', start=1, increment=1),
primary_key=True),
Column('issue',
String(32),
ForeignKey(issue_created.c.issue),
nullable=False),
Column('updated', TIMESTAMP, nullable=False),
Column('issue_status', String(32)),
Column('customer', String(200)))
metadata.create_all()
finally:
engine.dispose()
def __to_db(self,
dataframe: DataFrame,
conn: Engine,
params,
**kwargs) -> str:
table_name = params.get("table_name")
batch_size = params.get("batch_size")
mode = params.get("mode", 'append')
index_flag = params.get("index")
index_label = params.get("index_label")
try:
if mode == 'truncate' and conn.has_table(table_name=table_name):
conn.execution_options(autoCommit=True)\
.execute(f"""TRUNCATE TABLE {table_name}""")
dataframe.to_sql(con=conn,
name=table_name,
if_exists=self.modes.get(mode),
chunksize=batch_size,
index=index_flag,
index_label=index_label,
**kwargs)
except Exception as err:
msg = ("Error: Check your credentials (username,"
" password, host, port, database)\n")
raise ValueError(msg, err)
def _create_table_if_not_exists(db_engine: engine.Engine,
table_model_instance: Table) -> None:
"""
Creates each datalake table if doesn't exist
:param db_engine:
:param table_model_instance:
"""
table_name = table_model_instance.__tablename__
if not db_engine.has_table(table_name, schema=PATT_SCHEMA_NAME):
table_model_instance.__table__.create(bind=db_engine)
