def restore_tbls(dbn, sql, tablenames=None, dbset='default'):
"""
Restore tables from a sql Script
TODO: add mysql option
"""
from glass.pys import execmd
from glass.cons.psql import con_psql
from glass.pys import obj_to_lst
condb = con_psql(db_set=dbset)
tbls = obj_to_lst(tablenames)
tblStr = "" if not tablenames else " {}".format(" ".join([
"-t {}".format(t) for t in tbls]))
outcmd = execmd((
"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 psql_cmd(db_name, sqlfile, dbcon=None):
"""
Run a sql file do whatever is on that script
"""
import os
from glass.pys import execmd
from glass.cons.psql import con_psql
cdb = con_psql(db_set=dbcon)
if os.path.isdir(sqlfile):
from glass.pys.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 = execmd(
cmd.format(cdb['HOST'], cdb['USER'], cdb['PORT'], db_name, s))
return db_name
def restore_db(db, sqlScript, api='psql'):
"""
Restore Database using SQL Script
"""
from glass.pys import execmd
if api == 'psql':
from glass.cons.psql import con_psql
condb = con_psql()
cmd = 'psql -h {} -U {} -p {} -w {} < {}'.format(
condb['HOST'], condb['USER'], condb['PORT'],
db, sqlScript
)
elif api == 'mysql':
from glass.cons.mysql import con_mysql
condb = con_mysql()
cmd = 'mysql -u {} -p{} {} < {}'.format(
condb['USER'], condb['PASSWORD'], db,
sqlScript
)
else:
raise ValueError('{} API is not available'.format(api))
outcmd = execmd(cmd)
return db
def osm_to_psql(osmXml, osmdb):
"""
Use GDAL to import osmfile into PostGIS database
"""
from glass.pys import execmd
from glass.cons.psql import con_psql
from glass.ng.prop.sql import db_exists
is_db = db_exists(osmdb)
if not is_db:
from glass.ng.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 = execmd(cmd)
return osmdb
def dump_db(db, outSQL, api='psql'):
"""
DB to SQL Script
"""
from glass.pys import execmd
if api == 'psql':
from glass.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 glass.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 = execmd(cmd)
return outSQL
def create_db(newdb, overwrite=True, api='psql', use_template=True, dbset='default',
geosupport=None):
"""
Create Relational Database
APIS Available:
* psql;
* sqlite;
"""
if api == 'psql':
from glass.ng.sql.c import sqlcon
from glass.ng.prop.sql import lst_db
from glass.cons.psql import con_psql
conparam = con_psql(db_set=dbset)
dbs = lst_db()
con = sqlcon(None, sqlAPI='psql', dbset=dbset)
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 ""
)
)
if not use_template and geosupport:
ge = ['postgis', 'hstore', 'postgis_topology', 'postgis_raster', 'pgrouting']
for e in ge:
cs.execute("CREATE EXTENSION {};".format(e))
cs.close()
con.close()
elif api == 'sqlite':
import os
import sqlite3
try:
if os.path.exists(newdb) and overwrite:
from glass.pys.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', dbset='default'):
"""
Connect to PostgreSQL Database
"""
if sqlAPI == 'psql':
import psycopg2
from glass.cons.psql import con_psql
conparam = con_psql(db_set=dbset)
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 glass.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 glass.pys import execmd
from glass.pys import obj_to_lst
from glass.cons.psql import con_psql
tbls = obj_to_lst(tables)
if startWith:
from glass.ng.prop.sql 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 = execmd(("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 alchemy_engine(db, api='psql', dbset='default'):
"""
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 glass.cons.psql import con_psql
conparam = con_psql(db_set=dbset)
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 glass.pys.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 glass.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))
def create_pgstore(store, workspace, db, dbset='default'):
"""
Create a store for PostGIS data
"""
import os
import requests
from glass.pys.char import random_str
from glass.pys.Xml import write_xml_tree
from glass.pys.oss import mkdir, del_folder
from glass.cons.gsrv import con_gsrv
from glass.cons.psql import con_psql
gs_con = con_gsrv()
pg_con = con_psql(db_set=dbset)
# Create folder to write xml
wTmp = mkdir(
os.path.join(os.path.dirname(os.path.abspath(__file__)),
random_str(7)))
# Create obj with data to be written in the xml
tree_order = {
"dataStore": [
"name", "type", "enabled", "workspace", "connectionParameters",
"__default"
],
"connection:Parameters": [("entry", "key", "port"),
("entry", "key", "user"),
("entry", "key", "passwd"),
("entry", "key", "dbtype"),
("entry", "key", "host"),
("entry", "key", "database"),
("entry", "key", "schema")]
}
xml_tree = {
"dataStore": {
"name": store,
"type": "PostGIS",
"enabled": "true",
"workspace": {
"name": workspace
},
"connectionParameters": {
("entry", "key", "port"): pg_con["PORT"],
("entry", "key", "user"): pg_con["USER"],
("entry", "key", "passwd"): pg_con["PASSWORD"],
("entry", "key", "dbtype"): "postgis",
("entry", "key", "host"): pg_con["HOST"],
("entry", "key", "database"): db,
("entry", "key", "schema"): "public"
},
"__default": "false"
}
}
# Write xml
xml_file = write_xml_tree(xml_tree,
os.path.join(wTmp, 'pgrest.xml'),
nodes_order=tree_order)
# Create Geoserver Store
url = ('{pro}://{host}:{port}/geoserver/rest/workspaces/{wname}/'
'datastores.xml').format(host=gs_con['HOST'],
port=gs_con['PORT'],
wname=workspace,
pro=gs_con['PROTOCOL'])
with open(xml_file, 'rb') as f:
r = requests.post(url,
data=f,
headers={'content-type': 'text/xml'},
auth=(gs_con['USER'], gs_con['PASSWORD']))
f.close()
del_folder(wTmp)
return r
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 glass.g.wt.shp import df_to_shp
if outShpIsGRASS:
from glass.pys import execmd
from glass.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 = execmd(cmd_str)
else:
if api == 'pgsql2shp':
from glass.pys import execmd
from glass.cons.psql import con_psql
db_con = con_psql()
outcmd = execmd(
('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 glass.ng.sql.q 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