def geodf_to_psql(conParam,
df,
pgtable,
epsg,
geomType,
colGeom="geometry",
isAppend=None):
"""
Pandas GeoDataframe to PGSQL table
"""
from geoalchemy2 import Geometry, WKTElement
from gasp.sql.c import alchemy_engine
pgengine = alchemy_engine(conParam, api='psql')
df["geom"] = df[colGeom].apply(lambda x: WKTElement(x.wkt, srid=epsg))
if colGeom != 'geom':
df.drop(colGeom, axis=1, inplace=True)
df.to_sql(pgtable,
pgengine,
if_exists='replace' if not isAppend else 'append',
index=False,
dtype={'geom': Geometry(geomType, srid=epsg)})
return pgtable
def q_to_obj(dbname,
query,
db_api='psql',
geomCol=None,
epsg=None,
of='df',
cols=None):
"""
Query database and convert data to Pandas Dataframe/GeoDataFrame
API's Available:
* psql;
* sqlite;
* mysql;
output format options ("of" parameter):
* df (Pandas Dataframe);
* dict (Python Dict);
"""
if not query.startswith('SELECT '):
# Assuming query is a table name
from gasp.pyt import obj_to_lst
from gasp.sql.i import cols_name
cols = cols_name(dbname, query) if not cols else \
obj_to_lst(cols)
query = "SELECT {} FROM {}".format(
", ".join(["{t}.{c} AS {c}".format(t=query, c=i) for i in cols]),
query)
if not geomCol:
import pandas
from gasp.sql.c import alchemy_engine
pgengine = alchemy_engine(dbname, api=db_api)
df = pandas.read_sql(query, pgengine, columns=None)
else:
from geopandas import GeoDataFrame
from gasp.sql.c import sqlcon
con = sqlcon(dbname, sqlAPI='psql')
df = GeoDataFrame.from_postgis(
query,
con,
geom_col=geomCol,
crs="epsg:{}".format(str(epsg)) if epsg else None)
if of == 'dict':
df = df.to_dict(orient="records")
return df
def query_to_df(conParam, query, db_api='psql'):
"""
Query database and convert data to Pandas Dataframe
API's Available:
* psql;
* sqlite;
"""
import pandas
from gasp.sql.c import alchemy_engine
pgengine = alchemy_engine(conParam, api=db_api)
df = pandas.read_sql(query, pgengine, columns=None)
return df
def df_to_db(db, df, table, append=None, api='psql',
epsg=None, geomType=None, colGeom='geometry'):
"""
Pandas Dataframe/GeoDataFrame to PGSQL table
API options:
* psql;
* sqlite
"""
if api != 'psql' and api != 'sqlite':
raise ValueError('API {} is not available'.format(api))
from gasp.sql.c import alchemy_engine
pgengine = alchemy_engine(db, api=api)
if epsg and geomType:
from geoalchemy2 import Geometry, WKTElement
newdf = df.copy()
newdf["geom"] = newdf[colGeom].apply(
lambda x : WKTElement(x.wkt, srid=epsg)
)
if colGeom != 'geom':
newdf.drop(colGeom, axis=1, inplace=True)
newdf.to_sql(
table, pgengine,
if_exists='replace' if not append else 'append',
index=False, dtype={"geom" : Geometry(geomType, srid=epsg)}
)
else:
df.to_sql(
table, pgengine,
if_exists='replace' if not append else 'append',
index=False
)
return table
def df_to_db(conParam, df, table, append=None, api='psql'):
"""
Pandas Dataframe to PGSQL table
API options:
* psql;
* sqlite
"""
if api != 'psql' and api != 'sqlite':
raise ValueError('API {} is not available'.format(api))
from gasp.sql.c import alchemy_engine
pgengine = alchemy_engine(conParam, api=api)
df.to_sql(table,
pgengine,
if_exists='replace' if not append else 'append',
index=False)
return table
def lst_tbl(db, schema='public', excludeViews=None, api='psql', basename=None):
"""
list tables in a database
API's Available:
* psql;
* sqlite;
* mysql;
"""
from gasp.pyt import obj_to_lst
basename = obj_to_lst(basename)
basenameStr = "" if not basename else "{}".format(" OR ".join([
"{} LIKE '%%{}%%'".format("table_name" if api == 'psql' else "name", b)
for b in basename
]))
if api == 'psql':
from gasp.sql.fm import q_to_obj
Q = ("SELECT table_name FROM information_schema.tables "
"WHERE table_schema='{}'{}").format(
schema,
"" if not basename else " AND ({})".format(basenameStr))
tbls = q_to_obj(db, Q, db_api='psql')
if excludeViews:
views = lst_views(db, schema=schema)
__tbls = [i for i in tbls.table_name.tolist() if i not in views]
else:
__tbls = tbls.table_name.tolist()
elif api == 'sqlite':
"""
List tables in one sqliteDB
"""
import sqlite3
conn = sqlite3.connect(db)
cursor = conn.cursor()
tables = cursor.execute(
"SELECT name FROM sqlite_master WHERE type='table'{};".format(
"" if not basename else " AND ({})".format(basenameStr)))
__tbls = [n[0] for n in tables]
cursor.close()
conn.close()
elif api == 'mysql':
"""
List Tables in MySQL Database
"""
from gasp.sql.c import alchemy_engine
c = alchemy_engine(db, api='mysql')
__tbls = c.table_names()
else:
raise ValueError('API {} is not available!'.format(api))
return __tbls