def getGeomColumnDict(self):
'''
Dict in the form 'geomName':[-list of table names-]
'''
self.checkAndOpenDb()
sql = self.gen.getGeomColumnDict()
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem getting geom column dict: ") +
query.lastError().text())
geomDict = dict()
while query.next():
geomColumn = query.value(0)
tableName = query.value(1)
lyrName = '_'.join(tableName.split('_')[1::])
if geomColumn not in geomDict.keys():
geomDict[geomColumn] = []
geomDict[geomColumn].append(lyrName)
return geomDict
def obtainLinkColumn(self, complexClass, aggregatedClass):
'''
Obtains the link column between complex and aggregated class
complexClass: complex class name
aggregatedClass: aggregated class name
'''
self.checkAndOpenDb()
#query to obtain the link column between the complex and the feature layer
sql = self.gen.getLinkColumn(complexClass.replace('complexos_', ''),
aggregatedClass)
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem obtaining link column: ") +
query.lastError().text())
column_name = ""
while query.next():
column_name = query.value(0)
return column_name
def getStructureDict(self):
try:
self.checkAndOpenDb()
except:
return dict()
classDict = dict()
sql = self.gen.getStructure(self.getDatabaseVersion())
query = QSqlQuery(sql, self.db)
while query.next():
className = str(query.value(0))+'.'+str(query.value(1))
fieldName = str(query.value(2))
if str(query.value(0)) == 'complexos' or className.split('_')[-1] in ['p','l','a']:
if className not in classDict.keys():
classDict[className]=dict()
classDict[className][fieldName]=fieldName
if 'geom' in classDict[className].keys():
classDict[className]['geom'] = 'GEOMETRY'
if str(query.value(0)) <> 'complexos' and 'id' in classDict[className].keys():
classDict[className]['id'] = 'OGC_FID'
return classDict
def listGeomClassesFromDatabase(self, primitiveFilter=[]):
'''
Gets a list with geometry classes from database
'''
self.checkAndOpenDb()
classList = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem listing geom classes: ") +
query.lastError().text())
while query.next():
tableName = str(query.value(0))
layerName = tableName
if tableName.split("_")[-1] == "p" or tableName.split("_")[-1] == "l" \
or tableName.split("_")[-1] == "a":
classList.append(layerName)
return classList
def countElements(self, layers):
'''
Counts the number of elements in each layer present in layers
'''
self.checkAndOpenDb()
listaQuantidades = []
for layer in layers:
(table, schema) = self.getTableSchema(layer)
if layer.split('_')[-1] in ['p', 'l', 'a'
] or schema == 'complexos':
sql = self.gen.getElementCountFromLayer(layer)
query = QSqlQuery(sql, self.db)
query.next()
number = query.value(0)
if not query.exec_(sql):
raise Exception(
self.tr("Problem counting elements: ") +
query.lastError().text())
listaQuantidades.append([layer, number])
return listaQuantidades
def close_current_record(self, usrn, closure_date, state):
"""
Close the street record
:param usrn: current USRN
:param closure_date: date string (yyyymmdd)
:param state: index of state combo
"""
sql = """UPDATE tblSTREET
SET currency_flag=1,
closure_date={closure_date},
closed_by='{username}',
street_state={state},
state_date={closure_date}
WHERE usrn={usrn} AND
currency_flag=0;
""".format(closure_date=closure_date,
usrn=usrn,
username=self.params['UserName'],
state=state)
query = QSqlQuery(sql, self.db)
def listComplexClassesFromDatabase(self):
'''
Gets a list with complex classes from database
'''
self.checkAndOpenDb()
classList = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem listing complex classes: ") +
query.lastError().text())
while query.next():
tableName = str(query.value(0))
layerName = tableName
tableSchema = layerName.split('_')[0]
if tableSchema == 'complexos':
classList.append(layerName)
return classList
def getLayersWithElementsV2(self, layerList, useInheritance=False):
self.checkAndOpenDb()
lyrWithElemList = []
for layer in layerList:
if isinstance(layer, dict):
schema = layer['tableSchema']
lyr = layer['tableName']
else:
if '.' in layer:
schema, lyr = layer.replace('"', '').split('.')
else:
lyr = layer
schema = self.getTableSchemaFromDb(lyr)
sql = self.gen.getElementCountFromLayerV2(schema, lyr,
useInheritance)
query = QSqlQuery(sql, self.db)
query.next()
if query.value(0) > 0:
lyrWithElemList.append(lyr)
return lyrWithElemList
def reg_producto(self):
db = QSqlDatabase.addDatabase('QMYSQL')
db.setHostName("localhost")
db.setDatabaseName("TiendaVrt")
db.setUserName("root")
db.setPassword("")
if not db.open():
print("Could not open testdb database")
print(db.lastError().driverText())
print(db.lastError().databaseText())
else:
query = QSqlQuery()
query.exec_("CALL TiendaVrt.InsertarProducto(" +
self.txt_cod.text() + ",'" + self.txt_prod.text() +
"', " + self.txt_precio.text() + ", " +
self.txt_cant.text() + ", " + self.txt_cod_cat.text() +
");")
print("Database is OK ")
db.close()
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 makeValueRelationDict(self, table, codes):
"""
Makes the value relation dictionary. It is necessary for multi valued attributes
"""
ret = dict()
in_clause = '(%s)' % ",".join(map(str, codes))
if self.db.driverName() == 'QPSQL':
sql = 'select code, code_name from dominios.%s where code in %s' % (
table, in_clause)
elif self.db.driverName() == 'QSQLITE':
sql = 'select code, code_name from dominios_%s where code in %s' % (
table, in_clause)
query = QSqlQuery(sql, self.db)
while query.next():
code = str(query.value(0))
code_name = query.value(1)
ret[code_name] = code
return ret
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 street_ref_types(self, esu_id):
"""
Get the street type from the db
:param esu_id: esu ID
:return: List of all types associated with esu
"""
sql = """
SELECT tblSTREET.street_ref_type
FROM lnkESU_STREET INNER JOIN tblSTREET
ON lnkESU_STREET.usrn_version_no = tblSTREET.version_no
AND lnkESU_STREET.usrn = tblSTREET.usrn
WHERE lnkESU_STREET.esu_id = {esu_id}
AND lnkESU_STREET.currency_flag = 0
AND tblSTREET.currency_flag = 0
;""".format(esu_id=esu_id)
query = QSqlQuery(sql, self.db)
all_type_links = []
while query.next():
type_ = int(query.value(0))
all_type_links.append(type_)
return all_type_links
def getDefinition(uuid, oc):
remote_sql = QSqlQuery(get_remote_connection())
if oc:
remote_sql.prepare(get_definition_query_OC)
else:
remote_sql.prepare(get_definition_query)
remote_sql.bindValue(":uuid", uuid.__str__())
if remote_sql.exec_():
if remote_sql.next():
definition = remote_sql.value(0).__str__()
definition = definition.replace(' ', ' ')
definition = definition.replace(' </A>', '</A> ')
fs = re.findall(r'<[Aa][^>]*> ', definition)
for f in fs:
new_f = f.strip()
definition = definition.replace(f, new_f)
return definition
else:
return None
else:
return None
def dropDatabase(self, db):
"""
Drops the created database case a problem occurs during database creation
db: QSqlDatabase to be dropped
"""
host = db.hostName()
port = db.port()
user = db.userName()
password = db.password()
database = 'postgres'
pgDB = QSqlDatabase('QPSQL')
pgDB.setHostName(host)
pgDB.setPort(port)
pgDB.setUserName(user)
pgDB.setPassword(password)
pgDB.setDatabaseName(database)
if not pgDB.open():
return False
sql = self.gen.dropDatabase(db.databaseName())
query = QSqlQuery(pgDB)
return query.exec_(sql)
def setQuery(self, str, db=None):
if db == None:
self.query = QSqlQuery(str)
else:
self.query = str
QSqlQueryModel.setQuery(self, str)
del self.data
self.data = []
self.rCount = QSqlQueryModel.rowCount(self)
if self.rCount > 10000:
self.rCount = 10000
self.cCount = QSqlQueryModel.columnCount(self)
for i in range(self.rCount):
row = []
for j in range(self.cCount):
row.append(
QSqlQueryModel.data(self, QSqlQueryModel.index(self, i,
j)))
self.data.append(row)
self.clear()
print self.rowCount(), self.columnCount()
def _run_test_query(db_file_path):
"""
Runs SQL (sqlite) query to test database file is valid. Returns
True for success, False otherwise.
:param db: Open QSqlDatabase object
:return bool: True if query runs succesfully.
"""
if config.DEBUG_MODE:
print('DEBUG_MODE: testing database connection')
db = connect_and_open(db_file_path, 'test_connection')
query = QSqlQuery(db=db)
result = query.exec_("""SELECT * FROM sqlite_master""")
del (query)
connection_name = db.connectionName()
db.close()
del (db)
QSqlDatabase.removeDatabase(connection_name)
if config.DEBUG_MODE:
print('DEBUG_MODE: closing QSqlDatabase {}'.format(connection_name))
return result
def makeValueRelationDict(self, table, codes):
"""
Makes a query to obtain a dictionary with code names and related codes
"""
ret = dict()
in_clause = '(%s)' % ",".join(map(str, codes))
if self.abstractDb.db.driverName() == 'QPSQL':
sql = 'select code, code_name from dominios.%s where code in %s' % (
table, in_clause)
elif self.abstractDb.db.driverName() == 'QSQLITE':
sql = 'select code, code_name from dominios_%s where code in %s' % (
table, in_clause)
query = QSqlQuery(sql, self.abstractDb.db)
while query.next():
code = query.value(0)
code_name = query.value(1)
ret[code_name] = code
return ret
def getGeomDict(self, getCentroids=False):
'''
returns a dict like this:
{'tablePerspective' : {
'layerName' :
'''
self.checkAndOpenDb()
edgvVersion = self.getDatabaseVersion()
sql = self.gen.getGeomTablesFromGeometryColumns(edgvVersion)
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem getting geom tables from db: ") +
query.lastError().text())
geomDict = dict()
geomDict['primitivePerspective'] = self.getGeomTypeDict()
geomDict['tablePerspective'] = dict()
while query.next():
isCentroid = False
srid = query.value(0)
if edgvVersion in ('2.1.3', 'FTer_2a_Ed'):
geometryType = query.value(2)
else:
geometryType = self.getResolvedGeomType(query.value(2))
tableName = query.value(3)
tableSchema = tableName.split('_')[0]
geometryColumn = query.value(1)
layerName = '_'.join(tableName.split('_')[1::])
if layerName not in geomDict['tablePerspective'].keys():
geomDict['tablePerspective'][layerName] = dict()
geomDict['tablePerspective'][layerName]['schema'] = tableSchema
geomDict['tablePerspective'][layerName]['srid'] = str(srid)
geomDict['tablePerspective'][layerName][
'geometryColumn'] = geometryColumn
geomDict['tablePerspective'][layerName][
'geometryType'] = geometryType
geomDict['tablePerspective'][layerName][
'tableName'] = tableName
return geomDict
def getGeomTypeDict(self, loadCentroids=False):
self.checkAndOpenDb()
edgvVersion = self.getDatabaseVersion()
sql = self.gen.getGeomByPrimitive(edgvVersion)
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem getting geom types from db: ") +
query.lastError().text())
geomDict = dict()
while query.next():
if edgvVersion in ('2.1.3', 'FTer_2a_Ed'):
type = query.value(0)
else:
type = self.getResolvedGeomType(query.value(0))
tableName = query.value(1)
layerName = '_'.join(tableName.split('_')[1::])
if type not in geomDict.keys():
geomDict[type] = []
if layerName not in geomDict[type]:
geomDict[type].append(layerName)
return geomDict
def insertFrame(self, scale, mi, inom, frame, paramDict=dict()):
self.checkAndOpenDb()
srid = self.findEPSG()
geoSrid = QgsCoordinateReferenceSystem(
int(srid)).geographicCRSAuthId().split(':')[-1]
sql = self.gen.insertFrame(scale,
mi,
inom,
frame,
srid,
geoSrid,
paramDict=paramDict)
self.db.transaction()
query = QSqlQuery(self.db)
if not query.exec_(sql):
self.db.rollback()
self.db.close()
raise Exception(
self.tr('Problem inserting frame: ') +
query.lastError().text())
self.db.commit()
self.db.close()
def getUserRelatedRoles(self, username):
try:
self.checkAndOpenDb()
except:
return [], []
installed = []
assigned = []
sql = self.gen.getUserRelatedRoles(username)
query = QSqlQuery(sql, self.db)
while query.next():
rolname = query.value(0)
usename = query.value(1)
if not usename:
installed.append(rolname)
else:
assigned.append(rolname)
installed.sort()
assigned.sort()
return installed, assigned
def createResolvedDomainViews(self, createViewClause, fromClause):
try:
self.checkAndOpenDb()
except:
return
if self.checkSuperUser():
filename = self.getSqlViewFile()
if filename <> None:
file = codecs.open(filename, encoding='utf-8', mode="r")
sql = file.read()
sql = sql.replace('[VIEW]', createViewClause).replace('[FROM]', fromClause)
file.close()
commands = sql.split('#')
self.db.transaction()
query = QSqlQuery(self.db)
for command in commands:
if not query.exec_(command):
self.db.rollback()
self.db.close()
raise Exception(self.tr('Problem creating views: ') + str(query.lastError().text()))
self.db.commit()
self.db.close()
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 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 fill_fields(self):
query = QSqlQuery(get_remote_connection())
if self.onlyClassified:
query.prepare(get_name_and_short_def_query_OC)
else:
query.prepare(get_name_and_short_def_query)
query.bindValue(':uuid', self.uuid)
if query.exec_():
if query.next():
self.mainWordLabel.setText('Ссылка на термин: <b>' + query.value(0) + "</b>")
short_def = remove_all_tags(query.value(1))
if len(query.value(1)) < 299:
self.definitionLabel.setText("Определение: " + short_def)
else:
self.definitionLabel.setText("Определение: " + short_def + '...')
else:
self.mainWordLabel.setText('Термин не найден')
self.definitionLabel.setText("Возможно термин, на который ведет данная ссылка был удален или"
" Вы подключились к другой БД. Пожалуйста, обновите список терминов.")
else:
print(query.lastError().text())
print(query.lastQuery())
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 save(self, iddocumento, linea):
"""
Este metodo guarda la linea en la base de datos
@param iddocumento: el id del documento al que esta enlazada la linea
"""
if not self.valid:
raise Exception("Se intento guardar una linea no valida")
query = QSqlQuery()
if not query.prepare("""
INSERT INTO productosxdocumento (iddocumento, idprecioproducto,cantidadcajas,linea)
VALUES( :iddocumento, :idarticulo, :unidades,:linea)
"""):
raise Exception("no esta preparada")
query.bindValue(":iddocumento", iddocumento)
query.bindValue(":idarticulo", self.itemId)
query.bindValue(":unidades", self.quantity * -1)
query.bindValue(":linea", linea)
if not query.exec_():
print(query.lastError().text())
raise Exception("line %d" % self.itemId)
def CadastrarVeiculo(veiculo):
conn = ConexaoSQL
db = conn.getConexao()
db.open()
query = QSqlQuery()
query.prepare(
"INSERT INTO Veiculo(Modelo, Marca, AnoModelo, Placa, "
"Alugado, Batido, KmAtual, ValorDiaria, Descricao, TipoVeiculo) "
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
query.addBindValue(veiculo.Modelo)
query.addBindValue(veiculo.Marca)
query.addBindValue(veiculo.AnoModelo)
query.addBindValue(veiculo.Placa)
query.addBindValue(veiculo.Alugado)
query.addBindValue(veiculo.Batido)
query.addBindValue(veiculo.KmAtual)
query.addBindValue(veiculo.ValorDiaria)
query.addBindValue(veiculo.Descricao)
query.addBindValue(veiculo.TipoVeiculo)
query.exec_()
db.commit()
