def setUsername(self, username):
self.username = username
if (self.checkUserExists()):
sql = "SELECT name,access FROM postnas_search_access_control WHERE lower(username) = :username"
self.__openDB()
queryLoadUserData = QSqlQuery(self.db)
queryLoadUserData.prepare(sql)
queryLoadUserData.bindValue(":username", self.getUsername())
queryLoadUserData.exec_()
if (queryLoadUserData.size() == 1):
while (queryLoadUserData.next()):
self.setName(
queryLoadUserData.value(
queryLoadUserData.record().indexOf("name")))
self.setAccess(
queryLoadUserData.value(
queryLoadUserData.record().indexOf("access")))
def getAccessModes(self):
sql = "SELECT id,bezeichnung FROM postnas_search_accessmode"
self.__openDB()
queryLoadAccessModes = QSqlQuery(self.db)
queryLoadAccessModes.prepare(sql)
queryLoadAccessModes.exec_()
results = []
if (queryLoadAccessModes.size() > 0):
while (queryLoadAccessModes.next()):
list = {
'id':
queryLoadAccessModes.value(
queryLoadAccessModes.record().indexOf("id")),
'bezeichnung':
queryLoadAccessModes.value(
queryLoadAccessModes.record().indexOf("bezeichnung"))
}
results.append(list)
return results
def loadUserAccessTable(self):
sql = "SELECT lower(username) as username,name,bezeichnung FROM postnas_search_access_control LEFT JOIN postnas_search_accessmode ON postnas_search_access_control.access = postnas_search_accessmode.id"
self.__openDB()
queryLoadAccessTable = QSqlQuery(self.db)
queryLoadAccessTable.prepare(sql)
queryLoadAccessTable.exec_()
results = []
if (queryLoadAccessTable.size() > 0):
while (queryLoadAccessTable.next()):
list = {
'username':
queryLoadAccessTable.value(
queryLoadAccessTable.record().indexOf("username")),
'name':
queryLoadAccessTable.value(
queryLoadAccessTable.record().indexOf("name")),
'access':
queryLoadAccessTable.value(
queryLoadAccessTable.record().indexOf("bezeichnung"))
}
results.append(list)
return results
def fetchall(self, db, sql):
rows = []
qry = QSqlQuery(db)
if qry.exec_(sql):
rec = qry.record()
while qry.next():
row = {}
for i in range(0, rec.count()):
v = "%s" % qry.value(i)
if v == "NULL":
v = ''
row[rec.fieldName(i)] = v.strip()
rows.append(row)
else:
qDebug("Exec failed: " + qry.lastError().text())
return rows
def fetchall(self, db, sql):
rows = []
qry = QSqlQuery(db)
if qry.exec_(sql):
rec = qry.record()
while qry.next():
row = {}
for i in range(0, rec.count()):
v = "%s" % qry.value(i)
if v == "NULL":
v = ''
row[rec.fieldName(i)] = v.strip()
rows.append(row)
else:
qDebug("Exec failed: " + qry.lastError().text())
return rows
class QtSqlDBCursor(object):
def __init__(self, conn):
self.qry = QSqlQuery(conn)
self.description = None
self.rowcount = -1
self.arraysize = 1
def close(self):
self.qry.finish()
def execute(self, operation, parameters=[]):
if len(parameters) == 0:
if not self.qry.exec_(operation):
raise ExecError(self.qry.lastError().databaseText())
else:
if not self.qry.prepare(operation):
raise ExecError(self.qry.lastError().databaseText())
for i in range(len(parameters)):
self.qry.bindValue(i, parameters[i])
if not self.qry.exec_():
raise ExecError(self.qry.lastError().databaseText())
self.rowcount = self.qry.size()
self.description = []
for c in range(self.qry.record().count()):
f = self.qry.record().field(c)
if f.type() == QVariant.Date:
t = Date
elif f.type() == QVariant.Time:
t = Time
elif f.type() == QVariant.DateTime:
t = Timestamp
elif f.type() == QVariant.Double:
t = float
elif f.type() == QVariant.Int:
t = int
elif f.type() == QVariant.String:
t = str
elif f.type() == QVariant.ByteArray:
t = str
else:
continue
self.description.append([
f.name(), # name
t, # type_code
f.length(), # display_size
f.length(), # internal_size
f.precision(), # precision
None, # scale
f.requiredStatus() != QSqlField.Required # null_ok
])
def executemany(self, operation, seq_of_parameters):
if len(seq_of_parameters) == 0:
return
if not self.qry.prepare(operation):
raise ExecError(self.qry.lastError().databaseText())
for r in seq_of_parameters:
for i in range(len(r)):
self.qry.bindValue(i, r[i])
if not self.qry.exec_():
raise ExecError(self.qry.lastError().databaseText())
def scroll(self, row):
return self.qry.seek(row)
def fetchone(self):
if not self.qry.next():
return None
row = []
for i in range(len(self.description)):
value = self.qry.value(i)
if (isinstance(value, QDate) or isinstance(value, QTime)
or isinstance(value, QDateTime)):
value = value.toString()
elif isinstance(value, QByteArray):
value = u"GEOMETRY"
# value = value.toHex()
row.append(value)
return row
def fetchmany(self, size=10):
rows = []
while len(rows) < size:
row = self.fetchone()
if row is None:
break
rows.append(row)
return rows
def fetchall(self):
rows = []
while True:
row = self.fetchone()
if row is None:
break
rows.append(row)
return rows
def setinputsize(self, sizes):
raise ExecError("nyi")
def setoutputsize(self, size, column=None):
raise ExecError("nyi")
def gfzn(self):
g = self.cbxGemarkung.itemData(self.cbxGemarkung.currentIndex()) if self.cbxGemarkung.currentIndex() >= 0 else None
f = self.cbxFlur.itemData(self.cbxFlur.currentIndex()) if self.cbxFlur.currentIndex() >= 0 else None
z = self.cbxFSZ.itemData(self.cbxFSZ.currentIndex()) if self.cbxFSZ.currentIndex() >= 0 else None
n = self.cbxFSN.itemData(self.cbxFSN.currentIndex()) if self.cbxFSN.currentIndex() >= 0 else None
where = []
if g is not None and g != "":
where.append("gemashl='%s'" % g)
if f is not None and f != "":
where.append("flr='%s'" % f)
if z is not None and n is not None and z != "" and n != "":
where.append("flsnrk='%s/%s'" % (z, n))
elif z is not None and z != "":
where.append("flsnrk LIKE '%s/%%'" % z)
elif n is not None and n != "":
where.append("flsnrk LIKE '%%/%s'" % n)
where = u" WHERE {}".format(u" AND ".join(where)) if where else ""
qry = QSqlQuery(self.db)
# qDebug(u"WHERE:{}".format(where))
for cbx, sql, val in [
[
self.cbxGemarkung,
"SELECT {0} FROM gema_shl a LEFT OUTER JOIN gem_shl b USING (gemshl){1} GROUP BY {0} ORDER BY {0}".format(
"a.gemashl,a.gemarkung||' ('||a.gemashl||coalesce(', '||b.gemname,'')||')'",
u" JOIN flurst c USING (gemashl){0}".format(where) if where != "" else ""
),
g,
],
[
self.cbxFlur,
"SELECT {0} FROM flurst{1} GROUP BY {0} ORDER BY {0}".format("flr", where),
f,
],
[
self.cbxFSZ,
"SELECT {0} FROM flurst{1} GROUP BY {0} ORDER BY {0}".format("split_part(flsnrk,'/',1)", where),
z,
],
[
self.cbxFSN,
"SELECT {0} FROM flurst{1} GROUP BY {0} ORDER BY {0}".format("split_part(flsnrk,'/',2)", where),
n,
],
]:
cbx.blockSignals(True)
cbx.clear()
cbx.addItem("Alle", "")
# qDebug(u"SQL:{} [{}]".format(sql, val))
if qry.exec_(sql):
d = 0 if qry.record().count() == 1 else 1
while qry.next():
cbx.addItem(qry.value(d), qry.value(0))
cbx.setCurrentIndex(cbx.findData(val))
cbx.blockSignals(False)
if where == "":
return
hits = 0
if qry.exec_(u"SELECT count(*) FROM flurst{}".format(where)) and qry.next():
hits = qry.value(0)
if hits>0 and hits<int(self.leHighlightThreshold.text()):
self.evaluate()
else:
self.lblResult.setText(u"{} Flurstücke gefunden".format(hits) if hits > 0 else u"Keine Flurstücke gefunden")
class Config(object):
def __init__(self):
self.file = os.path.join(os.path.dirname('__file__'), 'settings.db')
#self.file = 'C:/Users/da2/AppData/Roaming/QGIS/QGIS3/profiles/default/python/plugins/CatastroV3/settings.db'
print(self.file)
self.db = self.get_db()
self.query = QSqlQuery(self.db)
config = {
'host':
'localhost',
'dbname':
'',
'port':
5432,
'user':
'',
'pswd':
'',
'schema':
'public',
'layers': [
"departamento", "circunscripcion", "seccion", "chacra",
"quinta", "fraccion", "manzana", "parcela", "subparcela"
],
'partidos':
self.get_partidos()
}
self.set_config(config)
#print(config)
config2 = self.get_config()
print(config2)
def get_db(self):
if QSqlDatabase.contains("midb"):
QSqlDatabase.removeDatabase("midb")
db = QSqlDatabase.addDatabase("QPSQL", "midb")
db.setHostName('localhost')
db.setPort(5432)
db.setDatabaseName('')
db.setUserName('')
db.setPassword('')
return db
def connect(self):
ok = self.db.open()
return ok
def get_partidos(self):
partidos = []
if self.connect():
sql = "SELECT id, nombre FROM public.departamento ORDER BY id"
if self.query.exec_(sql):
row = self.query.record()
while self.query.next():
id = self.query.value(int(row.indexOf("id")))
nombre = self.query.value(row.indexOf("nombre"))
partido = [str(id) + '- ' + nombre, str(id)]
partidos.append(partido)
print('Cargado todos los partidos')
else:
print('No se pudo ejecutar la consulta')
else:
print('No se pudo conectar a la base de datos')
return partidos
def get_config(self):
return pickle.load(open(self.file, 'rb'))
def set_config(self, config):
pickle.dump(config, open(self.file, 'wb'), 2)
class QtSqlDBCursor(object):
def __init__(self, conn):
self.qry = QSqlQuery(conn)
self.description = None
self.rowcount = -1
self.arraysize = 1
def close(self):
self.qry.finish()
def execute(self, operation, parameters=[]):
if len(parameters) == 0:
if not self.qry.exec_(operation):
raise ExecError(self.qry.lastError().databaseText())
else:
if not self.qry.prepare(operation):
raise ExecError(self.qry.lastError().databaseText())
for i in range(len(parameters)):
self.qry.bindValue(i, parameters[i])
if not self.qry.exec_():
raise ExecError(self.qry.lastError().databaseText())
self.rowcount = self.qry.size()
self.description = []
for c in range(self.qry.record().count()):
f = self.qry.record().field(c)
if f.type() == QVariant.Date:
t = Date
elif f.type() == QVariant.Time:
t = Time
elif f.type() == QVariant.DateTime:
t = Timestamp
elif f.type() == QVariant.Double:
t = float
elif f.type() == QVariant.Int:
t = int
elif f.type() == QVariant.String:
t = str
elif f.type() == QVariant.ByteArray:
t = str
else:
continue
self.description.append([
f.name(), # name
t, # type_code
f.length(), # display_size
f.length(), # internal_size
f.precision(), # precision
None, # scale
f.requiredStatus() != QSqlField.Required # null_ok
])
def executemany(self, operation, seq_of_parameters):
if len(seq_of_parameters) == 0:
return
if not self.qry.prepare(operation):
raise ExecError(self.qry.lastError().databaseText())
for r in seq_of_parameters:
for i in range(len(r)):
self.qry.bindValue(i, r[i])
if not self.qry.exec_():
raise ExecError(self.qry.lastError().databaseText())
def scroll(self, row):
return self.qry.seek(row)
def fetchone(self):
if not next(self.qry):
return None
row = []
for i in range(len(self.description)):
value = self.qry.value(i)
if (isinstance(value, QDate) or
isinstance(value, QTime) or
isinstance(value, QDateTime)):
value = value.toString()
elif isinstance(value, QByteArray):
value = u"GEOMETRY"
# value = value.toHex()
row.append(value)
return row
def fetchmany(self, size=10):
rows = []
while len(rows) < size:
row = self.fetchone()
if row is None:
break
rows.append(row)
return rows
def fetchall(self):
rows = []
while True:
row = self.fetchone()
if row is None:
break
rows.append(row)
return rows
def setinputsize(self, sizes):
raise ExecError("nyi")
def setoutputsize(self, size, column=None):
raise ExecError("nyi")
def gfzn(self):
g = self.cbxGemarkung.itemData(self.cbxGemarkung.currentIndex()) if self.cbxGemarkung.currentIndex() >= 0 else None
f = self.cbxFlur.itemData(self.cbxFlur.currentIndex()) if self.cbxFlur.currentIndex() >= 0 else None
z = self.cbxFSZ.itemData(self.cbxFSZ.currentIndex()) if self.cbxFSZ.currentIndex() >= 0 else None
n = self.cbxFSN.itemData(self.cbxFSN.currentIndex()) if self.cbxFSN.currentIndex() >= 0 else None
where = []
if g is not None and g != "":
where.append("gemashl='%s'" % g)
if f is not None and f != "":
where.append("flr='%s'" % f)
if z is not None and n is not None and z != "" and n != "":
where.append("flsnrk='%s/%s'" % (z, n))
elif z is not None and z != "":
where.append("flsnrk LIKE '%s/%%'" % z)
elif n is not None and n != "":
where.append("flsnrk LIKE '%%/%s'" % n)
where = u" WHERE {}".format(u" AND ".join(where)) if where else ""
qry = QSqlQuery(self.db)
# qDebug(u"WHERE:{}".format(where))
for cbx, sql, val in [
[
self.cbxGemarkung,
"SELECT {0} FROM gema_shl a LEFT OUTER JOIN gem_shl b USING (gemshl){1} GROUP BY {0} ORDER BY {0}".format(
"a.gemashl,a.gemarkung||' ('||a.gemashl||coalesce(', '||b.gemname,'')||')'",
u" JOIN flurst c USING (gemashl){0}".format(where) if where != "" else ""
),
g,
],
[
self.cbxFlur,
"SELECT {0} FROM flurst{1} GROUP BY {0} ORDER BY {0}".format("flr", where),
f,
],
[
self.cbxFSZ,
"SELECT {0} FROM flurst{1} GROUP BY {0} ORDER BY {0}".format("split_part(flsnrk,'/',1)", where),
z,
],
[
self.cbxFSN,
"SELECT {0} FROM flurst{1} GROUP BY {0} ORDER BY {0}".format("split_part(flsnrk,'/',2)", where),
n,
],
]:
cbx.blockSignals(True)
cbx.clear()
cbx.addItem("Alle", "")
# qDebug(u"SQL:{} [{}]".format(sql, val))
if qry.exec_(sql):
d = 0 if qry.record().count() == 1 else 1
while qry.next():
cbx.addItem(qry.value(d), qry.value(0))
cbx.setCurrentIndex(cbx.findData(val))
cbx.blockSignals(False)
if where == "":
return
hits = 0
if qry.exec_(u"SELECT count(*) FROM flurst{}".format(where)) and qry.next():
hits = qry.value(0)
if hits > 0 and hits < int(self.leHighlightThreshold.text()):
self.evaluate()
else:
self.lblResult.setText(u"{} Flurstücke gefunden".format(hits) if hits > 0 else u"Keine Flurstücke gefunden")