db

assign_gas_bus_id(dataframe, scn_name, carrier)

Assign `bus_id`s to points according to location.

The points are taken from the given dataframe and the geometries by which the bus_id`s are assigned to them are taken from the `grid.egon_gas_voronoi table.

Parameters:

• dataframe (pandas.DataFrame) – DataFrame cointaining points
• scn_name (str) – Name of the scenario
• carrier (str) – Name of the carrier

Returns:

res (pandas.DataFrame) – Dataframe including bus_id

check_db_unique_violation(func)

Wrapper to catch psycopg’s UniqueViolation errors during concurrent DB commits.

Preferrably used with next_etrago_id(). Retries DB operation 10 times before raising original exception.

Can be used as a decorator like this:

>>> @check_db_unique_violation
... def commit_something_to_database():
...     # commit something here
...    return
...
>>> commit_something_to_database()  # doctest: +SKIP

Examples

Add new bus to eTraGo’s bus table:

>>> from egon.data import db
>>> from egon.data.datasets.etrago_setup import EgonPfHvBus
...
>>> @check_db_unique_violation
... def add_etrago_bus():
...     bus_id = db.next_etrago_id("bus")
...     with db.session_scope() as session:
...         emob_bus_id = db.next_etrago_id("bus")
...         session.add(
...             EgonPfHvBus(
...                 scn_name="eGon2035",
...                 bus_id=bus_id,
...                 v_nom=1,
...                 carrier="whatever",
...                 x=52,
...                 y=13,
...                 geom="<some_geom>"
...             )
...         )
...         session.commit()
...
>>> add_etrago_bus()  # doctest: +SKIP

Parameters:func (func) – Function to wrap

Notes

Background: using next_etrago_id() may cause trouble if tasks are executed simultaneously, cf. https://github.com/openego/eGon-data/issues/514

Important: your function requires a way to escape the violation as the loop will not terminate until the error is resolved! In case of eTraGo tables you can use next_etrago_id(), see example above.

credentials()

Return local database connection parameters.

Returns:dict – Complete DB connection information

engine()

Engine for local database.

engine_for

execute_sql(sql_string)

Execute a SQL expression given as string.

The SQL expression passed as plain string is convert to a sqlalchemy.sql.expression.TextClause.

Parameters:sql_string (str) – SQL expression

execute_sql_script(script, encoding='utf-8-sig')

Execute a SQL script given as a file name.

Parameters:

• script (str) – Path of the SQL-script
• encoding (str) – Encoding which is used for the SQL file. The default is “utf-8-sig”.

Returns:

None.

next_etrago_id(component)

Select next id value for components in etrago tables

Parameters:component (str) – Name of component Returns:next_id (int) – Next index value

Notes

To catch concurrent DB commits, consider to use check_db_unique_violation() instead.

select_dataframe(sql, index_col=None, warning=True)

Select data from local database as pandas.DataFrame

Parameters:

• sql (str) – SQL query to be executed.
• index_col (str, optional) – Column(s) to set as index(MultiIndex). The default is None.

Returns:

df (pandas.DataFrame) – Data returned from SQL statement.

select_geodataframe(sql, index_col=None, geom_col='geom', epsg=3035)

Select data from local database as geopandas.GeoDataFrame

Parameters:

• sql (str) – SQL query to be executed.
• index_col (str, optional) – Column(s) to set as index(MultiIndex). The default is None.
• geom_col (str, optional) – column name to convert to shapely geometries. The default is ‘geom’.
• epsg (int, optional) – EPSG code specifying output projection. The default is 3035.

Returns:

gdf (pandas.DataFrame) – Data returned from SQL statement.

session_scope()

Provide a transactional scope around a series of operations.

session_scoped(function)

Provide a session scope to a function.

Can be used as a decorator like this:

>>> @session_scoped
... def get_bind(session):
...     return session.get_bind()
...
>>> get_bind()
Engine(postgresql+psycopg2://egon:***@127.0.0.1:59734/egon-data)

Note that the decorated function needs to accept a parameter named session, but is called without supplying a value for that parameter because the parameter’s value will be filled in by session_scoped. Using this decorator allows saving an indentation level when defining such functions but it also has other usages.

submit_comment(json, schema, table)

Add comment to table.

We use Open Energy Metadata standard for describing our data. Metadata is stored as JSON in the table comment.

Parameters:

• json (str) – JSON string reflecting comment
• schema (str) – The target table’s database schema
• table (str) – Database table on which to put the given comment