def updateComplexClass(self):
if self.db:
self.db.close()
self.db = None
if self.dbCombo.currentIndex() == 0:
return
dbName = self.dbCombo.currentText()
#getting the sql generator
self.gen = self.factory.createSqlGenerator(self.isSpatialiteDatabase(dbName))
(dataSourceUri, credentials) = self.databases[dbName]
#verifying the connection type
if self.isSpatialiteDatabase(dbName):
self.db = QSqlDatabase("QSQLITE")
self.db.setDatabaseName(dbName)
else:
self.db = QSqlDatabase("QPSQL")
self.db.setDatabaseName(dbName)
self.db.setHostName(dataSourceUri.host())
self.db.setPort(int(dataSourceUri.port()))
self.db.setUserName(credentials[0])
self.db.setPassword(credentials[1])
if not self.db.open():
print self.db.lastError().text()
self.populateComboBox()
def on_btnAdd_clicked(self):
if not self.database.isOpen():
if not self.database.open():
raise UserWarning( u"No se pudo establecer la conexión con "\
+ "la base de datos" )
try:
mov = dlgmovimientosbancarios(self)
# Rellenar el combobox de las CONCEPTOS
if mov.conceptosModel.rowCount() == 0:
raise UserWarning( u"No existen conceptos en la base de "\
+ "datos que justifiquen la elaboración de Notas de Crédito o Débito" )
mov.exec_()
except UserWarning as inst:
QMessageBox.critical( self, qApp.organizationName(), unicode( inst ) )
logging.error( unicode( inst ) )
logging.error( mov.conceptosModel.query().lastError().text() )
# except Exception as inst:
# QMessageBox.critical( self, qApp.organizationName(),
# "Hubo un problema al tratar de crear"\
# + " el nuevo pago" )
# logging.critical( unicode( inst ) )
# logging.error( query.lastError().text() )
finally:
if QSqlDatabase.database().isOpen():
QSqlDatabase.database().close()
def setData( self, column, value ):
value = value.toList()
if column < 0:
return False
if column == 0:
try:
if not QSqlDatabase.database().isOpen():
if not QSqlDatabase.database().open():
raise Exception( "No se pudo conectar con la base de datos" )
query = QSqlQuery()
if not query.prepare( """
UPDATE categorias SET nombre = :nombre
WHERE idcategoria = %d
""" % self.itemData[1] ):
raise Exception( "No se pudo preparar la consulta para actualizar la categoria" )
query.bindValue( ":nombre", value[column].toString().strip() )
if not query.exec_():
raise Exception( "No se pudo actualizar la categoria" )
except Exception as inst:
logging.error( unicode( inst ) )
return False
self.itemData[column] = value[column]
return True
def delCliRecord(self):
if not self.db.isOpen():
self.statusbar.showMessage(
"Database non aperto...",
5000)
return
selrows = self.cItmSelModel.selectedRows()
if not selrows:
self.statusbar.showMessage(
"No selected customers to delete...",
5000)
return
if(QMessageBox.question(self, "Delete Customers",
"Do you want to delete: {0} customer(s)?".format(len(selrows)),
QMessageBox.Yes|QMessageBox.No) ==
QMessageBox.No):
return
QSqlDatabase.database().transaction()
query = QSqlQuery()
query.prepare("DELETE FROM clienti WHERE id = :val")
for i in selrows:
if i.isValid():
query.bindValue(":val", QVariant(i.data().toInt()[0]))
query.exec_()
QSqlDatabase.database().commit()
self.cModel.select()
def save( self ):
"""
Slot documentation goes here.
"""
# self.datosRecibo.lineasAbonos =self.abonoeditmodel.lines
# self.datosRecibo.lineas = self.editmodel.lines
self.datosRecibo.observaciones = self.txtobservaciones.toPlainText()
if self.datosRecibo.valid( self ):
if QMessageBox.question( self, qApp.organizationName(),
u"¿Esta seguro que desea guardar el recibo?",
QMessageBox.Yes | QMessageBox.No ) == QMessageBox.Yes:
if not QSqlDatabase.database().isOpen():
QSqlDatabase.database().open()
if self.datosRecibo.save():
QMessageBox.information( self,
qApp.organizationName() ,
u"""El documento se ha guardado con éxito""" )
self.editmodel = None
self.updateModels()
self.navigate( 'last' )
self.status = True
else:
QMessageBox.critical( self,
qApp.organizationName(),
"""Ha ocurrido un error al guardar el documento""" )
if self.database.isOpen():
self.database.close()
def data( self ):
data = {}
fila = self.tblCuenta.selectionModel().currentIndex().row()
fecha = self.dtPicker.date()
data['banco'] = self.filtermodel.index( fila, 0 ).data().toString()
data['id_cuenta_contable'] = self.filtermodel.index( fila, 4 ).data().toInt()[0]
data['codigo_cuenta_contable'] = self.filtermodel.index( fila, 3 ).data().toString()
data['cuenta_bancaria'] = self.filtermodel.index( fila, 5 ).data().toString()
data['fecha'] = QDate( fecha.year(), fecha.month(), fecha.daysInMonth() )
data['moneda'] = self.filtermodel.index( fila, 2 ).data().toString()
if not QSqlDatabase.database().isOpen() and not QSqlDatabase.open():
raise Exception( QSqlDatabase.lastError() )
query = QSqlQuery()
if not query.exec_( "CALL spSaldoCuenta( %d, %s )" % (
data['id_cuenta_contable'],
QDate( data['fecha'].year(), data['fecha'].month(), data['fecha'].daysInMonth() ).toString( "yyyyMMdd" )
)
):
raise Exception( query.lastError().text() )
query.first()
data['saldo_inicial_libro'] = Decimal( query.value( 0 ).toString() )
return data
def __init__(self):
'''
Constructor
'''
super(SpatialiteDb, self).__init__()
self.db = QSqlDatabase('QSQLITE')
self.gen = SqlGeneratorFactory().createSqlGenerator(True)
def addConnection(self, driver, dbName, user, password, host, port):
"""
Public method to add a database connection.
@param driver name of the Qt database driver (QString)
@param dbName name of the database (QString)
@param user user name (QString)
@param password password (QString)
@param host host name (QString)
@param port port number (integer)
"""
err = QSqlError()
self.__class__.cCount += 1
db = QSqlDatabase.addDatabase(driver.toUpper(),
QString("Browser%1").arg(self.__class__.cCount))
db.setDatabaseName(dbName)
db.setHostName(host)
db.setPort(port)
if not db.open(user, password):
err = db.lastError()
db = QSqlDatabase()
QSqlDatabase.removeDatabase(QString("Browser%1").arg(self.__class__.cCount))
self.connections.refresh()
return err
def __init__(self,pluginname="default",typ="pg", hostname=None,port=None,dbname=None,username=None, passwort=""):
QDialog.__init__(self)
self.errorDB = ""
self.errorDriver = ""
self.hostName = hostname
self.databaseName = dbname
self.userName = username
self.port = port
self.passwort = passwort
self.pluginName = pluginname
self.typ = typ
# Mit SQLite verbinden
if typ == "sqlite":
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName(self.hostName)
self.db.open()
else:
# Mit PostgreSQL verbinden
if typ == "pg":
self.db = QSqlDatabase.addDatabase("QPSQL",pluginname)
self.db.setHostName(hostname)
self.db.setDatabaseName(dbname)
self.db.setUserName(username)
self.db.setPassword(passwort)
self.db.setPort(int(port))
# Mit Oracle verbinden
if typ == "oracle":
self.db = QSqlDatabase.addDatabase("QOCI")
self.db.setHostName(hostname)
self.db.setDatabaseName(dbname)
self.db.setUserName(username)
self.db.setPassword(passwort)
self.db.setPort(int(port))
# Mit MySQL Datenbank verbinden
if typ == "mysql":
self.db = QSqlDatabase.addDatabase("QMYSQL")
self.db.setHostName(hostname)
self.db.setDatabaseName(dbname)
self.db.setUserName(username)
self.db.setPassword(passwort)
self.db.setPort(int(port))
# Mit ODBC Datenbank verbinden
if typ == "odbc":
self.db = QSqlDatabase.addDatabase("QODBC")
self.db.setDatabaseName(hostname)
self.db.open()
# Wenn Fehler bei der DB-Verbindung
if self.db.open() == False:
self.errorDriver = str(self.db.lastError().driverText())
QMessageBox.warning(None, self.MSG_BOX_TITLE, (self.tr("Error:' {0}.' The connection to the database could not be established! Some functions of the PlugIn {1} may not work!").format(self.errorDriver, pluginname)), QMessageBox.Ok, QMessageBox.Ok)
def connect(cls, **connection):
dbtype = connection.get('type', "QODBC")
connectionname = connection.get("connectionname",
hash(tuple(connection.items())))
db = QSqlDatabase.database(connectionname)
if not db.isValid():
db = QSqlDatabase.addDatabase(dbtype, connectionname)
if dbtype == "QSQLITE":
db.setDatabaseName(connection['database'])
else:
if "constring" in connection:
db.setDatabaseName(connection['constring'])
else:
constring = "driver={driver};server={host};database={database};uid={user};pwd={password}"
connection["driver"] = "{SQL Server}"
constring = constring.format(**connection)
db.setHostName(connection['host'])
db.setDatabaseName(constring)
db.setUserName(connection['user'])
db.setPassword(connection['password'])
if not db.open():
raise DatabaseException(db.lastError().text())
return Database(db)
def __init__( self, parent = None ):
super( FrmAccounts, self ).__init__( parent )
self.setupUi( self )
self.user = user.LoggedUser
QSqlDatabase.database().open()
# self.accountsTree.setModel( AccountsModel( 1 ) )
self.model = AccountsModel( 1 )
self.filtermodel = TreeFilterProxyModel()
self.filtermodel.setShowAllChildren( True )
self.filtermodel.setSourceModel( self.model )
self.filtermodel.setFilterKeyColumn( DESCRIPCION )
self.filtermodel.setFilterCaseSensitivity( Qt.CaseInsensitive )
self.accountsTree.setModel( self.filtermodel )
self.accountsTree.setColumnHidden( IDCUENTA, True )
self.accountsTree.setColumnHidden( HIJOS, True )
self.accountsTree.setColumnHidden( PADRE, True )
self.accountsTree.setColumnWidth( CODIGO, 240 )
self.accountsTree.setColumnWidth( DESCRIPCION, 240 )
self.accountsTree.expandAll()
def loadDatabase(self):
self.closeDatabase()
if self.isSpatialite:
fd = QtGui.QFileDialog()
self.filename = fd.getOpenFileName(filter='*.sqlite')
if self.filename:
self.spatialiteFileEdit.setText(self.filename)
self.db = QSqlDatabase("QSQLITE")
self.db.setDatabaseName(self.filename)
else:
self.db = QSqlDatabase("QPSQL")
(database, host, port, user, password) = self.getPostGISConnectionParameters(self.comboBoxPostgis.currentText())
self.db.setDatabaseName(database)
self.db.setHostName(host)
self.db.setPort(int(port))
self.db.setUserName(user)
self.db.setPassword(password)
try:
if not self.db.open():
print self.db.lastError().text()
else:
self.dbLoaded = True
self.setCRS()
except:
pass
def refresh(self):
"""
Public slot to refresh the connection tree.
"""
self.__connectionTree.clear()
self.emit(SIGNAL("cleared()"))
connectionNames = QSqlDatabase.connectionNames()
foundActiveDb = False
for name in connectionNames:
root = QTreeWidgetItem(self.__connectionTree)
db = QSqlDatabase.database(name, False)
root.setText(0, self.__dbCaption(db))
if name == self.__activeDb:
foundActiveDb = True
self.__setActive(root)
if db.isOpen():
tables = db.tables()
for table in tables:
itm = QTreeWidgetItem(root)
itm.setText(0, table)
if not foundActiveDb and connectionNames:
self.__activeDb = connectionNames[0]
self.__setActive(self.__connectionTree.topLevelItem(0))
def delDettRecord(self):
if not self.db.isOpen():
self.statusbar.showMessage(
"Database non aperto...",
5000)
return
selrows = self.sItmSelModel.selectedRows()
if not selrows:
self.statusbar.showMessage(
"No articles selected to delete...",
5000)
return
if(QMessageBox.question(self, "Cancellazione righe",
"Vuoi cancellare: {0} righe?".format(len(selrows)),
QMessageBox.Yes|QMessageBox.No) ==
QMessageBox.No):
return
QSqlDatabase.database().transaction()
query = QSqlQuery()
query.prepare("DELETE FROM fattslave WHERE id = :val")
for i in selrows:
if i.isValid():
query.bindValue(":val", QVariant(i.data().toInt()[0]))
query.exec_()
QSqlDatabase.database().commit()
self.sModel.revertAll()
self.mmUpdate()
def updateModels( self ):
"""
Recargar todos los modelos
"""
try:
if not(QSqlDatabase.database().isOpen() or QSqlDatabase.database().open()):
raise Exception( "No se pudo abrir la base" )
self.navmodel.setQuery( """
SELECT
c.Fecha,
b.descripcion as Banco,
cb.ctabancaria,
m.Simbolo,
cc.Codigo,
c.saldolibro,
c.saldobanco,
cb.idcuentacontable,
c.iddocumento
FROM conciliaciones c
JOIN cuentasbancarias cb ON c.idcuentabancaria = cb.idcuentacontable
JOIN bancos b ON b.idbanco = cb.idbanco
JOIN tiposmoneda m ON m.idtipomoneda = cb.idtipomoneda
JOIN cuentascontables cc ON cc.idcuenta = cb.idcuentacontable
ORDER BY c.iddocumento
;
""" )
# Este objeto mapea una fila del modelo self.navproxymodel a los controles
self.mapper.setSubmitPolicy( QDataWidgetMapper.ManualSubmit )
self.mapper.setModel( self.navproxymodel )
self.mapper.addMapping( self.txtbanco, BANCO )
self.mapper.addMapping( self.txtmoneda, MONEDA )
self.mapper.addMapping( self.txtcuentabanco, CUENTABANCO )
self.mapper.addMapping( self.txtcuenta, CUENTA )
# asignar los modelos a sus tablas
self.tablenavigation.setModel( self.navproxymodel )
self.tabledetails.setModel( self.detailsmodel )
self.proxymodel.setSourceModel( self.detailsmodel )
self.tablenavigation.setColumnHidden( SALDOBANCO, True )
self.tablenavigation.setColumnHidden( SALDOLIBRO, True )
self.tablenavigation.setColumnHidden( IDCUENTABANCO, True )
self.tablenavigation.setColumnHidden( IDDOC, True )
self._ocultar_columnas()
except UserWarning as inst:
logging.error( unicode( inst ) )
QMessageBox.critical( self, qApp.organizationName(), unicode( inst ) )
except Exception as inst:
logging.critical( unicode( inst ) )
QMessageBox.critical( self, qApp.organizationName(),
u"Hubo un error al tratar de iniciar una nueva conciliación "\
+ "bancaria" )
finally:
if QSqlDatabase.database().isOpen():
QSqlDatabase.database().close()
def check_credentials(params, password):
"""
Check credentials against database. If correct, return 'role', else
return False.
:param params: Dictionary of parameters, including UserName and db path.
:param password: String of password
:return role / False: role (string), or false if denied.
"""
# Connect to database
if config.DEBUG_MODE:
print('DEBUG_MODE: checking credentials')
db_path = os.path.join(params['RNDataStorePath'], params['DbName'])
username = params['UserName']
db = database.connect_and_open(db_path, 'roadnet_db')
# Query database
login_query = QSqlQuery(db)
login_query.prepare("""SELECT usertype FROM tblUsers
WHERE username =:usr AND userpwd =:pwd""")
login_query.bindValue(":usr", username, QSql.Out)
login_query.bindValue(":pwd", password, QSql.Out)
executed = login_query.exec_()
if not executed:
raise Exception('Database query failed.')
if login_query.first() is True: # i.e. matching record returned
# Correct username or password: get role
role = login_query.value(0)
if role == 'admin':
role = 'editor' # Admin role is no longer used
else:
# Set debug mode settings to thinkwhere and editor and remove lock
if config.DEBUG_MODE:
params['UserName'] = '******'
params['role'] = 'editor'
role = 'editor'
lock_file_path = os.path.join(params['RNDataStorePath'], 'RNLock')
if os.path.isfile(lock_file_path):
os.remove(lock_file_path)
else:
# Wrong username or password: warning message
wrong_login_msg = QMessageBox(QMessageBox.Warning, " ",
"Incorrect username or password",
QMessageBox.Ok, None)
wrong_login_msg.setWindowFlags(Qt.CustomizeWindowHint
| Qt.WindowTitleHint)
wrong_login_msg.exec_()
role = 'init'
# Close database
del (login_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 role or None
return role
def getSpatialiteDatabase(self):
db = None
fd = QFileDialog()
filename = fd.getOpenFileName(filter='*.sqlite')
if filename:
db = QSqlDatabase("QSQLITE")
db.setDatabaseName(filename)
return (filename, db)
def tearDown(self):
super(TestWDMExports, self).tearDown()
if self.db: # Just in case self.db doesn't get set
self.db.close()
del self.db
QSqlDatabase.removeDatabase('integration_testing')
if os.path.exists(self.test_db_path):
os.remove(self.test_db_path)
def __init__(self):
'''
Constructor
'''
QSqlDatabase.removeDatabase( 'QMYSQL' )
self.database = QSqlDatabase.addDatabase( 'QMYSQL' )
self.database.setDatabaseName( "misimportaciones" )
self.database.setHostName( "localhost")
self.database.setUserName( "root" )
self.database.setPassword( "root" )
def __init__(self):
'''
Constructor
'''
QSqlDatabase.removeDatabase('QMYSQL')
self.database = QSqlDatabase.addDatabase('QMYSQL')
self.database.setDatabaseName("misimportaciones")
self.database.setHostName("localhost")
self.database.setUserName("root")
self.database.setPassword("root")
def __del__(self):
self.db.commit()
for q in self.query_set:
logger.debug("cleaning up query: {0}".format(q.lastQuery()))
q.finish()
del q
self.query_set = set()
logger.debug("cleaning out db connection: {0}".format(self.db.connectionName()))
self.db.close()
QSqlDatabase.removeDatabase(self.db.connectionName())
self.db = None
def cargarMarcas( self ):
if not QSqlDatabase.database().isOpen():
if not QSqlDatabase.database().open():
raise Exception( "No se pudo abrir la base de datos" )
self.brandsmodel.setQuery( """
SELECT idmarca, nombre
FROM marcas
""" )
if QSqlDatabase.database().isOpen():
QSqlDatabase.database().close()
def tearDown(self):
super(TestRampMclAutoNumbering, self).tearDown()
if self.db: # Just in case self.db doesn't get set
self.db.close()
del self.db
QSqlDatabase.removeDatabase('integration_testing')
if self.mcl:
del self.mcl
if os.path.exists(self.test_db_path):
os.remove(self.test_db_path)
def stop_roadnet(self):
"""
Stop the plugin. Close windows, disconnect and save databases, reset
toolbars to initial state.
"""
if config.DEBUG_MODE:
print('DEBUG_MODE: Stopping roadNet')
# Stop RAMP, then reinitialise
if self.ramp.ramp_started:
self.ramp.stop_ramp()
self.ramp = Ramp(self)
# Unset the street selector and reset toolbar
if self.iface.mapCanvas().mapTool(
): # Tool is None if roadNet just stopped
current_tool = self.iface.mapCanvas().mapTool().toolName()
if current_tool == "ESU SELECTOR":
self.selector_tool.unset_map_tool()
# Reinitialise toolbar to reflect changes in RAMP settings
self.toolbar.toolbar = None # Delete previous toolbar instance
self.init_toolbar()
# Remove layers
for vlayer in [self.esu, self.rdpoly]:
vlayer.layerDeleted.disconnect() # Disconnect auto-reload signal
try:
vector_layers.remove_spatialite_layer(vlayer, self.iface)
except rn_except.RemoveNonExistentLayerPopupError:
pass
self.esu = None
self.rdpoly = None
# Reset street browser and other components
self.street_browser_dk.close()
self.street_browser_dk = None
self.street_browser = None
self.model = None
# Disconnect database, and save if necessary
connection_name = self.db.connectionName()
self.db.close()
self.db = None
QSqlDatabase.removeDatabase(connection_name)
if not config.DEBUG_MODE:
database.update_sqlite_files(self.params)
# Update params file
self.params_file_handler.update_xml_file(self.params)
self.roadnet_started = False
def __init__(self):
QSqlDatabase.__init__(self, "QSQLITE")
if not DBHandle.default:
DBHandle.default = self
if not os.path.exists(self.dbPath()):
self.createDatabase()
self.setDatabaseName(self.dbPath())
if not self.open():
self.default = None
logException('opening %s: %s' % (self.dbPath(), self.lastError()))
# timeout in msec:
self.setConnectOptions("QSQLITE_BUSY_TIMEOUT=2000")
with Transaction(silent=True):
self.upgradeDb()
def setupModelData( self, parent ):
try:
if not QSqlDatabase.database().isOpen():
if not QSqlDatabase.database().open():
raise Exception( "No se pudo conectar con la base"\
+ " de datos" )
parents = [parent]
query = """
SELECT
p.nombre,
p.idcategoria,
COUNT(h.idcategoria) as hijos
FROM categorias p
LEFT JOIN categorias h ON p.idcategoria = h.padre
WHERE p.padre IS NULL
GROUP BY p.idcategoria
"""
query = QSqlQuery( query )
if not query.exec_():
raise Exception( "No se pudieron recuperar las categorias" )
x = 0
while query.next():
parent = parents[-1]
parent.insertChildren( x, 1, [query.value( 0 ).toString(),
query.value( 1 ).toInt()[0]] )
if query.value( 2 ) > 0:
y = 0
childquery = """
SELECT
p.nombre ,
p.idcategoria
FROM categorias p
WHERE p.padre = %d
""" % query.value( 1 ).toInt()[0]
childquery = QSqlQuery( childquery )
childquery.exec_()
while childquery.next():
parent.child( x ).insertChildren( y, 1, [
childquery.value( 0 ).toString(),
childquery.value( 1 ).toInt()[0]] )
y += 1
x += 1
return True
except Exception as inst:
logging.error( unicode( inst ) )
return False
def __init__(self):
QSqlDatabase.__init__(self, "QSQLITE")
if not DBHandle.default:
DBHandle.default = self
if not os.path.exists(self.dbPath()):
self.createDatabase()
self.setDatabaseName(self.dbPath())
if not self.open():
self.default = None
logException('opening %s: %s' % (self.dbPath(), self.lastError()))
# timeout in msec:
self.setConnectOptions("QSQLITE_BUSY_TIMEOUT=2000")
with Transaction(silent=True):
self.upgradeDb()
def createDbFactory(self,driverName):
#TODO Treat none return
if not ('QPSQL' in QSqlDatabase.drivers()): #Driver wasn't loaded
QgsMessageLog.logMessage('QT PSQL driver not installed!', 'DSG Tools Plugin', QgsMessageLog.CRITICAL)
return None
if not ('QSQLITE' in QSqlDatabase.drivers()): #Driver wasn't loaded
QgsMessageLog.logMessage('QT QSQLITE driver not installed!', 'DSG Tools Plugin', QgsMessageLog.CRITICAL)
return None
if driverName == "QSQLITE":
return SpatialiteDb()
if driverName == "QPSQL":
return PostgisDb()
else:
return None
def __init__(self, parent = None):
"""
Constructor
@param parent reference to the parent widget (QWidget)
"""
QWidget.__init__(self, parent)
self.setupUi(self)
self.table.addAction(self.insertRowAction)
self.table.addAction(self.deleteRowAction)
if QSqlDatabase.drivers().isEmpty():
KQMessageBox.information(None,
self.trUtf8("No database drivers found"),
self.trUtf8("""This tool requires at least one Qt database driver. """
"""Please check the Qt documentation how to build the """
"""Qt SQL plugins."""))
self.connect(self.connections, SIGNAL("tableActivated(QString)"),
self.on_connections_tableActivated)
self.connect(self.connections, SIGNAL("schemaRequested(QString)"),
self.on_connections_schemaRequested)
self.connect(self.connections, SIGNAL("cleared()"),
self.on_connections_cleared)
self.emit(SIGNAL("statusMessage(QString)"), self.trUtf8("Ready"))
def rollback(self, silent=None):
"""rollback and log it"""
if QSqlDatabase.rollback(self):
if not silent and Debug.sql:
logDebug('%x rollbacked transaction' % id(self))
else:
logWarning('%s cannot rollback: %s' % (self.name, self.lastError()))
def __init__( self, parent = None ):
"""
@param parent: El formulario padre de este frm
"""
super( FrmArticulos, self ).__init__( parent )
self.setupUi( self )
self.database = QSqlDatabase.database()
# self.__status = True
self.backmodel = ArticlesModel()
self.filtermodel = QSortFilterProxyModel()
self.filtermodel.setSourceModel( self.backmodel )
self.filtermodel.setFilterCaseSensitivity( Qt.CaseInsensitive )
self.update_models()
# self.tableview.addActions( ( self.actionEdit, self.actionNew ) )
self.tableview.setColumnHidden( 0, True )
self.tableview.resizeColumnsToContents()
self.setWindowTitle( "Catalogo de Articulos" )
self.tableview.setEditTriggers( QAbstractItemView.AllEditTriggers )
self.actionEdit.setVisible( True )
self.actionSave.setVisible( False )
self.actionCancel.setVisible( False )
self.nuevoarticulo = None
def connect_to_database(self, host, port, db, user, pwd):
""" Connect to database with selected parameters """
# Update current user
self.user = user
# We need to create this connections for Table Views
self.db = QSqlDatabase.addDatabase("QPSQL")
self.db.setHostName(host)
self.db.setPort(int(port))
self.db.setDatabaseName(db)
self.db.setUserName(user)
self.db.setPassword(pwd)
status = self.db.open()
if not status:
msg = "Database connection error. Please check connection parameters"
self.last_error = self.tr(msg)
return False
# Connect to Database
self.dao = PgDao()
self.dao.set_params(host, port, db, user, pwd)
status = self.dao.init_db()
if not status:
msg = "Database connection error. Please check connection parameters"
self.last_error = self.tr(msg)
return False
return status
def __init__(self, parent=None):
super(login, self).__init__(parent)
self.setupUi(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:
print("Database is OK")
self.txt_id.setValidator(QIntValidator())
self.r = Registro.Reg() #llama la clase que sera en el documento .py
self.p = Productos.products()
self.btn_registro.clicked.connect(
self.r.show) #muesta la clase en un boton
self.btn_Ingresar.clicked.connect(self.login)
db.close()
def createQtMSDB(self):
"""
Returns a db Connection to a MSSQL (SQL Server database) using
QtSql. This is requiered in order to create views with SQL Server
"""
db = QSqlDatabase.addDatabase("QODBC")
if db.isValid():
db.setDatabaseName("DRIVER={SQL Server};SERVER=" + self.uri.host()
+ ";DATABASE=" + self.uri.database())
db.setUserName(self.uri.username())
db.setPassword(self.uri.password())
if db.open():
return db, True
else:
self.messageBar.pushMessage("Connection SQl Server",
db.lastError().text(),
level=QgsMessageBar.CRITICAL)
db.close()
db.removeDatabase(db.databaseName())
db = None
return db, False
else:
self.messageBar.pushMessage("Connection SQL Server",
'QODBC db is NOT valid',
level=QgsMessageBar.CRITICAL)
def __init__(self, parent=None):
QDialog.__init__(self, parent)
self.ui = sm.Ui_Dialog()
self.ui.setupUi(self)
self.ui.table_studentList
# Call DB
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName("studentInformation001.db")
self.db.open()
# End of call DB
# student info into table view
projectModel = QSqlQueryModel()
projectModel.setQuery(
"SELECT schoolNumber, Name FROM Student ORDER BY schoolNumber ASC",
self.db)
projectView = self.ui.table_studentList
projectView.setModel(projectModel)
projectView.show()
#end of student info into table view
# QTableView Click Event
self.qTableView = self.ui.table_studentList
self.qTableView.clicked.connect(self.showInfo)
# click and connection update
self.ui.Btn_Update.clicked.connect(self.cont)
# click and connection grade
self.ui.Btn_Grade.clicked.connect(self.contGrade)
def transaction(self, silent=None):
"""commit and log it"""
if QSqlDatabase.transaction(self):
if not silent and Debug.sql:
logDebug('%x started transaction' % id(self))
else:
logWarning('%s cannot start transaction: %s' % (self.name, self.lastError()))
def __init__(self, iface, land, parent=None):
"""Constructor."""
super(GkukanMusiumdbDockWidget, self).__init__(parent)
# Set up the user interface from Designer.
# After setupUI you can access any designer object by doing
# self.<objectname>, and you can use autoconnect slots - see
# http://qt-project.org/doc/qt-4.8/designer-using-a-ui-file.html
# #widgets-and-dialogs-with-auto-connect
self.setupUi(self)
self.loadListCount = 0
self.loadmax = self.tblPhotos.columnCount()
self.tblPhotos.setEditTriggers(QtGui.QAbstractItemView.NoEditTriggers)
self.iface = iface
self.land = land #Landmark toolbox
self.canvas = self.iface.mapCanvas()
self.geoCrs = QgsCoordinateReferenceSystem(4326)
self.db = QSqlDatabase.addDatabase('QPSQL')
self.photolist = []
self.filtermode = False
self.btnReloadphoto.clicked.connect(self.FilterMode)
self.btnLoadMorePhoto.clicked.connect(self.AddMorePhotosToList)
self.btnSetLandmark.clicked.connect(self.AddSelectiontoLandmark)
self.btnClearSelection.clicked.connect(self.ClearSelection)
self.btnSetToPoint.clicked.connect(self.SetPointToSelection)
self.tblPhotos.cellDoubleClicked.connect(self.clickPhoto)
self.btnLoadMorePhoto.setEnabled(False)
self.btnSetLandmark.setEnabled(False)
self.btnSetToPoint.setEnabled(False)
def currentDatabase(self):
"""
Public method to get the current database.
@return reference to the current database (QSqlDatabase)
"""
return QSqlDatabase.database(self.__activeDb)
def __init__(self, header=None, db=None, parent=None):
super(FilterDialog, self).__init__(parent)
self.setupUi(self)
self.dbi = ""
if not db:
filename = ":memory:"
self.dbi = QSqlDatabase.addDatabase("QSQLITE")
self.dbi.setDatabaseName(filename)
self.dbi.open()
self.creaStrutturaDB(header)
self.sModel = QSqlTableModel(self)
self.sModel.setTable(QString("filtertable"))
self.sModel.setEditStrategy(QSqlTableModel.OnManualSubmit)
self.sModel.select()
self.filterTableView.setModel(self.sModel)
self.filterTableView.setColumnHidden(0, True)
self.filterTableView.setAlternatingRowColors(True)
self.connect(self.sModel, SIGNAL("primeInsert (int,QSqlRecord&)"),
self.updRows)
self.connect(self.addFilterPushButton, SIGNAL("clicked()"),
self.accept)
self.connect(self.addLinePushButton, SIGNAL("clicked()"),
self.addLine)
self.sModel.insertRows(0, 10)
self.sModel.submitAll()
self.restoreSettings()
self.resultFilter = None
def __init__(self, name, applicationWindow, hostname, username, password, port):
"""
@type name: str
@type connection: MySQLdb.Connection
@type applicationWindow: MainApplicationWindow
"""
self.name = name
self.hostname = hostname
self.username = username
self.password = password
self.port = port
self.applicationWindow = applicationWindow
self.databases = list()
self.currentDatabase = None
self.collations = list()
self.connection = MySQLdb.connect(host = hostname, user = username, passwd = password, port = port, cursorclass = MySQLdb.cursors.DictCursor)
db = QSqlDatabase.addDatabase('QMYSQL', name)
db.setHostName(hostname)
db.setUserName(username)
db.setPassword(password)
print db.open()
self.db = db
serverItem = HeidiTreeWidgetItem()
serverItem.setText(0, name)
serverItem.setIcon(0, QIcon('../resources/icons/server.png'))
serverItem.setFlags(Qt.ItemIsEnabled|Qt.ItemIsSelectable)
serverItem.setChildIndicatorPolicy(QTreeWidgetItem.DontShowIndicatorWhenChildless)
serverItem.itemType = 'server'
self.databaseTreeItem = serverItem
applicationWindow.mainWindow.databaseTree.addTopLevelItem(serverItem)
def globaldb():
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("caracate.db")
if not db.open():
QMessageBox.warning(None, "Phone Log", "Database Error: %s" % db.lastError().text())
sys.exit(1)
return db
def __init__(self, iface):
QDockWidget.__init__(self)
self.setupUi(self)
self.iface = iface
self.canvas = self.iface.mapCanvas()
self.geoCrs = QgsCoordinateReferenceSystem(4326)
self.btnAddPhoto.setIcon(QIcon(':/icons/camera.svg'))
self.txtPhotoComment.setPlaceholderText(self.tr('Comment'))
self.cmbLayers.setFilters(QgsMapLayerProxyModel.VectorLayer)
self.db = QSqlDatabase.addDatabase('QPSQL')
self.landmarkId = None
self.photoId = None
self.highlight = None
self.model = QStandardItemModel()
self.lstPhotos.setModel(self.model)
self.btnUpdateLandmark.clicked.connect(self.saveLandmark)
self.btnDeleteLandmark.clicked.connect(self.deleteLandmark)
self.btnAddPhoto.clicked.connect(self.addPhoto)
self.btnUpdatePhoto.clicked.connect(self.savePhoto)
self.btnDeletePhoto.clicked.connect(self.removePhoto)
self.lstPhotos.selectionModel().selectionChanged.connect(
self.photoSelected)
self.lstPhotos.doubleClicked.connect(self.showPhoto)
self._enableOrDisableButtons()
self.ToggleToolbox()
def onClickBtn3(self):
# Reuse the path to DB to set database name
uri = QgsDataSourceURI()
uri.setDatabase('F:/UTA/SEM 5/Adv DB/UTA_Final_DB/test.sqlite')
db = QSqlDatabase.addDatabase('QSPATIALITE')
db.setDatabaseName(uri.database())
FromL = self.dlg.q3combo1.currentText()
ToL = self.dlg.q3combo2.currentText()
FromLocation = []
ToLocation = []
if db.open():
query = db.exec_("SELECT * from Final_line")
while query.next():
record = query.record()
if (record.value(14) != None and record.value(15) != None):
FromLocation.append(record.value(14))
ToLocation.append(record.value(15))
print(type(FromLocation))
graph = defaultdict(list)
for i in range(0, len(FromLocation)):
graph[FromLocation[i]].append(ToLocation[i])
print(graph)
print(self.find_shortest_path(graph, FromL, ToL))
def connect_and_open(db_file_path, connection_name):
"""
Create a QSqlDatabase connection to the database and open it.
:param db_file_path: String with database location
:param connection_name: String with name of connection for Qt use
:return db: QSqlDatabase object representing open database
"""
db = QSqlDatabase.addDatabase("QSPATIALITE", connection_name)
db.setDatabaseName(db_file_path)
# db.setConnectOptions("QSQLITE_BUSY_TIMEOUT")
# Also a read only option could use to restrict concurrent access
if not db.open():
db_not_open_msg_box = QMessageBox(QMessageBox.Warning, " ",
"Database error: {}").format(
db.lastError().text(),
QMessageBox.Ok, None)
db_not_open_msg_box.setWindowFlags(Qt.CustomizeWindowHint
| Qt.WindowTitleHint)
db_not_open_msg_box.exec_()
return
if config.DEBUG_MODE:
print('DEBUG_MODE: opening QSqlDatabase {}'.format(
db.connectionName()))
return db
def __init__(self, parent=None):
QtGui.QWidget.__init__(self, parent)
self.ui = sm.Ui_MainWindow()
self.ui.setupUi(self)
self.ui.table_studentList
# Call DB
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("studentInformation001.db")
db.open()
# End of call DB
# student info into table view
projectModel = QSqlQueryModel()
projectModel.setQuery(
"SELECT schoolNumber, Name FROM Student ORDER BY schoolNumber ASC",
db)
projectView = self.ui.table_studentList
projectView.setModel(projectModel)
projectView.show()
#end of student info into table view
# QTableView Click Event
self.qTableView = self.ui.table_studentList
self.qTableView.clicked.connect(self.showInfo)
def __init__( self, user, user2, sesion, parent = None ):
super( FrmCierreCaja, self ).__init__( parent )
self.setupUi( self )
self.user = user
self.user2 = user2
self.db = QSqlDatabase.database()
self.query = QSqlQuery( """
SELECT
d.fechacreacion,
c.idcaja,
c.descripcion
FROM documentos d
LEFT JOIN docpadrehijos dp ON dp.idpadre=d.iddocumento
LEFT JOIN documentos hijo ON dp.idhijo=hijo.iddocumento AND hijo.idtipodoc=%d
JOIN cajas c on d.idcaja=c.idcaja
WHERE d.idtipodoc=%d and d.idusuario= %d
AND hijo.iddocumento IS NULL
ORDER BY d.iddocumento DESC
LIMIT 1;""" % ( constantes.IDAPERTURA, constantes.IDAPERTURA, user.uid ) )
if not self.query.exec_():
raise Exception( "No se pudo preparar la Query" )
self.query.first()
self.dtFechaTime.setDate( self.query.value( 0 ).toDate() )
self.cboCaja.addItem( self.query.value( 2 ).toString() )
self.txtUsuario.setText( self.user.user )
self.txtUsuario.setReadOnly( True )
self.dtFechaTime.setReadOnly( True )
self.dtFechaTime.dateTime().toString( "yyyyMMddhhmmss" )
self.sesion = sesion
def __init__(self):
'''
Constructor
'''
super(tbFactura, self).__init__()
# ESTABLECER LA INTERFAZ AL FORMULARIO
self.setupUi(self)
# VALIDADOR DE MODO DE EDICION
self.readOnly = True
self.editmodel = None
#ESTABLECER LA FECHA INICIAL , (establecida al dia de mañana)
self.categoriesview.headers = [
"Descripcion", "Precio", "Unidades", "Existencia", "", "", ""
]
# Crear el modelo para cargar catalogo de clientes
self.clientesModel = QSqlQueryModel()
# Crear lista de autocompletado para el combo de clientes
self.clienteCompleter = QCompleter()
# Modelo que carga el catalogo de productos
self.existenciaModel = QSqlQueryModel()
# Establecer todos los controles en modo de edicion
self.setControls(False)
# Crear la conexion a la base de datos
self.database = QSqlDatabase.database()
self.vistaprevia = False
# Cargar los modelos del modo de edicion
self.updateEditModels()
self.parent = self.parent()
def onClickBtn1(self):
print('onClickBtn1')
dist_range = self.dlg.numberOfElements.text()
aptName = self.dlg.qcombo1.currentText()
aptType = self.dlg.qcombo2.currentText()
# Reuse the path to DB to set database name
uri = QgsDataSourceURI()
uri.setDatabase('F:/UTA/SEM 5/Adv DB/UTA_Final_DB/test.sqlite')
db = QSqlDatabase.addDatabase('QSPATIALITE');
db.setDatabaseName(uri.database())
myList=[]
if db.open():
query = db.exec_("SELECT src.id,src.name From Final_poly src, Final_poly dest where st_contains(st_buffer(st_transform(dest.geom, 3857), {0}), st_transform(src.geom,3857)) AND dest.name='{1}' AND src.Btype='{2}' ".format(dist_range,aptName,aptType));
while query.next():
record = query.record()
myList.append(record.value(0))
print(myList)
selectedLayerIndex = "final_poly"
self.iface.setActiveLayer(QgsMapLayerRegistry.instance().mapLayersByName( selectedLayerIndex )[0])
layer = self.iface.activeLayer()
self.iface.mapCanvas().setSelectionColor( QColor('red'))
layer.selectByIds(myList)
def onClickBtn2(self):
print('onClickBtn2')
aptName = self.dlg.q2combo.currentText()
# Reuse the path to DB to set database name
uri = QgsDataSourceURI()
uri.setDatabase('F:/UTA/SEM 5/Adv DB/UTA_Final_DB/test.sqlite')
db = QSqlDatabase.addDatabase('QSPATIALITE');
db.setDatabaseName(uri.database())
myList=[]
if db.open():
query = db.exec_("Select id from Final_poly where name='{0}'".format(aptName));
while query.next():
record = query.record()
myList.append(record.value(0))
print(myList)
selectedLayerIndex = "final_poly"
self.iface.setActiveLayer(QgsMapLayerRegistry.instance().mapLayersByName( selectedLayerIndex )[0])
layer = self.iface.activeLayer()
self.iface.mapCanvas().setSelectionColor( QColor('green'))
layer.selectByIds(myList)
def __init__( self ):
'''
Constructor
'''
super( tbFactura, self ).__init__( )
# ESTABLECER LA INTERFAZ AL FORMULARIO
self.setupUi(self)
# VALIDADOR DE MODO DE EDICION
self.readOnly = True
self.editmodel = None
#ESTABLECER LA FECHA INICIAL , (establecida al dia de mañana)
self.categoriesview.headers = ["Descripcion", "Precio", "Unidades","Existencia","","",""]
# Crear el modelo para cargar catalogo de clientes
self.clientesModel = QSqlQueryModel()
# Crear lista de autocompletado para el combo de clientes
self.clienteCompleter = QCompleter()
# Modelo que carga el catalogo de productos
self.existenciaModel = QSqlQueryModel()
# Establecer todos los controles en modo de edicion
self.setControls( False )
# Crear la conexion a la base de datos
self.database = QSqlDatabase.database()
self.vistaprevia = False
# Cargar los modelos del modo de edicion
self.updateEditModels()
self.parent = self.parent()
def __init__(self):
db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('app.db')
if not db.open():
print("Couldn't open the database.")
sys.exit(1)
def __init__(self):
'''
Constructor
'''
super(SpatialiteDb,self).__init__()
self.db = QSqlDatabase('QSQLITE')
self.gen = SqlGeneratorFactory().createSqlGenerator(True)
def createConnection():
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("my.db")
if (not db.open()):
QMessageBox.critical(0, "Cannot open database1",
"Unable to establish a database connection.",
QMessageBox.Cancel)
return False
query = QSqlQuery()
# 创建student表
print query.exec_(
QString("create table student (id int primary key, "
"name varchar, course int)"))
query.exec_(QString("insert into student values(1, '李强', 11)"))
query.exec_(QString("insert into student values(2, '马亮', 11)"))
query.exec_(QString("insert into student values(3, '孙红', 12)"))
# 创建course表
print query.exec_(
QString("create table course (id int primary key, "
"name varchar, teacher varchar)"))
query.exec_(QString("insert into course values(10, '数学', '王老师')"))
query.exec_(QString("insert into course values(11, '英语', '张老师')"))
query.exec_(QString("insert into course values(12, '计算机', '白老师')"))
return True
def connect_to_database(self, host, port, db, user, pwd):
""" Connect to database with selected parameters """
# Update current user
self.user = user
# We need to create this connections for Table Views
self.db = QSqlDatabase.addDatabase("QPSQL")
self.db.setHostName(host)
self.db.setPort(int(port))
self.db.setDatabaseName(db)
self.db.setUserName(user)
self.db.setPassword(pwd)
status = self.db.open()
if not status:
message = "Database connection error. Please open plugin log file to get more details"
self.last_error = self.tr(message)
details = self.db.lastError().databaseText()
self.log_warning(str(details))
return False
# Connect to Database
self.dao = PgDao()
self.dao.set_params(host, port, db, user, pwd)
status = self.dao.init_db()
if not status:
message = "Database connection error. Please open plugin log file to get more details"
self.last_error = self.tr(message)
self.log_warning(str(self.dao.last_error))
return False
return status
def createDatabase(self):
"""use a temp file name. When done, rename to final file name,
thusly avoiding races if two processes want to build the same
database"""
tempName = '%s.new.%d' % (self.dbPath(), os.getpid())
self.setDatabaseName(tempName)
if not self.open():
logException('creating %s: %s' % (tempName, self.lastError()))
with Transaction(silent=True):
self.createTables()
self.__generateDbIdent()
QSqlDatabase.close(self)
newName = self.dbPath()
if os.path.exists(newName):
os.remove(tempName)
else:
os.rename(tempName, newName)
def transaction(self, silent=None):
"""commit and log it"""
if QSqlDatabase.transaction(self):
if not silent and Debug.sql:
logDebug('%x started transaction' % id(self))
else:
logWarning('%s cannot start transaction: %s' %
(self.name, self.lastError()))
def rollback(self, silent=None):
"""rollback and log it"""
if QSqlDatabase.rollback(self):
if not silent and Debug.sql:
logDebug('%x rollbacked transaction' % id(self))
else:
logWarning('%s cannot rollback: %s' %
(self.name, self.lastError()))
def __openDatabase(self, dbPath):
"""
:type dbPath: str
:return:
"""
qDebug("\n(VFK) Open DB: {}".format(dbPath))
if not QSqlDatabase.isDriverAvailable('QSQLITE'):
raise VFKError(u'Databázový ovladač QSQLITE není dostupný.')
connectionName = QUuid.createUuid().toString()
db = QSqlDatabase.addDatabase("QSQLITE", connectionName)
db.setDatabaseName(dbPath)
if not db.open():
raise VFKError(u'Nepodařilo se otevřít databázi. ')
self.setProperty("connectionName", connectionName)
