Python cursor.execute函数代码示例

    def __init__(self, certificates_instance, event_id):
        super(AddClientDialog,self).__init__()
        self.setWindowTitle(u"Adicionar cliente")

        self.event_id = event_id
        self.certificates_instance = certificates_instance

        cursor.execute("SELECT * FROM clients ORDER BY name ASC")
        self.clients = cursor.fetchall()

        self.mainLayout = QtGui.QVBoxLayout()

        self.titleLabel = QtGui.QLabel(u"Selecione um cliente")
        self.titleLabel.setFont(titleFont)

        self.clientsList = QtGui.QComboBox()
        for client in self.clients:
            self.clientsList.addItem(unicode(client[1]))

        self.saveBtn = QtGui.QPushButton(u"Selecionar")
        self.saveBtn.clicked.connect(self.add_client)

        self.mainLayout.addWidget(self.titleLabel)
        self.mainLayout.addWidget(self.clientsList)
        self.mainLayout.addWidget(self.saveBtn)

        self.setLayout(self.mainLayout)

    def remove_client(self):
        """
            Remove a client from the event's subscriptions.
        """

        # If there is a client selected, removes it.
        # If there is not, shows up an error message.
        try:
            current_subscription = self.subscriptionList.currentRow()
            subscription_id = self.clients[current_subscription][0]
            # Asks if the user really wants to remove the client
            choice = QtGui.QMessageBox.question(
                self, u"Apagar inscrição",
                u"Tem certeza que deseja apagar esta inscrição?",
                QtGui.QMessageBox.Yes | QtGui.QMessageBox.No
            )
            if choice == QtGui.QMessageBox.Yes:
                cursor.execute(
                    "DELETE FROM subscriptions WHERE id=?",
                    str(subscription_id)
                )
                conn.commit()
            else:
                pass
            self.load_list()
        except IndexError:
            self.errorMsg.setText(u"Selecione um cliente existente!")

    def load_table(self):
        self.clientsTable.clear()

        cursor.execute("SELECT * FROM clients")
        data = cursor.fetchall()

        if len(data) < 10:
            self.clientsTable.setRowCount(10)
        else:
            self.clientsTable.setRowCount(len(data))
        self.clientsTable.setColumnCount(4)
        self.clientsTable.setHorizontalHeaderLabels((u"ID",
                                                    u"Nome Completo",
                                                    u"E-mail",
                                                    u"CPF"))
        self.clientsTable.horizontalHeader().setResizeMode(QtGui.QHeaderView.Stretch)
        self.clientsTable.verticalHeader().setResizeMode(QtGui.QHeaderView.Stretch)
        r,c=0,0
        for item in data:
            for i in range(4):
                newitem = QtGui.QTableWidgetItem(unicode(item[i]))
                self.clientsTable.setItem(r,c,newitem)
                c += 1
            r += 1
            c = 0

    def remove_event(self):
        """
            Delete an existing event.
        """
        # Verifies if there is a selected event
        try:
            # Gets the event id
            r = self.eventsTable.currentRow()
            event_id = self.eventsTable.item(r, 0).text()

            # Asks if the user really wants to remove the event
            choice = QtGui.QMessageBox.question(
                self, u"Apagar evento",
                u"Tem certeza que deseja apagar este evento?",
                QtGui.QMessageBox.Yes | QtGui.QMessageBox.No
            )
            if choice == QtGui.QMessageBox.Yes:
                cursor.execute("DELETE FROM events WHERE id=?", str(event_id))
                conn.commit()
            else:
                pass

            # Updates the events table
            self.load_table()
        except AttributeError:
            self.errorMsg.setText(u"Selecione um registro existente!")

    def remove_signature(self):
        """
            Delete an existing signature.
        """
        # Verifies if there is a selected signature
        try:
            # Gets the selected signature id
            sig_id = self.signatures[self.signaturesList.currentRow()][0]

            # Asks if the user really wants to delete the signature
            choice = QtGui.QMessageBox.question(
                self, u"Apagar assinatura",
                u"Tem certeza que deseja apagar esta assinatura?",
                QtGui.QMessageBox.Yes | QtGui.QMessageBox.No
            )

            if choice == QtGui.QMessageBox.Yes:
                os.remove(os.path.join(app_dir, "signatures",
                                       "{0}.png".format(str(sig_id))))
                cursor.execute(
                    "DELETE FROM signatures WHERE id=?",
                    (str(sig_id),)
                )
                conn.commit()
            else:
                pass

            # Updates the signatures list
            self.load_list()
        except IndexError:
            self.errorMsg.setText(u"Selecione um registro existente!")

    def load_list(self):
        """
            Loads all subscriptions of the selected event
            and insert them in the subscriptionList widget.
        """

        # Get the subscripted clients
        if self.events:
            cursor.execute(
                "SELECT id,client_id FROM subscriptions WHERE event_id=?",
                str(self.events[self.eventsList.currentIndex()][0])
            )
            self.clients = cursor.fetchall()
        else:
            self.clients = []

        # Clears the widget to insert updated info
        self.subscriptionList.clear()

        # Select all subscripted clients and add them
        # to the subscriptionList widget
        for client in self.clients:
            cursor.execute(
                "SELECT name FROM clients WHERE id=?",
                str(client[1])
            )
            client_name = cursor.fetchone()
            if client:
                self.subscriptionList.addItem(unicode(client_name[0]))

    def load_table(self):
        self.eventsTable.clear()

        cursor.execute("SELECT * FROM events ORDER BY id DESC")
        data = cursor.fetchall()

        if len(data) < 10:
            self.eventsTable.setRowCount(10)
        else:
            self.eventsTable.setRowCount(len(data))
        self.eventsTable.setColumnCount(4)
        self.eventsTable.setHorizontalHeaderLabels((u"ID",
                                                    u"Título",
                                                    u"Data de Início",
                                                    u"Data de Término"))
        self.eventsTable.horizontalHeader().setResizeMode(QtGui.QHeaderView.Stretch)
        self.eventsTable.verticalHeader().setResizeMode(QtGui.QHeaderView.Stretch)
        r,c=0,0
        for item in data:
            for i in range(4):
                newitem = QtGui.QTableWidgetItem(unicode(item[i]))
                self.eventsTable.setItem(r,c,newitem)
                c += 1
            r += 1
            c = 0

    def generate(self):
        if len(self.clients) == 0:
            self.errorMsg.setText(u"Primeiro inscreva clientes!")
        else:
            self.save_folder = QtGui.QFileDialog.getExistingDirectory(None,
                                                                u"Salvar em")

            self.cert_data = {"event": unicode(self.events[self.eventsList.currentIndex()][1]).upper(),
                         "start_date": unicode(self.events[self.eventsList.currentIndex()][2]),
                         "end_date": unicode(self.events[self.eventsList.currentIndex()][3]),
                         "hours": unicode(self.events[self.eventsList.currentIndex()][4]),
                         "content": unicode(self.events[self.eventsList.currentIndex()][5]).upper(),
                         "responsible_sig": unicode(self.signatures[self.responsibleList.currentIndex()][0]),
                         "institution_sig": unicode(self.signatures[self.institutionList.currentIndex()][0]),
                         "role": unicode(self.signatures[self.institutionList.currentIndex()][2]),
                         "institution": unicode(self.Config.get("Main","Name")).upper(),
                         "inst_register": unicode(self.Config.get("Main","ID"))}

            for client in self.clients:
                cursor.execute("SELECT name,register FROM clients WHERE id=?",str(client[1]))
                client_data = cursor.fetchone()
                self.cert_data["name"] = unicode(client_data[0]).upper()
                self.cert_data["register"] = unicode(client_data[1])
                generate_certificate(self.save_folder, self.cert_data)

            cursor.execute("SELECT * FROM signatures WHERE id=?",
                           str(self.signatures[self.responsibleList.currentIndex()][0]))
            responsible = cursor.fetchone()
            self.cert_data["name"] = unicode(responsible[1]).upper()
            self.cert_data["register"] = unicode(responsible[4]).upper()
            generate_certificate_responsible(self.save_folder, self.cert_data)

    def run(self):
        self.mailer = Mailer()
        self.mailer.connect()
        self.emit(QtCore.SIGNAL("update"),1,0)

        n = 1
        for client in self.clients:
            cursor.execute("SELECT name,email FROM clients WHERE id=?",str(client[1]))
            client_data = cursor.fetchone()

            filepath = os.path.join(unicode(self.save_folder),u''.join(i for i in \
                                                                unicode(client_data[0]) \
                                                                if ord(i)<128)\
                                                                .upper()\
                                                                .replace(" ",""))
            filepath += ".pdf"

            self.cert_data["name"] = unicode(client_data[0]).upper()
            self.cert_data["register"] = unicode(client_data[1])

            self.emit(QtCore.SIGNAL("update"),2,n)
            generate_certificate(self.save_folder, self.cert_data)
            self.mailer.send_certificate(filepath,unicode(client_data[1]))
            n+=1

        filepath = os.path.join(unicode(self.save_folder),u"responsible.pdf")
        self.cert_data["name"] = unicode(self.responsible[1]).upper()
        self.cert_data["register"] = unicode(self.responsible[4]).upper()
        self.emit(QtCore.SIGNAL("update"),3,0)
        generate_certificate_responsible(self.save_folder, self.cert_data)
        self.mailer.send_certificate(unicode(filepath),unicode(self.responsible[3]))

        self.emit(QtCore.SIGNAL("update"),4,0)
        self.mailer.quit()

    def load_list(self):
        self.signaturesList.clear()

        cursor.execute("SELECT * FROM signatures")
        self.signatures = cursor.fetchall()

        for sig in self.signatures:
            self.signaturesList.addItem(sig[1])

    def load_list(self):
        self.signaturesList.clear()

        cursor.execute("SELECT * FROM signatures ORDER BY name ASC")
        self.signatures = cursor.fetchall()

        for sig in self.signatures:
            self.signaturesList.addItem(unicode(sig[1]))

 def add_client(self):
     cursor.execute("INSERT INTO subscriptions VALUES (NULL,?,?)",
                   (
                   str(self.event_id),
                   str(self.clients[self.clientsList.currentIndex()][0])
                   ))
     conn.commit()
     self.certificates_instance.load_list()
     self.hide()

    def __init__(self, clients_list_instance, client_id=None):
        super(ClientDialog,self).__init__()
        self.setGeometry(450,300,400,200)
        self.clients_list_instance = clients_list_instance

        self.client_id = client_id

        self.mainLayout = QtGui.QVBoxLayout()
        self.formLayout = QtGui.QFormLayout()

        self.clientName = QtGui.QLabel(u"Nome completo", self)
        self.clientNameLineEdit = QtGui.QLineEdit(self)
        self.clientNameLineEdit.setPlaceholderText(u"Ex: Fulano da Silva")
        self.formLayout.addRow(self.clientName, self.clientNameLineEdit)

        self.clientEmail = QtGui.QLabel(u"E-mail", self)
        self.clientEmailLineEdit = QtGui.QLineEdit(self)
        self.clientEmailLineEdit.setPlaceholderText(u"Ex: [email protected]")
        self.formLayout.addRow(self.clientEmail, self.clientEmailLineEdit)

        self.clientRegister = QtGui.QLabel(u"CPF", self)
        self.clientRegisterLineEdit = QtGui.QLineEdit(self)
        self.clientRegisterLineEdit.setInputMask(u"999.999.999-99") ## Brazil CPF
        self.formLayout.addRow(self.clientRegister, self.clientRegisterLineEdit)

        self.errorMsg = QtGui.QLabel(u"",self)
        self.errorMsg.setStyleSheet("color: red; font-weight: bold;")

        self.saveBtn = QtGui.QPushButton(u"Salvar")
        self.saveBtn.clicked.connect(self.save_data)

        if self.client_id:
            self.setWindowTitle(u"Editar cliente")
            self.titleLabel = QtGui.QLabel(u"Editar cliente", self)
            self.titleLabel.setFont(titleFont)

            cursor.execute("SELECT * FROM clients WHERE id=?", str(self.client_id))
            data = cursor.fetchone()

            self.clientNameLineEdit.setText(unicode(data[1]))
            self.clientEmailLineEdit.setText(unicode(data[2]))
            self.clientRegisterLineEdit.setText(unicode(data[3]))
        else:
            self.setWindowTitle(u"Cadastrar cliente")
            self.titleLabel = QtGui.QLabel(u"Cadastrar cliente", self)
            self.titleLabel.setFont(titleFont)

        self.mainLayout.addWidget(self.titleLabel)
        self.mainLayout.addLayout(self.formLayout)
        self.mainLayout.addWidget(self.errorMsg)
        self.mainLayout.addWidget(self.saveBtn)

        self.setLayout(self.mainLayout)

    def generate_send(self):
        self.generate_general()

        cursor.execute("SELECT * FROM signatures WHERE id=?",
                       str(self.signatures[self.responsibleList.currentIndex()][0]))
        responsible = cursor.fetchone()

        self.generate_send_progress = GenerateSendProgress(self.save_folder,
                                                           self.cert_data,
                                                           self.clients,
                                                           responsible
                                                          )
        self.generate_send_progress.show()

    def load_list(self):
        if self.events:
            cursor.execute("SELECT id,client_id FROM subscriptions WHERE event_id=?",
                            str(self.events[self.eventsList.currentIndex()][0]))
            self.clients = cursor.fetchall()
        else:
            self.clients = []

        self.subscriptionList.clear()
        for client in self.clients:
            cursor.execute("SELECT name FROM clients WHERE id=?",str(client[1]))
            client_name = unicode(cursor.fetchone()[0])
            self.subscriptionList.addItem(client_name)