Python QSqlQuery.record方法代码示例

# 需要导入模块: from qgis.PyQt.QtSql import QSqlQuery [as 别名]
# 或者: from qgis.PyQt.QtSql.QSqlQuery import record [as 别名]
    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

# 需要导入模块: from qgis.PyQt.QtSql import QSqlQuery [as 别名]
# 或者: from qgis.PyQt.QtSql.QSqlQuery import record [as 别名]
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)
#.........这里部分代码省略.........

# 需要导入模块: from qgis.PyQt.QtSql import QSqlQuery [as 别名]
# 或者: from qgis.PyQt.QtSql.QSqlQuery import record [as 别名]
    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")