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 – any additional arguments to pass to psycopg.connect user (str): the database user to connect as host (str): the database host to connect to port (int): the database port to connect to connect_timeout (int): Connection timeout in seconds, default 30.

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.

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.

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

  • **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

  • **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