pyiem.database module#

Database helpers.

pyiem.database.get_dbconnstr(name, **kwargs)[source]#

Create a database connection string/URI.

Parameters:

  • name (str) – the database name to connect to.

  • **kwargs – optional keyword arguments: user, host, port, connect_timeout, gssencmode, and rw. Missing user, host, and port values default to the current mapped user, iemdb-{name}.local, and 5432, respectively. connect_timeout defaults to 30 and gssencmode defaults to disable. rw controls target_session_attrs: true requires a read-write server and false accepts any server. With rw=None, a read-write server is required unless user is omitted or None and maps to nobody, in which case any server is accepted. Explicitly passing user=”nobody” still requires a read-write server.

Returns:

str

Return type:

str

pyiem.database.get_dbconn(database='mesosite', user=None, host=None, port=5432, **kwargs)[source]#

Helper function with business logic to get a database connection

Note that this helper could return a read-only database connection if the connection to the primary server fails.

Parameters:

  • database (str,optional) – the database name to connect to. default: mesosite

  • user (str,optional) – hard coded user to connect as, default: current user

  • host (str,optional) – hard coded hostname to connect as, default: iemdb.local

  • port (int,optional) – the TCP port that PostgreSQL is listening defaults to 5432

  • password (str,optional) – the password to use.

  • rw (bool | None) – True requires a read-write server; false accepts any server. With rw=None, any server is accepted only when user is omitted or None and maps to nobody; explicitly passing user=”nobody” still requires a read-write server.

Returns:

psycopg database connection

pyiem.database.get_dbconnc(database='mesosite', user=None, host=None, cursor_name=None, **kwargs)[source]#

Helper function to get a database connection + dict_row cursor.

Note that this helper could return a read-only database connection if the connection to the primary server fails.

Parameters:

  • database (str,optional) – the database name to connect to. default: mesosite

  • user (str,optional) – hard coded user to connect as, default: current user

  • host (str,optional) – hard coded hostname to connect as, default: iemdb.local

  • cursor_name (str,optional) – name of the cursor to create

  • port (int,optional) – the TCP port that PostgreSQL is listening defaults to 5432

  • password (str,optional) – the password to use.

  • rw (bool | None) – Require that the connected cluster can accept write requests. The default is None, which some opinionated logic happens. If nobody is computed, then read-only is assumed.

Return type:

tuple[Connection[dict[str, Any]], ServerCursor[dict[str, Any]]]

pyiem.database.get_sqlalchemy_conn(name, **kwargs)[source]#

An auto-disposing sqlalchemy context-manager helper.

This is used for when we really do not want to manage having pools of database connections open. So this isn’t something that is fast!

Parameters:

  • name (str) – the database to connect to, passed to get_dbconnstr

  • rw (bool | None) – Require that the connected cluster can accept write requests. The default is None, which some opinionated logic happens. If nobody is computed, then read-only is assumed.

  • **kwargs – any additional arguments to pass to get_dbconnstr

Return type:

Generator[Connection, None, None]

pyiem.database.with_sqlalchemy_conn(name, **kwargs)[source]#

Decorator variant of get_sqlalchemy_conn adding conn= to function.

Usage:

@with_sqlalchemy_conn("dbname")
def foo(args, conn=None, **kwargs):
    ...

Note

Be sure to commit any transactions before returning from the decorated function.

Parameters:

  • name (str) – the database to connect to, passed to get_dbconnstr

  • rw (bool | None) – Require that the connected cluster can accept write requests. The default is None, which some opinionated logic happens. If nobody is computed, then read-only is assumed.

  • **kwargs – any additional arguments to pass to get_dbconnstr

pyiem.database.sql_helper(sql, **kwargs)[source]#

Run string through psycopg.sql machinery destined for sqlalchemy.Allows for removal of boilerplate and appease SQL injection detection.

Example

`python sql = "select bah from {table} where {limiter} foo = :bar" stm = sql_helper(sql, table='foo', limiter='a = :a and ') pd.read_sql(stm, conn, params={'bar': 'baz', 'a': 1}) `

Parameters:

  • sql (str) – the SQL statement to process

  • **kwargs – arguments needed to build the string.

Return type:

TextClause