Skip to content

dbconnection

ensembl.utils.database.dbconnection

Database connection handler.

This module provides the main class to connect to and access databases. It will be an ORM-less connection, that is, the data can only be accessed via SQL queries (see example below).

Examples:

>>> from ensembl.utils.database import DBConnection
>>> dbc = DBConnection("mysql://ensro@mysql-server:4242/mydb")
>>> # You can access the database data via sql queries, for instance:
>>> results = dbc.execute("SELECT * FROM my_table;")
>>> # Or via a connection in a transaction manner:
>>> with dbc.begin() as conn:
>>>     results = conn.execute("SELECT * FROM my_table;")

Query = TypeVar('Query', str, sqlalchemy.sql.expression.ClauseElement, sqlalchemy.sql.expression.TextClause) module-attribute

StrURL = TypeVar('StrURL', str, sqlalchemy.engine.URL) module-attribute

DBConnection

Database connection handler, providing also the database's schema and properties.

Parameters:

Name Type Description Default
url StrURL

URL to the database, e.g. mysql://user:passwd@host:port/my_db.

 required
reflect bool

Reflect the database schema or not.

 True
Source code in src/ensembl/utils/database/dbconnection.py 
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
class DBConnection:
    """Database connection handler, providing also the database's schema and properties.

    Args:
        url: URL to the database, e.g. `mysql://user:passwd@host:port/my_db`.
        reflect: Reflect the database schema or not.

    """

    def __init__(self, url: StrURL, reflect: bool = True, **kwargs: Any) -> None:
        self._engine = create_engine(url, future=True, **kwargs)
        self._metadata: MetaData | None = None
        if reflect:
            self.load_metadata()

    def __repr__(self) -> str:
        """Returns a string representation of this object."""
        return f"{self.__class__.__name__}({self.url!r})"

    def load_metadata(self) -> None:
        """Loads the metadata information of the database."""
        # Note: Just reflect() is not enough as it would not delete tables that no longer exist
        self._metadata = sqlalchemy.MetaData()
        self._metadata.reflect(bind=self._engine)

    def create_all_tables(self, metadata: MetaData) -> None:
        """Create the tables from the metadata and set the metadata.

        This assumes the database is empty beforehand. If the tables already exist, they will be ignored.
        If there are other tables, you may need to run `self.load_metadata()` to update the metadata schema.
        """
        self._metadata = metadata
        metadata.create_all(self._engine)

    def create_table(self, table: Table) -> None:
        """Create a table in the database and update the metadata. Do nothing if the table already exists."""
        table.create(self._engine)
        # We need to update the metadata to register the new table
        self.load_metadata()

    @property
    def url(self) -> str:
        """Returns the database URL."""
        return self._engine.url.render_as_string(hide_password=False)

    @property
    def db_name(self) -> Optional[str]:
        """Returns the database name."""
        return self._engine.url.database

    @property
    def host(self) -> Optional[str]:
        """Returns the database host."""
        return self._engine.url.host

    @property
    def port(self) -> Optional[int]:
        """Returns the port of the database host."""
        return self._engine.url.port

    @property
    def dialect(self) -> str:
        """Returns the SQLAlchemy database dialect name of the database host."""
        return self._engine.name

    @property
    def tables(self) -> dict[str, sqlalchemy.schema.Table]:
        """Returns the database tables keyed to their name, or an empty dict if no metadata was loaded."""
        if self._metadata:
            return self._metadata.tables
        return {}

    def get_primary_key_columns(self, table: str) -> list[str]:
        """Returns the primary key column names for the given table.

        Args:
            table: Table name.

        """
        return [col.name for col in self.tables[table].primary_key.columns.values()]

    def get_columns(self, table: str) -> list[str]:
        """Returns the column names for the given table.

        Args:
            table: Table name.

        """
        return [col.name for col in self.tables[table].columns]

    def connect(self) -> sqlalchemy.engine.Connection:
        """Returns a new database connection."""
        return self._engine.connect()

    def begin(self, *args: Any) -> ContextManager[sqlalchemy.engine.Connection]:
        """Returns a context manager delivering a database connection with a transaction established."""
        return self._engine.begin(*args)

    def dispose(self) -> None:
        """Disposes of the connection pool."""
        self._engine.dispose()

    def _enable_sqlite_savepoints(self, engine: sqlalchemy.engine.Engine) -> None:
        """Enables SQLite SAVEPOINTS to allow session rollbacks."""

        @event.listens_for(engine, "connect")
        def do_connect(
            dbapi_connection: Any,  # SQLAlchemy is not clear about the type of this argument
            connection_record: sqlalchemy.pool.ConnectionPoolEntry,  # pylint: disable=unused-argument
        ) -> None:
            """Disables emitting the BEGIN statement entirely, as well as COMMIT before any DDL."""
            dbapi_connection.isolation_level = None

        @event.listens_for(engine, "begin")
        def do_begin(conn: sqlalchemy.engine.Connection) -> None:
            """Emits a custom own BEGIN."""
            conn.exec_driver_sql("BEGIN")

    @contextmanager
    def session_scope(self) -> Generator[sqlalchemy.orm.Session, None, None]:
        """Provides a transactional scope around a series of operations with rollback in case of failure.

        Bear in mind MySQL's storage engine MyISAM does not support rollback transactions, so all
        the modifications performed to the database will persist.

        """
        # Create a dedicated engine for this session
        engine = create_engine(self._engine.url)
        if self.dialect == "sqlite":
            self._enable_sqlite_savepoints(engine)
        Session = sessionmaker(future=True)
        session = Session(bind=engine, autoflush=False)
        try:
            yield session
            session.commit()
        except:
            # Rollback to ensure no changes are made to the database
            session.rollback()
            raise
        finally:
            # Whatever happens, make sure the session is closed
            session.close()

    @contextmanager
    def test_session_scope(self) -> Generator[sqlalchemy.orm.Session, None, None]:
        """Provides a transactional scope around a series of operations that will be rolled back at the end.

        Bear in mind MySQL's storage engine MyISAM does not support rollback transactions, so all
        the modifications performed to the database will persist.

        """
        # Create a dedicated engine for this session
        engine = create_engine(self._engine.url)
        if self.dialect == "sqlite":
            self._enable_sqlite_savepoints(engine)
        # Connect to the database
        connection = engine.connect()
        # Begin a non-ORM transaction
        transaction = connection.begin()
        # Bind an individual session to the connection
        Session = sessionmaker(future=True)
        try:
            # Running on SQLAlchemy 2.0+
            session = Session(bind=connection, join_transaction_mode="create_savepoint")
        except TypeError:
            # Running on SQLAlchemy 1.4
            session = Session(bind=connection)
            # If the database supports SAVEPOINT, starting a savepoint will allow to also use rollback
            connection.begin_nested()

            # Define a new transaction event
            @event.listens_for(session, "after_transaction_end")
            def end_savepoint(
                session: sqlalchemy.orm.Session,  # pylint: disable=unused-argument
                transaction: sqlalchemy.orm.SessionTransaction,  # pylint: disable=unused-argument
            ) -> None:
                if not connection.in_nested_transaction():
                    connection.begin_nested()

        try:
            yield session
        finally:
            # Whatever happens, make sure the session and connection are closed, rolling back
            # everything done with the session (including calls to commit())
            session.close()
            transaction.rollback()
            connection.close()

db_name: Optional[str] property

Returns the database name.

dialect: str property

Returns the SQLAlchemy database dialect name of the database host.

host: Optional[str] property

Returns the database host.

port: Optional[int] property

Returns the port of the database host.

tables: dict[str, sqlalchemy.schema.Table] property

Returns the database tables keyed to their name, or an empty dict if no metadata was loaded.

url: str property

Returns the database URL.

begin(*args)

Returns a context manager delivering a database connection with a transaction established.

Source code in src/ensembl/utils/database/dbconnection.py 
147
148
149
def begin(self, *args: Any) -> ContextManager[sqlalchemy.engine.Connection]:
    """Returns a context manager delivering a database connection with a transaction established."""
    return self._engine.begin(*args)

connect()

Returns a new database connection.

Source code in src/ensembl/utils/database/dbconnection.py 
143
144
145
def connect(self) -> sqlalchemy.engine.Connection:
    """Returns a new database connection."""
    return self._engine.connect()

create_all_tables(metadata)

Create the tables from the metadata and set the metadata.

This assumes the database is empty beforehand. If the tables already exist, they will be ignored. If there are other tables, you may need to run self.load_metadata() to update the metadata schema.

Source code in src/ensembl/utils/database/dbconnection.py 
78
79
80
81
82
83
84
85
def create_all_tables(self, metadata: MetaData) -> None:
    """Create the tables from the metadata and set the metadata.

    This assumes the database is empty beforehand. If the tables already exist, they will be ignored.
    If there are other tables, you may need to run `self.load_metadata()` to update the metadata schema.
    """
    self._metadata = metadata
    metadata.create_all(self._engine)

create_table(table)

Create a table in the database and update the metadata. Do nothing if the table already exists.

Source code in src/ensembl/utils/database/dbconnection.py 
87
88
89
90
91
def create_table(self, table: Table) -> None:
    """Create a table in the database and update the metadata. Do nothing if the table already exists."""
    table.create(self._engine)
    # We need to update the metadata to register the new table
    self.load_metadata()

dispose()

Disposes of the connection pool.

Source code in src/ensembl/utils/database/dbconnection.py 
151
152
153
def dispose(self) -> None:
    """Disposes of the connection pool."""
    self._engine.dispose()

get_columns(table)

Returns the column names for the given table.

Parameters:

Name Type Description Default
table str

Table name.

 required
Source code in src/ensembl/utils/database/dbconnection.py 
134
135
136
137
138
139
140
141
def get_columns(self, table: str) -> list[str]:
    """Returns the column names for the given table.

    Args:
        table: Table name.

    """
    return [col.name for col in self.tables[table].columns]

get_primary_key_columns(table)

Returns the primary key column names for the given table.

Parameters:

Name Type Description Default
table str

Table name.

 required
Source code in src/ensembl/utils/database/dbconnection.py 
125
126
127
128
129
130
131
132
def get_primary_key_columns(self, table: str) -> list[str]:
    """Returns the primary key column names for the given table.

    Args:
        table: Table name.

    """
    return [col.name for col in self.tables[table].primary_key.columns.values()]

load_metadata()

Loads the metadata information of the database.

Source code in src/ensembl/utils/database/dbconnection.py 
72
73
74
75
76
def load_metadata(self) -> None:
    """Loads the metadata information of the database."""
    # Note: Just reflect() is not enough as it would not delete tables that no longer exist
    self._metadata = sqlalchemy.MetaData()
    self._metadata.reflect(bind=self._engine)

session_scope()

Provides a transactional scope around a series of operations with rollback in case of failure.

Bear in mind MySQL's storage engine MyISAM does not support rollback transactions, so all the modifications performed to the database will persist.

Source code in src/ensembl/utils/database/dbconnection.py 
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
@contextmanager
def session_scope(self) -> Generator[sqlalchemy.orm.Session, None, None]:
    """Provides a transactional scope around a series of operations with rollback in case of failure.

    Bear in mind MySQL's storage engine MyISAM does not support rollback transactions, so all
    the modifications performed to the database will persist.

    """
    # Create a dedicated engine for this session
    engine = create_engine(self._engine.url)
    if self.dialect == "sqlite":
        self._enable_sqlite_savepoints(engine)
    Session = sessionmaker(future=True)
    session = Session(bind=engine, autoflush=False)
    try:
        yield session
        session.commit()
    except:
        # Rollback to ensure no changes are made to the database
        session.rollback()
        raise
    finally:
        # Whatever happens, make sure the session is closed
        session.close()

test_session_scope()

Provides a transactional scope around a series of operations that will be rolled back at the end.

Bear in mind MySQL's storage engine MyISAM does not support rollback transactions, so all the modifications performed to the database will persist.

Source code in src/ensembl/utils/database/dbconnection.py 
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
@contextmanager
def test_session_scope(self) -> Generator[sqlalchemy.orm.Session, None, None]:
    """Provides a transactional scope around a series of operations that will be rolled back at the end.

    Bear in mind MySQL's storage engine MyISAM does not support rollback transactions, so all
    the modifications performed to the database will persist.

    """
    # Create a dedicated engine for this session
    engine = create_engine(self._engine.url)
    if self.dialect == "sqlite":
        self._enable_sqlite_savepoints(engine)
    # Connect to the database
    connection = engine.connect()
    # Begin a non-ORM transaction
    transaction = connection.begin()
    # Bind an individual session to the connection
    Session = sessionmaker(future=True)
    try:
        # Running on SQLAlchemy 2.0+
        session = Session(bind=connection, join_transaction_mode="create_savepoint")
    except TypeError:
        # Running on SQLAlchemy 1.4
        session = Session(bind=connection)
        # If the database supports SAVEPOINT, starting a savepoint will allow to also use rollback
        connection.begin_nested()

        # Define a new transaction event
        @event.listens_for(session, "after_transaction_end")
        def end_savepoint(
            session: sqlalchemy.orm.Session,  # pylint: disable=unused-argument
            transaction: sqlalchemy.orm.SessionTransaction,  # pylint: disable=unused-argument
        ) -> None:
            if not connection.in_nested_transaction():
                connection.begin_nested()

    try:
        yield session
    finally:
        # Whatever happens, make sure the session and connection are closed, rolling back
        # everything done with the session (including calls to commit())
        session.close()
        transaction.rollback()
        connection.close()