def updateDatabaseTable(self):
if self._main_tabs.currentIndex() == 1:
table_name = self._tabs.currentWidget().getTableName()
tab_name = self._tabs.currentWidget().getTabName()
self._database_table_label.setText("Table: {tn} ({tbn})".format(
tn=table_name, tbn=tab_name))
self._database.openDatabase()
data = self._database.readTable(table_name)
self._database.closeDatabase()
headers = getFieldNames(table_name)
data_count = len(data)
self._database_table.setRowCount(0)
self._database_table.setColumnCount(len(headers))
self._database_table.setRowCount(data_count)
for col, header_text in enumerate(headers):
header = QTableWidgetItem(header_text)
self._database_table.setHorizontalHeaderItem(col, header)
for row, data_row in enumerate(data):
data_row = list(data_row)
data_row.pop(0)
for col, data_value in enumerate(data_row):
item = QTableWidgetItem(str(data_value))
self._database_table.setItem(row, col, item)
def __init__(self, parent=None):
super(Highlighter, self).__init__(parent)
keywordFormat = QTextCharFormat()
keywordFormat.setForeground(QColor("#00CCFF"))
keywordFormat.setFontCapitalization(QFont.AllUppercase)
keywordPatterns = ["\\bSELECT\\b", "\\bFROM\\b", "\\bWHERE\\b",
"\\bselect\\b", "\\bfrom\\b", "\\bwhere\\b",
"\\bTABLE\\b", "\\btable\\b", "\\bON\\b", "\\bon\\b",
"\\bORDER\\b", "\\border\\b", "\\bBY\\b", "\\bby\\b",
"\\bLIMIT\\b", "\\blimit\\b", "\\bBETWEEN\\b",
"\\bbetween\\b", "\\bLIKE\\b", "\\blike\\b", "\\bTO\\b", "\\bto\\b",
"\\bINNER\\b", "\\inner\\b", "\\bJOIN\\b", "\\bjoin\\b",
"\\bAND\\b", "\\and\\b", "\\bOR\\b", "\\bor\\b",
]
self.highlightingRules = [(QRegExp(pattern), keywordFormat)
for pattern in keywordPatterns]
keyword2Format = QTextCharFormat()
keyword2Format.setForeground(QColor("#DE0000"))
keyword2Format.setFontCapitalization(QFont.AllUppercase)
keyword2Patterns = ["\\bCREATE\\b", "\\bcreate\\b",
"\\bINSERT\\b", "\\binsert\\b", "\\bUPDATE\\b", "\\bupdate\\b",
"\\bDELETE\\b","\\bdelete\\b", "\\bREPLACE\\b", "\\breplace\\b",
"\\bDROP\\b", "\\bdrop\\b", "\\bRENAME\\b", "\\rename\\b",
"\\bALTER\\b", "\\alter\\b",
"\\bSET\\b", "\\bset\\b"
]
self.highlightingRules.extend([(QRegExp(pattern), keyword2Format)
for pattern in keyword2Patterns])
table_name_format = QTextCharFormat()
table_name_format.setForeground(QColor("#00FF7F"))
table_name_format.setFontWeight(QFont.Bold)
table_name_patterns = ["\\b{tn}\\b".format(tn=table.name) for table in Tables]
ex_tables = ["\\b{tn}\\b".format(tn=table.name) for table in ExclusiveDbTables]
table_name_patterns.extend(ex_tables)
self.highlightingRules.extend([(QRegExp(pattern), table_name_format) for pattern in table_name_patterns])
field_name_format = QTextCharFormat()
field_name_format.setForeground(QColor("#00FF7F"))
field_names = []
for table in Tables:
field_names.extend(getFieldNames(table.name))
for table in ExclusiveDbTables:
field_names.extend(getAllFieldNames(table.name, False))
field_name_patterns = ["\\b{fn}\\b".format(fn=field_name) for field_name in field_names]
self.highlightingRules.extend([(QRegExp(pattern), field_name_format) for pattern in field_name_patterns])
def __init__(self, parent=None):
super().__init__(parent)
self._main_widget = QWidget(self)
self._main_layout = QVBoxLayout(self._main_widget)
self._sql_cmd_box = SQLCommandBox()
self._highlighter = Highlighter(self._sql_cmd_box.document())
self._field_names = {}
self._field_names = {
table.name: getFieldNames(table.name)
for table in Tables
}
self._search_label = QLabel("Enter SQL Command:")
self._search_button = QPushButton("Run SQL")
self._search_button.clicked.connect(self.sqlSearch)
search_button_row = QWidget(self)
search_button_lay = QHBoxLayout(search_button_row)
search_button_lay.addWidget(HorizontalFiller())
search_button_lay.addWidget(self._search_button)
self._results_table = DataTable(use_max_height=False)
self._export_results_button = QPushButton("Export Table (CSV)")
self._export_results_button.clicked.connect(self.exportTable)
self._export_results_button.setDisabled(True)
res_row = QWidget(self)
res_lay = QHBoxLayout(res_row)
res_lay.addWidget(HorizontalFiller())
res_lay.addWidget(self._export_results_button)
self._feedback_label = QLabel("Feedback:")
self._feedback_box = QTextEdit()
self._feedback_box.setReadOnly(True)
self._clear_fb_button = QPushButton("Clear")
self._clear_fb_button.clicked.connect(self.clearFeedBack)
fb_row = QWidget(self)
fb_lay = QHBoxLayout(fb_row)
fb_lay.addWidget(HorizontalFiller())
fb_lay.addWidget(self._clear_fb_button)
# Layout
self._main_layout.addWidget(self._search_label)
self._main_layout.addWidget(self._sql_cmd_box)
self._main_layout.addWidget(search_button_row)
self._main_layout.addWidget(self._results_table)
self._main_layout.addWidget(res_row)
self._main_layout.addWidget(BreakLine())
self._main_layout.addWidget(self._feedback_label)
self._main_layout.addWidget(self._feedback_box)
self._main_layout.addWidget(fb_row)
self.setCentralWidget(self._main_widget)
def __init__(self, tables, database, parent=None):
super().__init__(parent)
try:
self._tables = tables
self._database = database
self.setWindowTitle("Search for Records")
self._main_widget = QWidget(self)
self._bas_search_tab = QWidget(self)
self._bas_search_lay = QVBoxLayout(self._bas_search_tab)
self._adv_search_tab = QWidget(self)
self._adv_search_lay = QVBoxLayout(self._adv_search_tab)
self._search_tabs = QTabWidget(self)
self._search_tabs.addTab(self._bas_search_tab, "Search")
self._search_tabs.addTab(self._adv_search_tab, "Advance Search")
layout = QVBoxLayout(self._main_widget)
layout.setContentsMargins(0,0,0,0)
layout.addWidget(self._search_tabs)
# ----------------------------------------------------------------------------------------
# ADVANCE SEARCH
sql_form_box = FormGroupBox("Enter SQL Command:")
self._sql_cmd_box = SQLCommandBox()
self._highlighter = Highlighter(self._sql_cmd_box.document())
self._field_names = {}
self._field_names = {table.name:getFieldNames(table.name) for table in Tables}
for table in ExclusiveDbTables:
self._field_names[table.name] = table.fields
self._adv_search_button = QPushButton("Run SQL")
self._adv_search_button.clicked.connect(self.sqlSearch)
search_button_row = QWidget(self)
search_button_lay = QHBoxLayout(search_button_row)
search_button_lay.setContentsMargins(0,0,0,0)
search_button_lay.addWidget(HorizontalFiller())
search_button_lay.addWidget(self._adv_search_button)
sql_results_box = FormGroupBox("Results:")
self._adv_table_label = QLabel("Number of Results:")
self._adv_results_table = DataTable(use_max_height=False, stretch=False)
self._export_results_button = QPushButton("Export Table (CSV)")
self._export_results_button.clicked.connect(self.exportTable)
self._export_results_button.setDisabled(True)
res_row = QWidget(self)
res_lay = QHBoxLayout(res_row)
res_lay.setContentsMargins(0,0,0,0)
res_lay.addWidget(HorizontalFiller())
res_lay.addWidget(self._export_results_button)
sql_form_box.frame_layout.addRow(self._sql_cmd_box)
sql_form_box.frame_layout.addRow(search_button_row)
self._adv_search_lay.addWidget(sql_form_box)
sql_results_box.frame_layout.addRow(self._adv_table_label)
sql_results_box.frame_layout.addRow(self._adv_results_table)
sql_results_box.frame_layout.addRow(res_row)
self._adv_search_lay.addWidget(sql_results_box)
# ----------------------------------------------------------------------------------------
# BASIC SEARCH
form_box = FormGroupBox("Search for Record by Value", self)
results_box = FormGroupBox("Results", self)
self._table_combo = FormCombo(self)
table_names = [table.name for table in self._tables]
table_names.extend([table.name for table in ExclusiveDbTables])
self._table_combo.addItems(table_names)
self._field_combo = FormCombo(self)
self._condition_combo = FormCombo(self)
self._condition_combo.setMinimumWidth(150)
self._value_entry = ExtendedComboBox(self)
self._value_entry.setMinimumWidth(250)
self._range_entry = FormEntry(self)
self._range_entry.setMinimumWidth(250)
self._table_label = QLabel("Number of Results:")
self._result_table = DataTable(use_max_height=False, stretch=False, parent=self)
self._search_button = QPushButton(" Search ")
self._search_button.clicked.connect(self.searchForData)
self._export_button = QPushButton("Export CSV")
self._export_button.clicked.connect(self.exportCSV)
self._export_button.setEnabled(False)
self._value_type = None
form_box.frame_layout.addRow("Table:", self._table_combo)
condition_row = QWidget(self)
condition_lay = QGridLayout(condition_row)
condition_lay.addWidget(QLabel("Field:"), 0,0)
condition_lay.addWidget(QLabel("Condition:"), 0,1)
condition_lay.addWidget(QLabel("Value:"), 0,2)
condition_lay.addWidget(QLabel("Value 2 (Range Between Value 1 and Value 2):"), 0, 3)
condition_lay.addWidget(self._field_combo, 1,0)
condition_lay.addWidget(self._condition_combo, 1,1)
condition_lay.addWidget(self._value_entry, 1,2)
condition_lay.addWidget(self._range_entry, 1,3)
form_box.frame_layout.addRow(condition_row)
search_row = QWidget(self)
search_lay = QHBoxLayout(search_row)
search_lay.addWidget(HorizontalFiller(self))
search_lay.addWidget(self._search_button)
form_box.frame_layout.addRow(search_row)
search_lay.setContentsMargins(0,0,0,0)
form_box.frame_layout.setContentsMargins(0,0,0,0)
results_box.frame_layout.setContentsMargins(0,0,0,0)
results_box.frame_layout.addRow(self._table_label)
results_box.frame_layout.addRow(self._result_table)
export_row = QWidget(self)
export_lay = QHBoxLayout(export_row)
export_lay.addWidget(HorizontalFiller(self))
export_lay.addWidget(self._export_button)
results_box.frame_layout.addRow(export_row)
export_lay.setContentsMargins(0,0,0,0)
self._current_table = None
self._setTableFieldsCombo()
self._table_combo.currentTextChanged.connect(self._setTableFieldsCombo)
self._setConditionsCombo()
self._field_combo.currentTextChanged.connect(self._setConditionsCombo)
self._conditionChange()
self._condition_combo.currentTextChanged.connect(self._conditionChange)
self._bas_search_lay.addWidget(form_box)
self._bas_search_lay.addWidget(results_box)
self.setCentralWidget(self._main_widget)
except Exception as e:
print("Window failed", e)