Python QSqlQuery.value方法代码示例

# 需要导入模块: from qgis.PyQt.QtSql import QSqlQuery [as 别名]
# 或者: from qgis.PyQt.QtSql.QSqlQuery import value [as 别名]
    def on_pbSearchStr_clicked(self):
        # qDebug("on_pbSearchStr_clicked: text={}".format(self.leStr.text()))
        qry = QSqlQuery(self.db)

        self.cbxStrassen.blockSignals(True)
        self.cbxStrassen.clear()
        if qry.exec_(u"SELECT k.schluesselgesamt, k.bezeichnung || coalesce(', ' || g.bezeichnung,'') FROM ax_lagebezeichnungkatalogeintrag k LEFT OUTER JOIN ax_gemeinde g ON k.land=g.land AND k.regierungsbezirk=g.regierungsbezirk AND k.kreis=g.kreis AND k.gemeinde::int=g.gemeinde::int AND g.endet IS NULL WHERE lower(k.bezeichnung) LIKE {0} AND k.endet IS NULL ORDER BY k.bezeichnung || coalesce(', ' || g.bezeichnung,'')".format(quote(self.leStr.text().lower() + '%'))):
            while qry.next():
                self.cbxStrassen.addItem(qry.value(1), qry.value(0))
        self.cbxStrassen.blockSignals(False)

        self.lblResult.setText(u"Keine Straßen gefunden" if self.cbxStrassen.count() == 0 else u"{} Straßen gefunden".format(self.cbxStrassen.count()))

        self.cbxStrassen.setEnabled(self.cbxStrassen.count() > 0)
        self.cbxStrassen.setCurrentIndex(0 if self.cbxStrassen.count() == 1 else -1)
        self.on_cbxStrassen_currentIndexChanged(self.cbxStrassen.currentIndex())

# 需要导入模块: from qgis.PyQt.QtSql import QSqlQuery [as 别名]
# 或者: from qgis.PyQt.QtSql.QSqlQuery import value [as 别名]
    def on_cbxStrassen_currentIndexChanged(self, index):
        # qDebug(u"on_cbxStrassen_currentIndexChanged: index={} text={}".format(self.cbxStrassen.currentIndex(), self.cbxStrassen.currentText()))
        qry = QSqlQuery(self.db)

        schluesselgesamt = self.cbxStrassen.itemData(self.cbxStrassen.currentIndex())

        self.cbxHNR.blockSignals(True)
        self.cbxHNR.clear()
        if qry.exec_(u"SELECT h.hausnummer FROM ax_lagebezeichnungmithausnummer h JOIN ax_lagebezeichnungkatalogeintrag k ON h.land=k.land AND h.regierungsbezirk=k.regierungsbezirk AND h.kreis=k.kreis AND h.gemeinde=k.gemeinde AND h.lage=k.lage WHERE k.schluesselgesamt={0} ORDER BY NULLIF(regexp_replace(h.hausnummer, E'\\\\D', '', 'g'), '')::int".format(quote(schluesselgesamt))):
            while qry.next():
                self.cbxHNR.addItem(qry.value(0))
            if self.cbxHNR.count() > 1:
                self.cbxHNR.addItem("Alle")
        self.cbxHNR.blockSignals(False)

        self.cbxHNR.setEnabled(self.cbxHNR.count() > 0)
        self.cbxHNR.setCurrentIndex(0 if self.cbxHNR.count() == 1 else -1)

# 需要导入模块: from qgis.PyQt.QtSql import QSqlQuery [as 别名]
# 或者: from qgis.PyQt.QtSql.QSqlQuery import value [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 value [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 value [as 别名]
    def getPage(self, fs):
        (db, conninfo) = self.plugin.opendb()
        if db is None:
            return None

        qry = QSqlQuery(db)
        if qry.exec_("SELECT 1 FROM information_schema.columns WHERE table_schema={} AND table_name='eignerart' AND column_name='anteil'".format(quote(self.plugin.settings.schema))) and qry.next():
            exists_ea_anteil = qry.value(0) == 1
        else:
            exists_ea_anteil = False

        html = ""
        for i in range(0, len(fs)):
            flsnr = fs[i]['flsnr']

            best = self.fetchall(db, (
                "SELECT " +
                "ea.bvnr" +
                ",'' as pz" +
                ",(SELECT eignerart FROM eign_shl WHERE ea.b=b) as eignerart" +
                ",%s as anteil" +
                ",ea.ff_stand AS zhist" +
                ",b.bestdnr" +
                ",b.gbbz" +
                ",b.gbblnr" +
                ",b.bestfl" +
                ",b.ff_stand AS bhist" +
                " FROM eignerart ea" +
                " JOIN bestand b ON ea.bestdnr = b.bestdnr" +
                " WHERE ea.flsnr = '%s'" +
                " ORDER BY zhist,bhist,b") % ("ea.anteil" if exists_ea_anteil else "''", flsnr)
            )

            res = self.fetchall(db, "SELECT f.*,g.gemarkung FROM flurst f LEFT OUTER JOIN gema_shl g ON (f.gemashl=g.gemashl) WHERE f.flsnr='%s' AND f.ff_stand=0" % flsnr)
            if len(res) == 1:
                res = res[0]
            else:
                QMessageBox.information(None, "Fehler", u"Flurstück %s nicht gefunden.\n[%s]" % (flsnr, repr(fs)))
                return None

            res['datum'] = QDate.currentDate().toString("d. MMMM yyyy")
            res['hist'] = 0

            if qry.exec_(u"SELECT " + u" AND ".join(["has_table_privilege('{}', 'SELECT')".format(x) for x in ['strassen', 'str_shl']])) and qry.next() and qry.value(0):
                res['str'] = self.fetchall(db, "SELECT sstr.strname,str.hausnr FROM str_shl sstr JOIN strassen str ON str.strshl=sstr.strshl WHERE str.flsnr='%s' AND str.ff_stand=0" % flsnr)

            if qry.exec_(u"SELECT " + u" AND ".join(["has_table_privilege('{}', 'SELECT')".format(x) for x in ['nutz_21', 'nutz_shl']])) and qry.next() and qry.value(0):
                res['nutz'] = self.fetchall(db, "SELECT n21.*, nu.nutzshl, nu.nutzung FROM nutz_21 n21, nutz_shl nu WHERE n21.flsnr='%s' AND n21.nutzsl=nu.nutzshl AND n21.ff_stand=0" % flsnr)

            if qry.exec_(u"SELECT " + u" AND ".join(["has_table_privilege('{}', 'SELECT')".format(x) for x in ['klas_3x', 'kls_shl']])) and qry.next() and qry.value(0):
                res['klas'] = self.fetchall(db, "SELECT sum(fl::int) AS fl, min(kls.klf_text) AS klf_text FROM klas_3x kl, kls_shl kls WHERE kl.flsnr='%s' AND kl.klf=kls.klf AND kl.ff_stand=0 GROUP BY kls.klf" % flsnr)

            if qry.exec_(u"SELECT " + u" AND ".join(["has_table_privilege('{}', 'SELECT')".format(x) for x in ['ausfst', 'afst_shl']])) and qry.next() and qry.value(0):
                res['afst'] = self.fetchall(db, "SELECT au.*, af.afst_txt FROM ausfst au,afst_shl af WHERE au.flsnr='%s' AND au.ausf_st=af.ausf_st AND au.ff_stand=0" % flsnr)

            if qry.exec_(u"SELECT " + u" AND ".join(["has_table_privilege('{}', 'SELECT')".format(x) for x in ['bestand', 'eignerart', 'eign_shl']])) and qry.next() and qry.value(0):
                res['best'] = self.fetchall(db, "SELECT ea.bvnr,'' as pz,(SELECT eignerart FROM eign_shl WHERE ea.b = b) as eignerart,%s as anteil,ea.ff_stand AS zhist,b.bestdnr,b.gbbz,b.gbblnr,b.bestfl,b.ff_stand AS bhist FROM eignerart ea JOIN bestand b ON ea.bestdnr = b.bestdnr WHERE ea.flsnr='%s' ORDER BY zhist,bhist,b" % (
                    "ea.anteil" if exists_ea_anteil else "''",
                    flsnr
                ))

                if qry.exec_("SELECT has_table_privilege('eigner', 'SELECT')") and qry.next() and qry.value(0):
                    for b in res['best']:
                        b['bse'] = self.fetchall(db, "SELECT * FROM eigner WHERE bestdnr='%s' AND ff_stand=0" % b['bestdnr'])

#                        for k,v in res.iteritems():
#                                qDebug( u"%s:%s\n" % ( k, unicode(v) ) )

            html = u"""
<HTML xmlns="http://www.w3.org/1999/xhtml">
  <HEAD>
      <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  </HEAD>
  <BODY>
<style>
.fls_tab{width:100%%;empty-cells:show}
.fls_headline{font-weight:bold;font-size:4em;}
.fls_headline_col{background-color:#EEEEEE;width:100%%;height:30px;text-align:left;}
.fls_time        {background-color:#EEEEEE;font-weight:bold;font-size:4em;text-align:right;width:100%%}
.fls_col_names{font-weight:bold;}
.fls_col_values{vertical-align:top;}
.fls_bst{width:100%%;empty-cells:show}
.fls_hr{border:dotted 1px;color:#080808;}
.fls_footnote{text-align:center;}
</style>

<TABLE class="fls_tab" border="0" width="100%%" cellspacing="0">
    <TR class="fls_headline">
        <TD colspan="3" class="fls_headline_col">Flurst&uuml;cksnachweis</TD><TD class="fls_time" colspan="4" align="right">%(datum)s</TD></TR>
    </TR>
    <TR><TD colspan="7">&nbsp;</TD></TR>
    <TR>
        <TD colspan="7"><h3>Flurst&uuml;ck %(gemashl)s-%(flr)s-%(flsnrk)s<hr style="width:100%%"></h3></TD>
    </TR>
    <TR class="fls_col_names">
        <TD width="15%%">Gemarkung</TD>
        <TD width="6%%">Flur</TD>
        <TD width="15%%">Flurst&uuml;ck</TD>
        <TD width="20%%">Flurkarte</TD>
        <TD width="17%%">Entstehung</TD>
#.........这里部分代码省略.........

# 需要导入模块: from qgis.PyQt.QtSql import QSqlQuery [as 别名]
# 或者: from qgis.PyQt.QtSql.QSqlQuery import value [as 别名]
    def evaluate(self):
        if not self.plugin.initLayers():
            return False

        if self.tabWidget.currentWidget() == self.tabLabels:
            text = self.leSuchbegriff.text()
            if text != "":
                if self.cbTeiltreffer.isChecked():
                    # Teiltreffer
                    text = u"lower(text) LIKE %s" % quote("%%%s%%" % text.lower())
                else:
                    # Exakter Treffer
                    text = u"text=%s" % quote(text)

                qry = QSqlQuery(self.db)

                sql = u"SELECT count(*),st_extent(coalesce(point,line)) FROM po_labels WHERE {0}".format(text)
                if qry.exec_(sql) and qry.next() and qry.value(0) > 0:
                    self.lblResult.setText("{} Objekte gefunden".format(qry.value(0)))
                    self.plugin.zoomToExtent(qry.value(1), self.plugin.pointMarkerLayer.crs())
                else:
                    self.lblResult.setText("Keine Objekte gefunden")
                    return False
            else:
                text = "false"

            self.plugin.pointMarkerLayer.setSubsetString(text)
            self.plugin.lineMarkerLayer.setSubsetString(text)

            self.updateButtons()

        elif self.tabWidget.currentWidget() == self.tabGFF:
            g = self.cbxGemarkung.itemData(self.cbxGemarkung.currentIndex())
            f = self.cbxFlur.itemData(self.cbxFlur.currentIndex())
            z = self.cbxFSZ.itemData(self.cbxFSZ.currentIndex())
            n = self.cbxFSN.itemData(self.cbxFSN.currentIndex())

            flsnr = ""
            flsnr += ("%" if g is None or g == "" else g) + "-"
            flsnr += ("%" if f is None or f == "" else f) + "-"
            flsnr += ("%" if z is None or z == "" else z) + "/"
            flsnr += ("%" if n is None or n == "" else n)

            # qDebug(u"flsnr:{}".format(flsnr))
            fs = self.plugin.highlight(where=u"EXISTS (SELECT * FROM fs WHERE gml_id=fs_obj AND alb_key LIKE %s)" % quote(flsnr), zoomTo=True)

            self.lblResult.setText(u"{} Flurstücke gefunden".format(len(fs)) if len(fs) > 0 else u"Keine Flurstücke gefunden")
            self.updateButtons(fs)

        elif self.tabWidget.currentWidget() == self.tabFLSNR:
            hits = 0

            m = re.search("(\\d+)(-\\d+)?-(\\d+)(/\\d+)?", self.leFLSNR.text())
            if m:
                g, f, z, n = int(m.group(1)), m.group(2), int(m.group(3)), m.group(4)
                f = int(f[1:]) if f else 0
                n = int(n[1:]) if n else 0

                flsnr = "%06d" % g
                flsnr += "%03d" % f if f > 0 else "___"
                flsnr += "%05d" % z
                flsnr += "%04d" % n if n > 0 else "____"
                flsnr += "%"

                fs = self.plugin.highlight(where=u"flurstueckskennzeichen LIKE %s" % quote(flsnr), zoomTo=True)
                hits = len(fs)

            self.lblResult.setText(u"{} Flurstücke gefunden".format(hits) if hits > 0 else u"Keine Flurstücke gefunden")

            self.updateButtons(fs)

        elif self.tabWidget.currentWidget() == self.tabSTRHNR:
            text = self.leStr.text()
            if text != "":
                m = re.search("^(.*)\\s+(\\d+[a-zA-Z]?)$", text)
                if m:
                    strasse, ha = m.group(1), m.group(2)
                    fs = self.plugin.highlight(where=u"EXISTS (SELECT * FROM ax_lagebezeichnungmithausnummer h JOIN ax_lagebezeichnungkatalogeintrag k ON h.land=k.land AND h.regierungsbezirk=k.regierungsbezirk AND h.kreis=k.kreis AND h.gemeinde=k.gemeinde AND h.lage=k.lage WHERE ARRAY[h.gml_id] <@ ax_flurstueck.weistauf AND lower(k.bezeichnung) LIKE {0} AND h.hausnummer={1})".format(quote(strasse.lower() + '%'), quote(ha.upper())), zoomTo=True)
                    if len(fs) > 0:
                        self.lblResult.setText(u"{} Flurstücke gefunden".format(len(fs)))
                    else:
                        self.lblResult.setText(u"Keine Flurstücke gefunden")

                    self.updateButtons(fs)

            if self.cbxHNR.isEnabled():
                hnr = self.cbxHNR.currentText()

                sql = u"EXISTS (SELECT * FROM ax_lagebezeichnungmithausnummer h JOIN ax_lagebezeichnungkatalogeintrag k USING (land,regierungsbezirk,kreis,gemeinde,lage) WHERE ARRAY[h.gml_id] <@ ax_flurstueck.weistauf AND k.schluesselgesamt={0}{1})"
                if hnr == "Alle":
                    sql += u" OR EXISTS (SELECT * FROM ax_lagebezeichnungohnehausnummer h JOIN ax_lagebezeichnungkatalogeintrag k USING (land,regierungsbezirk,kreis,gemeinde,lage) WHERE ARRAY[h.gml_id] <@ ax_flurstueck.zeigtauf AND k.schluesselgesamt={0})"

                fs = self.plugin.highlight(
                    where=sql.format(
                        quote(self.cbxStrassen.itemData(self.cbxStrassen.currentIndex())),
                        ' AND h.hausnummer={0}'.format(quote(hnr)) if hnr != "Alle" else ""
                    ),
                    zoomTo=True
                )
                self.lblResult.setText(u"{} Flurstücke gefunden".format(len(fs)) if len(fs) > 0 else u"Keine Flurstücke gefunden")
#.........这里部分代码省略.........

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

# 需要导入模块: from qgis.PyQt.QtSql import QSqlQuery [as 别名]
# 或者: from qgis.PyQt.QtSql.QSqlQuery import value [as 别名]
    def __init__(self, plugin):
        QDialog.__init__(self)
        self.setupUi(self)
        self.plugin = plugin

        s = QSettings("norBIT", "norGIS-ALKIS-Erweiterung")

        v = QIntValidator()
        v.setBottom(1)
        self.leHighlightThreshold.setValidator(v)
        self.leHighlightThreshold.setText(str(s.value("highlightThreshold", 1000)))

        (db, conninfo) = self.plugin.opendb()
        self.db = db

        qry = QSqlQuery(db)
        if not qry.exec_("SELECT has_table_privilege('eigner', 'SELECT')") or not qry.next() or not qry.value(0):
            self.tabWidget.removeTab(self.tabWidget.indexOf(self.tabEigentuemer))

        self.replaceButton = self.buttonBox.addButton(u"Ersetzen", QDialogButtonBox.ActionRole)
        self.addButton = self.buttonBox.addButton(u"Hinzufügen", QDialogButtonBox.ActionRole)
        self.removeButton = self.buttonBox.addButton(u"Entfernen", QDialogButtonBox.ActionRole)
        self.clearButton = self.buttonBox.addButton(u"Leeren", QDialogButtonBox.ActionRole)

        self.replaceButton.clicked.connect(self.replaceClicked)
        self.addButton.clicked.connect(self.addClicked)
        self.removeButton.clicked.connect(self.removeClicked)
        self.clearButton.clicked.connect(self.clearClicked)

        self.cbxStrassen.setEnabled(False)
        self.cbxHNR.setEnabled(False)

        self.pbLabelSearch.clicked.connect(self.evaluate)
        self.pbOwnerSearch.clicked.connect(self.evaluate)
        self.pbSearchFSK.clicked.connect(self.evaluate)

        self.highlighted = set(self.plugin.highlighted())

        self.lblResult.setText(u"{} Objekte bereits gewählt.".format(len(self.highlighted)) if len(self.highlighted) > 0 else "")

        self.restoreGeometry(QSettings("norBIT", "norGIS-ALKIS-Erweiterung").value("searchgeom", QByteArray(), type=QByteArray))

        self.tabWidget.setCurrentIndex(s.value("suchmodus", 0, type=int))

        self.cbxGemarkung.currentIndexChanged.connect(self.gfzn)
        self.cbxFlur.currentIndexChanged.connect(self.gfzn)
        self.cbxFSZ.currentIndexChanged.connect(self.gfzn)
        self.cbxFSN.currentIndexChanged.connect(self.gfzn)
        self.gfzn()

# 需要导入模块: from qgis.PyQt.QtSql import QSqlQuery [as 别名]
# 或者: from qgis.PyQt.QtSql.QSqlQuery import value [as 别名]
    def loadModels(self, error=True):
        self.settings.servicE = self.leSERVICE.text()
        self.settings.host = self.leHOST.text()
        self.settings.port = self.lePORT.text()
        self.settings.dbname = self.leDBNAME.text()
        self.settings.schema = self.leSCHEMA.text()
        self.settings.uid = self.leUID.text()
        self.settings.pwd = self.lePWD.text()

        if hasattr(qgis.gui, 'QgsAuthConfigSelect'):
            self.settings.authcfg = self.authCfgSelect.configId()

        self.twModellarten.clearContents()
        self.cbxSignaturkatalog.clear()

        (db, conninfo) = self.plugin.opendb()
        if not db:
            if error:
                QMessageBox.critical(None, "ALKIS", u"Datenbankverbindung schlug fehl.")

            self.twModellarten.clearContents()
            self.twModellarten.setDisabled(True)
            self.twModellarten.setRowCount(0)

            self.settings.load()

            return

        modelle = self.settings.modellarten
        if modelle is None:
            modelle = ['DLKM', 'DKKM1000']

        qry = QSqlQuery(db)
        if qry.exec_("SELECT 1 FROM information_schema.tables WHERE table_schema={} AND table_name='po_modelle'".format(quote(self.plugin.settings.schema))) and qry.next():
            sql = "SELECT modell,n FROM po_modelle WHERE modell IS NOT NULL ORDER BY n DESC"
        else:
            sql = """
SELECT modell,count(*)
FROM (
SELECT unnest(modell) AS modell FROM po_points   UNION ALL
SELECT unnest(modell) AS modell FROM po_lines    UNION ALL
SELECT unnest(modell) AS modell FROM po_polygons UNION ALL
SELECT unnest(modell) AS modell from po_labels
) AS foo
WHERE modell IS NOT NULL
GROUP BY modell
ORDER BY count(*) DESC
"""

        if qry.exec_(sql):
            res = {}
            while qry.next():
                res[qry.value(0)] = qry.value(1)

            self.twModellarten.setRowCount(len(res))
            i = 0
            for k, n in sorted(iter(list(res.items())), key=operator.itemgetter(1), reverse=True):
                item = QTableWidgetItem(k)
                item.setCheckState(Qt.Checked if (item.text() in modelle) else Qt.Unchecked)
                self.twModellarten.setItem(i, 0, item)

                item = QTableWidgetItem(str(n))
                self.twModellarten.setItem(i, 1, item)
                i += 1
            self.twModellarten.resizeColumnsToContents()
            self.twModellarten.setEnabled(True)
        else:
            self.twModellarten.clearContents()
            self.twModellarten.setDisabled(True)
            self.twModellarten.setRowCount(0)

        if qry.exec_("SELECT id,name FROM alkis_signaturkataloge"):
            while qry.next():
                self.cbxSignaturkatalog.addItem(qry.value(1), int(qry.value(0)))
            self.cbxSignaturkatalog.setEnabled(True)
        else:
            self.cbxSignaturkatalog.addItem(u"Farbe", -1)

        self.cbxSignaturkatalog.setCurrentIndex(max([0, self.cbxSignaturkatalog.findData(self.settings.signaturkatalog)]))

        self.settings.load()