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
예제 #4
0
    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
예제 #5
0
    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
예제 #6
0
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")
예제 #7
0
    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")
예제 #8
0
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)
예제 #9
0
파일: QtSqlDB.py 프로젝트: CS-SI/QGIS
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")
예제 #10
0
    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")