def make_descobjet_dict(self):
query = QSqlQuery(self.db)
query.exec_("SELECT * FROM DescObjet")
objet_dict = dict()
knownGroupe = []
knownEsapce = []
while query.next():
nom = str(query.record().value(0).toString())
libelle = unicode(query.record().value(1).toString(), "utf-8")
type_objet = str(query.record().value(2).toString())
groupe = str(query.record().value(3).toString())
espace = str(query.record().value(4).toString())
if not groupe in knownGroupe:
objet_dict[groupe] = dict()
knownGroupe.append(groupe)
if not espace in knownEsapce:
objet_dict[groupe][espace] = dict()
knownEsapce.append(espace)
objet_dict[groupe][espace][libelle] = dict()
objet_dict[groupe][espace][libelle]["nom"] = nom
objet_dict[groupe][espace][libelle]["type"] = type_objet
return objet_dict
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 list_name_libelle_type(self):
query = QSqlQuery(self.db)
query.exec_("SELECT * FROM DescObjet")
names_list = []
while query.next():
name = str(query.record().value(0).toString())
libelle = str(query.record().value(1).toString())
type_objet = str(query.record().value(2).toString())
names_list.append('name: "{0}"\nlibelle: "{1}"\ntypeObjet: "{2}"'.format(name, libelle, type_objet))
return names_list
def make_objet_table(self):
query = QSqlQuery(self.db)
query.exec_("SELECT * FROM DescObjet")
table = dict()
while query.next():
name = str(query.record().value(0).toString())
libelle = unicode(query.record().value(1).toString(), "utf-8")
type_objet = str(query.record().value(2).toString())
table[name] = dict()
table[name]["libelle"] = libelle
table[name]["type"] = type_objet
return table
def cols(self, abfrage):
sql = abfrage
query = QSqlQuery(sql,self.db)
query.next()
result = []
i = 0
while i < query.record().count():
result.append(str(query.record().fieldName(i)))
i = i + 1
return result
def colType(self, abfrage):
sql = abfrage
query = QSqlQuery(sql,self.db)
query.next()
datensatz = {}
i = 0
while i < query.record().count():
fieldType = query.record().field(i).type()
datensatz.update({str(query.record().fieldName(i)).upper(): query.record().field(i).value().typeToName(fieldType)})
i = i + 1
return datensatz
def populate_length_lineedit(self, mcl_ref):
"""
Calculate the length of the MCL and populate lineedit with data.
:param mcl_ref: int, id of the MCL to calculate
"""
# Don't do calculation if spatialite version is too low. (libgeos bug)
if lor.get_spatialite_version_as_int(self.db) < 430:
length_text = "Spatialite < 4.3.0"
self.dlg.ui.lengthLineEdit.setText(length_text)
return
# Run query
sql = """
SELECT GLength(geometry) AS length FROM mcl
WHERE mcl_ref = {}
;""".format(mcl_ref)
query = QSqlQuery(sql, self.db)
# Raise exception if query fails
if not query.first():
msg = ("Could not calculate MCL length. Query:\n{}\n"
"Database returned:\n{}".format(sql,
query.lastError().text()))
raise rn_except.MclFormLengthCalculationError(msg)
# Update field
length = query.record().value('length')
length_text = "{:.2f}".format(length)
self.dlg.ui.lengthLineEdit.setText(length_text)
def asd_last_date(self):
"""
get the last update date (e.g. latest of entry or updates date) from db to show in ASD label
:return: string
"""
last_update = 0
tbl_names = ["tblMAINT", "tblREINS_CAT", "tblSPEC_DES"]
for tbl_name in tbl_names:
query = QSqlQuery(self.db)
query.exec_(self.get_last_date.format(tbl_name=tbl_name))
query.next()
rec = query.record()
last_entry, last_close = (rec.value('LastEntry'),
rec.value('LastClose'))
if not isinstance(last_entry, QPyNullVariant):
if int(last_entry) > last_update:
last_update = last_entry
if not isinstance(last_close, QPyNullVariant):
if int(last_close) > last_update:
last_update = last_close
last_update_date = datetime.strptime(str(last_update), "%Y%m%d")
asd_date_clean = last_update_date.strftime("%d/%m/%Y")
return asd_date_clean
def results_to_csv_row(self, vals, sql, mode):
query = QSqlQuery(self.db)
query.exec_(sql)
rec = query.record()
if mode == "streets":
avals = [
rec.indexOf(vals[0]),
rec.indexOf(vals[1]),
rec.indexOf(vals[2]),
rec.indexOf(vals[3]),
rec.indexOf(vals[4]),
rec.indexOf(vals[5]),
rec.indexOf(vals[6]),
rec.indexOf(vals[7]),
rec.indexOf(vals[8]),
rec.indexOf(vals[9]),
rec.indexOf(vals[10]),
rec.indexOf(vals[11])
]
while query.next():
line = [
query.value(avals[0]),
query.value(avals[1]),
query.value(avals[2]),
query.value(avals[3]),
query.value(avals[4]),
query.value(avals[5]),
self.format_dates(str(query.value(avals[6]))),
self.format_dates(str(query.value(avals[7]))),
self.format_dates(str(query.value(avals[8]))),
self.format_dates(str(query.value(avals[9]))),
query.value(avals[10]),
query.value(avals[11])
]
self.csv.writerow(line)
else:
avals = [
rec.indexOf(vals[0]),
rec.indexOf(vals[1]),
rec.indexOf(vals[2]),
rec.indexOf(vals[3]),
rec.indexOf(vals[4]),
rec.indexOf(vals[5]),
rec.indexOf(vals[6]),
rec.indexOf(vals[7])
]
while query.next():
line = [
query.value(avals[0]),
query.value(avals[1]),
query.value(avals[2]),
query.value(avals[3]),
query.value(avals[4]),
query.value(avals[5]),
query.value(avals[6]),
query.value(avals[7])
]
self.csv.writerow(line)
def tableHasField(self, table, field):
"""does the table contain a column named field?"""
query = QSqlQuery(self)
query.exec_('select * from %s' % table)
record = query.record()
for idx in range(record.count()):
if record.fieldName(idx) == field:
return True
def tableHasField(dbhandle, table, field):
"""does the table contain a column named field?"""
query = QSqlQuery(dbhandle)
query.exec_('select * from %s' % table)
record = query.record()
for idx in range(record.count()):
if record.fieldName(idx) == field:
return True
def list_names(self):
query = QSqlQuery(self.db)
query.exec_("SELECT * FROM DescObjet")
names_list = []
while query.next():
name = str(query.record().value(0).toString())
names_list.append(name)
return names_list
def get_descobjet_list(self):
query = QSqlQuery(self.db)
if not query.exec_("SELECT * FROM DescObjet"):
logging.error("%s", query.lastError().text())
descobjet_list = []
while query.next():
dds_data = unicode(query.record().value(0).toString())
descobjet_list.append(dds_data)
return descobjet_list
def get_from_gaz_metadata(db, column):
"""
Get the gazetteer metadata from the database
(e.g. org name, custodian code)
:rtype: str
:return: metadata item
"""
sql = "SELECT {} FROM tblGazMetadata".format(column)
query = QSqlQuery(sql, db)
query.first()
gaz_metadata = query.value(query.record().indexOf(column))
return gaz_metadata
def get_lor_ref_2_values(db):
"""
Get lor_ref_2 values a list of ints.
:param db: QSqlDatabase
:return: list of ints
"""
sql = "SELECT lor_ref_2 FROM mcl;"
query = QSqlQuery(sql, db)
data = []
while query.next():
record = query.record()
# Skip last record that contains geometry
data.append(record.value('lor_ref_2'))
return data
def read(self, abfrage):
sql = abfrage
query = QSqlQuery(sql,self.db)
datensatz = {}
i = 0
while i < query.record().count():
result = []
query.first()
result.append(self.__pystring(query.value(i)))
lastError = query.lastError().text()
if len(lastError) > 1:
QMessageBox.information(None, self.tr('DB-Error'), lastError)
while query.next():
result.append(self.__pystring(query.value(i)))
lastError = query.lastError().text()
if len(lastError) > 1:
QMessageBox.information(None, self.tr('DB-Error'), lastError)
datensatz.update({str(query.record().fieldName(i)).upper(): result})
i = i + 1
return datensatz
def export_meta(self):
"""
Exports Metadata records and writes them to CSV in new line
:return:
"""
if self.version == 75:
sqlgazmetadata = "SELECT * from tblGazMetadata;"
query = QSqlQuery(self.db)
query.exec_(sqlgazmetadata)
query.first()
rec = query.record()
now_format = str(datetime.datetime.now().date())
aval = [rec.indexOf("name"),
rec.indexOf("scope"),
rec.indexOf("territory"),
rec.indexOf("owner"),
rec.indexOf("custodian"),
rec.indexOf("coord_sys"),
rec.indexOf("coord_units"),
rec.indexOf("metadata_date"),
rec.indexOf("class_scheme"),
rec.indexOf("code_scheme"),
rec.indexOf("current_date"),
rec.indexOf("gaz_language"),
rec.indexOf("charset"),
rec.indexOf("custodian_code")]
ameta = [29,
query.value(aval[0]),
query.value(aval[1]),
query.value(aval[2]),
query.value(aval[3]),
query.value(aval[4]),
query.value(aval[13]),
query.value(aval[5]),
query.value(aval[6]),
self.format_date(query.value(aval[7])),
query.value(aval[8]),
query.value(aval[9]),
now_format,
query.value(aval[12]),
query.value(aval[11])]
self.csv.writerow(ameta)
else:
pass
def populate_list(self, current):
"""
changes the content of the list widget according to the selected value
in the combo box
:param current: the current combo box index
:return: void
"""
# 0 = None, 1 = maintenance, 2 = reinstatement, 3 = special designation
qry = {
0: [],
1: [
'SELECT * from tlkpRoad_Status', 'Description',
'Road_Status_Ref'
],
2: [
'SELECT * from tlkpREINS_CAT', 'Description',
'Reinstatement_Code'
],
3: [
'SELECT designation_code,designation_text from tlkpSPEC_DES',
'Designation_Text', 'Designation_code'
]
}
# if the parameter is not null change the query accordingly
if current != 0:
query = QSqlQuery(self.db)
query.exec_(qry.get(current)[0])
rec = query.record()
# aval are the names of the indexes columns
aval = [
rec.indexOf(qry.get(current)[1]),
rec.indexOf(qry.get(current)[2])
]
self.street_dia.ui.listWidget.clear()
while query.next():
# adds values to widget list
entry = str(query.value(aval[1])) + ": " + str(
query.value(aval[0]))
self.street_dia.ui.listWidget.addItem(entry)
# Select the first item
first_item = self.street_dia.ui.listWidget.item(0)
first_item.setSelected(True)
else:
self.street_dia.ui.listWidget.clear()
def get_dds_data_list(self, id_objet, topic_name):
query = QSqlQuery(self.db)
# "DATA LIKE" to match things like AlarmeV2 even when the topic_name passed is Alarme
query.prepare(
'SELECT * FROM DDS WHERE idObjet = :idObjet AND topicName LIKE "{0}%"'
.format(topic_name))
query.bindValue(":idObjet", id_objet)
# query.bindValue(":topicName", topic_name)
if not query.exec_():
logging.error("%s", query.lastError().text())
dds_data_list = []
while query.next():
dds_data = unicode(query.record().value(3).toString())
dds_data_list.append(dds_data)
return dds_data_list
def get_linked_polys_in_db(self, mcl_ref):
"""
Get the polygons that have current mcl_ref
:param mcl_ref: str with reference
:return: list of strings
"""
sql = """
SELECT rd_pol_id FROM rdpoly
WHERE mcl_cref = {}
;""".format(mcl_ref)
query = QSqlQuery(sql, self.db)
linked_polys = []
while query.next():
record = query.record()
rd_pol_id = str(record.value('rd_pol_id'))
linked_polys.append(rd_pol_id)
return linked_polys
def load(self, query):
self.nesting = 1
self.root = BranchNode("")
try:
query = QSqlQuery(query)
if not query.exec_():
raise Exception("No se pudieron recuperar las categorias")
self.columns = query.record().count()
print query.size()
if query.size() > 0:
while query.next():
fields = []
for i in range(self.columns):
fields.append(str(query.value(i)))
self.addRecord(fields, False)
except Exception as inst:
print unicode(inst)
return False
def load(self,query):
self.nesting = 1
self.root = BranchNode("")
try:
query = QSqlQuery( query )
if not query.exec_():
raise Exception( "No se pudieron recuperar las categorias" )
self.columns = query.record().count()
print query.size()
if query.size()>0:
while query.next():
fields= []
for i in range (self.columns):
fields.append(str(query.value(i)))
self.addRecord(fields, False)
except Exception as inst:
print unicode( inst )
return False
def get_mcl_ref_from_rd_pol_id(self, rd_pol_id):
"""
Query database to get mcl_ref associated with given polygon
:param rd_pol_id: str, id number
:return mcl_ref: str, id number
"""
sql = """
SELECT mcl_cref FROM rdpoly
WHERE rd_pol_id = {}""".format(rd_pol_id)
query = QSqlQuery(sql, self.db)
if not query.first():
msg = "No MCLs are linked to polygon {}".format(rd_pol_id)
raise rn_except.RampNoLinkedPolyPopupError(msg)
mcl_ref = query.record().value('mcl_cref')
if isinstance(mcl_ref, QPyNullVariant):
msg = "No MCLs are linked to polygon {}".format(rd_pol_id)
raise rn_except.RampNoLinkedPolyPopupError(msg)
return str(mcl_ref)
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
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
def get_mcl_cref(self, rd_pol_id):
"""
Get the MCL ref attached to given polygon
:param rd_pol_id:
:return: str, mcl_cref
"""
sql = """
SELECT mcl_cref FROM rdpoly
WHERE rd_pol_id = '{}'
;""".format(rd_pol_id)
query = QSqlQuery(sql, self.db)
if not query.isActive():
msg = "Invalid rd_pol_id:"
msg += "\n\nSQL command:\n\n{}".format(sql)
msg += "\n\nDatabase reply:\n\n{}".format(query.lastError().text())
raise rn_except.RampRdPolyUpdateFailedPopupError(msg)
query.first()
mcl_ref = str(query.record().value('mcl_cref'))
return mcl_ref
def get_spatialite_version_as_int(db):
"""
Query the database to find spatialite version
:param db: open QSqlDatabase object
:return: Integer form of version number e.g. 411
"""
# Query the database
sql = "SELECT spatialite_version() AS version;"
query = QSqlQuery(sql, db)
query_success = query.first()
if query_success is False:
msg = "Cannot get spatialite version. Database replied: {}".format(
query.lastError().text())
raise RuntimeError(msg)
# Get the version number and convert to int
record = query.record()
version = record.value('version')
version_as_int = int(re.sub('\D', '', version))
return version_as_int
def set_values(self):
"""
set all required values to show in the form widgets from db
:return: string[]
"""
i = 0
meta_values = []
qry_md_vals = QSqlQuery(self.db)
qry_md_vals.exec_(self.get_meta_info)
rec = qry_md_vals.record()
field_count = rec.count()
meta_vals = [
rec.indexOf("name"),
rec.indexOf("scope"),
rec.indexOf("territory"),
rec.indexOf("owner"),
rec.indexOf("custodian"),
rec.indexOf("coord_sys"),
rec.indexOf("coord_units"),
rec.indexOf("metadata_date"),
rec.indexOf("class_scheme"),
rec.indexOf("code_scheme"),
rec.indexOf("custodian_code"),
rec.indexOf("gaz_language"),
rec.indexOf("charset")
]
while qry_md_vals.next():
while i <= field_count - 1:
if i == 7: # handles date formatting
date_obj = datetime.strptime(
str(qry_md_vals.value(meta_vals[i])), "%Y%m%d")
meta_date_clean = str(date_obj.strftime("%d/%m/%Y"))
meta_values.append(meta_date_clean)
i += 1
continue
meta_values.append(str(qry_md_vals.value(meta_vals[i])))
i += 1
return meta_values
def get_mcl_attrs_for_rdpoly(self, mcl_ref):
"""
Get values from database and prepare attributes to insert into rdpoly
table.
:param mcl_ref: str, mcl_ref
:return: dict, mcl_attributes
"""
sql = """
SELECT lor_ref_1 || "/" || lor_ref_2 AS part_label
FROM mcl WHERE mcl_ref={};""".format(mcl_ref)
query = QSqlQuery(sql, self.db)
if not query.isActive():
msg = "Failed to get MCL attributes."
msg += "\n\nSQL command:\n\n{}".format(sql)
msg += "\n\nDatabase reply:\n\n{}".format(query.lastError().text())
raise rn_except.RampRdPolyUpdateFailedPopupError(msg)
query.first()
part_label = query.record().value("part_label")
mcl_attrs = {'mcl_cref': mcl_ref, 'part_label': part_label}
return mcl_attrs
def _get_foreign_key_id(self, row, col):
"""
Return the foreign key used in the relational model i.e. the value stored in
the master database table. This is a hack because the relationModel method was
failing. Column IDs are used because names can change in model.
:param row: Row of the model
:param col: Column of the model
:return: Integer data row id converted to a string.
"""
record = self.model.record(row)
pk_uid = record.field('PK_UID').value()
# Using SELECT * is also a horrible hack, but the model has different column names
# to the original table. This will break if SELECT statement for model changes.
sql = """SELECT *
FROM tblSTREET WHERE
PK_UID IS {pk_uid};""".format(pk_uid=pk_uid)
db = self.model.database()
foreign_key_query = QSqlQuery(sql, db)
if not foreign_key_query.next():
# Something went wrong when selecting first result
raise ValueError(foreign_key_query.lastError().text())
record = foreign_key_query.record()
id_as_string = "{}".format(record.value(col))
return id_as_string
def get_road_length_from_db(sql, db):
"""
Run pre-prepared sql query on database to get length of road
:param sql: str sql selecting by road classification etc
:param db: open QSqlDatabase object
:return: float length of road
"""
query = QSqlQuery(sql, db)
query_success = query.first()
if query_success is False:
msg = ("Length of Roads calculation failed running SQL:\n{}\n"
"Database output: {}".format(sql,
query.lastError().text()))
raise rn_except.LengthOfRoadsCalculationDatabaseError(msg)
# Get result from query
record = query.record()
road_length = record.value('length')
if isinstance(road_length, PyQt4.QtCore.QPyNullVariant):
# Replace NULL from database with zero length
road_length = 0
return road_length
# 以下是在例程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_()
def updateTextBrowser(self):
# check to make sure we have a feature selected in our selectList -- note that there might be more than one feature
phoutput = ""
moistureoutput = ""
if self.phList:
# ############ EXAMPLE 1 EDITS GO HERE ####################
''' write code that will output ALL selected feature attributes for a single feature into the Text Browser'''
''' instead of using the dataProvider.select() function get the actual QgsFeature using dataProvider.featureAtId() '''
# get the feature by passing in empty Feature
request = QgsFeatureRequest(self.phList[0])
for f in self.pHLayer.getFeatures(request):
phoutput = "Soil pH Level: %s \n" % (f['pH'])
if self.MoistureList:
request = QgsFeatureRequest(self.MoistureList[0])
for f in self.MoistureLayer.getFeatures(request):
moistureoutput = "Soil Moisture Level: %s \n" % (f['moisture_level'])
output = phoutput + moistureoutput
self.dlg.setTextBrowser(output)
provider = self.pHLayer.dataProvider()
if provider.name() == 'postgres':
# get the URI containing the connection parameters
uri = QgsDataSourceURI(provider.dataSourceUri())
print uri.uri()
# create a PostgreSQL connection using QSqlDatabase
db = QSqlDatabase.addDatabase('QPSQL')
# check to see if it is valid
if db.isValid():
print "QPSQL db is valid"
# set the parameters needed for the connection
db.setHostName(uri.host())
db.setDatabaseName(uri.database())
db.setPort(int(uri.port()))
db.setUserName(uri.username())
db.setPassword(uri.password())
# open (create) the connection
if db.open():
print "Opened %s" % uri.uri()
# execute a simple query
#query = db.exec_("""select genus,species from botanical_name where genus = 'Prunus'""")
query = QSqlQuery ("""select genus,species from botanical_name where genus = 'Prunus'""")
self.dlg.tblPlants.clear()
self.dlg.tblPlants.setRowCount(query.size())
self.dlg.tblPlants.setColumnCount(query.record().count())
self.dlg.tblPlants.setHorizontalHeaderLabels(["Genus", "Species"])
self.dlg.tblPlants.setSelectionMode(QTableWidget.SingleSelection)
self.dlg.tblPlants.setSelectionBehavior(QTableWidget.SelectRows)
# loop through the result set and print the name
index=0
while query.next():
record = query.record()
self.dlg.tblPlants.setItem(index, 0, QTableWidgetItem(query.value(0)))
self.dlg.tblPlants.setItem(index, 1, QTableWidgetItem(query.value(1)))
index = index+1
# print record.field('name').value().toString()
self.dlg.tblPlants.resizeColumnsToContents()
else:
err = db.lastError()
print err.driverText()
class Query(object):
"""a more pythonic interface to QSqlQuery. We could instead use
the python sqlite3 module but then we would either have to do
more programming for the model/view tables, or we would have
two connections to the same database.
For selecting queries we fill a list with ALL records.
Every record is a list of all fields. q.records[0][1] is record 0, field 1.
For select, we also convert to python data
types - as far as we need them"""
localServerName = m18ncE('kajongg name for local game server', 'Local Game')
def __init__(self, cmdList, args=None, dbHandle=None, silent=False, mayFail=False):
"""we take a list of sql statements. Only the last one is allowed to be
a select statement.
Do prepared queries by passing a single query statement in cmdList
and the parameters in args. If args is a list of lists, execute the
prepared query for every sublist.
If dbHandle is passed, use that for db access.
Else if the default dbHandle (DBHandle.default) is defined, use it."""
# pylint: disable=R0912
# pylint says too many branches
silent |= not Debug.sql
self.dbHandle = dbHandle or DBHandle.default
preparedQuery = not isinstance(cmdList, list) and bool(args)
self.query = QSqlQuery(self.dbHandle)
self.msg = None
self.records = []
if not isinstance(cmdList, list):
cmdList = list([cmdList])
self.cmdList = cmdList
for cmd in cmdList:
retryCount = 0
while retryCount < 100:
self.lastError = None
if preparedQuery:
self.query.prepare(cmd)
if not isinstance(args[0], list):
args = list([args])
for dataSet in args:
if not silent:
_, utf8Args = xToUtf8(u'', dataSet)
logDebug("{cmd} [{args}]".format(cmd=cmd, args=", ".join(utf8Args)))
for value in dataSet:
self.query.addBindValue(QVariant(value))
self.success = self.query.exec_()
if not self.success:
break
else:
if not silent:
logDebug('%s %s' % (self.dbHandle.name, cmd))
self.success = self.query.exec_(cmd)
if self.success or self.query.lastError().number() not in (5, 6):
# 5: database locked, 6: table locked. Where can we get symbols for this?
break
time.sleep(0.1)
retryCount += 1
if not self.success:
self.lastError = unicode(self.query.lastError().text())
self.msg = 'ERROR in %s: %s' % (self.dbHandle.databaseName(), self.lastError)
if mayFail:
if not silent:
logDebug(self.msg)
else:
logException(self.msg)
return
self.records = None
self.fields = None
if self.query.isSelect():
self.retrieveRecords()
def rowcount(self):
"""how many rows were affected?"""
return self.query.numRowsAffected()
def retrieveRecords(self):
"""get all records from SQL into a python list"""
record = self.query.record()
self.fields = [record.field(x) for x in range(record.count())]
self.records = []
while self.query.next():
self.records.append([self.__convertField(x) for x in range(record.count())])
def __convertField(self, idx):
"""convert a QSqlQuery field into a python value"""
result = self.query.value(idx).toPyObject()
if isinstance(result, QString):
result = unicode(result)
if isinstance(result, long) and -sys.maxint -1 <= result <= sys.maxint:
result = int(result)
return result
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")
class Query(object):
"""a more pythonic interface to QSqlQuery. We could instead use
the python sqlite3 module but then we would either have to do
more programming for the model/view tables, or we would have
two connections to the same database.
For selecting queries we fill a list with ALL records.
Every record is a list of all fields. q.records[0][1] is record 0, field 1.
For select, we also convert to python data
types - as far as we need them"""
dbhandle = None
localServerName = m18ncE('kajongg name for local game server', 'Local Game')
def __init__(self, cmdList, args=None, dbHandle=None, silent=False, mayFail=False):
"""we take a list of sql statements. Only the last one is allowed to be
a select statement.
Do prepared queries by passing a single query statement in cmdList
and the parameters in args. If args is a list of lists, execute the
prepared query for every sublist.
If dbHandle is passed, use that for db access.
Else if the default dbHandle (Query.dbhandle) is defined, use it."""
# pylint: disable=R0912
# pylint says too many branches
silent |= not Debug.sql
self.dbHandle = dbHandle or Query.dbhandle
assert self.dbHandle
preparedQuery = not isinstance(cmdList, list) and bool(args)
self.query = QSqlQuery(self.dbHandle)
self.msg = None
self.records = []
if not isinstance(cmdList, list):
cmdList = list([cmdList])
self.cmdList = cmdList
for cmd in cmdList:
retryCount = 0
while retryCount < 100:
self.lastError = None
if preparedQuery:
self.query.prepare(cmd)
if not isinstance(args[0], list):
args = list([args])
for dataSet in args:
if not silent:
logDebug('%s %s' % (cmd, dataSet))
for value in dataSet:
self.query.addBindValue(QVariant(value))
self.success = self.query.exec_()
if not self.success:
break
else:
if not silent:
logDebug(cmd)
self.success = self.query.exec_(cmd)
if self.success or self.query.lastError().number() not in (5, 6):
# 5: database locked, 6: table locked. Where can we get symbols for this?
break
time.sleep(0.1)
retryCount += 1
if not self.success:
self.lastError = unicode(self.query.lastError().text())
self.msg = 'ERROR in %s: %s' % (self.dbHandle.databaseName(), self.lastError)
if mayFail:
if not silent:
logDebug(self.msg)
else:
logError(self.msg)
return
self.records = None
self.fields = None
if self.query.isSelect():
self.retrieveRecords()
def rowcount(self):
"""how many rows were affected?"""
return self.query.numRowsAffected()
def retrieveRecords(self):
"""get all records from SQL into a python list"""
record = self.query.record()
self.fields = [record.field(x) for x in range(record.count())]
self.records = []
while self.query.next():
self.records.append([self.__convertField(x) for x in range(record.count())])
def __convertField(self, idx):
"""convert a QSqlQuery field into a python value"""
field = self.fields[idx]
name = str(field.name())
valType = field.type()
if valType == QVariant.String:
value = unicode(self.query.value(idx).toString())
elif valType == QVariant.Double:
value = self.query.value(idx).toDouble()[0]
elif valType == QVariant.Int:
value = unicode(self.query.value(idx).toString())
if '.' in value:
# rule.limits is defined as integer in older versions
# but we save floats anyway. Sqlite3 lets us do a lot
# of illegal things...
value = self.query.value(idx).toDouble()[0]
else:
value = self.query.value(idx).toInt()[0]
elif valType == QVariant.UInt:
value = self.query.value(idx).toUInt()[0]
elif valType == QVariant.LongLong:
value = self.query.value(idx).toLongLong()[0]
elif valType == QVariant.ULongLong:
value = self.query.value(idx).toULongLong()[0]
elif valType == QVariant.Invalid:
value = None
else:
raise Exception('Query: variant type %s not implemented for field %s ' % \
(QVariant.typeToName(valType), name))
return value
@staticmethod
def tableHasField(dbhandle, table, field):
"""does the table contain a column named field?"""
query = QSqlQuery(dbhandle)
query.exec_('select * from %s' % table)
record = query.record()
for idx in range(record.count()):
if record.fieldName(idx) == field:
return True
schema = {}
schema['player'] = """
id INTEGER PRIMARY KEY,
name TEXT unique"""
schema['game'] = """
id integer primary key,
seed text,
autoplay integer default 0,
starttime text default current_timestamp,
endtime text,
ruleset integer references usedruleset(id),
p0 integer constraint fk_p0 references player(id),
p1 integer constraint fk_p1 references player(id),
p2 integer constraint fk_p2 references player(id),
p3 integer constraint fk_p3 references player(id)"""
schema['score'] = """
game integer constraint fk_game references game(id),
hand integer,
data text,
manualrules text,
rotated integer,
notrotated integer,
player integer constraint fk_player references player(id),
scoretime text,
won integer,
penalty integer default 0,
prevailing text,
wind text,
points integer,
payments integer,
balance integer"""
schema['ruleset'] = """
id integer primary key,
name text unique,
hash text,
lastused text,
description text"""
schema['rule'] = """
ruleset integer,
list integer,
position integer,
name text,
definition text,
points text,
doubles text,
limits text,
parameter text,
primary key(ruleset,list,position),
unique (ruleset,name)"""
schema['usedruleset'] = """
id integer primary key,
name text,
hash text,
lastused text,
description text"""
schema['usedrule'] = """
ruleset integer,
list integer,
position integer,
name text,
definition text,
points text,
doubles integer,
limits integer,
parameter text,
primary key(ruleset,list,position),
unique (ruleset,name)"""
schema['server'] = """
url text,
lastname text,
lasttime text,
lastruleset integer,
primary key(url)"""
schema['passwords'] = """
url text,
player integer,
password text"""
schema['general'] = """
ident text"""
@staticmethod
def createTable(dbhandle, table):
"""create a single table using the predefined schema"""
if table not in dbhandle.driver().tables(QSql.Tables):
Query("create table %s(%s)" % (table, Query.schema[table]), dbHandle=dbhandle)
@staticmethod
def createTables(dbhandle):
"""creates empty tables"""
for table in ['player', 'game', 'score', 'ruleset', 'rule', 'usedruleset', 'usedrule']:
Query.createTable(dbhandle, table)
Query.createIndex(dbhandle, 'idxgame', 'score(game)')
if InternalParameters.isServer:
Query('ALTER TABLE player add password text', dbHandle=dbhandle)
else:
Query.createTable(dbhandle, 'passwords')
Query.createTable(dbhandle, 'server')
@staticmethod
def createIndex(dbhandle, name, cmd):
"""only try to create it if it does not yet exist. Do not use create if not exists because
we want debug output only if we really create the index"""
if not Query("select 1 from sqlite_master where type='index' and name='%s'" % name,
dbHandle=dbhandle, silent=True).records:
Query("create index %s on %s" % (name, cmd), dbHandle=dbhandle)
@staticmethod
def cleanPlayerTable(dbhandle):
"""remove now unneeded columns host, password and make names unique"""
playerCounts = IntDict()
names = {}
keep = {}
for nameId, name in Query('select id,name from player', dbHandle=dbhandle).records:
playerCounts[name] += 1
names[int(nameId)] = name
for name, counter in defaultdict.items(playerCounts):
nameIds = [x[0] for x in names.items() if x[1] == name]
keepId = nameIds[0]
keep[keepId] = name
if counter > 1:
for nameId in nameIds[1:]:
Query('update score set player=%d where player=%d' % (keepId, nameId), dbHandle=dbhandle)
Query('update game set p0=%d where p0=%d' % (keepId, nameId), dbHandle=dbhandle)
Query('update game set p1=%d where p1=%d' % (keepId, nameId), dbHandle=dbhandle)
Query('update game set p2=%d where p2=%d' % (keepId, nameId), dbHandle=dbhandle)
Query('update game set p3=%d where p3=%d' % (keepId, nameId), dbHandle=dbhandle)
Query('delete from player where id=%d' % nameId, dbHandle=dbhandle)
Query('drop table player', dbHandle=dbhandle)
Query.createTable(dbhandle, 'player')
for nameId, name in keep.items():
Query('insert into player(id,name) values(?,?)', list([nameId, name]), dbHandle=dbhandle)
@staticmethod
def removeGameServer(dbhandle):
"""drops column server from table game. Sqlite3 cannot drop columns"""
Query('create table gameback(%s)' % Query.schema['game'], dbHandle=dbhandle)
Query('insert into gameback '
'select id,seed,autoplay,starttime,endtime,ruleset,p0,p1,p2,p3 from game', dbHandle=dbhandle)
Query('drop table game', dbHandle=dbhandle)
Query('create table game(%s)' % Query.schema['game'], dbHandle=dbhandle)
Query('insert into game '
'select id,seed,autoplay,starttime,endtime,ruleset,p0,p1,p2,p3 from gameback', dbHandle=dbhandle)
Query('drop table gameback', dbHandle=dbhandle)
@staticmethod
def upgradeDb(dbhandle):
"""upgrade any version to current schema"""
# TODO: scan rulesets and usedrulesets for unfinished games
# for regex. Warn before removing such rulesets and setting those
# unfinished games to finished. Alternative is to downgrade kajongg.
Query.createIndex(dbhandle, 'idxgame', 'score(game)')
if not Query.tableHasField(dbhandle, 'game', 'autoplay'):
Query('ALTER TABLE game add autoplay integer default 0', dbHandle=dbhandle)
if not Query.tableHasField(dbhandle, 'score', 'penalty'):
Query('ALTER TABLE score add penalty integer default 0', dbHandle=dbhandle)
Query("UPDATE score SET penalty=1 WHERE manualrules LIKE "
"'False Naming%' OR manualrules LIKE 'False Decl%'", dbHandle=dbhandle)
if Query.tableHasField(dbhandle, 'player', 'host'):
Query.cleanPlayerTable(dbhandle)
if InternalParameters.isServer:
if not Query.tableHasField(dbhandle, 'player', 'password'):
Query('ALTER TABLE player add password text', dbHandle=dbhandle)
else:
Query.createTable(dbhandle, 'passwords')
if not Query.tableHasField(dbhandle, 'server', 'lastruleset'):
Query('alter table server add lastruleset integer', dbHandle=dbhandle)
if Query.tableHasField(dbhandle, 'game', 'server'):
Query.removeGameServer(dbhandle)
if not Query.tableHasField(dbhandle, 'score', 'notrotated'):
Query('ALTER TABLE score add notrotated integer default 0', dbHandle=dbhandle)
