Пример #1
0
    def get_last_lsg_date(self):
        """
        Finds the most recent record insert/update date from either ESU's or street records.
        :return: date formatted YYYYMMDD
        """
        last_date = 0

        last_street = "Select max(Update_Date) as LastChange FROM tblStreet"
        query = QSqlQuery(self.db)
        query.exec_(last_street)
        query.seek(0)
        rec = query.record()
        last_street_date = query.value(rec.indexOf('LastChange'))

        last_esu = "SELECT Max([closure_date]) AS LastClose, Max([entry_date]) AS lastEntry FROM tblESU"
        query_esu = QSqlQuery(self.db)
        query_esu.exec_(last_esu)
        query_esu.seek(0)
        rec_esu = query_esu.record()

        last_esu_closure = query_esu.value(rec_esu.indexOf('LastClose'))
        last_esu_entry = query_esu.value(rec_esu.indexOf('lastEntry'))

        if last_street_date > last_date:
            last_date = last_street_date
        if last_esu_closure > last_date:
            last_date = last_esu_closure
        if last_esu_entry > last_date:
            last_date = last_esu_entry

        return last_date
Пример #2
0
def GetFindSpotNumbers(db, siteNumbers):
    query = QSqlQuery(db)
    sites = u", ".join(u"'{0}'".format(siteNumber) for siteNumber in siteNumbers)
    query.prepare(u"SELECT fundortnummer || '.' || fundstellenummer FROM fundstelle WHERE fundortnummer  IN ({0})".format(sites))
    res = query.exec_()
    query.seek(-1)
    findSpots = []
    while query.next():
        findSpots.append(query.value(0))

    return findSpots
Пример #3
0
 def headerData(self, section, orientation, role=None):
     """Generate the months on the rows and the clients on the columns"""
     # pylint: disable-msg=C0103
     if role not in (Qt.DisplayRole, Qt.CheckStateRole):
         return QVariant()
     if orientation == Qt.Horizontal:
         query = QSqlQuery(
             'SELECT client, machine, selldate, deltamonth, '
             'anticiped FROM clients ORDER BY client, machine, '
             'selldate', self._db)
         if not query.exec_():
             raise StandardError('SYNTAX ERROR')
         if not query.first():
             raise StandardError("Non c'e' manco un risultato?")
         if not query.seek(section):
             raise StandardError('Not enough elements into the table')
         client = Client(query)
         return QVariant(
             '%s\n%s\n%s\nOgni %s mesi\nPagamento %scipato' %
             (client.client, client.machine,
              client.selldate.toString('d MMMM yyyy'), client.deltamonth,
              'anti' if client.anticiped else 'posti'))
     else:
         return QVariant(QDate.currentDate().addMonths(
             section - self.months_before).toString('MMMM yyyy'))
Пример #4
0
 def commit_esu_link(self):
     """
     Updates existing esu links on edit and deal with adding/remove links via editing
     """
     usrn = str(self.new_usrn_no)
     if self.edit_esu:
         # get new set of esu links
         esus = self.edit_esu.get_final_selection()
         final = esus[0]
     else:
         # No esu edits made so query for existing esu links
         final = self.esu_version.query_esu(usrn)
     date = str(datetime.datetime.now().strftime("%Y%m%d"))
     try:
         for esu in final:
             query_str = "SELECT version_no FROM tblESU WHERE esu_id = %s AND currency_flag = 0;" % esu
             query = QSqlQuery(query_str, self.db)
             seek = query.seek(0)
             if seek:
                 esu_ver = query.value(0)
             else:
                 esu_ver = str(1)
             # Create new links
             insert_sql = "INSERT INTO lnkESU_STREET (esu_id, usrn, esu_version_no, usrn_version_no, currency_flag," \
                          " entry_date, update_date) VALUES (%s, %s, %s, 1, 0, %s, %s)" \
                          % (esu, usrn, esu_ver, date, date)
             new_lnk_query = QSqlQuery(insert_sql, self.db)
     except TypeError:
         # No esu's attached to record
         pass
Пример #5
0
 def new_usrn(self):
     """
     Returns a new usrn (max usrn + 1)
     :rtype : int
     :return: USRN
     """
     query = QSqlQuery("SELECT MAX(usrn) from tblSTREET", self.db)
     query.seek(0)
     try:
         usrn = int(query.value(0)) + 1
     except TypeError:
         # Throws if there are no USRNs yet.  Example for demo db inserted here
         # This must be set manually for a new local authority
         usrn = 12700001
     self.new_usrn_no = usrn
     return usrn
Пример #6
0
 def validation_query(self, desc, town_ref, loc_ref):
     """
     Run validation query, returns True for unique description text
     :param desc: Description string
     :param town_ref: town id
     :param loc_ref: location id
     :return: true if match found
     """
     sql = "SELECT description FROM tblSTREET WHERE LOWER(description) = " \
           "LOWER('%s') AND town_ref = %s AND loc_ref = %s AND currency_flag = 0" % (desc, town_ref, loc_ref)
     if self.usrn:
         # usrn is provided for existing records, so it doesnt count itself
         sql += " AND usrn != %s" % str(self.usrn)
     query = QSqlQuery(sql, self.db)
     if query.seek(0):
         return False
     else:
         return True
Пример #7
0
 def update_esu_link(self, old_usrn_ver, new_usrn_ver):
     """
     Updates existing esu links on edit and deal with adding/remove links via editing
     :param old_usrn_ver: Old usrn version no
     :param new_usrn_ver: New usrn version no
     """
     usrn = str(self.street_browser.ui.usrnLineEdit.text())
     if self.edit_esu:
         # get new set of esu links
         esus = self.edit_esu.get_final_selection()
         final = esus[0]
     else:
         # No esu edits made so query for existing esu links
         final = self.canvas_functs.query_esu(usrn)
     date = str(datetime.datetime.now().strftime("%Y%m%d"))
     try:
         for esu in final:
             query_str = "SELECT version_no FROM tblESU WHERE esu_id = %s AND currency_flag = 0;" % esu
             query = QSqlQuery(query_str, self.db)
             seek = query.seek(0)
             if seek:
                 esu_ver = query.value(0)
             else:
                 esu_ver = str(1)
             # Create new links
             insert_sql = "INSERT INTO lnkESU_STREET (esu_id, usrn, esu_version_no, usrn_version_no, currency_flag," \
                          " entry_date, update_date) VALUES (%s, %s, %s, %s, 0, %s, %s)" \
                          % (esu, usrn, esu_ver, new_usrn_ver, date, date)
             insert = QSqlQuery(insert_sql, self.db)
         # Close existing links
         update_sql = "UPDATE lnkESU_STREET SET currency_flag=1, closure_date=%s WHERE usrn = %s " \
                      "AND usrn_version_no = %s" % (date, usrn, old_usrn_ver)
         update = QSqlQuery(update_sql, self.db)
     except TypeError:
         # No esu's attached to record
         pass
Пример #8
0
    # 以下是在例程17-4中添加的代码
    # 先判断该数据库驱动是否支持QuerySize特性,如果支持,则可以使用size()函数,
    # 如果不支持,那么就使用其他方法来获取总行数
    if (db2.driver().hasFeature(QSqlDriver.QuerySize)):
        print "has feature: query size"
        numRows = query2.size()
    else:
        print "no feature: query size"
        query2.last()
        numRows = query2.at() + 1

    print "row number: %s " % numRows

    # 指向索引为1的记录,即第二条记录
    query2.seek(1)
    # 返回当前索引值
    print "current index:  %s " % query2.at()
    # 获取当前行的记录
    record = query2.record()
    # 获取记录中“id”和“name”两个属性的值
    id = record.value("id").toInt()
    name = record.value("name").toString()
    print "id: %s name:  %s" % (id, name)
    # 获取索引为1的属性,即第二个属性
    field = record.field(1)
    # 输出属性名和属性值,结果为“name”和“MaLiang”
    print  "second field: %s field value: %s " % \
            (field.name(), field.value().toString())

    # app.exec_()
Пример #9
0
class QtSqlDBCursor:
    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 = unicode
            elif f.type() == QVariant.ByteArray:
                t = unicode
            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")
Пример #10
0
class QtSqlDBCursor:

    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 = unicode
            elif f.type() == QVariant.ByteArray:
                t = unicode
            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")