Database¶

class dapi.db.accessor.DatabaseAccessor[source]¶

Bases: object

Provides lazy access to different DesignSafe database connections via properties.

This class manages multiple database connections and provides convenient property-based access to different DesignSafe databases. Each database connection is created only when first accessed (lazy initialization) and reused for subsequent calls.

The accessor supports the following databases through properties: - ngl: Natural hazards engineering research database - vp: Vulnerability and performance database - eq: Post-earthquake recovery database

_connections¶

Internal storage for database instances.

Type:: Dict[str, Optional[DSDatabase]]

Example

>>> accessor = DatabaseAccessor()
DatabaseAccessor initialized. Connections will be created on first access.

>>> # Access NGL database (created on first access)
>>> ngl_db = accessor.ngl
First access to 'ngl', initializing DSDatabase...
Successfully connected to database 'sjbrande_ngl_db' on 129.114.52.174.

>>> # Query the database
>>> results = ngl_db.read_sql("SELECT COUNT(*) as total FROM users")

>>> # Close all connections when done
>>> accessor.close_all()
Closing all active database engines/pools...
Closed 1 database engine(s).

__init__()[source]¶

Initialize the DatabaseAccessor with empty connection slots.

Creates a dictionary to hold database connections for each configured database, but does not establish any connections until they are first accessed.

property ngl: DSDatabase¶

Access the NGL (Natural Hazards Engineering) database connection manager.

Provides access to the sjbrande_ngl_db database containing natural hazards engineering research data. The connection is created on first access.

Returns:: Connected database instance for the NGL database.
Return type:: DSDatabase
Raises:: Exception – If database connection fails during initialization.

Example

>>> ngl_db = accessor.ngl
>>> df = ngl_db.read_sql("SELECT * FROM earthquake_data LIMIT 10")

property vp: DSDatabase¶

Access the VP (Vulnerability and Performance) database connection manager.

Provides access to the sjbrande_vpdb database containing vulnerability and performance analysis data. The connection is created on first access.

Returns:: Connected database instance for the VP database.
Return type:: DSDatabase
Raises:: Exception – If database connection fails during initialization.

Example

>>> vp_db = accessor.vp
>>> df = vp_db.read_sql("SELECT * FROM vulnerability_models LIMIT 10")

property eq: DSDatabase¶

Access the EQ (Post-Earthquake Recovery) database connection manager.

Provides access to the post_earthquake_recovery database containing post-earthquake recovery research data. The connection is created on first access.

Returns:: Connected database instance for the EQ database.
Return type:: DSDatabase
Raises:: Exception – If database connection fails during initialization.

Example

>>> eq_db = accessor.eq
>>> df = eq_db.read_sql("SELECT * FROM recovery_metrics LIMIT 10")

close_all()[source]¶

Close all active database engines and their connection pools.

This method iterates through all database connections and properly closes their SQLAlchemy engines and connection pools. This should be called when the DatabaseAccessor is no longer needed to prevent connection leaks.

Note

After calling close_all(), accessing any database property will create new connections since the instances are reset to None.

Example

>>> accessor = DatabaseAccessor()
>>> ngl_db = accessor.ngl  # Creates connection
>>> vp_db = accessor.vp  # Creates connection
>>> accessor.close_all()  # Closes both connections
Closing all active database engines/pools...
Closing connection pool for database 'sjbrande_ngl_db'.
Closing connection pool for database 'sjbrande_vpdb'.
Closed 2 database engine(s).

class dapi.db.db.DSDatabase(dbname='ngl')[source]¶

Bases: object

Manages connection and querying for a specific DesignSafe database.

This class provides a high-level interface for connecting to preconfigured DesignSafe databases using SQLAlchemy with connection pooling. It supports environment-based configuration and provides query results in multiple formats.

user¶

Database username for authentication.

Type:: str

password¶

Database password for authentication.

Type:: str

host¶

Database host address.

Type:: str

port¶

Database port number.

Type:: int

db¶

Name of the connected database.

Type:: str

dbname_short¶

Shorthand name for the database.

Type:: str

engine¶

SQLAlchemy engine for database connections.

Type:: sqlalchemy.Engine

Session¶

Session factory for database operations.

Type:: sqlalchemy.orm.sessionmaker

Example

>>> db = DSDatabase("ngl")
>>> df = db.read_sql("SELECT COUNT(*) as total FROM users")
>>> print(df.iloc[0]["total"])
>>> db.close()

__init__(dbname='ngl')[source]¶

Initialize the DSDatabase instance and create the SQLAlchemy engine.

Sets up database connection parameters from environment variables or defaults, creates a SQLAlchemy engine with connection pooling, and prepares the session factory.

Parameters:

dbname (str, optional) – Shorthand name for the database to connect to. Must be a key in db_config. Defaults to “ngl”. Available options: “ngl”, “vp”, “eq”.

Raises:

ValueError – If dbname is not a valid configured database name.
SQLAlchemyError – If database engine creation or connection fails.

Example

>>> db = DSDatabase("ngl")  # Connect to NGL database
>>> db = DSDatabase("vp")  # Connect to VP database

read_sql(sql, output_type='DataFrame')[source]¶

Execute a SQL query using a dedicated session and return the results.

This method obtains a session from the connection pool, executes the provided SQL query, and returns results in the specified format. The session is automatically closed after execution, returning the connection to the pool.

Parameters:

sql (str) – The SQL query string to execute. Can be any valid SQL statement including SELECT, INSERT, UPDATE, DELETE, etc.
output_type (str, optional) – Format for query results. Must be either ‘DataFrame’ for pandas.DataFrame or ‘dict’ for list of dictionaries. Defaults to “DataFrame”.

Returns:

Query results in the requested format.

’DataFrame’: Returns a pandas DataFrame with column names as headers
’dict’: Returns a list of dictionaries where each dict represents a row

Return type:

pandas.DataFrame or List[Dict]

Raises:

ValueError – If sql is empty/None or output_type is not ‘DataFrame’ or ‘dict’.
SQLAlchemyError – If database error occurs during query execution.
Exception – If unexpected errors occur during query processing.

Example

>>> # Get DataFrame result
>>> df = db.read_sql("SELECT name, age FROM users WHERE age > 25")
>>> print(df.columns.tolist())  # ['name', 'age']

>>> # Get dictionary result
>>> results = db.read_sql(
...     "SELECT COUNT(*) as total FROM users", output_type="dict"
... )
>>> print(results[0]["total"])  # 150

close()[source]¶

Dispose of the SQLAlchemy engine and close all database connections.

This method properly shuts down the database engine and its connection pool. It should be called when the database instance is no longer needed to prevent connection leaks and free up database resources.

Note

After calling close(), this DSDatabase instance should not be used for further database operations as the engine will be disposed.

Example

>>> db = DSDatabase("ngl")
>>> # ... perform database operations ...
>>> db.close()
Disposing engine and closing pool for 'ngl'...
Engine for 'ngl' disposed.