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 iterate_csv_dir(db_engine: engine.Engine = None): current_dir = os.getcwd() db_config = config.DB() for _, services_dir, _ in os.walk(f'{current_dir}/csv'): for service_db in services_dir: db_engine = create_engine(db_config.print()) db_connection = db_engine.raw_connection() try: db_cursor = db_connection.cursor() db_cursor.execute( f"SELECT 1 FROM pg_catalog.pg_database WHERE datname = '{service_db}'" ) db_exists = db_cursor.fetchone() if not db_exists: db_cursor.execute(f'CREATE DATABASE {service_db}') db_cursor.close() db_connection.commit() finally: db_connection.close() db_engine.dispose() db_engine = create_engine(db_config.print(service_db)) for filename in os.listdir(f'{current_dir}/csv/{service_db}'): filename = os.path.splitext(filename)[0] from_csv_to_db(service_db, filename, db_engine) db_engine.dispose()
def test_connection( ctx: object, metadata: MetaData, engine: Engine, real_transaction: bool = False, ctx_connection_attribute_name: str = '_test_fx_connection', ) -> typing.Generator: """Joining a SQLAlchemy session into an external transaction for test suit. :param object ctx: Context object to inject test connection into attribute :param MetaData metadata: SQLAlchemy schema metadata :param bool real_transaction: (Optional) Whether to use engine as connection directly or make separate connection. Default: `False` :param str ctx_connection_attribute_name: (Optional) Attribute name for injecting test connection to the context object Default: `'_test_fx_connection'` .. seealso:: Documentation of the SQLAlchemy session used in test suites. <http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#joining-a-session-into-an-external-transaction-such-as-for-test-suites> """ # noqa if real_transaction: metadata.create_all(engine) try: yield engine finally: metadata.drop_all(engine, checkfirst=True) return connection = engine.connect() try: metadata.drop_all(connection, checkfirst=True) transaction = connection.begin() try: metadata.create_all(bind=connection) setattr(ctx, ctx_connection_attribute_name, connection) try: yield connection finally: delattr(ctx, ctx_connection_attribute_name) finally: transaction.rollback() finally: connection.close() engine.dispose()