def show_all_rows(self):
''' Remove all hidden rows '''
for r in range(0, self.ui.tableWidget_results.rowCount()):
self.ui.tableWidget_results.setRowHidden(r, False)
self.ui.label.setText(str(len(self.file_data) - 1) + u" rows")
self.queryFilters = ""
self.ui.label.setToolTip(self.queryTime + self.queryFilters)
if __name__ == "__main__":
import sys
app = QtWidgets.QApplication(sys.argv)
Dialog_sql = QtWidgets.QDialog()
ui = Ui_Dialog_sql()
ui.setupUi(Dialog_sql)
Dialog_sql.show()
sys.exit(app.exec_())
class NewTableWidget(QtWidgets.QTableWidget):
''' This extends the table widget by adding a context menu and associated actions '''
row = None
col = None
cellValue = None
def __init__(self, parent=None):
super(NewTableWidget, self).__init__(parent)
def contextMenuEvent(self, event):
class DialogSQL(QtWidgets.QDialog):
''' Uses single inheritance, sub-class QDialog and set up the user interface in
the __init__() method.
A gui to allow the user to enter sql queries and return results.
Data outputs are as tab (or other) separated files. '''
settings = None
parent_textEdit = None
sql = ""
joinData = []
delimiter = "\t" # default delimiter for file exports
file_data = [] # for file exports
results = None # SQL results
queryTime = "" # for label tooltip
queryFilters = "" # for label tooltip
def __init__(self, settings, parent_textEdit):
sys.excepthook = exception_handler
QtWidgets.QDialog.__init__(self)
self.settings = settings
self.parent_textEdit = parent_textEdit
self.queryTime = ""
self.queryFilters = ""
textEditSql = ""
# Set up the user interface from Designer.
self.ui = Ui_Dialog_sql()
self.ui.setupUi(self)
#self.setWindowTitle("Query: " + self.queryname)
self.ui.treeWidget.setContextMenuPolicy(Qt.CustomContextMenu)
highlighter = Highlighter(self.ui.textEdit_sql)
self.ui.textEdit_sql.setContextMenuPolicy(Qt.CustomContextMenu)
self.ui.textEdit_sql.customContextMenuRequested.connect(self.sql_menu)
# fill textEdit_sql from queryname
self.ui.textEdit_sql.setText(textEditSql)
self.ui.tableWidget_results.setContextMenuPolicy(Qt.CustomContextMenu)
self.ui.tableWidget_results.customContextMenuRequested.connect(
self.table_menu)
# Add tables and fields to treeWidget
self.get_schema_update_treeWidget()
self.ui.treeWidget.itemClicked.connect(self.get_item)
self.ui.pushButton_runSQL.clicked.connect(self.run_SQL)
self.ui.pushButton_export.clicked.connect(self.export_file)
self.ui.splitter.setSizes([20, 180])
self.ui.splitter_2.setSizes([10, 290])
def export_file(self):
''' Load resultset.
Export results to a delimited .csv file using \r\n as line separators '''
cur = self.settings['conn'].cursor()
sql = self.ui.textEdit_sql.toPlainText()
try:
cur.execute(sql)
except Exception as e:
QtWidgets.QMessageBox.error(None, 'SQL error', str(e))
return
results = cur.fetchall()
col_names = []
if cur.description is not None:
col_names = list(map(lambda x: x[0],
cur.description)) # gets column names
tmp_name = os.getenv('HOME') + "/Desktop/TEMP.csv"
file_tuple = QtWidgets.QFileDialog.getSaveFileName(
None, "Save text file", tmp_name)
if file_tuple[0] == "":
return
fileName = file_tuple[0]
self.delimiter = str(self.ui.comboBox_delimiter.currentText())
if self.delimiter == "tab":
self.delimiter = "\t"
f = open(fileName, 'w')
# write the header row
file_line = ""
for item in col_names:
file_line += item + self.delimiter
file_line = file_line[:len(file_line) - 1]
f.write(file_line + "\r\n")
# write the data rows
for r, row in enumerate(results):
file_line = ""
for item in row:
if item is None:
file_line += self.delimiter
else:
file_line += str(item) + self.delimiter
file_line = file_line[:len(file_line) - 1]
f.write(file_line + "\r\n")
f.close()
self.parent_textEdit.append("SQL Results exported to " + fileName)
self.parent_textEdit.append("Query:\n" + sql)
QtWidgets.QMessageBox.information(None, "Text file Export",
str(fileName) + " exported")
def get_item(self):
''' Get the selected table name or tablename.fieldname and add to the sql text
at the current cursor position '''
itemText = self.ui.treeWidget.currentItem().text(0)
index = self.ui.treeWidget.currentIndex()
#logger.debug("item index:" + index.row(), index.parent().row())
if index.parent().row() != -1: # there is a parent if not -1
itemParent = self.ui.treeWidget.itemFromIndex(index.parent())
itemParentText = itemParent.text(0)
if itemParentText != "-- Joins --":
itemText = itemParentText + "." + itemText
cursor = self.ui.textEdit_sql.textCursor()
#logger.debug("Cursor position:" + cursor.position())
cursor.insertText(" " + itemText + " ")
def run_SQL(self):
''' Run the sql text and add the results to the results text edit '''
# clear tableWidget and file data
numRows = self.ui.tableWidget_results.rowCount()
for row in range(0, numRows):
self.ui.tableWidget_results.removeRow(0)
self.ui.tableWidget_results.setHorizontalHeaderLabels([""])
self.file_data = []
self.ui.label.setText("Running query. Please wait.")
QtWidgets.QApplication.processEvents() # stops gui freeze
self.sql = self.ui.textEdit_sql.toPlainText()
cur = self.settings['conn'].cursor()
self.sql = str(self.sql)
QtWidgets.QApplication.processEvents() # stops gui freeze
try:
time0 = datetime.now()
cur.execute(self.sql)
self.ui.label.setToolTip("")
self.results = cur.fetchall()
time1 = datetime.now()
timediff = time1 - time0
self.queryTime = "Time:" + str(timediff)
self.ui.label.setToolTip(self.queryTime)
self.ui.label.setText(str(len(self.results)) + " rows")
#extra messaging where rows will be zero
if self.sql[0:12].upper() == "CREATE TABLE":
self.ui.label.setText("Table created")
if self.sql[0:12].upper() == "CREATE INDEX":
self.ui.label.setText("Index created")
self.settings['conn'].commit()
if self.sql[0:6].upper() == "DELETE":
self.ui.label.setText(str(cur.rowcount) + " rows deleted")
self.settings['conn'].commit()
if self.sql[0:6].upper() == "UPDATE":
self.ui.label.setText(str(cur.rowcount) + " rows updated")
self.settings['conn'].commit()
colNames = []
if cur.description is not None:
colNames = list(map(lambda x: x[0],
cur.description)) # gets column names
self.ui.tableWidget_results.setColumnCount(len(colNames))
self.ui.tableWidget_results.setHorizontalHeaderLabels(colNames)
self.file_data.append(colNames)
for row, row_results in enumerate(self.results):
self.file_data.append(row_results)
self.ui.tableWidget_results.insertRow(row)
for col, value in enumerate(row_results):
if value is None:
value = ""
cell = QtWidgets.QTableWidgetItem(str(value))
cell.setFlags(QtCore.Qt.ItemIsSelectable
| QtCore.Qt.ItemIsEnabled)
self.ui.tableWidget_results.setItem(row, col, cell)
self.ui.tableWidget_results.resizeColumnsToContents()
self.ui.tableWidget_results.resizeRowsToContents()
sqlString = str(self.sql).upper()
if sqlString.find("CREATE ") == 0 or sqlString.find(
"DROP ") == 0 or sqlString.find("ALTER ") == 0:
self.getSchemaUpdateTreeWidget()
except (sqlite3.OperationalError, sqlite3.IntegrityError) as e:
QtWidgets.QMessageBox.critical(None, 'Error', str(e),
QtWidgets.QMessageBox.Ok)
self.ui.label.setText("SQL Error")
self.ui.label.setToolTip(str(e))
self.results = None
self.settings['conn'].commit()
def get_schema_update_treeWidget(self):
''' Get table schema from database, and update the tables_an_views tree widget.
The schema needs to be updated when drop table or create queries are run '''
# get schema
self.settings["schema"] = []
tableDict = {}
cur = self.settings["conn"]
result = cur.execute(
"SELECT sql, type, name FROM sqlite_master WHERE type IN ('table', 'view') "
)
for row in result:
tableName = row[2]
fields = []
fieldResults = cur.execute("PRAGMA table_info(" + tableName + ")")
# each field is a tuple of cid, name, type (integer, text, ), notNull (1=notNull),
# defaultValue(None usually), primaryKey(as integers 1 up, or 0)
for field in fieldResults:
fields.append(field)
tableDict[tableName] = fields
self.settings["schema"] = tableDict
# update tables and views in tree widget
tablesAndViews = []
for k in self.settings["schema"].keys():
tablesAndViews.append(k)
tablesAndViews.sort()
self.ui.treeWidget.clear()
for tableName in tablesAndViews:
topItem = QtWidgets.QTreeWidgetItem()
topItem.setText(0, tableName)
result = cur.execute(
"SELECT type FROM sqlite_master WHERE name='" + tableName +
"' ")
tableOrView = result.fetchone()[0]
if tableOrView == "view":
topItem.setBackground(
0, QtGui.QBrush(Qt.yellow, Qt.Dense6Pattern))
self.ui.treeWidget.addTopLevelItem(topItem)
for field in self.settings["schema"][tableName]:
fieldItem = QtWidgets.QTreeWidgetItem()
if tableOrView == "view":
fieldItem.setBackground(
0, QtGui.QBrush(Qt.yellow, Qt.Dense6Pattern))
if field[5] > 0:
fieldItem.setForeground(0, QtGui.QBrush(Qt.red))
fieldItem.setText(0, field[1])
topItem.addChild(fieldItem)
# add join syntax
joinItem = QtWidgets.QTreeWidgetItem()
joinItem.setText(0, "-- Joins --")
self.ui.treeWidget.addTopLevelItem(joinItem)
for join in self.joinData:
jItem = QtWidgets.QTreeWidgetItem()
jItem.setText(0, join)
joinItem.addChild(jItem)
# sql text edit widget context menu
def sql_menu(self, position):
''' add context menu to textedit_sql
includes:cut ctrlX copy ctrlC paste ctrlV delete select_all ctrlA '''
menu = QtWidgets.QMenu()
action_SelectAll = menu.addAction("Select all")
action_copy = menu.addAction("Copy")
action_paste = menu.addAction("Paste")
action_delete = menu.addAction("Delete")
action_SQL_SelectAllFrom = menu.addAction("SELECT * FROM ")
action = menu.exec_(self.ui.textEdit_sql.mapToGlobal(position))
cursor = self.ui.textEdit_sql.textCursor()
if action == action_delete:
text = cursor.selectedText()
text = str(text)
clipboard = QtGui.QApplication.clipboard()
clipboard.setText(text)
start = cursor.position()
end = cursor.anchor()
if start > end:
tmp = end
end = start
start = tmp
beginText = self.ui.textEdit_sql.toPlainText()[0:start]
endText = self.ui.textEdit_sql.toPlainText(
)[end:len(self.ui.textEdit_sql.toPlainText())]
self.ui.textEdit_sql.setText(beginText + endText)
if action == action_paste:
clipboard = QtGui.QApplication.clipboard()
text = clipboard.text()
cursor.insertText(text)
if action == action_copy:
text = cursor.selectedText()
text = str(text)
clipboard = QtWidgets.QApplication.clipboard()
clipboard.setText(text)
if action == action_SelectAll:
clipboard = QtWidgets.QApplication.clipboard()
cursor.setPosition(0)
cursor.setPosition(len(self.ui.textEdit_sql.toPlainText()),
QtGui.QTextCursor.KeepAnchor)
self.ui.textEdit_sql.setTextCursor(cursor)
if action == action_SQL_SelectAllFrom:
cursor.insertText("SELECT * FROM ")
# start table results context menu section
def table_menu(self, position):
''' Context menu for table_results '''
menu = QtWidgets.QMenu()
try:
self.row = self.ui.tableWidget_results.currentRow()
self.col = self.ui.tableWidget_results.currentColumn()
#print(self.row, self.col)
self.cellValue = str(
self.ui.tableWidget_results.item(self.row, self.col).text())
except AttributeError as e:
logger.warning("No table for table menu: " + str(e))
return
ActionShowAllRows = menu.addAction("Clear filter")
ActionShowAllRows.triggered.connect(self.show_all_rows)
ActionFilterOnCellValue = menu.addAction("Filter equals: " +
str(self.cellValue))
ActionFilterOnCellValue.triggered.connect(self.filter_cell_value)
ActionFilterOnTextLike = menu.addAction("Filter on text like")
ActionFilterOnTextLike.triggered.connect(self.filter_text_like)
ActionFilterOnTextStartsWith = menu.addAction(
"Filter on text starts with")
ActionFilterOnTextStartsWith.triggered.connect(
self.filter_text_starts_with)
ActionSortAscending = menu.addAction("Sort ascending")
ActionSortAscending.triggered.connect(self.sort_ascending)
ActionSortDescending = menu.addAction("Sort descending")
ActionSortDescending.triggered.connect(self.sort_descending)
action = menu.exec_(self.ui.tableWidget_results.mapToGlobal(position))
#TODO need to add numerical filters
#TODO need to store type of data to do this
def sort_ascending(self):
''' Sort rows on selected column in ascending order '''
self.ui.tableWidget_results.sortItems(self.col,
QtCore.Qt.AscendingOrder)
self.ui.label.setText(
str(len(self.file_data) - 1) + " rows (" +
self.file_data[0][self.col] + " asc)")
def sort_descending(self):
''' Sort rows on selected column in descending order '''
self.ui.tableWidget_results.sortItems(self.col,
QtCore.Qt.DescendingOrder)
self.ui.label.setText(
str(len(self.file_data) - 1) + " rows (" +
self.file_data[0][self.col] + " desc)")
def filter_text_like(self):
''' Hide rows where cells in the column do not contain the text fragment '''
text, ok = QtWidgets.QInputDialog.getText(None, "Text filter",
"Text contains:",
QtWidgets.QLineEdit.Normal,
str(self.cellValue))
if ok and text != '':
for r in range(0, self.ui.tableWidget_results.rowCount()):
if self.ui.tableWidget_results.item(
r, self.col).text().find(text) == -1:
self.ui.tableWidget_results.setRowHidden(r, True)
self.ui.label.setText(
str(len(self.file_data) - 1) + " rows " + " (filtered)")
self.queryFilters += "\n" + self.ui.tableWidget_results.horizontalHeaderItem(
self.col).text() + " like: " + text
self.ui.label.setToolTip(self.queryTime + self.queryFilters)
def filter_text_starts_with(self):
''' Hide rows where cells in the column do not contain the text start fragment '''
text, ok = QtWidgets.QInputDialog.getText(None, "Text filter",
"Text contains:",
QtWidgets.QLineEdit.Normal,
str(self.cellValue))
if ok and text != '':
for r in range(0, self.ui.tableWidget_results.rowCount()):
if self.ui.tableWidget_results.item(
r, self.col).text().startswith(text) is False:
self.ui.tableWidget_results.setRowHidden(r, True)
self.ui.label.setText(
str(len(self.file_data) - 1) + " rows " + " (filtered)")
self.ui.label.setToolTip(self.queryTime)
self.queryFilters += "\n" + self.ui.tableWidget_results.horizontalHeaderItem(
self.col).text() + " starts with: " + text
self.ui.label.setToolTip(self.queryTime + self.queryFilters)
def filter_cell_value(self):
''' Hide rows that do not have the selected cell value '''
for r in range(0, self.ui.tableWidget_results.rowCount()):
if self.ui.tableWidget_results.item(
r, self.col).text() != self.cellValue:
self.ui.tableWidget_results.setRowHidden(r, True)
self.ui.label.setText(
str(len(self.file_data) - 1) + " rows " + " (filtered)")
self.queryFilters += "\n" + str(
self.ui.tableWidget_results.horizontalHeaderItem(
self.col).text()) + " equals: " + str(self.cellValue)
self.ui.label.setToolTip(self.queryTime + self.queryFilters)
def show_all_rows(self):
''' Remove all hidden rows '''
for r in range(0, self.ui.tableWidget_results.rowCount()):
self.ui.tableWidget_results.setRowHidden(r, False)
self.ui.label.setText(str(len(self.file_data) - 1) + u" rows")
self.queryFilters = ""
self.ui.label.setToolTip(self.queryTime + self.queryFilters)
class DialogSQL(QtWidgets.QDialog):
""" Uses single inheritance, sub-class QDialog and set up the user interface in
the __init__() method.
A gui to allow the user to enter sql queries and return results.
Data outputs are as tab (or other) separated files.
EXTRA_SQL is listed at end of module for additional complex queries. """
app = None
schema = None
parent_textEdit = None
sql = ""
delimiter = "\t" # default delimiter for file exports
file_data = [] # for file exports
results = None # SQL results
queryTime = "" # for label tooltip
queryFilters = "" # for label tooltip
cell_value = ""
def __init__(self, app, parent_textEdit):
sys.excepthook = exception_handler
QtWidgets.QDialog.__init__(self)
self.app = app
self.parent_textEdit = parent_textEdit
self.queryTime = ""
self.queryFilters = ""
textEditSql = ""
# Set up the user interface from Designer.
self.ui = Ui_Dialog_sql()
self.ui.setupUi(self)
self.setWindowFlags(self.windowFlags()
& ~QtCore.Qt.WindowContextHelpButtonHint)
font = 'font: ' + str(self.app.settings['fontsize']) + 'pt '
font += '"' + self.app.settings['font'] + '";'
self.setStyleSheet(font)
#self.setWindowTitle("Query: " + self.queryname)
self.ui.treeWidget.setContextMenuPolicy(Qt.CustomContextMenu)
highlighter = Highlighter(self.ui.textEdit_sql)
self.ui.textEdit_sql.setContextMenuPolicy(Qt.CustomContextMenu)
self.ui.textEdit_sql.customContextMenuRequested.connect(self.sql_menu)
# fill textEdit_sql from queryname
self.ui.textEdit_sql.setText(textEditSql)
self.ui.tableWidget_results.setContextMenuPolicy(Qt.CustomContextMenu)
self.ui.tableWidget_results.customContextMenuRequested.connect(
self.table_menu)
# Add tables and fields to treeWidget
self.get_schema_update_treeWidget()
self.ui.treeWidget.itemClicked.connect(self.get_item)
self.ui.pushButton_runSQL.clicked.connect(self.run_SQL)
#icon = QtGui.QIcon(QtGui.QPixmap('GUI/cogs_icon.png'))
pm = QtGui.QPixmap()
pm.loadFromData(QtCore.QByteArray.fromBase64(cogs_icon), "png")
self.ui.pushButton_runSQL.setIcon(QtGui.QIcon(pm))
self.ui.pushButton_export.clicked.connect(self.export_file)
#icon = QtGui.QIcon(QtGui.QPixmap('GUI/doc_export_csv_icon.png'))
pm = QtGui.QPixmap()
pm.loadFromData(QtCore.QByteArray.fromBase64(doc_export_csv_icon),
"png")
self.ui.pushButton_export.setIcon(QtGui.QIcon(pm))
self.ui.splitter.setSizes([20, 180])
try:
s0 = int(self.app.settings['dialogsql_splitter_h0'])
s1 = int(self.app.settings['dialogsql_splitter_h1'])
if s0 > 10 and s1 > 10:
self.ui.splitter.setSizes([s0, s1])
except:
pass
self.ui.splitter_2.setSizes([10, 290])
try:
s0 = int(self.app.settings['dialogsql_splitter_v0'])
s1 = int(self.app.settings['dialogsql_splitter_v1'])
if s0 > 10 and s1 > 10:
self.ui.splitter_2.setSizes([s0, s1])
except:
pass
self.ui.splitter.splitterMoved.connect(self.update_sizes)
self.ui.splitter_2.splitterMoved.connect(self.update_sizes)
def update_sizes(self):
""" Called by splitter resized """
sizes = self.ui.splitter.sizes()
self.app.settings['dialogsql_splitter_h0'] = sizes[0]
self.app.settings['dialogsql_splitter_h1'] = sizes[1]
sizes = self.ui.splitter_2.sizes()
self.app.settings['dialogsql_splitter_v0'] = sizes[0]
self.app.settings['dialogsql_splitter_v1'] = sizes[1]
def export_file(self):
""" Load result set and export results to a delimited .csv file
using \r\n as line separators. """
cur = self.app.conn.cursor()
sql = self.ui.textEdit_sql.toPlainText()
try:
cur.execute(sql)
except Exception as e:
Message(self.app, _("SQL error"), str(e), "warning").exec_()
return
results = cur.fetchall()
col_names = []
if cur.description is not None:
col_names = list(map(lambda x: x[0],
cur.description)) # gets column names
# os.getenv('HOME') does not work on Windows
tmp_name = self.app.last_export_directory + "/TEMP.csv"
file_tuple = QtWidgets.QFileDialog.getSaveFileName(
None, "Save text file", tmp_name)
if file_tuple[0] == "":
return
filename = file_tuple[0]
tmp = filename.split("/")[-1]
directory = filename[:len(filename) - len(tmp)]
if directory != self.app.last_export_directory:
self.app.last_export_directory = directory
if os.path.exists(filename):
mb = QtWidgets.QMessageBox()
mb.setWindowTitle(_("File exists"))
mb.setText(_("Overwrite?"))
mb.setStandardButtons(QtWidgets.QMessageBox.Yes
| QtWidgets.QMessageBox.No)
mb.setStyleSheet("* {font-size:" +
str(self.app.settings['fontsize']) + "pt} ")
if mb.exec_() == QtWidgets.QMessageBox.No:
return
self.delimiter = str(self.ui.comboBox_delimiter.currentText())
if self.delimiter == "tab":
self.delimiter = "\t"
''' https://stackoverflow.com/questions/39422573/python-writing-weird-unicode-to-csv
Using a byte order mark so that other software recognised UTF-8
'''
f = open(filename, 'w', encoding='utf-8-sig')
# write the header row
file_line = ""
for item in col_names:
file_line += item + self.delimiter
file_line = file_line[:len(file_line) - 1]
f.write(file_line + "\r\n")
# write the data rows
for r, row in enumerate(results):
file_line = ""
for item in row:
if item is None:
file_line += self.delimiter
else:
file_line += str(item) + self.delimiter
file_line = file_line[:len(file_line) - 1]
f.write(file_line + "\r\n")
f.close()
self.parent_textEdit.append(_("SQL Results exported to: ") + filename)
self.parent_textEdit.append(_("Query:") + "\n" + sql)
Message(self.app, _("Text file export"), filename,
"information").exec_()
def get_item(self):
""" Get the selected table name or tablename.fieldname and add to the sql text
at the current cursor position.
Also get a prepared query and replace sql text in the text edit. """
item_text = self.ui.treeWidget.currentItem().text(0)
index = self.ui.treeWidget.currentIndex()
if index.parent().row() != -1: # there is a parent if not -1
item_parent = self.ui.treeWidget.itemFromIndex(index.parent())
item_parent_text = item_parent.text(0)
if item_parent_text != "-- Query --":
item_text = item_parent_text + "." + item_text
if item_parent_text == "-- Query --":
self.ui.textEdit_sql.clear()
cursor = self.ui.textEdit_sql.textCursor()
cursor.insertText(" " + item_text + " ")
def run_SQL(self):
""" Run the sql text and add the results to the results text edit. """
# clear tableWidget and file data
numRows = self.ui.tableWidget_results.rowCount()
for row in range(0, numRows):
self.ui.tableWidget_results.removeRow(0)
self.ui.tableWidget_results.setHorizontalHeaderLabels([""])
self.file_data = []
self.ui.label.setText(_("Running query. Please wait."))
QtWidgets.QApplication.processEvents() # stops gui freeze
self.sql = self.ui.textEdit_sql.toPlainText()
cur = self.app.conn.cursor()
self.sql = str(self.sql)
QtWidgets.QApplication.processEvents() # stops gui freeze
try:
time0 = datetime.now()
cur.execute(self.sql)
self.ui.label.setToolTip("")
self.results = cur.fetchall()
time1 = datetime.now()
timediff = time1 - time0
self.queryTime = "Time:" + str(timediff)
self.ui.label.setToolTip(self.queryTime)
self.ui.label.setText(str(len(self.results)) + _(" rows"))
#extra messaging where rows will be zero
if self.sql[0:12].upper() == "CREATE TABLE":
self.ui.label.setText(_("Table created"))
self.app.delete_backup = False
if self.sql[0:12].upper() == "CREATE INDEX":
self.ui.label.setText(_("Index created"))
self.app.delete_backup = False
self.app.conn.commit()
if self.sql[0:6].upper() == "DELETE":
self.ui.label.setText(str(cur.rowcount) + _(" rows deleted"))
self.app.delete_backup = False
self.app.conn.commit()
if self.sql[0:6].upper() == "UPDATE":
self.ui.label.setText(str(cur.rowcount) + _(" rows updated"))
self.app.delete_backup = False
self.app.conn.commit()
colNames = []
if cur.description is not None:
colNames = list(map(lambda x: x[0],
cur.description)) # gets column names
self.ui.tableWidget_results.setColumnCount(len(colNames))
self.ui.tableWidget_results.setHorizontalHeaderLabels(colNames)
self.file_data.append(colNames)
for row, row_results in enumerate(self.results):
self.file_data.append(row_results)
self.ui.tableWidget_results.insertRow(row)
for col, value in enumerate(row_results):
if value is None:
value = ""
cell = QtWidgets.QTableWidgetItem(str(value))
cell.setFlags(QtCore.Qt.ItemIsSelectable
| QtCore.Qt.ItemIsEnabled)
self.ui.tableWidget_results.setItem(row, col, cell)
self.ui.tableWidget_results.resizeColumnsToContents()
self.ui.tableWidget_results.resizeRowsToContents()
sqlString = str(self.sql).upper()
if sqlString.find("CREATE ") == 0 or sqlString.find(
"DROP ") == 0 or sqlString.find("ALTER ") == 0:
self.get_schema_update_treeWidget()
except (sqlite3.OperationalError, sqlite3.IntegrityError) as e:
Message(self.app, _("Error"), str(e), "warning").exec_()
self.ui.label.setText(_("SQL Error"))
self.ui.label.setToolTip(str(e))
self.results = None
self.app.conn.commit()
def get_schema_update_treeWidget(self):
""" Get table schema from database, and update the tables_an_views tree widget.
The schema needs to be updated when drop table or create queries are run. """
self.schema = []
table_dict = {}
cur = self.app.conn.cursor()
cur.execute(
"SELECT sql, type, name FROM sqlite_master WHERE type IN ('table', 'view') "
)
result = cur.fetchall()
for row in result:
table_name = row[2]
fields = []
field_results = cur.execute("PRAGMA table_info(" + table_name +
")")
# each field is a tuple of cid, name, type (integer, text, ), notNull (1=notNull),
# defaultValue(None usually), primaryKey(as integers 1 up, or 0)
for field in field_results:
fields.append(field)
table_dict[table_name] = fields
self.schema = table_dict
# Fill tree widget with tables and views
tables_and_views = []
for k in self.schema.keys():
tables_and_views.append(k)
tables_and_views.sort()
self.ui.treeWidget.clear()
for table_name in tables_and_views:
top_item = QtWidgets.QTreeWidgetItem()
top_item.setText(0, table_name)
result = cur.execute(
"SELECT type FROM sqlite_master WHERE name='" + table_name +
"' ")
table_or_view = result.fetchone()[0]
if table_or_view == "view":
top_item.setBackground(
0, QtGui.QBrush(Qt.yellow, Qt.Dense6Pattern))
self.ui.treeWidget.addTopLevelItem(top_item)
for field in self.schema[table_name]:
field_item = QtWidgets.QTreeWidgetItem()
if table_or_view == "view":
field_item.setBackground(
0, QtGui.QBrush(Qt.yellow, Qt.Dense6Pattern))
if field[5] > 0:
field_item.setForeground(0, QtGui.QBrush(Qt.red))
field_item.setText(0, field[1])
top_item.addChild(field_item)
# Add prepared query sqls
query_item = QtWidgets.QTreeWidgetItem()
query_item.setText(0, "-- Query --")
self.ui.treeWidget.addTopLevelItem(query_item)
for query in EXTRA_SQL:
item = QtWidgets.QTreeWidgetItem()
item.setText(0, query)
query_item.addChild(item)
def sql_menu(self, position):
""" Context menu to textedit_sql
Cut Ctrl + X, Copy Ctrl + C, Paste Ctrl + V, Delete, Ctrl + A selectall. """
menu = QtWidgets.QMenu()
menu.setStyleSheet("QMenu {font-size:" +
str(self.app.settings['fontsize']) + "pt} ")
action_SelectAll = menu.addAction(_("Select all"))
action_copy = menu.addAction(_("Copy"))
action_paste = menu.addAction(_("Paste"))
action_delete = menu.addAction(_("Delete"))
action_SelectAllFrom = menu.addAction("SELECT * FROM ")
action = menu.exec_(self.ui.textEdit_sql.mapToGlobal(position))
cursor = self.ui.textEdit_sql.textCursor()
if action == action_delete:
text = cursor.selectedText()
text = str(text)
start = cursor.position()
end = cursor.anchor()
if start > end:
tmp = end
end = start
start = tmp
beginText = self.ui.textEdit_sql.toPlainText()[0:start]
endText = self.ui.textEdit_sql.toPlainText(
)[end:len(self.ui.textEdit_sql.toPlainText())]
self.ui.textEdit_sql.setText(beginText + endText)
if action == action_paste:
clipboard = QtWidgets.QApplication.clipboard()
text = clipboard.text()
cursor.insertText(text)
if action == action_copy:
text = cursor.selectedText()
text = str(text)
clipboard = QtWidgets.QApplication.clipboard()
clipboard.setText(text)
if action == action_SelectAll:
clipboard = QtWidgets.QApplication.clipboard()
cursor.setPosition(0)
cursor.setPosition(len(self.ui.textEdit_sql.toPlainText()),
QtGui.QTextCursor.KeepAnchor)
self.ui.textEdit_sql.setTextCursor(cursor)
if action == action_SelectAllFrom:
cursor.insertText("SELECT * FROM ")
# start table results context menu section
def table_menu(self, position):
""" Context menu for table_results. """
menu = QtWidgets.QMenu()
menu.setStyleSheet("QMenu {font-size:" +
str(self.app.settings['fontsize']) + "pt} ")
try:
self.row = self.ui.tableWidget_results.currentRow()
self.col = self.ui.tableWidget_results.currentColumn()
self.cell_value = str(
self.ui.tableWidget_results.item(self.row, self.col).text())
except AttributeError as e:
logger.warning("No table for table menu: " + str(e))
return
ActionShowAllRows = menu.addAction(_("Clear filter"))
ActionShowAllRows.triggered.connect(self.show_all_rows)
ActionFilterOnCellValue = menu.addAction(
_("Filter equals: ") + str(self.cell_value))
ActionFilterOnCellValue.triggered.connect(self.filter_cell_value)
ActionFilterOnTextLike = menu.addAction(_("Filter on text like"))
ActionFilterOnTextLike.triggered.connect(self.filter_text_like)
ActionFilterOnTextStartsWith = menu.addAction(
_("Filter on text starts with"))
ActionFilterOnTextStartsWith.triggered.connect(
self.filter_text_starts_with)
ActionSortAscending = menu.addAction(_("Sort ascending"))
ActionSortAscending.triggered.connect(self.sort_ascending)
ActionSortDescending = menu.addAction(_("Sort descending"))
ActionSortDescending.triggered.connect(self.sort_descending)
action = menu.exec_(self.ui.tableWidget_results.mapToGlobal(position))
#TODO need to add numerical filters
#TODO need to store or determine type of data to do this
def sort_ascending(self):
""" Sort rows on selected column in ascending order. """
self.ui.tableWidget_results.sortItems(self.col,
QtCore.Qt.AscendingOrder)
self.ui.label.setText(
str(len(self.file_data) - 1) + _(" rows [") +
self.file_data[0][self.col] + _(" asc]"))
def sort_descending(self):
""" Sort rows on selected column in descending order. """
self.ui.tableWidget_results.sortItems(self.col,
QtCore.Qt.DescendingOrder)
self.ui.label.setText(
str(len(self.file_data) - 1) + _(" rows [") +
self.file_data[0][self.col] + _(" desc]"))
def filter_text_like(self):
""" Hide rows where cells in the column do not contain the text fragment. """
text, ok = QtWidgets.QInputDialog.getText(None, _("Text filter"),
_("Text contains:"),
QtWidgets.QLineEdit.Normal,
str(self.cell_value))
if ok and text != '':
for r in range(0, self.ui.tableWidget_results.rowCount()):
if self.ui.tableWidget_results.item(
r, self.col).text().find(text) == -1:
self.ui.tableWidget_results.setRowHidden(r, True)
self.ui.label.setText(
str(len(self.file_data) - 1) + _(" rows [filtered]"))
self.queryFilters += "\n" + self.ui.tableWidget_results.horizontalHeaderItem(
self.col).text() + " like: " + text
self.ui.label.setToolTip(self.queryTime + self.queryFilters)
def filter_text_starts_with(self):
""" Hide rows where cells in the column do not contain the text start fragment. """
text, ok = QtWidgets.QInputDialog.getText(None, _("Text filter"),
_("Text contains:"),
QtWidgets.QLineEdit.Normal,
str(self.cell_value))
if ok and text != '':
for r in range(0, self.ui.tableWidget_results.rowCount()):
if self.ui.tableWidget_results.item(
r, self.col).text().startswith(text) is False:
self.ui.tableWidget_results.setRowHidden(r, True)
self.ui.label.setText(
str(len(self.file_data) - 1) + _(" rows [filtered]"))
self.ui.label.setToolTip(self.queryTime)
self.queryFilters += "\n" + self.ui.tableWidget_results.horizontalHeaderItem(
self.col).text() + _(" starts with: ") + text
self.ui.label.setToolTip(self.queryTime + self.queryFilters)
def filter_cell_value(self):
""" Hide rows that do not have the selected cell value. """
for r in range(0, self.ui.tableWidget_results.rowCount()):
if self.ui.tableWidget_results.item(
r, self.col).text() != self.cell_value:
self.ui.tableWidget_results.setRowHidden(r, True)
self.ui.label.setText(
str(len(self.file_data) - 1) + _(" rows [filtered]"))
self.queryFilters += "\n" + str(
self.ui.tableWidget_results.horizontalHeaderItem(
self.col).text()) + _(" equals: ") + str(self.cell_value)
self.ui.label.setToolTip(self.queryTime + self.queryFilters)
def show_all_rows(self):
""" Remove all hidden rows. """
for r in range(0, self.ui.tableWidget_results.rowCount()):
self.ui.tableWidget_results.setRowHidden(r, False)
self.ui.label.setText(str(len(self.file_data) - 1) + _(" rows"))
self.queryFilters = ""
self.ui.label.setToolTip(self.queryTime + self.queryFilters)
