def cols_name(conparam, table, sanitizeSpecialWords=True, api='psql'): """ Return the columns names of a table in one Database """ if api == 'psql': from gasp.sql import pgsql_special_words c = psqlcon(conparam) cursor = c.cursor() cursor.execute("SELECT * FROM {} LIMIT 50;".format(table)) colnames = [desc[0] for desc in cursor.description] if sanitizeSpecialWords: # Prepare one wayout for special words special_words = pgsql_special_words() for i in range(len(colnames)): if colnames[i] in special_words: colnames[i] = '"{}"'.format(colnames[i]) elif api == 'sqlite': import sqlite3 con = sqlite3.connect(conparam) cursor = con.execute("SELECT * FROM {}".format(table)) colnames = list(map(lambda x: x[0], cursor.description)) else: raise ValueError('API {} is not available'.format(api)) return colnames
def replace_null_with_other_col_value(con_pgsql, pgtable, nullFld, replaceFld): """ Do the following Convert the next table: FID | COL1 | COL2 0 | 1 | -99 1 | 2 | -99 2 | NULL | -88 3 | NULL | -87 4 | 7 | -99 5 | 9 | -99 Into: FID | COL1 | COL2 0 | 1 | -99 1 | 2 | -99 2 | -88 | -88 3 | -87 | -87 4 | 7 | -99 5 | 9 | -99 """ con = psqlcon(con_pgsql) cursor = con.cursor() cursor.execute( "UPDATE {t} SET {nullF}=COALESCE({repF}) WHERE {nullF} IS NULL".format( t=pgtable, nullF=nullFld, repF=replaceFld)) con.commit() cursor.close() con.close()
def get_columns_type(pgsqlDic, table, sanitizeColName=True, pyType=True): """ Return columns names and types of a PostgreSQL table """ from gasp.sql import pgsql_special_words c = psqlcon(pgsqlDic) cursor = c.cursor() cursor.execute("SELECT * FROM {} LIMIT 50;".format(table)) coltypes = { desc[0]: map_psqltypes(desc[1], python=pyType) for desc in cursor.description } if sanitizeColName: # Prepare one wayout for special words special_words = pgsql_special_words() for name in coltypes: if name in special_words: n = '"{}"'.format(name) coltypes[n] = coltypes[name] del coltypes[name] return coltypes
def change_field_type(lnk, table, fields, outable, cols=None): """ Imagine a table with numeric data saved as text. This method convert that numeric data to a numeric field. fields = {'field_name' : 'field_type'} """ if not cols: cols = cols_name(lnk, table) else: from gasp import goToList cols = goToList(cols) select_fields = [f for f in cols if f not in fields] con = psqlcon(lnk) # Create new table with the new field with converted values cursor = con.cursor() cursor.execute(('CREATE TABLE {} AS SELECT {}, {} FROM {}').format( outable, ', '.join(select_fields), ', '.join([ 'CAST({f_} AS {t}) AS {f_}'.format(f_=f, t=fields[f]) for f in fields ]), table)) con.commit() cursor.close() con.close()
def create_db(lnk, newdb, overwrite=True, api='psql'): """ Create Relational Database APIS Available: * psql; * sqlite; """ if api == 'psql': def drop(cursor, database): cursor.execute("DROP DATABASE {};".format(database)) if "DATABASE" in lnk: raise ValueError("For this method, the dict used to connected to " "PostgreSQL could not have a DATABASE key") dbs = list_db(lnk) con = psqlcon(lnk) cs = con.cursor() if newdb in dbs and overwrite: drop(cs, newdb) cs.execute( "CREATE DATABASE {}{};".format( newdb, " TEMPLATE={}".format(lnk["TEMPLATE"]) \ if "TEMPLATE" in lnk else "" ) ) cs.close() con.close() elif api == 'sqlite': import os import sqlite3 try: DB = os.path.join(lnk, newdb) if os.path.exists(DB) and overwrite: from gasp.oss.ops import del_file del_file(os.path.join(DB)) conn = sqlite3.connect(DB) except Error as e: print e finally: conn.close() else: raise ValueError('API {} is not available'.format(api)) return newdb
def update_table(con_pgsql, pg_table, dic_new_values, dic_ref_values=None, logic_operator='OR'): """ Update Values on a PostgreSQL table new_values and ref_values are dict with fields as keys and values as keys values. If the values (ref and new) are strings, they must be inside '' e.g. dic_new_values = {field: '\'value\''} """ from gasp.sql.c import psqlcon __logic_operator = ' OR ' if logic_operator == 'OR' else ' AND ' \ if logic_operator == 'AND' else None if not __logic_operator: raise ValueError(('Defined operator is not valid.\n ' 'The valid options are \'OR\' and \'AND\'')) con = psqlcon(con_pgsql) cursor = con.cursor() if dic_ref_values: whrLst = [] for x in dic_ref_values: if dic_ref_values[x] == 'NULL': whrLst.append('{} IS NULL'.format(x)) else: whrLst.append('{}={}'.format(x, dic_ref_values[x])) whr = " WHERE {}".format(__logic_operator.join(whrLst)) else: whr = "" update_query = "UPDATE {tbl} SET {pair_new}{where};".format( tbl=pg_table, pair_new=",".join([ "{fld}={v}".format(fld=x, v=dic_new_values[x]) for x in dic_new_values ]), where=whr) cursor.execute(update_query) con.commit() cursor.close() con.close()
def create_tbl(conParam, table, fields, orderFields=None, api='psql'): """ Create Table in Database API's Available: * psql; * sqlite; """ if api == 'psql': from gasp.sql.c import psqlcon ordenedFields = orderFields if orderFields else fields.keys() con = psqlcon(conParam) cursor = con.cursor() cursor.execute( "CREATE TABLE {} ({})".format( table, ', '.join([ '{} {}'.format( ordenedFields[x], fields[ordenedFields[x]] ) for x in range(len(ordenedFields)) ]) ) ) con.commit() cursor.close() con.close() elif api == 'sqlite': import sqlite3 conn = sqlite3.connect(conParam) cursor = conn.cursor() cursor.execute( "CREATE TABLE {} ({})".format( table, ', '.join([ "{} {}".format(k, fields[k]) for k in fields ]) ) ) conn.commit() cursor.close() conn.close() return table
def list_db(conParam): """ List all PostgreSQL databases """ con = psqlcon(conParam) cursor = con.cursor() cursor.execute("SELECT datname FROM pg_database") return [d[0] for d in cursor.fetchall()]
def db_exists(lnk, db): """ Database exists """ con = psqlcon(lnk) cursor = con.cursor() cursor.execute("SELECT datname FROM pg_database") dbs = [d[0] for d in cursor.fetchall()] return 1 if db in dbs else 0
def drop_where_cols_are_same(conParam, table, colA, colB): """ Delete rows Where colA has the same value than colB """ from gasp.sql.c import psqlcon con = psqlcon(conParam) cursor = con.cursor() cursor.execute('DELETE FROM {} WHERE {}={}'.format(table, colA, colB)) con.commit() cursor.close() con.close()
def psql_to_geodf(conParam, query, geomCol='geom', epsg=None): """ Query database and convert data to Pandas GeoDataframe """ from geopandas import GeoDataFrame from gasp.sql.c import psqlcon con = psqlcon(conParam) df = GeoDataFrame.from_postgis( query, con, geom_col=geomCol, crs="epsg:{}".format(str(epsg)) if epsg else None) return df
def drop_table_data(dic_con, table, where=None): """ Delete all data on a PGSQL Table """ from gasp.sql.c import psqlcon con = psqlcon(dic_con) cursor = con.cursor() cursor.execute("DELETE FROM {}{};".format( table, "" if not where else " WHERE {}".format(where))) con.commit() cursor.close() con.close()
def distinct_to_table(lnk, pgtable, columns, outable): """ Distinct values of one column to a new table """ con = psqlcon(lnk) cs = con.cursor() cs.execute( "CREATE TABLE {nt} AS SELECT {cls} FROM {t} GROUP BY {cls}".format( nt=outable, cls=', '.join(goToList(columns)), t=pgtable)) con.commit() cs.close() con.close() return outable
def ntbl_by_query(lnk, outbl, query, ntblIsView=None, api='psql'): """ Create table by query API's Available: * psql; * ogr2ogr """ if api == 'psql': from gasp.sql.c import psqlcon con = psqlcon(lnk) curs = con.cursor() _q = "CREATE {} {} AS {}".format("TABLE" if not ntblIsView else "VIEW", outbl, query) curs.execute(_q) con.commit() curs.close() con.close() elif api == 'ogr2ogr': """ Execute a SQL Query in a SQLITE Database and store the result in the same database. Uses OGR2OGR instead of the regular SQLITE API """ from gasp import exec_cmd cmd = ('ogr2ogr -update -append -f "SQLite" {db} -nln "{nt}" ' '-dialect sqlite -sql "{q}" {db}').format(db=lnk, nt=outbl, q=query) outcmd = exec_cmd(cmd) else: raise ValueError('API {} is not available!'.format(api)) return outbl
def rename_tbl(conParam, table, newName): """ Rename PGSQL Table """ from gasp.sql.c import psqlcon con = psqlcon(conParam) cursor = con.cursor() cursor.execute("ALTER TABLE {} RENAME TO {}".format(table, newName)) con.commit() cursor.close() con.close() return newName
def drop_column(lnk, pg_table, columns): """ Delete column from pg_table """ from gasp import goToList con = psqlcon(lnk) cursor = con.cursor() columns = goToList(columns) cursor.execute('ALTER TABLE {} {};'.format( pg_table, ', '.join(['DROP COLUMN {}'.format(x) for x in columns]))) con.commit() cursor.close() con.close()
def add_idx_to_geom(conParam, table, geomCol): """ Add index to Geometry """ from gasp.sql.c import psqlcon con = psqlcon(conParam) cursor = con.cursor() cursor.execute( "CREATE INDEX {tbl}_{col}_idx ON {tbl} USING gist ({col})".format( tbl=table, col=geomCol)) con.commit() cursor.close() con.close() return table
def add_field(lnk, pgtable, columns): """ Add new field to a table """ # Verify is columns is a dict if type(columns) != dict: raise ValueError( 'columns should be a dict (name as keys; field type as values)') con = psqlcon(lnk) cursor = con.cursor() cursor.execute("ALTER TABLE {} ADD {};".format( pgtable, ", ".join(["{} {}".format(x, columns[x]) for x in columns]))) con.commit() cursor.close() con.close()
def lst_tbl_basename(basename, dic_con, schema='public'): """ List tables with name that includes basename """ from gasp.sql.c import psqlcon conn = psqlcon(dic_con) cs = conn.cursor() cs.execute(("SELECT table_name FROM information_schema.tables " "WHERE table_schema='{}' AND table_name LIKE '%{}%'".format( schema, basename))) f = [x[0] for x in cs.fetchall()] cs.close() conn.close() return f
def exec_write_q(conDB, queries, api='psql'): """ Execute Queries and save result in the database """ from gasp import goToList qs = goToList(queries) if not qs: raise ValueError("queries value is not valid") if api == 'psql': from gasp.sql.c import psqlcon con = psqlcon(conDB) cs = con.cursor() for q in qs: cs.execute(q) con.commit() cs.close() con.close() elif api == 'sqlite': import sqlite3 con = sqlite3.connect(conDB) cs = con.cursor() for q in qs: cs.execute(q) con.commit() cs.close() con.close() else: raise ValueError('API {} is not available'.format(api))
def check_last_id(lnk, pk, table): """ Check last ID of a given table return 0 if there is no data TODO: Do this with Pandas """ from gasp.sql.c import psqlcon con = psqlcon(lnk) cs = con.cursor() cs.execute("SELECT {fid} FROM {tbl};".format(fid=pk, tbl=table)) f = [x[0] for x in cs.fetchall()] cs.close() con.close() if len(f) == 0: return 0 else: return max(f)
def drop_db(lnk, database): """ Delete PostgreSQL database Return 0 if the database does not exist """ if "DATABASE" in lnk: raise ValueError("For this method, the dict used to connected to " "PostgreSQL could not have a DATABASE key") databases = list_db(lnk) if database not in databases: return 0 con = psqlcon(lnk) cursor = con.cursor() cursor.execute("DROP DATABASE {};".format(database)) cursor.close() con.close()
def del_tables(lnk, pg_table_s, isViews=None): """ Delete all tables in pg_table_s """ from gasp import goToList from gasp.sql.c import psqlcon pg_table_s = goToList(pg_table_s) con = psqlcon(lnk) l = [] for i in range(0, len(pg_table_s), 100): l.append(pg_table_s[i:i + 100]) for lt in l: cursor = con.cursor() cursor.execute('DROP {} IF EXISTS {};'.format( 'TABLE' if not isViews else 'VIEW', ', '.join(lt))) con.commit() cursor.close() con.close()
def intersection(lnk, aShp, bShp, pk, aGeom, bGeom, output, primitive, priority, new_pk='fid_pk', new_geom='geom'): """ Intersect two layers primitive is the geometric primitive (point, line, polygons) priority is an indication of the fields that the user wants to include in the output - fields of aShp or fields of bShp. The user could giver a list (with fields for selection) as value for the priority argument. """ from gasp.sql.mng.fld import cols_name if priority == 'a': cols_tbl = cols_name(lnk, aShp) cols_tbl.remove(aGeom) elif priority == 'b': cols_tbl = cols_name(lnk, bShp) cols_tbl.remove(bGeom) elif type(priority) == type([0]): cols_tbl = priority cols_tbl.remove(pk) conn = psqlcon( lnk['HOST'], lnk['USER'], lnk['PASSWORD'], lnk['PORT'], lnk['DATABASE'] ) cursor = conn.cursor() if primitive == 'point': cols_tbl = ['{t}.{c}'.format(t=aShp, c=x) for x in cols_tbl] if priority == 'a': sel_geom = "{f}.{g}".format(f=aShp, g=aGeom) elif priority == 'b' or type(priority) == type([]): sel_geom = "{f}.{g}".format(f=bShp, g=bGeom) cursor.execute(( "CREATE TABLE {out} AS SELECT {cols}, {int_geom} AS {ngeom} FROM {pnt} " "INNER JOIN {poly} ON ST_Within({pnt}.{geom_a}, " "{poly}.{geom_b});").format( out=output, cols=','.join(cols_tbl), pnt=aShp, geom_a=aGeom, geom_b=bGeom, poly=bShp, int_geom=sel_geom, ngeom=new_geom )) elif primitive == 'line': cols_tbl = ['{t}.{c}'.format(t=output, c=x) for x in cols_tbl] cols_tbl.append(new_geom) cursor.execute(( "CREATE TABLE {out} AS SELECT {cols} FROM (SELECT {shp_a}.*, " "(ST_DUMP(ST_Intersection({shp_b}.geom, {shp_a}.{geom_fld}))).geom " "FROM {shp_b} INNER JOIN {shp_a} ON ST_Intersects({shp_b}.geom, " "{shp_a}.{geom_fld})) As {out} WHERE ST_Dimension({out}.geom) = " "1;").format( out=output, cols=','.join(cols_tbl), shp_a=aShp, shp_b=bShp, geom_fld=aGeom )) elif primitive == 'polygon': cols_tbl = ['{t}.{c}'.format(t=aShp, c=x) for x in cols_tbl] cursor.execute(( 'CREATE TABLE {out} AS SELECT {cols}, ST_Multi(ST_Buffer' '(ST_Intersection({shp_b}.geom, {shp_a}.{geom_fld}), 0.0)) As ' '{ngeom} FROM {shp_b} INNER JOIN {shp_a} ON ST_Intersects({shp_b}.geom, ' '{shp_a}.{geom_fld}) WHERE Not ST_IsEmpty(ST_Buffer(' 'ST_Intersection({shp_b}.geom, {shp_a}.{geom_fld}), 0.0));').format( out=output, cols=','.join(cols_tbl), shp_a=aShp, shp_b = bShp, geom_fld=aGeom, ngeom=new_geom )) cursor.execute( "ALTER TABLE {out} ADD COLUMN {fid_pk} BIGSERIAL PRIMARY KEY;".format( out=output, fid_pk=new_pk)) conn.commit() cursor.close() conn.close() return output, new_pk, new_geom