Python QtSql.QSqlDatabase类代码示例

 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 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 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 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 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 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 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 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 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 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 __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 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 getSpatialiteDatabase(self):
     db = None
     fd = QFileDialog()
     filename = fd.getOpenFileName(filter='*.sqlite')
     if filename:
         db = QSqlDatabase("QSQLITE")
         db.setDatabaseName(filename)
     return (filename, db)

 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