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
