def restore_tbls(dbn, sql, tablenames=None):
"""
Restore tables from a sql Script
TODO: add mysql option
"""
from gasp import exec_cmd
from gasp.cons.psql import con_psql
from gasp.pyt import obj_to_lst
condb = con_psql()
tbls = obj_to_lst(tablenames)
tblStr = "" if not tablenames else " {}".format(" ".join([
"-t {}".format(t) for t in tbls]))
outcmd = exec_cmd((
"pg_restore -U {user} -h {host} -p {port} "
"-w{tbl} -d {db} {sqls}"
).format(
user=condb["USER"], host=condb["HOST"],
port=condb["PORT"], db=dbn, sqls=sql, tbl=tblStr
))
return tablenames
def dump_db(db, outSQL, api='psql'):
"""
DB to SQL Script
"""
from gasp import exec_cmd
if api == 'psql':
from gasp.cons.psql import con_psql
condb = con_psql()
cmd = "pg_dump -U {} -h {} -p {} -w {} > {}".format(
condb["USER"], condb["HOST"], condb["PORT"], db, outSQL)
elif api == 'mysql':
from gasp.cons.mysql import con_mysql
condb = con_mysql()
cmd = ("mysqldump -u {} --port {} -p{} --host {} "
"{} > {}").format(condb["USER"], condb["PORT"],
condb["PASSWORD"], condb["HOST"], db, outSQL)
else:
raise ValueError('{} API is not available'.format(api))
outcmd = exec_cmd(cmd)
return outSQL
def osm_to_psql(osmXml, osmdb):
"""
Use GDAL to import osmfile into PostGIS database
"""
from gasp import exec_cmd
from gasp.cons.psql import con_psql
from gasp.sql.i import db_exists
is_db = db_exists(osmdb)
if not is_db:
from gasp.sql.db import create_db
create_db(osmdb, api='psql')
con = con_psql()
cmd = ("ogr2ogr -f PostgreSQL \"PG:dbname='{}' host='{}' port='{}' "
"user='******' password='******'\" {} -lco COLUM_TYPES=other_tags=hstore"
).format(osmdb, con["HOST"], con["PORT"], con["USER"],
con["PASSWORD"], osmXml)
cmdout = exec_cmd(cmd)
return osmdb
def psql_cmd(db_name, sqlfile, dbcon=None):
"""
Run a sql file do whatever is on that script
"""
import os
from gasp import exec_cmd
from gasp.cons.psql import con_psql
cdb = con_psql(db_set=dbcon)
if os.path.isdir(sqlfile):
from gasp.pyt.oss import lst_ff
sqls = lst_ff(sqlfile, file_format='.sql')
else:
sqls = [sqlfile]
cmd = 'psql -h {} -U {} -p {} -w {} < {}'
for s in sqls:
outcmd = exec_cmd(
cmd.format(cdb['HOST'], cdb['USER'], cdb['PORT'], db_name, s))
return db_name
def create_db(newdb, overwrite=True, api='psql', use_template=True):
"""
Create Relational Database
APIS Available:
* psql;
* sqlite;
"""
if api == 'psql':
from gasp.sql.c import sqlcon
from gasp.sql.i import lst_db
from gasp.cons.psql import con_psql
conparam = con_psql()
dbs = lst_db()
con = sqlcon(None, sqlAPI='psql')
cs = con.cursor()
if newdb in dbs and overwrite:
cs.execute("DROP DATABASE {};".format(newdb))
cs.execute("CREATE DATABASE {}{};".format(
newdb, " TEMPLATE={}".format(conparam["TEMPLATE"]) \
if "TEMPLATE" in conparam and use_template else ""
)
)
cs.close()
con.close()
elif api == 'sqlite':
import os
import sqlite3
try:
if os.path.exists(newdb) and overwrite:
from gasp.pyt.oss import del_file
del_file(newdb)
conn = sqlite3.connect(newdb)
except Error as e:
print(e)
finally:
conn.close()
else:
raise ValueError('API {} is not available'.format(api))
return newdb
def sqlcon(db, sqlAPI='psql'):
"""
Connect to PostgreSQL Database
"""
if sqlAPI == 'psql':
import psycopg2
from gasp.cons.psql import con_psql
conparam = con_psql()
try:
if not db:
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
c = psycopg2.connect(user=conparam["USER"],
password=conparam["PASSWORD"],
host=conparam["HOST"],
port=conparam["PORT"])
c.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
else:
c = psycopg2.connect(
database=db,
user=conparam["USER"],
password=conparam["PASSWORD"],
host=conparam["HOST"],
port=conparam["PORT"],
)
return c
except psycopg2.Error as e:
raise ValueError(str(e))
elif sqlAPI == 'mysql':
import mysql.connector
from gasp.cons.mysql import con_mysql
conparam = con_mysql()
c = mysql.connector.connect(user=conparam["USER"],
password=conparam["PASSWORD"],
host=conparam["HOST"],
database=db,
port=conparam["PORT"])
return c
else:
raise ValueError("{} API is not available".format(sqlAPI))
def dump_tbls(db, tables, outsql, startWith=None):
"""
Dump one table into a SQL File
"""
from gasp import exec_cmd
from gasp.pyt import obj_to_lst
from gasp.cons.psql import con_psql
tbls = obj_to_lst(tables)
if startWith:
from gasp.sql.i import lst_tbl
db_tbls = lst_tbl(db, api='psql')
dtbls = []
for t in db_tbls:
for b in tbls:
if t.startswith(b):
dtbls.append(t)
tbls = dtbls
condb = con_psql()
outcmd = exec_cmd(("pg_dump -Fc -U {user} -h {host} -p {port} "
"-w {tbl} {db} > {out}").format(
user=condb["USER"],
host=condb["HOST"],
port=condb["PORT"],
db=db,
out=outsql,
tbl=" ".join(["-t {}".format(t) for t in tbls])))
return outsql
def dbtbl_to_shp(db, tbl, geom_col, outShp, where=None, inDB='psql',
notTable=None, filterByReg=None, outShpIsGRASS=None,
tableIsQuery=None, api='psql', epsg=None):
"""
Database Table to Feature Class file
idDB Options:
* psql
* sqlite
api Options:
* psql
* sqlite
* pgsql2shp
if outShpIsGRASS if true, the method assumes that outShp is
a GRASS Vector. That implies that a GRASS Session was been
started already.
"""
from gasp.gt.toshp import df_to_shp
if outShpIsGRASS:
from gasp import exec_cmd
from gasp.cons.psql import con_psql
db_con = con_psql()
whr = "" if not where else " where=\"{}\"".format(where)
cmd_str = (
"v.in.ogr input=\"PG:host={} dbname={} user={} password={} "
"port={}\" output={} layer={} geometry={}{}{}{} -o --overwrite --quiet"
).format(
db_con["HOST"], db, db_con["USER"], db_con["PASSWORD"],
db_con["PORT"], outShp, tbl, geom_col, whr,
" -t" if notTable else "", " -r" if filterByReg else ""
) if inDB == 'psql' else (
"v.in.ogr -o input={} layer={} output={}{}{}{}"
).format(db, tbl, outShp, whr,
" -t" if notTable else "", " -r" if filterByReg else ""
) if inDB == 'sqlite' else None
rcmd = exec_cmd(cmd_str)
else:
if api == 'pgsql2shp':
from gasp import exec_cmd
from gasp.cons.psql import con_psql
db_con = con_psql()
outcmd = exec_cmd((
'pgsql2shp -f {out} -h {hst} -u {usr} -p {pt} -P {pas}{geom} '
'{bd} {t}'
).format(
hst=db_con['HOST'], usr=db_con["USER"], pt=db_con["PORT"],
pas=db_con['PASSWORD'], bd=db, out=outShp,
t=tbl if not tableIsQuery else '"{}"'.format(tbl),
geom="" if not geom_col else " -g {}".format(geom_col)
))
elif api == 'psql' or api == 'sqlite':
from gasp.sql.fm import q_to_obj
q = "SELECT * FROM {}".format(tbl) if not tableIsQuery else tbl
df = q_to_obj(db, q, db_api=api, geomCol=geom_col, epsg=epsg)
outsh = df_to_shp(df, outShp)
else:
raise ValueError((
'api value must be \'psql\', \'sqlite\' or \'pgsql2shp\''))
return outShp
def alchemy_engine(db, api='psql'):
"""
SQLAlchemy Enignes
API's available:
* psql;
* sqlite;
* mysql;
"""
from sqlalchemy import create_engine
if api == 'psql':
"""
Get engine that could be used for pandas to import data into
PostgreSQL
"""
from gasp.cons.psql import con_psql
conparam = con_psql()
return create_engine(
'postgresql+psycopg2://{user}:{password}@{host}:{port}/{db}'.
format(user=conparam["USER"],
password=conparam["PASSWORD"],
host=conparam["HOST"],
port=conparam["PORT"],
db=db))
elif api == 'sqlite':
"""
Return Alchemy Engine for SQLITE
"""
from gasp.pyt.oss import os_name
if os_name() == 'Windows':
constr = r'sqlite:///{}'.format(db)
else:
constr = 'sqlite:///{}'.format(db)
return create_engine(constr)
elif api == 'mysql':
"""
Return MySQL Engine
"""
from gasp.cons.mysql import con_mysql
conparam = con_mysql()
return create_engine('mysql://{usr}:{pw}@{host}/{db}'.format(
usr=conparam['USER'],
pw=conparam["PASSWORD"],
host=conparam['HOST'],
db=db))
else:
raise ValueError('API {} is not available!'.format(api))