Python DB.return_sql方法代碼示例

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import return_sql [as 別名]
    def populate_list(self):
        """Populates the talk_list widget with the outlines

        Format of outline_list: [(DB ID, number, title, visibility), ...]
        """
        db = DB()
        self.table_outline.clearContents()
        sql_number_sort = "SELECT * FROM Talk WHERE visibility='True' ORDER " \
                          "BY CAST (number AS INTEGER)"
        sql_title_sort = "SELECT * FROM Talk WHERE visibility='True' ORDER BY" \
                         " title ASC"

        if self.radio_number.isChecked():
            outline_list = DB.return_sql(None, sql_number_sort)
        else:
            outline_list = DB.return_sql(None, sql_title_sort)

        self.table_outline.setColumnCount(2)
        self.table_outline.setRowCount(db.count_rows('Talk', True))
        self.sorted_list = []  # Table IDs of items added sorted to the table

        index = 0  # Index of table_outline widget
        for item in outline_list:
            number = QtGui.QTableWidgetItem(item[1])
            title = QtGui.QTableWidgetItem(item[2])
            self.table_outline.setItem(index, 0, number)
            self.table_outline.setItem(index, 1, title)
            self.sorted_list.append(item[0])
            index += 1

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import return_sql [as 別名]
    def populate_table(self, name="first_name", resp="NOT NULL",
                       coord="NOT NULL", cong="NOT NULL"):
        """
        Returns information about all the brothers in the database for the
        purpose of populating the brother table.

        :param resp: The responsibility that will be displayed. "NOT NULL"
        shows all. In order to show a specific responsibility, the name of
        the responsibility need to be passed within quotations such as:
        '"Elder"'
        :param name: The method in which the brother's names will be sorted.
        The default method is by first name.
        :return: Brother id, first name, middle name, last name,
        and congregation
        """

        sql = """
        SELECT Brother.id, first_name, middle_name, last_name,
        congregation.name FROM Brother JOIN Congregation ON
        Brother.congregation=Congregation.id WHERE
        Brother.responsibility is {RESP} AND Brother.coordinator is {COORD}
        AND Congregation.name is {CONG} ORDER BY {NAME} ASC
        """.format(RESP=resp, COORD=coord, CONG=cong, NAME=name)

        return DB.return_sql(self, sql)

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import return_sql [as 別名]
    def __init__(self, row_id, parent=None):
        super(EditCongregationDialog, self).__init__(parent)
        self.setupUi(self)

        self.setWindowTitle("Edit Congregation")
        self.checkBatch.hide()
        self.button_add.setText("Save")

        sql = "SELECT * FROM Congregation WHERE id={}".format(row_id)
        congregation = DB.return_sql(self, sql)

        self.button_add.clicked.connect(
            lambda: self.submit_edit(congregation[0][0]))

        # Load information of selected congregation into the dialog.
        self.line_name.setText(str(congregation[0][1]))
        self.line_phone.setText(str(congregation[0][2]))
        self.line_email.setText(str(congregation[0][3]))
        self.line_address.setText(str(congregation[0][4]))
        self.line_city.setText(str(congregation[0][5]))
        self.line_state.setText(str(congregation[0][6]))
        self.line_zipcode.setText(str(congregation[0][7]))
        # Select the correct radio box when dialogs loads.
        if str(congregation[0][8]) == "Saturday":
            self.radioSaturday.setChecked(True)
        else:
            self.radioSunday.setChecked(True)
        # show the time
        h, m, s, ms = congregation[0][9].split(',')
        self.timeEdit.setTime(QtCore.QTime(int(h), int(m)))
        self.line_longitude.setText(str(congregation[0][10]))
        self.line_latitude.setText(str(congregation[0][11]))
        self.text_note.setText(str(congregation[0][12]))

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import return_sql [as 別名]
    def check_sanity(self, number, title, edit=False, new_number=0, new_title=0):
        """"
        Checks to make sure there are no duplicates in the database. 

        :param number: The outline number being checked
        :param title: The outline title being checked
        :param new_number: The new outline number if edit is True.
        :param new_title: The new outline title if edit is True.
        """
        self.number = number
        self.title = title
        self.new_number = new_number
        self.new_title = new_title

        if str(self.number) == '':
            return ('False', 'Outline number is missing.')
        if str(self.title) == '':
            return ('False', 'Outline title is missing.')

        outline_number_list = []
        outline_title_list = []

        sql_code = """SELECT * FROM Talk WHERE visibility IS 'True'"""
        database_titles = DB.return_sql(None, sql_code)
        
        # Check for duplicates

        for outline in database_titles:
            outline_number_list.append(outline[1])
            outline_title_list.append(outline[2].lower())

        # If edit is true, remove the original item to not cause false duplicate warnings.
        if edit:  
            outline_number_list.remove(self.number)
            outline_title_list.remove(self.title.lower())
            self.number = self.new_number
            self.title = self.new_title

        if str(self.number) in outline_number_list:
            return ('False', "duplicate outline number")
        if self.title.lower() in outline_title_list:
            return ('False', "duplicate outline title")

        return ('True',)

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import return_sql [as 別名]
    def __check_for_dup(self, name):
        """
        Checks to see if the congregation already exists in the database.
        If the congregation exists then return 'Failed' along with the
        name of the duplicated congregation, otherwise return Passed.

        :argument name: The name of the congregation to check.
        :returns Passed: If no duplicates are found.
        :returns (Failed, 'item'): If a duplicate is found.
        """

        sql = "SELECT name FROM Congregation"
        congregations = DB.return_sql(None, sql)

        if congregations == []:
            return "Passed"
        else:
            for item in congregations:
                item = item[0]
                if item.lower() == name.lower():
                    return ("Failed", item)
            return "Passed"