db.tools.add_primary_key() - Code Metrics - oemof/oemof.db - Measure and Improve Code Quality continuously with Scrutinizer

db.tools.add_primary_key() A
last analyzed 2019-12-05 17:24 UTC

↳ Parent: db.tools

Complexity

Conditions

Size

Total Lines	20
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
eloc	4
nop	4
dl	0
loc	20
rs	10
c	0
b	0
f	0

# -*- coding: utf-8 -*-
"""
Created on Mon Aug 17 11:08:15 2015

This is a collection of helper functions which work on there own an can be
used by various classes. If there are too many helper-functions, they will
be sorted in different modules.

All special import should be in try/except loops to avoid import errors.
"""

import logging
import pandas as pd


# get_polygon_from_nuts
hlp_fkt = 'get_polygon_from_nuts'
try:
    from shapely.wkt import loads as wkt_loads
except Exception:
    logging.info(
        'You will not be able to use the helper function: {0}'.format(hlp_fkt)
    )
    logging.info('Install shapely to use it.')


def get_polygons_from_table(conn, schema, table, g_col='geom', n_col='name'):
    sql = '''
        SELECT {n_col}, st_astext({g_col})
        FROM {schema}.{table};
    '''.format(
        **{'n_col': n_col, 'g_col': g_col, 'schema': schema, 'table': table}
    )
    logging.debug(sql)
    raw_data = conn.execute(sql).fetchall()
    polygon_dc = {}
    for d in raw_data:
        polygon_dc[d[0]] = [d[0], wkt_loads(d[1])]
    return polygon_dc


def get_polygon_from_nuts(conn, nuts):
    r"""A one-line summary that does not use variable names or the
    function name.

    Several sentences providing an extended description. Refer to
    variables using back-ticks, e.g. `var`.

    Parameters
    ----------
    var1 : array_like
        Array_like means all those objects -- lists, nested lists, etc. --
        that can be converted to an array.  We can also refer to
        variables like `var1`.
    var2 : int
        The type above can either refer to an actual Python type
        (e.g. ``int``), or describe the type of the variable in more
        detail, e.g. ``(N,) ndarray`` or ``array_like``.
    Long_variable_name : {'hi', 'ho'}, optional
        Choices in brackets, default first when optional.

    Returns
    -------
    type
        Explanation of anonymous return value of type ``type``.
    describe : type
        Explanation of return value named `describe`.
    out : type
        Explanation of `out`.

    Other Parameters
    ----------------
    only_seldom_used_keywords : type
        Explanation
    common_parameters_listed_above : type
        Explanation

    Raises
    ------
    BadException
        Because you shouldn't have done that.

    See Also
    --------
    otherfunc : relationship (optional)
    newfunc : Relationship (optional), which could be fairly long, in which
              case the line wraps here.
    thirdfunc, fourthfunc, fifthfunc

    Notes
    -----
    Notes about the implementation algorithm (if needed).

    This can have multiple paragraphs.

    You may include some math:

    .. math:: X(e^{j\omega } ) = x(n)e^{ - j\omega n}

    And even use a greek symbol like :math:`omega` inline.

    References
    ----------
    Cite the relevant literature, e.g. [1]_.  You may also cite these
    references in the notes section above.

    .. [1] O. McNoleg, "The integration of GIS, remote sensing,
       expert systems and adaptive co-kriging for environmental habitat
       modelling of the Highland Haggis using object-oriented, fuzzy-logic
       and neural-network techniques," Computers & Geosciences, vol. 22,
       pp. 585-588, 1996.

    Examples
    --------
    These are written in doctest format, and should illustrate how to
    use the function.

    >>> a=[1,2,3]
    >>> print [x + 3 for x in a]
    [4, 5, 6]
    >>> print "a\n\nb"
    a
    b

    """
    # TODO@Günni
    if isinstance(nuts, str):
        nuts = [nuts, 'xyz']
    logging.debug('Getting polygon from DB')
    sql = '''
        SELECT st_astext(ST_Transform(st_union(geom), 4326))
        FROM oemof.geo_nuts_rg_2013
        WHERE nuts_id in {0};
    '''.format(
        tuple(nuts)
    )
    return wkt_loads(conn.execute(sql).fetchone()[0])


def get_polygon_from_postgis(conn, schema, table, gcol='geom', union=False):
    r"""A one-line summary that does not use variable names or the
    function name.

    Several sentences providing an extended description. Refer to
    variables using back-ticks, e.g. `var`.

    Parameters
    ----------
    var1 : array_like
        Array_like means all those objects -- lists, nested lists, etc. --
        that can be converted to an array.  We can also refer to
        variables like `var1`.
    var2 : int
        The type above can either refer to an actual Python type
        (e.g. ``int``), or describe the type of the variable in more
        detail, e.g. ``(N,) ndarray`` or ``array_like``.
    Long_variable_name : {'hi', 'ho'}, optional
        Choices in brackets, default first when optional.

    Returns
    -------
    type
        Explanation of anonymous return value of type ``type``.
    describe : type
        Explanation of return value named `describe`.
    out : type
        Explanation of `out`.

    Other Parameters
    ----------------
    only_seldom_used_keywords : type
        Explanation
    common_parameters_listed_above : type
        Explanation

    Raises
    ------
    BadException
        Because you shouldn't have done that.

    See Also
    --------
    otherfunc : relationship (optional)
    newfunc : Relationship (optional), which could be fairly long, in which
              case the line wraps here.
    thirdfunc, fourthfunc, fifthfunc

    Notes
    -----
    Notes about the implementation algorithm (if needed).

    This can have multiple paragraphs.

    You may include some math:

    .. math:: X(e^{j\omega } ) = x(n)e^{ - j\omega n}

    And even use a greek symbol like :math:`omega` inline.

    References
    ----------
    Cite the relevant literature, e.g. [1]_.  You may also cite these
    references in the notes section above.

    .. [1] O. McNoleg, "The integration of GIS, remote sensing,
       expert systems and adaptive co-kriging for environmental habitat
       modelling of the Highland Haggis using object-oriented, fuzzy-logic
       and neural-network techniques," Computers & Geosciences, vol. 22,
       pp. 585-588, 1996.

    Examples
    --------
    These are written in doctest format, and should illustrate how to
    use the function.

    >>> a=[1,2,3]
    >>> print [x + 3 for x in a]
    [4, 5, 6]
    >>> print "a\n\nb"
    a
    b

    """
    # TODO@Günni
    logging.debug('Getting polygon from DB table')
    if union:
        geo_string = 'st_union({0})'.format(gcol)
    else:
        geo_string = '{0}'.format(gcol)

    sql = '''
        SELECT st_astext(ST_Transform({geo_string}, 4326))
        FROM {schema}.{table};
    '''.format(
        **{'geo_string': geo_string, 'schema': schema, 'table': table}
    )
    return wkt_loads(conn.execute(sql).fetchone()[0])


def tz_from_geom(connection, geometry):
    r"""Finding the timezone of a given point or polygon geometry, assuming
    that the polygon is not crossing a border of a timezone. For a given point
    or polygon geometry not located within the timezone dataset (e.g. sea) the
    nearest timezone based on the bounding boxes of the geometries is returned.

    Parameters
    ----------
    connection : sqlalchemy connection object
        A valid connection to a postigs database containing the timezone table
    geometry : shapely geometry object
        A point or polygon object. The polygon should not cross a timezone.

    Returns
    -------
    string
        Timezone using the naming of the IANA time zone database

    References
    ----------
    http://postgis.net/docs/manual-2.2/geometry_distance_box.html
    """

    # TODO@Günni
    if geometry.geom_type in ['Polygon', 'MultiPolygon']:
        coords = geometry.centroid
    else:
        coords = geometry
    sql = """
        SELECT tzid FROM oemof_test.tz_world
        WHERE st_contains(geom, ST_PointFromText('{wkt}', 4326));
        """.format(
        wkt=coords.wkt
    )

    if not connection.execute(sql).fetchone():
        sql = """
            SELECT tzid FROM oemof_test.tz_world
            ORDER BY ST_PointFromText('{wkt}', 4326) <#> geom LIMIT 1;
            """.format(
            wkt=coords.wkt
        )
    return connection.execute(sql).fetchone()[0]


def get_windzone(conn, geometry):
    'Find windzone from map.'
    # TODO@Günni
    if geometry.geom_type in ['Polygon', 'MultiPolygon']:
        coords = geometry.centroid
    else:
        coords = geometry
    sql = """
        SELECT zone FROM oemof_test.windzones
        WHERE st_contains(geom, ST_PointFromText('{wkt}', 4326));
        """.format(
        wkt=coords.wkt
    )
    zone = conn.execute(sql).fetchone()
    if zone is not None:
        zone = zone[0]
    else:
        zone = 0
    return zone


def create_empty_table_serial_primary(
    conn, schema, table, columns=None, id_col='id'
):
    r"""New database table with primary key type serial and empty columns

    Parameters
    ----------
    conn : sqlalchemy connection object
        A valid connection to a database
    schema : str
        The database schema
    table : str
        The database table
    columns : list, optional
        Columns that are to be created
    id_col : str, optional
        Name of index column of database table

    Notes
    -------
    Currently all created table columns will be of type `double precision`.
    Feel free to enhance this function by
    by generalizing this aspect.
    """

    sql_str = """CREATE TABLE {schema}.{table} ({id_col} SERIAL PRIMARY KEY
        NOT NULL)
        """.format(
        schema=schema, table=table, id_col=id_col
    )

    conn.execute(sql_str)

    # define more columns
    if columns is not None:
        for col in columns:
            col_str = """alter table {schema}.{table} add column {col}
                double precision;
                """.format(
                schema=schema, table=table, col=col
            )
            conn.execute(col_str)


def grant_db_access(conn, schema, table, role):
    r"""Gives access to database users/ groups

    Parameters
    ----------
    conn : sqlalchemy connection object
        A valid connection to a database
    schema : str
        The database schema
    table : str
        The database table
    role : str
        database role that access is granted to

    """
    grant_str = """GRANT ALL ON TABLE {schema}.{table}
    TO {role} WITH GRANT OPTION;""".format(
        schema=schema, table=table, role=role
    )

    conn.execute(grant_str)


def add_primary_key(conn, schema, table, pk_col):
    r"""Adds primary key to database table

    Parameters
    ----------
    conn : sqlalchemy connection object
        A valid connection to a database
    schema : str
        The database schema
    table : str
        The database table
    pk_col : str
        Column that primary key is applied to

    """
    sql_str = "alter table {schema}.{table} add primary key ({col})".format(
        schema=schema, table=table, col=pk_col
    )

    conn.execute(sql_str)


def change_owner_to(conn, schema, table, role):
    r"""Changes table's ownership to role

    Parameters
    ----------
    conn : sqlalchemy connection object
        A valid connection to a database
    schema : str
        The database schema
    table : str
        The database table
    role : str
        database role that access is granted to

    """
    sql_str = """ALTER TABLE {schema}.{table}
        OWNER TO {role};""".format(
        schema=schema, table=table, role=role
    )

    conn.execute(sql_str)


def db_table2pandas(conn, schema, table, columns=None):
    if columns is None:
        columns = '*'
    sql = "SELECT {0} FROM {1}.{2};".format(columns, schema, table)
    logging.debug("SQL query: {0}".format(sql))
    results = conn.execute(sql)
    columns = results.keys()
    return pd.DataFrame(results.fetchall(), columns=columns)


1			# -- coding: utf-8 --
2			"""
3			Created on Mon Aug 17 11:08:15 2015
4
5			This is a collection of helper functions which work on there own an can be
6			used by various classes. If there are too many helper-functions, they will
7			be sorted in different modules.
8
9			All special import should be in try/except loops to avoid import errors.
10			"""
11
12			import logging
13			import pandas as pd
14
15
16			# get_polygon_from_nuts
17			hlp_fkt = 'get_polygon_from_nuts'
18			try:
19			from shapely.wkt import loads as wkt_loads
20			except Exception:
21			logging.info(
22			'You will not be able to use the helper function: {0}'.format(hlp_fkt)
23			)
24			logging.info('Install shapely to use it.')
25
26
27			def get_polygons_from_table(conn, schema, table, g_col='geom', n_col='name'):
28			sql = '''
29			SELECT {n_col}, st_astext({g_col})
30			FROM {schema}.{table};
31			'''.format(
32			**{'n_col': n_col, 'g_col': g_col, 'schema': schema, 'table': table}
33			)
34			logging.debug(sql)
35			raw_data = conn.execute(sql).fetchall()
36			polygon_dc = {}
37			for d in raw_data:
38			polygon_dc[d[0]] = [d[0], wkt_loads(d[1])]
39			return polygon_dc
40
41
42			def get_polygon_from_nuts(conn, nuts):
43			r"""A one-line summary that does not use variable names or the
44			function name.
45
46			Several sentences providing an extended description. Refer to
47			variables using back-ticks, e.g. `var`.
48
49			Parameters
50			----------
51			var1 : array_like
52			Array_like means all those objects -- lists, nested lists, etc. --
53			that can be converted to an array. We can also refer to
54			variables like `var1`.
55			var2 : int
56			The type above can either refer to an actual Python type
57			(e.g. ``int``), or describe the type of the variable in more
58			detail, e.g. ``(N,) ndarray`` or ``array_like``.
59			Long_variable_name : {'hi', 'ho'}, optional
60			Choices in brackets, default first when optional.
61
62			Returns
63			-------
64			type
65			Explanation of anonymous return value of type ``type``.
66			describe : type
67			Explanation of return value named `describe`.
68			out : type
69			Explanation of `out`.
70
71			Other Parameters
72			----------------
73			only_seldom_used_keywords : type
74			Explanation
75			common_parameters_listed_above : type
76			Explanation
77
78			Raises
79			------
80			BadException
81			Because you shouldn't have done that.
82
83			See Also
84			--------
85			otherfunc : relationship (optional)
86			newfunc : Relationship (optional), which could be fairly long, in which
87			case the line wraps here.
88			thirdfunc, fourthfunc, fifthfunc
89
90			Notes
91			-----
92			Notes about the implementation algorithm (if needed).
93
94			This can have multiple paragraphs.
95
96			You may include some math:
97
98			.. math:: X(e^{j\omega } ) = x(n)e^{ - j\omega n}
99
100			And even use a greek symbol like :math:`omega` inline.
101
102			References
103			----------
104			Cite the relevant literature, e.g. [1]_. You may also cite these
105			references in the notes section above.
106
107			.. [1] O. McNoleg, "The integration of GIS, remote sensing,
108			expert systems and adaptive co-kriging for environmental habitat
109			modelling of the Highland Haggis using object-oriented, fuzzy-logic
110			and neural-network techniques," Computers & Geosciences, vol. 22,
111			pp. 585-588, 1996.
112
113			Examples
114			--------
115			These are written in doctest format, and should illustrate how to
116			use the function.
117
118			>>> a=[1,2,3]
119			>>> print [x + 3 for x in a]
120			[4, 5, 6]
121			>>> print "a\n\nb"
122			a
123			b
124
125			"""
126			# TODO@Günni
127			if isinstance(nuts, str):
128			nuts = [nuts, 'xyz']
129			logging.debug('Getting polygon from DB')
130			sql = '''
131			SELECT st_astext(ST_Transform(st_union(geom), 4326))
132			FROM oemof.geo_nuts_rg_2013
133			WHERE nuts_id in {0};
134			'''.format(
135			tuple(nuts)
136			)
137			return wkt_loads(conn.execute(sql).fetchone()[0])
138
139
140			def get_polygon_from_postgis(conn, schema, table, gcol='geom', union=False):
141			r"""A one-line summary that does not use variable names or the
142			function name.
143
144			Several sentences providing an extended description. Refer to
145			variables using back-ticks, e.g. `var`.
146
147			Parameters
148			----------
149			var1 : array_like
150			Array_like means all those objects -- lists, nested lists, etc. --
151			that can be converted to an array. We can also refer to
152			variables like `var1`.
153			var2 : int
154			The type above can either refer to an actual Python type
155			(e.g. ``int``), or describe the type of the variable in more
156			detail, e.g. ``(N,) ndarray`` or ``array_like``.
157			Long_variable_name : {'hi', 'ho'}, optional
158			Choices in brackets, default first when optional.
159
160			Returns
161			-------
162			type
163			Explanation of anonymous return value of type ``type``.
164			describe : type
165			Explanation of return value named `describe`.
166			out : type
167			Explanation of `out`.
168
169			Other Parameters
170			----------------
171			only_seldom_used_keywords : type
172			Explanation
173			common_parameters_listed_above : type
174			Explanation
175
176			Raises
177			------
178			BadException
179			Because you shouldn't have done that.
180
181			See Also
182			--------
183			otherfunc : relationship (optional)
184			newfunc : Relationship (optional), which could be fairly long, in which
185			case the line wraps here.
186			thirdfunc, fourthfunc, fifthfunc
187
188			Notes
189			-----
190			Notes about the implementation algorithm (if needed).
191
192			This can have multiple paragraphs.
193
194			You may include some math:
195
196			.. math:: X(e^{j\omega } ) = x(n)e^{ - j\omega n}
197
198			And even use a greek symbol like :math:`omega` inline.
199
200			References
201			----------
202			Cite the relevant literature, e.g. [1]_. You may also cite these
203			references in the notes section above.
204
205			.. [1] O. McNoleg, "The integration of GIS, remote sensing,
206			expert systems and adaptive co-kriging for environmental habitat
207			modelling of the Highland Haggis using object-oriented, fuzzy-logic
208			and neural-network techniques," Computers & Geosciences, vol. 22,
209			pp. 585-588, 1996.
210
211			Examples
212			--------
213			These are written in doctest format, and should illustrate how to
214			use the function.
215
216			>>> a=[1,2,3]
217			>>> print [x + 3 for x in a]
218			[4, 5, 6]
219			>>> print "a\n\nb"
220			a
221			b
222
223			"""
224			# TODO@Günni
225			logging.debug('Getting polygon from DB table')
226			if union:
227			geo_string = 'st_union({0})'.format(gcol)
228			else:
229			geo_string = '{0}'.format(gcol)
230
231			sql = '''
232			SELECT st_astext(ST_Transform({geo_string}, 4326))
233			FROM {schema}.{table};
234			'''.format(
235			**{'geo_string': geo_string, 'schema': schema, 'table': table}
236			)
237			return wkt_loads(conn.execute(sql).fetchone()[0])
238
239
240			def tz_from_geom(connection, geometry):
241			r"""Finding the timezone of a given point or polygon geometry, assuming
242			that the polygon is not crossing a border of a timezone. For a given point
243			or polygon geometry not located within the timezone dataset (e.g. sea) the
244			nearest timezone based on the bounding boxes of the geometries is returned.
245
246			Parameters
247			----------
248			connection : sqlalchemy connection object
249			A valid connection to a postigs database containing the timezone table
250			geometry : shapely geometry object
251			A point or polygon object. The polygon should not cross a timezone.
252
253			Returns
254			-------
255			string
256			Timezone using the naming of the IANA time zone database
257
258			References
259			----------
260			http://postgis.net/docs/manual-2.2/geometry_distance_box.html
261			"""
262
263			# TODO@Günni
264			if geometry.geom_type in ['Polygon', 'MultiPolygon']:
265			coords = geometry.centroid
266			else:
267			coords = geometry
268			sql = """
269			SELECT tzid FROM oemof_test.tz_world
270			WHERE st_contains(geom, ST_PointFromText('{wkt}', 4326));
271			""".format(
272			wkt=coords.wkt
273			)
274
275			if not connection.execute(sql).fetchone():
276			sql = """
277			SELECT tzid FROM oemof_test.tz_world
278			ORDER BY ST_PointFromText('{wkt}', 4326) <#> geom LIMIT 1;
279			""".format(
280			wkt=coords.wkt
281			)
282			return connection.execute(sql).fetchone()[0]
283
284
285			def get_windzone(conn, geometry):
286			'Find windzone from map.'
287			# TODO@Günni
288			if geometry.geom_type in ['Polygon', 'MultiPolygon']:
289			coords = geometry.centroid
290			else:
291			coords = geometry
292			sql = """
293			SELECT zone FROM oemof_test.windzones
294			WHERE st_contains(geom, ST_PointFromText('{wkt}', 4326));
295			""".format(
296			wkt=coords.wkt
297			)
298			zone = conn.execute(sql).fetchone()
299			if zone is not None:
300			zone = zone[0]
301			else:
302			zone = 0
303			return zone
304
305
306			def create_empty_table_serial_primary(
307			conn, schema, table, columns=None, id_col='id'
308			):
309			r"""New database table with primary key type serial and empty columns
310
311			Parameters
312			----------
313			conn : sqlalchemy connection object
314			A valid connection to a database
315			schema : str
316			The database schema
317			table : str
318			The database table
319			columns : list, optional
320			Columns that are to be created
321			id_col : str, optional
322			Name of index column of database table
323
324			Notes
325			-------
326			Currently all created table columns will be of type `double precision`.
327			Feel free to enhance this function by
328			by generalizing this aspect.
329			"""
330
331			sql_str = """CREATE TABLE {schema}.{table} ({id_col} SERIAL PRIMARY KEY
332			NOT NULL)
333			""".format(
334			schema=schema, table=table, id_col=id_col
335			)
336
337			conn.execute(sql_str)
338
339			# define more columns
340			if columns is not None:
341			for col in columns:
342			col_str = """alter table {schema}.{table} add column {col}
343			double precision;
344			""".format(
345			schema=schema, table=table, col=col
346			)
347			conn.execute(col_str)
348
349
350			def grant_db_access(conn, schema, table, role):
351			r"""Gives access to database users/ groups
352
353			Parameters
354			----------
355			conn : sqlalchemy connection object
356			A valid connection to a database
357			schema : str
358			The database schema
359			table : str
360			The database table
361			role : str
362			database role that access is granted to
363
364			"""
365			grant_str = """GRANT ALL ON TABLE {schema}.{table}
366			TO {role} WITH GRANT OPTION;""".format(
367			schema=schema, table=table, role=role
368			)
369
370			conn.execute(grant_str)
371
372
373			def add_primary_key(conn, schema, table, pk_col):
374			r"""Adds primary key to database table
375
376			Parameters
377			----------
378			conn : sqlalchemy connection object
379			A valid connection to a database
380			schema : str
381			The database schema
382			table : str
383			The database table
384			pk_col : str
385			Column that primary key is applied to
386
387			"""
388			sql_str = "alter table {schema}.{table} add primary key ({col})".format(
389			schema=schema, table=table, col=pk_col
390			)
391
392			conn.execute(sql_str)
393
394
395			def change_owner_to(conn, schema, table, role):
396			r"""Changes table's ownership to role
397
398			Parameters
399			----------
400			conn : sqlalchemy connection object
401			A valid connection to a database
402			schema : str
403			The database schema
404			table : str
405			The database table
406			role : str
407			database role that access is granted to
408
409			"""
410			sql_str = """ALTER TABLE {schema}.{table}
411			OWNER TO {role};""".format(
412			schema=schema, table=table, role=role
413			)
414
415			conn.execute(sql_str)
416
417
418			def db_table2pandas(conn, schema, table, columns=None):
419			if columns is None:
420			columns = '*'
421			sql = "SELECT {0} FROM {1}.{2};".format(columns, schema, table)
422			logging.debug("SQL query: {0}".format(sql))
423			results = conn.execute(sql)
424			columns = results.keys()
425			return pd.DataFrame(results.fetchall(), columns=columns)
426

oemof / oemof.db

db.tools.add_primary_key() A last analyzed 2019-12-05 17:24 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

db.tools.add_primary_key() A
last analyzed 2019-12-05 17:24 UTC