def db_to_db(db_a, db_b, typeDBA, typeDBB):
"""
All tables in one Database to other database
Useful when we want to migrate a SQLITE DB to a PostgreSQL DB
typesDB options:
* sqlite
* psql
"""
import os
from gasp.sql.fm import q_to_obj
from gasp.sql.i import lst_tbl
from gasp.sql.db import create_db
# List Tables in DB A
tbls = lst_tbl(db_a, excludeViews=True, api=typeDBA)
# Create database B
db_b = create_db(db_b, overwrite=False, api=typeDBB)
# Table to Database B
for tbl in tbls:
df = q_to_obj(
db_a, "SELECT * FROM {}".format(tbl), db_api=typeDBA
)
df_to_db(db_b, df, tbl, append=None, api=typeDBB)
def del_tables(db, pg_table_s, isViews=None, isBasename=None):
"""
Delete all tables in pg_table_s
"""
from gasp.pyt import obj_to_lst
from gasp.sql.c import sqlcon
pg_table_s = obj_to_lst(pg_table_s)
if isBasename:
if not isViews:
from gasp.sql.i import lst_tbl
pg_table_s = lst_tbl(db, api='psql', basename=pg_table_s)
else:
from gasp.sql.i import lst_views
pg_table_s = lst_views(db, basename=pg_table_s)
con = sqlcon(db)
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 pgtables_groups_to_layers(groups_of_tables, db, workName, storeName):
"""
Import all tables in pgsql database to geoserver
Each table belongs to a group. One group has the same basename. One group
is related to a single style specified in groups_of_tables
groups_of_tables = {
group_basename : path_to_sld_file,
...
}
"""
import os
from gasp.sql.i import lst_tbl
from gasp.web.geosrv.ws import create_ws
from gasp.web.geosrv.stores import create_pgstore
from gasp.web.geosrv.lyrs import pub_pglyr
from gasp.web.geosrv.sty import create_style
from gasp.web.geosrv.sty import lst_styles
from gasp.web.geosrv.sty import del_style
from gasp.web.geosrv.sty import assign_style_to_layer
# Create a new workspace
workName = 'w_{}'.format(db) if not workName else workName
create_ws(workName, overwrite=True)
# Create a new store
storeName = db if not storeName else storeName
create_pgstore(storeName, workName, db)
# List styles
STYLES = lst_styles()
# For each group:
for group in groups_of_tables:
print("START PROCESSING {} GROUP".format(group))
# - Identify tables
tables = lst_tbl(db, basename=group, excludeViews=True)
# - Create Style
STYLE_NAME = os.path.splitext(os.path.basename(
groups_of_tables[group]))[0]
if STYLE_NAME in STYLES:
del_style(STYLE_NAME)
create_style(STYLE_NAME, groups_of_tables[group])
# - Create layers
# - Assign style
for table in tables:
TITLE = 'lyr_{}'.format(table)
pub_pglyr(workName, storeName, table, title=TITLE)
assign_style_to_layer(STYLE_NAME, table)
print("{} GROUP IS IN GEOSERVER".format(group))
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 merge_dbs(destinationDb, dbs,
tbls_to_merge=None, ignoreCols=None):
"""
Put several database into one
For now works only with PostgreSQL
"""
import os
from gasp.pyt.oss import fprop, del_file
from gasp.sql import psql_cmd
from gasp.sql.i import db_exists, lst_tbl
from gasp.sql.db import create_db, drop_db
from gasp.sql.tbl import rename_tbl, tbls_to_tbl
from gasp.sql.fm import dump_tbls
from gasp.sql.to import restore_tbls
from gasp.sql.tbl import distinct_to_table, del_tables
# Prepare database
fdb = fprop(destinationDb, ['fn', 'ff'])
if os.path.isfile(destinationDb):
if fdb['fileformat'] == '.sql':
newdb = create_db(fdb['filename'],
overwrite=True, api='psql')
psql_cmd(newdb, destinationDb)
destinationDb = newdb
else:
raise ValueError((
'destinationDb is a file but is not correct. The file must be'
' a SQL Script'
))
else:
# Check if destination db exists
if not db_exists(destinationDb):
create_db(destinationDb, overwrite=None, api='psql')
# Check if dbs is a list or a dir
if type(dbs) == list:
dbs = dbs
elif os.path.isdir(dbs):
# list SQL files
from gasp.pyt.oss import lst_ff
dbs = lst_ff(dbs, file_format='.sql')
else:
raise ValueError(
'''
dbs value should be a list with paths
to sql files or a dir with sql files inside
'''
)
TABLES = {}
for i in range(len(dbs)):
# Create DB
DB_NAME = fprop(dbs[i], 'fn')
create_db(DB_NAME, overwrite=True, api='psql')
# Restore DB
psql_cmd(DB_NAME, dbs[i])
# List Tables
if not tbls_to_merge:
tbls__ = lst_tbl(DB_NAME, excludeViews=True, api='psql')
tbls = [t for t in tbls__ if t not in ignoreCols]
else:
tbls = tbls_to_merge
# Rename Tables
newTbls = rename_tbl(DB_NAME, {tbl : "{}_{}".format(
tbl, str(i)) for tbl in tbls})
for t in range(len(tbls)):
if tbls[t] not in TABLES:
TABLES[tbls[t]] = ["{}_{}".format(tbls[t], str(i))]
else:
TABLES[tbls[t]].append("{}_{}".format(tbls[t], str(i)))
# Dump Tables
SQL_DUMP = os.path.join(
os.path.dirname(dbs[i]), 'tbl_{}.sql'.format(DB_NAME)
); dump_tbls(DB_NAME, newTbls, SQL_DUMP)
# Restore Tables in the destination Database
restore_tbls(destinationDb, SQL_DUMP, newTbls)
# Delete Temp Database
drop_db(DB_NAME)
# Delete SQL File
del_file(SQL_DUMP)
# Union of all tables
max_len = max([len(TABLES[t]) for t in TABLES])
for tbl in TABLES:
# Rename original table
NEW_TBL = "{}_{}".format(tbl, max_len)
rename_tbl(destinationDb, {tbl : NEW_TBL})
TABLES[tbl].append(NEW_TBL)
# Union
tbls_to_tbl(destinationDb, TABLES[tbl], tbl + '_tmp')
# Group By
distinct_to_table(destinationDb, tbl + '_tmp', tbl, cols=None)
# Drop unwanted tables
del_tables(destinationDb, TABLES[tbl] + [tbl + '_tmp'])
return destinationDb
def psql_to_djgdb(sql_dumps,
db_name,
djg_proj=None,
mapTbl=None,
userDjgAPI=None):
"""
Import PGSQL database in a SQL Script into the database
controlled by one Django Project
To work, the name of a model instance of type foreign key should be
equal to the name of the 'db_column' clause.
"""
import os
from gasp import __import
from gasp.pyt import obj_to_lst
from gasp.sql.to import restore_tbls
from gasp.sql.db import create_db, drop_db
from gasp.sql.i import lst_tbl
from gasp.sql.fm import q_to_obj
from gasp.web.djg.mdl.rel import order_mdl_by_rel
from gasp.web.djg.mdl.i import lst_mdl_proj
# Global variables
TABLES_TO_EXCLUDE = [
'geography_columns', 'geometry_columns', 'spatial_ref_sys',
'raster_columns', 'raster_columns', 'raster_overviews',
'pointcloud_formats', 'pointcloud_columns'
]
# Several SQL Files are expected
sql_scripts = obj_to_lst(sql_dumps)
# Create Database
tmp_db_name = db_name + '_xxxtmp'
create_db(tmp_db_name)
# Restore tables in SQL files
for sql in sql_scripts:
restore_tbls(tmp_db_name, sql)
# List tables in the database
tables = [x for x in lst_tbl(tmp_db_name, excludeViews=True, api='psql')
] if not mapTbl else mapTbl
# Open Django Project
if djg_proj:
from gasp.web.djg import open_Django_Proj
application = open_Django_Proj(djg_proj)
# List models in project
app_mdls = lst_mdl_proj(djg_proj, thereIsApp=True, returnClassName=True)
data_tbl = {}
for t in tables:
if t == 'auth_user' or t == 'auth_group' or t == 'auth_user_groups':
data_tbl[t] = t
elif t.startswith('auth') or t.startswith('django'):
continue
elif t not in app_mdls or t in TABLES_TO_EXCLUDE:
continue
else:
data_tbl["{}.models.{}".format(t.split('_')[0], app_mdls[t])] = t
from django.contrib.gis.db import models
mdl_cls = [
"{}.models.{}".format(m.split('_')[0], app_mdls[m]) for m in app_mdls
]
orderned_table = order_mdl_by_rel(mdl_cls)
# Add default tables of Django
def_djg_tbl = []
if 'auth_group' in data_tbl:
def_djg_tbl.append('auth_group')
if 'auth_user' in data_tbl:
def_djg_tbl.append('auth_user')
if 'auth_user_groups' in data_tbl:
def_djg_tbl.append('auth_user_groups')
orderned_table = def_djg_tbl + orderned_table
if userDjgAPI:
for table in orderned_table:
# Map pgsql table data
tableData = q_to_obj(tmp_db_name, data_tbl[table], of='dict')
# Table data to Django Model
if table == 'auth_user':
mdl_cls = __import('django.contrib.auth.models.User')
elif table == 'auth_group':
mdl_cls = __import('django.contrib.auth.models.Group')
else:
mdl_cls = __import(table)
__mdl = mdl_cls()
for row in tableData:
for col in row:
# Check if field is a foreign key
field_obj = mdl_cls._meta.get_field(col)
if not isinstance(field_obj, models.ForeignKey):
# If not, use the value
# But first check if value is nan (special type of float)
if row[col] != row[col]:
row[col] = None
setattr(__mdl, col, row[col])
else:
# If yes, use the model instance of the related table
# Get model of the table related com aquela cujos dados
# estao a ser restaurados
related_name = field_obj.related_model.__name__
related_model = __import('{a}.models.{m}'.format(
a=table.split('_')[0], m=related_name))
# If NULL, continue
if not row[col]:
setattr(__mdl, col, row[col])
continue
related_obj = related_model.objects.get(
pk=int(row[col]))
setattr(__mdl, col, related_obj)
__mdl.save()
else:
import json
import pandas as pd
from gasp.sql.fm import q_to_obj
from gasp.sql.to import df_to_db
for tbl in orderned_table:
if tbl not in data_tbl:
continue
data = q_to_obj(tmp_db_name,
"SELECT * FROM {}".format(data_tbl[tbl]))
if tbl == 'auth_user':
data['last_login'] = pd.to_datetime(data.last_login, utc=True)
data['date_joined'] = pd.to_datetime(data.date_joined,
utc=True)
df_to_db(db_name, data, data_tbl[tbl], append=True)
drop_db(tmp_db_name)
def db_to_tbl(db,
tables,
outTbl,
txtDelimiter=None,
dbAPI='psql',
outTblF=None,
sheetsNames=None):
"""
Database data to File table
API's Avaialble:
* psql;
* sqlite;
* mysql;
"""
import os
from gasp.pyt import obj_to_lst
from gasp.sql.fm import q_to_obj
if tables == 'ALL':
from gasp.sql.i import lst_tbl
tables = lst_tbl(db, schema='public', excludeViews=True, api=dbAPI)
else:
tables = obj_to_lst(tables)
sheetsNames = obj_to_lst(sheetsNames)
outTblF = None if not outTblF else outTblF \
if outTblF[0] == '.' else '.' + outTblF
if len(tables) > 1:
if not sheetsNames:
if not os.path.isdir(outTbl) or not outTblF:
raise ValueError(
("When tables has more than one table, "
"outTbl must be dir and outTblF must be specified"))
elif len(tables) == 1:
if os.path.isdir(outTbl) and outTblF:
outTbl = os.path.join(outTbl, tables[0] + outTblF)
elif os.path.isdir(outTbl) and not outTbl:
raise ValueError(
('We find only a table to export and outTbl is a dir. '
'Please give a path to a file or specify the table format '
'using outTblF format'))
else:
outTbl = outTbl
else:
raise ValueError("tables value is not valid")
DFS = [
q_to_obj(db,
t if t.startswith("SELECT") else "SELECT * FROM {}".format(t),
db_api=dbAPI) for t in tables
]
if os.path.splitext(outTbl)[1] != '':
from gasp.pyt.oss import fprop
ff = fprop(outTbl, 'ff')
if ff == '.xlsx' or ff == '.xls':
obj_to_tbl(DFS, outTbl, sheetsName=sheetsNames, sanitizeUtf8=None)
return outTbl
for i in range(len(DFS)):
obj_to_tbl(
DFS[i],
outTbl if len(DFS) == 1 else os.path.join(outTbl, tables[i] +
outTblF),
delimiter=txtDelimiter,
sheetsName=sheetsNames)
return outTbl