import sys
from PyQt5.QtWidgets import QWidget,QApplication,\
QPushButton,QMessageBox,QLineEdit,QInputDialog,\
QTextEdit
from PyQt5.QtCore import QCoreApplication
import pymysql
class Example(QWidget):
def __init__(self):
super().__init__()
self.initUI()
def initUI(self):
self.tx = QTextEdit(self)
self.tx.setGeometry(350,50,300,300)
btn1 = QPushButton('创建数据库',self)
btn1.resize(100,40)
btn1.move(50,50)
btn1.clicked.connect(self.create_db)
btn2 = QPushButton('创建表', self)
btn2.resize(100, 40)
btn2.move(50, 100)
btn2.clicked.connect(self.create_tab)
btn3 = QPushButton('插入数据', self)
btn3.resize(100, 40)
btn3.move(50, 150)
btn3.clicked.connect(self.add_tab)
btn4 = QPushButton('删除数据', self)
btn4.resize(100, 40)
btn4.move(50, 200)
btn4.clicked.connect(self.del_tab)
btn5 = QPushButton('修改数据', self)
btn5.resize(100, 40)
btn5.move(50, 250)
btn5.clicked.connect(self.update_tab)
btn6 = QPushButton('查询数据', self)
btn6.resize(100, 40)
btn6.move(50, 300)
btn6.clicked.connect(self.search_tab)
btn7 = QPushButton('创建视图', self)
btn7.resize(100, 40)
btn7.move(200, 50)
btn7.clicked.connect(self.create_view)
btn8 = QPushButton('插入视图数据', self)
btn8.resize(100, 40)
btn8.move(200, 100)
btn8.clicked.connect(self.add_view)
btn9 = QPushButton('删除视图数据', self)
btn9.resize(100, 40)
btn9.move(200, 150)
btn9.clicked.connect(self.del_view)
btn10 = QPushButton('修改视图数据', self)
btn10.resize(100, 40)
btn10.move(200, 200)
btn10.clicked.connect(self.update_view)
btn11 = QPushButton('查询视图数据', self)
btn11.resize(100, 40)
btn11.move(200, 250)
btn11.clicked.connect(self.search_view)
quitbtn = QPushButton('退出',self)
quitbtn.clicked.connect(QCoreApplication.instance().quit)
quitbtn.resize(100,40)
quitbtn.move(200,300)
self.setGeometry(350,200,700,400)
self.setWindowTitle("数据库操作")
self.show()
def create_db(self):
try:
text,action = QInputDialog.getText(self,'创建数据库',
'输入新建数据库名(CREATE DATABASE )',QLineEdit.Normal)
if action and text.replace(' ','') != ' ':
print(text)
sql = 'CREATE DATABASE ' + text + ';'
cursor.execute(sql)
print(sql)
self.tx.setText(sql)
print(cursor.fetchall())
print('创建数据库%s成功'%text)
except Exception as e:
print(e)
def create_tab(self):
try:
cursor.execute('SHOW DATABASES;')
print(cursor.fetchall())
db_name, action1 = QInputDialog.getText(self,'创建表',
'输入数据库名(USE (db_name))',QLineEdit.Normal)
if action1 and db_name.replace(' ','') != ' ':
sql = 'USE ' + db_name + ';'
cursor.execute(sql)
try:
tab_name, action2 = QInputDialog.getText(self,'创建表',
'输入新建表名(CREATE TABLE )',QLineEdit.Normal)
if action2 and tab_name.replace(' ','') != ' ':
sql = 'CREATE TABLE ' + tab_name + '('
try:
colums, action3 = QInputDialog.getText(self,'创建表',
'列名 数据类型 约束条件',QLineEdit.Normal)
if action3 and colums.replace(' ','') != ' ':
sql = sql + colums + ');'
cursor.execute(sql)
print(sql)
self.tx.setText(sql)
print(cursor.fetchall())
print('创建表%s成功'%tab_name)
except Exception as e:
print(e)
except Exception as e:
print(e)
except Exception as e:
print(e)
def add_tab(self):
try:
cursor.execute('SHOW DATABASES;')
print(cursor.fetchall())
db_name, action1 = QInputDialog.getText(self, '插入数据',
'输入数据库名(USE (db_name))', QLineEdit.Normal)
if action1 and db_name.replace(' ', '') != ' ':
sql = 'USE ' + db_name + ';'
cursor.execute(sql)
cursor.execute('SHOW TABLES;')
print(cursor.fetchall())
try:
tab_name, action2 = QInputDialog.getText(self, '插入数据',
'输入表名(INSERT INTO )', QLineEdit.Normal)
if action2 and tab_name.replace(' ', '') != ' ':
sql = 'INSERT INTO ' + tab_name
try:
colums, action3 = QInputDialog.getText(self, '插入数据',
'选择列名（’,‘隔开）', QLineEdit.Normal)
if action3 and colums.replace(' ', '') != ' ':
if(colums != '*'):
sql = sql + '(' + colums + ') '
else:
sql = sql + ' '
try:
values, action4 = QInputDialog.getText(self, '插入数据',
'VALUES ', QLineEdit.Normal)
if action4 and values.replace(' ', '') != ' ':
sql = sql + 'VALUES' + '(' + values + ');'
cursor.execute(sql)
print(sql)
self.tx.setText(sql)
print(cursor.fetchall())
print('插入数据成功')
db.commit()
except Exception as e:
print(e)
except Exception as e:
print(e)
except Exception as e:
print(e)
except Exception as e:
print(e)
def del_tab(self):
try:
cursor.execute('SHOW DATABASES;')
print(cursor.fetchall())
db_name, action1 = QInputDialog.getText(self,'删除数据',
'输入数据库名(USE (db_name))',QLineEdit.Normal)
if action1 and db_name.replace(' ','') != ' ':
sql = 'USE ' + db_name + ';'
cursor.execute(sql)
cursor.execute('SHOW TABLES;')
print(cursor.fetchall())
try:
tab_name, action2 = QInputDialog.getText(self,'删除数据',
'输入表名(DELETE FROM )',QLineEdit.Normal)
if action2 and tab_name.replace(' ','') != ' ':
sql = 'DELETE FROM ' + tab_name + ' '
try:
value, action3 = QInputDialog.getText(self,'删除数据',
'输入删除条件(WHERE )',QLineEdit.Normal)
if action3 and value.replace(' ','') != ' ':
if(value != '*'):
sql = sql + 'WHERE ' + value + ';'
else:
sql = 'DELETE * FROM ' + tab_name + ';'
cursor.execute(sql)
print(sql)
self.tx.setText(sql)
print(cursor.fetchall())
print('删除数据成功')
db.commit()
except Exception as e:
print(e)
except Exception as e:
print(e)
except Exception as e:
print(e)
def update_tab(self):
try:
cursor.execute('SHOW DATABASES;')
print(cursor.fetchall())
db_name, action1 = QInputDialog.getText(self, '修改数据',
'输入数据库名(USE (db_name))', QLineEdit.Normal)
if action1 and db_name.replace(' ', '') != ' ':
sql = 'USE ' + db_name + ';'
cursor.execute(sql)
cursor.execute('SHOW TABLES;')
print(cursor.fetchall())
try:
tab_name, action2 = QInputDialog.getText(self, '修改数据',
'输入表名(UPDATE )', QLineEdit.Normal)
if action2 and tab_name.replace(' ', '') != ' ':
sql = 'UPDATE ' + tab_name + ' '
try:
value, action3 = QInputDialog.getText(self, '修改数据',
'输入修改数据(SET )', QLineEdit.Normal)
if action3 and value.replace(' ', '') != ' ':
sql = sql + 'SET ' + value + ' '
try:
where, action4 = QInputDialog.getText(self, '修改数据',
'输入修改条件(WHERE )', QLineEdit.Normal)
if action4 and where.replace(' ', '') != ' ':
if where != '':
sql = sql + 'WHERE ' + where + ';'
else:
sql = sql + ';'
cursor.execute(sql)
print(sql)
self.tx.setText(sql)
print(cursor.fetchall())
print('修改数据成功')
db.commit()
except Exception as e:
print(e)
except Exception as e:
print(e)
except Exception as e:
print(e)
except Exception as e:
print(e)
def search_tab(self):
try:
cursor.execute('SHOW DATABASES;')
print(cursor.fetchall())
db_name, action1 = QInputDialog.getText(self, '查询数据',
'输入数据库名(USE (db_name))', QLineEdit.Normal)
if action1 and db_name.replace(' ', '') != ' ':
sql = 'USE ' + db_name + ';'
cursor.execute(sql)
cursor.execute('SHOW TABLES;')
print(cursor.fetchall())
try:
tab_name, action2 = QInputDialog.getText(self, '查询数据',
'输入查询表', QLineEdit.Normal)
if action2 and tab_name.replace(' ', '') != ' ':
try:
colums, action3 = QInputDialog.getText(self, '查询数据',
'输入属性', QLineEdit.Normal)
if action3 and colums.replace(' ', '') != ' ':
sql = 'SELECT ' + colums + ' FROM ' + tab_name
try:
values, action4 = QInputDialog.getText(self, '插入数据',
'输入条件', QLineEdit.Normal)
if action4 and values.replace(' ', '') != ' ':
if(values != ''):
sql = sql + ' WHERE ' + values + ';'
else:
sql = sql + ';'
cursor.execute(sql)
print(sql)
self.tx.setText(sql)
print(cursor.fetchall())
db.commit()
except Exception as e:
print(e)
except Exception as e:
print(e)
except Exception as e:
print(e)
except Exception as e:
print(e)
def create_view(self):
try:
cursor.execute('SHOW DATABASES;')
print(cursor.fetchall())
db_name, action = QInputDialog.getText(self,'创建视图','输入数据库名(USE (db_name))',QLineEdit.Normal)
if action and db_name.replace(' ','') != ' ':
sql = 'USE ' + db_name + ';'
cursor.execute(sql)
try:
view_name,action1 = QInputDialog.getText(self,'创建视图','输入视图名及列名(CREAETE VIEW )',QLineEdit.Normal)
if action1 and view_name.replace(' ','') != ' ':
sql = 'CREATE VIEW ' + view_name + ' AS '
try:
tab_name, action2 = QInputDialog.getText(self,'创建视图','输入表名',QLineEdit.Normal)
if action2 and tab_name.replace(' ','') != ' ':
try:
colums, action3 = QInputDialog.getText(self,'创建视图','输入列名(\',\'隔开)',QLineEdit.Normal)
if action3 and colums.replace(' ','') != ' ':
sql = sql + 'SELECT ' + colums + ' FROM ' + tab_name
try:
where, action4 = QInputDialog.getText(self,'创建视图','输入条件(WHERE )',QLineEdit.Normal)
if action4 and where.replace(' ','') != ' ':
sql = sql + ' WHERE ' + where + ';'
cursor.execute(sql)
print(sql)
self.tx.setText(sql)
print(cursor.fetchall())
db.commit()
except Exception as e:
print(e)
except Exception as e:
print(e)
except Exception as e:
print(e)
except Exception as e:
print(e)
except Exception as e:
print(e)
def add_view(self):
try:
cursor.execute('SHOW DATABASES;')
print(cursor.fetchall())
db_name, action1 = QInputDialog.getText(self, '插入视图数据',
'输入数据库名(USE (db_name))', QLineEdit.Normal)
if action1 and db_name.replace(' ', '') != ' ':
sql = 'USE ' + db_name + ';'
cursor.execute(sql)
try:
view_name, action2 = QInputDialog.getText(self, '插入视图数据',
'输入视图名(INSERT INTO )', QLineEdit.Normal)
if action2 and view_name.replace(' ', '') != ' ':
sql = 'INSERT INTO ' + view_name
try:
colums, action3 = QInputDialog.getText(self, '插入视图数据',
'选择列名（’,‘隔开）', QLineEdit.Normal)
if action3 and colums.replace(' ', '') != ' ':
if (colums != '*'):
sql = sql + '(' + colums + ') '
else:
sql = sql + ' '
try:
values, action4 = QInputDialog.getText(self, '插入视图数据',
'VALUES ', QLineEdit.Normal)
if action4 and values.replace(' ', '') != ' ':
sql = sql + 'VALUES' + '(' + values + ');'
cursor.execute(sql)
print(sql)
self.tx.setText(sql)
print(cursor.fetchall())
print('插入视图数据成功')
db.commit()
except Exception as e:
print(e)
except Exception as e:
print(e)
except Exception as e:
print(e)
except Exception as e:
print(e)
def del_view(self):
try:
cursor.execute('SHOW DATABASES;')
print(cursor.fetchall())
db_name, action1 = QInputDialog.getText(self, '删除视图数据',
'输入数据库名(USE (db_name))', QLineEdit.Normal)
if action1 and db_name.replace(' ', '') != ' ':
sql = 'USE ' + db_name + ';'
cursor.execute(sql)
try:
view_name, action2 = QInputDialog.getText(self, '删除视图数据',
'输入视图名(DELETE FROM )', QLineEdit.Normal)
if action2 and view_name.replace(' ', '') != ' ':
sql = 'DELETE FROM ' + view_name + ' '
try:
value, action3 = QInputDialog.getText(self, '删除视图数据',
'输入删除条件(WHERE )', QLineEdit.Normal)
if action3 and value.replace(' ', '') != ' ':
if (value != '*'):
sql = sql + 'WHERE ' + value + ';'
else:
sql = 'DELETE * FROM ' + view_name + ';'
cursor.execute(sql)
print(sql)
self.tx.setText(sql)
print(cursor.fetchall())
print('删除视图数据成功')
db.commit()
except Exception as e:
print(e)
except Exception as e:
print(e)
except Exception as e:
print(e)
def update_view(self):
try:
cursor.execute('SHOW DATABASES;')
print(cursor.fetchall())
db_name, action1 = QInputDialog.getText(self, '修改视图数据',
'输入数据库名(USE (db_name))', QLineEdit.Normal)
if action1 and db_name.replace(' ', '') != ' ':
sql = 'USE ' + db_name + ';'
cursor.execute(sql)
try:
view_name, action2 = QInputDialog.getText(self, '修改视图数据',
'输入视图名(UPDATE (view_name))', QLineEdit.Normal)
if action2 and view_name.replace(' ', '') != ' ':
sql = 'UPDATE ' + view_name + ' '
try:
value, action3 = QInputDialog.getText(self, '修改视图数据',
'输入修改数据(SET 列名=值)', QLineEdit.Normal)
if action3 and value.replace(' ', '') != ' ':
sql = sql + 'SET ' + value + ' '
try:
where, action4 = QInputDialog.getText(self, '修改视图数据',
'输入修改条件(WHERE )', QLineEdit.Normal)
if action4 and where.replace(' ', '') != ' ':
if where != '':
sql = sql + 'WHERE ' + where + ';'
else:
sql = sql + ';'
cursor.execute(sql)
print(sql)
self.tx.setText(sql)
print(cursor.fetchall())
print('修改视图数据成功')
db.commit()
except Exception as e:
print(e)
except Exception as e:
print(e)
except Exception as e:
print(e)
except Exception as e:
print(e)
def search_view(self):
try:
db_name, action1 = QInputDialog.getText(self, '查询视图数据',
'输入数据库名(USE (db_name))', QLineEdit.Normal)
if action1 and db_name.replace(' ', '') != ' ':
sql = 'USE ' + db_name + ';'
cursor.execute(sql)
try:
view_name, action2 = QInputDialog.getText(self, '查询视图数据',
'输入查询视图', QLineEdit.Normal)
if action2 and view_name.replace(' ', '') != ' ':
try:
colums, action3 = QInputDialog.getText(self, '查询视图数据',
'输入属性', QLineEdit.Normal)
if action3 and colums.replace(' ', '') != ' ':
sql = 'SELECT ' + colums + ' FROM ' + view_name
try:
values, action4 = QInputDialog.getText(self, '查询视图数据',
'输入查询条件(WHERE )', QLineEdit.Normal)
if action4 and values.replace(' ', '') != ' ':
if (values != ''):
sql = sql + ' WHERE ' + values + ';'
else:
sql = sql + ';'
cursor.execute(sql)
print(sql)
self.tx.setText(sql)
print(cursor.fetchall())
db.commit()
except Exception as e:
print(e)
except Exception as e:
print(e)
except Exception as e:
print(e)
except Exception as e:
print(e)
def closeEvent(self,event):
reply = QMessageBox.question(self,'Message',
'确认退出？',
QMessageBox.Yes|QMessageBox.No,
QMessageBox.No)
if reply == QMessageBox.Yes:
event.accept()
else:
event.ignore()
if __name__ == '__main__':
db = pymysql.connect(host = 'localhost',
port = 3306,
user = 'root',
password = 'root')
cursor = db.cursor()
app = QApplication(sys.argv)
ex = Example()
sys.exit(app.exec_())操作说明：
学生选课数据库操作说明文档
- 创建数据库：输入数据库名称即可完成创建
- 创建表：先输入要保存表的数据库名，在依次输入创建表的名称、列名+属性类型+约束条件
- 插入数据：依次输入数据库名和表名，列名和插入数据，若插入成功会显示“插入数据成功”
- 删除数据：依次输入数据库名、表名、删除条件，若删除成功会显示“删除数据成功”
- 修改数据：依次输入数据库名、表名、修改的数据、修改条件，修改数据成功后显示“修改数据成功”
- 查询数据：依次输入数据名、表名、查询属性、条件
- 创建视图：先输入数据库名，再依次输入视图名及列名、表名、创建条件
- 插入视图数据：依次输入数据库名、视图名、数据，插入成功显示“插入数据成功”
- 删除视图数据：依次输入数据库名、视图名、删除条件，删除成功显示“删除数据成功”
- 修改视图数据：依次输入数据库名、视图名、修改数据、修改条件，修改成功显示“数据修改成功”
- 查询视图数据：依次输入数据库名、视图名、属性、条件
- 退出：点击后退出界面
所有指令运行成功后会在文本框显示本次执行的sql语句。
282
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
