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
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
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'))
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
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
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
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
# 以下是在例程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_()
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")
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")