def add_user(self): # 数据库操作流程 # 1、获取连接 conn = get_conn() # 2、获取cursor cur = conn.cursor() # 3、SQl语句 sql = "insert into users(user_name, user_pwd, user_email) values(%s, %s, %s)" user_name = self.add_user_name.text() user_email = self.add_user_email.text() user_pwd = self.add_user_pwd.text() user_confirm_pwd = self.add_user_confirm_pwd.text() self.error_message.setText("") if user_pwd == user_confirm_pwd: # 加盐 hash = hashlib.md5(bytes("禾木AI", encoding="utf-8")) hash.update(bytes(user_pwd, encoding="utf-8")) user_pwd_hash = hash.hexdigest() # 4、执行语句 cur.execute(sql, (user_name, user_pwd_hash, user_email)) # 5、insert、update、delete必须显示提交 conn.commit() # 6、关闭资源 close_conn(conn, cur) # 7、消息提示 self.statusBar().showMessage('用户添加成功!') self.add_user_name.setText('') self.add_user_email.setText('') self.add_user_pwd.setText('') self.add_user_confirm_pwd.setText('') else: self.error_message.setText("两次密码不一致") self.add_user_pwd.setText('') self.add_user_confirm_pwd.setText('')
def export_day_operations(self): conn = get_conn() cur = conn.cursor() sql = "select book_name, client_name, type, day_from, day_to from dayoperations" cur.execute(sql) data = cur.fetchall() wb = Workbook("day_operations.xlsx") sheet1 = wb.add_worksheet() sheet1.write(0, 0, "书名") sheet1.write(0, 1, "客户") sheet1.write(0, 2, "类型") sheet1.write(0, 3, "开始时间") sheet1.write(0, 4, "结束时间") row_number = 1 for row in data: column_number = 0 for item in row: sheet1.write(row_number, column_number, str(item)) column_number += 1 row_number += 1 wb.close() self.statusBar().showMessage("数据导出成功!")
def editor_user(self): old_name = self.old_user_name.text() new_name = self.editor_user_name.text() new_email = self.editor_user_email.text() user_pwd = self.eidtor_user_pwd.text() user_confirm_pwd = self.editor_user_confirm_pwd.text() if user_pwd == user_confirm_pwd: # 数据库操作流程 # 1、获取连接 conn = get_conn() # 2、获取cursor cur = conn.cursor() if user_pwd == user_confirm_pwd: # 加盐 hash = hashlib.md5(bytes("禾木AI", encoding="utf-8")) hash.update(bytes(user_pwd, encoding="utf-8")) user_pwd_hash = hash.hexdigest() sql = "update users set user_name=%s, user_email=%s, user_pwd=%s where user_name=%s" # 4、执行语句 cur.execute(sql, (new_name, new_email, user_pwd_hash, old_name)) # 5、insert、update、delete必须显示提交 conn.commit() # 6、关闭资源 close_conn(conn, cur) self.statusBar().showMessage('用户修改成功!') self.user_groupBox.setEnabled(False) else: self.statusBar().showMessage('两次密码不一致!')
def editor_book(self): # 数据库操作流程 # 1、获取连接 conn = get_conn() # 2、获取cursor cur = conn.cursor() # 3、SQl语句 sql = "update book set book_name=%s, book_description=%s, book_code=%s,book_category=%s, " \ "book_author=%s, book_publisher=%s, book_price=%s where book_name=%s" book_name = self.editor_book_name.text() book_description = self.editor_book_desc.toPlainText() book_code = self.editor_book_code.text() book_category = self.editor_book_category.currentText() book_author = self.editor_book_author.currentText() book_publisher = self.editor_book_publisher.currentText() book_price = self.editor_book_price.text() book_old_name = self.editor_search_name.text() # 4、执行语句 cur.execute(sql, (book_name, book_description, book_code, book_category, book_author, book_publisher, book_price, book_old_name)) # 5、insert、update、delete必须显示提交 conn.commit() self.show_books() self.statusBar().showMessage('图书修改成功!')
def editor_client(self): old_client_id = self.old_client_id.text() client_name = self.editor_client_name.text() client_phone = self.editor_client_phone.text() client_id = self.editor_client_id.text() regex = re.compile(r"(^0\d{2,3}-\d{7,8}$)") if re.match(regex, client_phone): conn = get_conn() # 2、获取cursor cur = conn.cursor() # 3、SQl语句 sql = "update client set client_name=%s, client_phone=%s, client_id=%s " \ "where client_id=%s" # 4、执行语句 cur.execute(sql, (client_name, client_phone, client_id, old_client_id)) # 5、insert、update、delete必须显示提交 conn.commit() # 6、关闭资源 close_conn(conn, cur) self.statusBar().showMessage("修改成功!") self.show_client() else: self.statusBar().showMessage("电话格式位区号-电话")
def add_book(self): # 数据库操作流程 # 1、获取连接 conn = get_conn() # 2、获取cursor cur = conn.cursor() # 3、SQl语句 sql = "insert into book(book_name, book_description, book_code,book_category, " \ "book_author, book_publisher, book_price) values(%s, %s, %s, %s, %s, %s, %s)" book_name = self.add_book_name.text() book_description = self.add_book_desc.toPlainText() book_code = self.add_book_code.text() book_category = self.add_book_category.currentText() book_author = self.add_book_author.currentText() book_publisher = self.add_book_publisher.currentText() book_price = self.add_book_price.text() # 4、执行语句 cur.execute(sql, (book_name, book_description, book_code, book_category, book_author, book_publisher, book_price)) # 5、insert、update、delete必须显示提交 conn.commit() # 6、关闭资源 close_conn(conn, cur) self.add_book_name.setText('') self.add_book_desc.setPlainText('') self.add_book_code.setText('') self.add_book_category.setCurrentIndex(0) self.add_book_author.setCurrentIndex(0) self.add_book_publisher.setCurrentIndex(0) self.add_book_price.setText('') self.statusBar().showMessage('图书添加成功!') self.add_publisher_name.setText('') self.show_books()
def select_client(self): conn = get_conn() cur = conn.cursor() sql = "select client_name, client_phone, client_id from client where client_id = %s" old_client_id = self.old_client_id.text() cur.execute(sql, (old_client_id, )) data = cur.fetchone() if data: self.editor_client_name.setText(data[0]) self.editor_client_phone.setText(data[1]) self.editor_client_id.setText(data[2])
def show_publisher_combobox(self): conn = get_conn() cur = conn.cursor() sql = "select publisher_name from publisher" cur.execute(sql) data = cur.fetchall() if data: self.add_book_publisher.clear() self.editor_book_publisher.clear() for publisher in data: self.add_book_publisher.addItem(publisher[0]) self.editor_book_publisher.addItem(publisher[0])
def show_author_combobox(self): conn = get_conn() cur = conn.cursor() sql = "select author_name from author" cur.execute(sql) data = cur.fetchall() if data: self.add_book_author.clear() self.editor_book_author.clear() for author in data: self.add_book_author.addItem(author[0]) self.editor_book_author.addItem(author[0])
def show_category_combobox(self): conn = get_conn() cur = conn.cursor() sql = "select category_name from category" cur.execute(sql) data = cur.fetchall() if data: self.add_book_category.clear() self.editor_book_category.clear() for category in data: self.add_book_category.addItem(category[0]) self.editor_book_category.addItem(category[0])
def user_login(self): conn = get_conn() cur = conn.cursor() sql = "select * from users where user_name=%s and user_pwd=%s" old_user_name = self.old_user_name.text() editor_pwd = self.editor_pwd.text() pwd = self.md5(editor_pwd) cur.execute(sql, (old_user_name, pwd)) data = cur.fetchone() if data: self.user_groupBox.setEnabled(True) self.editor_user_name.setText(data[1]) self.editor_user_email.setText(data[3])
def handel_login(self): conn = get_conn() cur = conn.cursor() sql = "select * from users where user_name=%s and user_pwd=%s" user_name = self.username.text() pwd = self.pwd.text() pwd = self.md5(pwd) cur.execute(sql, (user_name, pwd)) data = cur.fetchone() if data: self.main_app = MainApp() self.close() self.main_app.show() else: self.error_message.setText("用户名或密码错误,重新输入")
def delete_book(self): # 数据库操作流程 # 1、获取连接 conn = get_conn() # 2、获取cursor cur = conn.cursor() sql = "delete from book where book_name = %s" book_name = self.editor_book_name.text() warning = QMessageBox.warning(self, '删除图书', '你确定要删除', QMessageBox.Yes | QMessageBox.No) if warning == QMessageBox.Yes: cur.execute(sql, (book_name, )) conn.commit() close_conn(conn, cur) self.show_books() self.statusBar().showMessage('图书成功删除')
def show_all_operations(self): conn = get_conn() cur = conn.cursor() sql = "select book_name, client_name, type, day_from, day_to from dayoperations" cur.execute(sql) data = cur.fetchall() if data: self.operation_table.setRowCount(0) self.operation_table.insertRow(0) for row, form in enumerate(data): for column, item in enumerate(form): self.operation_table.setItem(row, column, QTableWidgetItem(str(item))) column += 1 row_position = self.operation_table.rowCount() self.operation_table.insertRow(row_position)
def delete_client(self): old_client_id = self.old_client_id.text() warning = QMessageBox.warning(self, "删除客户", "你确定删除吗?", QMessageBox.Yes | QMessageBox.No) if warning == QMessageBox.Yes: conn = get_conn() # 2、获取cursor cur = conn.cursor() # 3、SQl语句 sql = "delete from client where client_id = %s" # 4、执行语句 cur.execute(sql, (old_client_id, )) # 5、insert、update、delete必须显示提交 conn.commit() close_conn(cur, conn) self.statusBar().showMessage("删除客户成功!") self.show_client()
def search_book(self): conn = get_conn() cur = conn.cursor() sql = "select * from book where book_name = %s" book_name = self.editor_search_name.text() cur.execute(sql, (book_name, )) data = cur.fetchone() if data: self.editor_book_name.setText(data[1]) self.editor_book_desc.setPlainText(data[2]) self.editor_book_category.setCurrentText(data[3]) self.editor_book_author.setCurrentText(data[4]) self.editor_book_publisher.setCurrentText(data[5]) self.editor_book_price.setText(str(data[6])) self.editor_book_code.setText(data[7]) else: self.statusBar().showMessage('没有这个书籍')
def show_client(self): conn = get_conn() cur = conn.cursor() sql = "select client_name, client_phone, client_id from client" cur.execute(sql) data = cur.fetchall() if data: self.client_table.setRowCount(0) self.client_table.insertRow(0) for row, form in enumerate(data): for column, item in enumerate(form): self.client_table.setItem(row, column, QTableWidgetItem(str(item))) column += 1 row_position = self.client_table.rowCount() self.client_table.insertRow(row_position)
def show_publisher(self): conn = get_conn() cur = conn.cursor() sql = "select publisher_name from publisher" cur.execute(sql) data = cur.fetchall() if data: self.publisher_table.setRowCount(0) self.publisher_table.insertRow(0) for row, form in enumerate(data): for column, item in enumerate(form): self.publisher_table.setItem(row, column, QTableWidgetItem(str(item))) column += 1 row_position = self.publisher_table.rowCount() self.publisher_table.insertRow(row_position)
def add_publisher(self): # 数据库操作流程 # 1、获取连接 conn = get_conn() # 2、获取cursor cur = conn.cursor() # 3、SQl语句 sql = "insert into publisher(publisher_name) values(%s)" publisher_name = self.add_publisher_name.text() # 4、执行语句 cur.execute(sql, (publisher_name, )) # 5、insert、update、delete必须显示提交 conn.commit() # 6、关闭资源 close_conn(conn, cur) # 7、消息提示 self.statusBar().showMessage('作者添加成功!') self.add_publisher_name.setText('') self.show_publisher()
def handel_day_operation(self): book_name = self.book_name.text() client_name = self.client_name.text() operate_type = self.tpye_combox.currentText() day_number = self.day_combox.currentIndex() + 1 today_date = datetime.date.today() to_day = today_date + datetime.timedelta(days=day_number) # 数据库操作 conn = get_conn() cur = conn.cursor() sql = "insert into dayoperations(book_name, client_name, type, days, day_from, day_to)" \ "values(%s, %s, %s, %s, %s, %s)" cur.execute(sql, (book_name, client_name, operate_type, day_number, today_date, to_day)) conn.commit() close_conn(conn, cur) self.statusBar().showMessage("操作添加成功!") self.show_all_operations()
def show_books(self): # 数据库操作流程 # 1、获取连接 conn = get_conn() # 2、获取cursor cur = conn.cursor() sql = "select book_code, book_name, book_description, book_category," \ " book_author, book_publisher, book_price from book" cur.execute(sql) data = cur.fetchall() self.book_table.setRowCount(0) self.book_table.insertRow(0) for row, form in enumerate(data): for column, item in enumerate(form): self.book_table.setItem(row, column, QTableWidgetItem(str(item))) column += 1 row_postion = self.book_table.rowCount() self.book_table.insertRow(row_postion)
def add_client(self): # 数据库操作流程 # 1、获取连接 conn = get_conn() # 2、获取cursor cur = conn.cursor() # 3、SQl语句 sql = "insert into client(client_name, client_phone, client_id) values(%s, %s, %s)" client_name = self.add_client_name.text() client_phone = self.add_client_phone.text() client_id = self.add_client_id.text() # 4、执行语句 cur.execute(sql, (client_name, client_phone, client_id)) # 5、insert、update、delete必须显示提交 conn.commit() # 6、关闭资源 close_conn(conn, cur) # 7、消息提示 self.statusBar().showMessage('客户添加成功!') self.add_client_name.setText('') self.add_client_phone.setText('') self.add_client_id.setText('') self.show_client()