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