def test_typeerror(self):
with self.assertRaises(TypeError) as ctx:
mysqlparse.parse(None)
self.assertEqual(
str(ctx.exception),
"Expected file-like or string object, but got 'NoneType' instead.")
def get_parse_ret(cls, query: str) -> ParseRet:
parsed = mysqlparse.parse(query)
statement = parsed.statements[0] # type:ast.stmt
statement_type = statement.statement_type
table_name = statement.table_name
alter_specification = statement.alter_specification[0]
alter_action = alter_specification.alter_action
column_name = alter_specification.column_name
data_type = alter_specification.data_type
null = alter_specification.null
new_column_name = alter_specification.new_column_name
column_position = alter_specification.column_position
default = alter_specification.default
decimals = alter_specification.decimals
length = alter_specification.length
comment = alter_specification.comment
return ParseRet(
statement_type=statement_type,
table_name=table_name,
alter_action=alter_action,
column_name=column_name,
data_type=data_type,
null=null,
column_position=column_position,
new_column_name=new_column_name,
comment=comment,
decimals=decimals,
default=default,
length=length,
)
def test_file(self):
with open(path.join(self.fixture_dir, 'test.sql'), 'r') as f:
sql_file = mysqlparse.parse(f)
self.assertEqual(len(sql_file.statements), 2)
self.assertEqual(sql_file.statements[0].table_name, 'test_table1')
self.assertEqual(sql_file.statements[1].table_name, 'test_table2')
def test_file(self):
with open(path.join(self.fixture_dir, 'test.sql'), 'r') as f:
sql_file = mysqlparse.parse(f)
self.assertEqual(len(sql_file.statements), 2)
self.assertEqual(sql_file.statements[0].table_name, 'test_table1')
self.assertEqual(sql_file.statements[1].table_name, 'test_table2')
def test_string(self):
with open(path.join(self.fixture_dir, 'test.sql'), 'r') as f:
sql_file = mysqlparse.parse(f.read())
self.assertEqual(len(sql_file.statements), 4)
self.assertEqual(sql_file.statements[0].table_name, 'test_table1')
self.assertEqual(sql_file.statements[1].table_name, 'test_table2')
self.assertEqual(sql_file.statements[2].table_name, 'test_table3')
self.assertEqual(sql_file.statements[3].table_name, 'test_table4')
def allExec(self):
# count lines in text edit
self.textEdit.moveCursor(QtGui.QTextCursor.End)
self.textEdit.setFocus(True)
cursor = self.textEdit.textCursor()
curEnd = cursor.blockNumber() + 1
# start at the beginning of text edit
self.textEdit.moveCursor(QtGui.QTextCursor.Start)
cursor = self.textEdit.textCursor()
curStart = cursor.blockNumber() + 1
selText = ''
for i in range(0, curEnd):
# save line in text edit
cursor = self.textEdit.textCursor()
cursor.select(QtGui.QTextCursor.LineUnderCursor)
line = cursor.selectedText(
) # save content of line under cursor in text edit
selText = selText + ' ' + line
if ';' in line:
print('line: ', selText)
prog = mysqlparse.parse(selText)
if (mysqlparse.error):
self.errorMessageBox(mysqlparse.errorTitle,
mysqlparse.errorDesc)
else:
print('operation: ', mysqlparse.operation)
print('columns: ', mysqlparse.columns) #
print('table_selected: ', mysqlparse.table_selected)
print('withcondition: ', mysqlparse.withcondition) #
print('condition: ', mysqlparse.condition) #
print('value_list: ', mysqlparse.value_list)
print('assignment_list: ', mysqlparse.assignment_list)
print('value_list_bool: ', mysqlparse.value_list_bool) #
print('column_name_bool: ', mysqlparse.column_name_bool)
print('col_name: ', mysqlparse.col_name)
print('comp_operator: ', mysqlparse.comp_operator)
print('cond_exp: ', mysqlparse.cond_exp)
# print(mysqlparse.operation)
self.execute() # execute queries
selText = ''
# move to next line
curPos = cursor.blockNumber() + 1
self.textEdit.moveCursor(QtGui.QTextCursor.Down)
def test_typeerror(self):
with self.assertRaises(TypeError) as ctx:
mysqlparse.parse(None)
self.assertEqual(str(ctx.exception), "Expected file-like or string object, but got 'NoneType' instead.")
def lineExec(self):
# reset errors
mysqlparse.error = False
mysqlparse.errorTitle = ''
mysqlparse.errorDesc = ''
# count lines in text edit
cursor = self.textEdit.textCursor()
curPos = cursor.blockNumber() + 1
self.textEdit.moveCursor(QtGui.QTextCursor.End)
self.textEdit.setFocus(True)
cursor = self.textEdit.textCursor()
curEnd = cursor.blockNumber() + 1
# print(curEnd)
# get start line number
self.textEdit.moveCursor(QtGui.QTextCursor.Start)
cursor = self.textEdit.textCursor()
curStart = cursor.blockNumber() + 1
# print(curStart)
# start at original cursor position
self.textEdit.setTextCursor(
QtGui.QTextCursor(
self.textEdit.document().findBlockByLineNumber(curPos - 1)))
selText = ''
for i in range(0, curEnd):
cursor = self.textEdit.textCursor()
curCurrent = cursor.blockNumber() + 1
# print(curCurrent)
if curCurrent != curStart:
# print('not equal')
# move up one line
curPos = cursor.blockNumber() + 1
self.textEdit.moveCursor(QtGui.QTextCursor.Up)
# save line in text edit
cursor = self.textEdit.textCursor()
cursor.select(QtGui.QTextCursor.LineUnderCursor)
line = cursor.selectedText(
) # save content of line under cursor in text edit
# print(line)
if (';' in line) or (curCurrent == curStart):
if curCurrent == curStart:
selText = selText + ' ' + line
if ';' in selText:
break
# print('start moving down')
# move down one line
curPos = cursor.blockNumber() + 1
self.textEdit.moveCursor(QtGui.QTextCursor.Down)
for j in range(curPos, curEnd + 1):
# get line
cursor = self.textEdit.textCursor()
cursor.select(QtGui.QTextCursor.LineUnderCursor)
line2 = cursor.selectedText()
# print(line2)
# append line to selected text
selText = selText + ' ' + line2
if ';' in line2:
break
# move down one line
curPos = cursor.blockNumber() + 1
self.textEdit.moveCursor(QtGui.QTextCursor.Down)
break
print('selected text: ', selText)
# print("selText")
# selText = "insert into student values ('2013-12345', 'Juan Dela Cruz', '1994-01-01', 'BS Computer Science', 'Security', 144);"
# selText = "insert into student values ('2013-12345', 'Juan Dela Cruz', '1994-01-01', 'BS Computer Science', 'Security', 144);"
# print('\n', selText, '\n')
# selText = selText.lower()
prog = mysqlparse.parse(selText)
if (mysqlparse.error):
self.errorMessageBox(mysqlparse.errorTitle, mysqlparse.errorDesc)
else:
print('operation: ', mysqlparse.operation)
print('columns: ', mysqlparse.columns) #
print('table_selected: ', mysqlparse.table_selected)
print('withcondition: ', mysqlparse.withcondition) #
print('condition: ', mysqlparse.condition) #
print('value_list: ', mysqlparse.value_list)
print('assignment_list: ', mysqlparse.assignment_list)
print('value_list_bool: ', mysqlparse.value_list_bool) #
print('column_name_bool: ', mysqlparse.column_name_bool)
print('col_name: ', mysqlparse.col_name)
print('comp_operator: ', mysqlparse.comp_operator)
print('cond_exp: ', mysqlparse.cond_exp)
self.execute() # execute query
def importDB(self):
print("Edit importDB function")
dlg = QtGui.QFileDialog()
dlg.setFileMode(QFileDialog.AnyFile)
filenames = list()
if dlg.exec_():
filenames = dlg.selectedFiles()
f = open(filenames[0], 'rU')
# with f:
# data = f.read()
# print(data)
a = 0
nameOfFile = os.path.basename(f.name[:-4])
tableColumns = []
tableStringEx = "("
# print(f.name[:])
if f.name[-4:] == ('.csv'):
# Show QInputDialog with dropdown list to choose which table to import into
self.dropdown, ok = QtGui.QInputDialog.getItem(
self.centralwidget, "Select table name", "",
list(tables.keys()), 0, False)
if ok and self.dropdown:
# print(self.dropdown)
reader = csv.reader(f)
for row in reader:
if a == 0: #To get first line
for column in row:
tableColumns.append(column)
tableStringEx = tableStringEx + column + ","
tableStringEx = tableStringEx[:-1] + ")"
a += 1
else:
dataString = ""
for data in row:
if data.isdigit() or data == "NULL":
dataString += data + ","
else:
dataString += "'" + data + "',"
dataString = dataString[:-1]
insertString2 = "INSERT INTO " + self.dropdown + " VALUES(" + dataString + ");" #Without column names
# print(insertString2)
# append.write(dataString+";\n")
# insertString2 = insertString2.lower()
prog = mysqlparse.parse(insertString2)
if mysqlparse.operation.lower() == 'insert':
#returned_rows = trees[mysqlparse.table_selected].insert
errorcheck = trees[
mysqlparse.table_selected].insert(
mysqlparse.value_list_bool,
mysqlparse.column_name_bool,
mysqlparse.value_list, mysqlparse.col_name,
mysqlparse.assignment_list)
if errorcheck:
self.showErrorDialog(
"Error encountered. Abort!", "Error")
# print("Error seen")
break
elif f.name[-4:] == ('.sql'):
multiLineCommentFlag = False
commentRegex = r'/\*|.*\*/|//|--|#'
start = time.time()
for line in iter(f):
if re.match(commentRegex, line) and not multiLineCommentFlag:
multiLineCommentFlag = True
# print "Start"
elif re.match(commentRegex, line) and multiLineCommentFlag:
multiLineCommentFlag = False
# print "End"
elif not multiLineCommentFlag and not re.match(
commentRegex, line):
# print(line[line.find("VALUES (")+1:line.find(");")]) #Will then be sent to finished parser
# line = line.lower()
prog = mysqlparse.parse(line)
if mysqlparse.operation == 'insert':
errorcheck = trees[
mysqlparse.table_selected.lower()].insert(
mysqlparse.value_list_bool,
mysqlparse.column_name_bool,
mysqlparse.value_list, mysqlparse.col_name,
mysqlparse.assignment_list)
if errorcheck:
# print("Error seen")
self.showErrorDialog("Error encountered. Abort!",
"Error")
break
if mysqlparse.operation == 'select':
returned_rows = trees[
mysqlparse.table_selected].select(
mysqlparse.columns, mysqlparse.withcondition,
mysqlparse.condition, mysqlparse.col_name,
mysqlparse.comp_operator, mysqlparse.cond_exp)
self.showQueryResult(returned_rows)
# print(line)
# # print(tables[nameOfFile])
# if(line[line.find(nameOfFile)+len(nameOfFile):line.find("VALUES")].strip() == ""):
# # append.write(line[line.find("VALUES (")+6:line.find(");")+2])
# print(line[line.find("VALUES (")+6:line.find(");")+2])
# else:
# print("with column names")
end = time.time()
self.statusbar.showMessage(
"Time elapsed: " + str("%.3f" %
((end - start) * 1000)) + " ms"
) # show number of rows returned on status bar. -1 for column names
self.showDialog("Importing data was successful.", "Import data")
else:
self.showErrorDialog("Only csv and sql files are accepted!",
"File type error")
self.clearGlobals()
f.close()
