说明:代码是从项目源代码中抽取,需要进行适应性修改,不保证可运行。
1 读取MySQL数据库
需要提前安装 pymysql 库。
简单版本:
# 需安装 pymysql 库
import pymysql
import sys
try:
con = pymysql.connect(host='localhost', port='3306',
user='kitty', password='123456',
database='user_db')
except Exception:
pass
# QMessageBox.information(None, '错误', 'MySQL数据库连接错误')
# con 就代表数据库的链接
cur = con.cursor()
sql = "select * from t_user where userid = %s and userpwd = %s"
data = ("Kitty","123")
cur.execute(sql, data)
result = cur.fetchall()
cur.close() # 关闭游标
con.close() # 关闭数据库链接
print(result)
包装成类:
import pymysql
class MyDB:
""" 简单包装 """
def __init__(self):
self.con = None
self.initCon()
def initCon(self):
try:
con = pymysql.connect(host='localhost', port='3306',
user='kitty', password='123456',
database='user_db')
self.con = con
except Exception:
pass
# QMessageBox.information(None, '错误', 'MySQL数据库连接错误')
def query(self, sql, data=None):
"""
查询数据,data只能是单个元组,不能是元组的列表
使用 %s 占位符,防止SQL注入
:param sql: 带有%s占位符的SQL语句 "SELECT * FROM user WHERE uid=%s and name=%s and pwd=%s "
:param data: (uid,name,pwd...)
:return:
"""
if not self.con:
self.initCon()
con = self.con
cur = con.cursor()
cur.execute(sql, data)
l = cur.fetchall()
cur.close()
return l
def execute(self, sql, data):
"""
新增/删除/更新数据,注意:data必须是list!里面可包裹1到多个元组
此函数不返回数据。支持insert,delete,update
insert: insert into user(name,pwd) values(%s,%s)
delete: update user set delflag=1 where id=%s
update: update user set name=%s and pwd=%s where id = %s
:param sql: 带有%s占位符的SQL语句
:param data: [('tom','123456'),('jerry','123456')]
:return:
"""
if not self.con:
self.initCon()
con = self.con
try:
cur = con.cursor()
cur.executemany(sql, data)
con.commit()
# return cur.lastrowid
except Exception:
pass
# con.rollback()
# QMessageBox.information(None, '错误', '操作出现错误')
# traceback.print_exc() # 可打印错误行号,异常的详细内容
finally:
cur.close()
def insertSglRtnId(self, sql, data):
"""
插入单条数据,并返回新插入记录id
:param sql: 带有%s占位符的SQL语句
:param data: 数据元组
:return: lastrowid
"""
if not self.con:
self.initCon()
con = self.con
try:
cur = con.cursor()
cur.execute(sql, data)
con.commit()
return cur.lastrowid
except Exception:
pass
# traceback.print_exc() # 可打印错误行号,异常的详细内容
finally:
cur.close()
def cleartbl(self, tablename):
"""
清空表内容
:return:
"""
if not self.con:
self.initCon()
con = self.con
cur = con.cursor()
cur.execute("delete from " + tablename + " where 1=1 ")
con.commit()
cur.close()
def close(self):
"""
关闭数据库链接,必须要调用
:return:
"""
if self.con:
self.con.close()
if __name__ == "__main__":
pass
使用方法:
sql = "select * from t_user where username = %s usersex = %s"
data = ('Kitty', 'male')
db = MyDB()
if not db:
records = db.query(sql, data)
db.close()
print(records)
2 填充到Qt的表格控件中
def fillTbl(self):
# ...省略读取数据库代码
# records 是从数据库中获取的数据list
# 填充表格控件
header = ['ID', '姓名', '密码', '性别', '年龄', '排序号'] # 设置表头
cols = len(header) # 列数
rows = len(records) # 行数
table = self.tbl # 表格控件
table.setColumnCount(cols)
table.setRowCount(rows)
table.horizontalHeader().setDefaultAlignment(Qt.AlignLeft | Qt.AlignVCenter) # 左右居左 上下居中
table.setHorizontalHeaderLabels(header)
# 遍历records 逐项填充每个单元格
for i in range(0, rows):
for j in range(0, cols):
if not records[i][j]:
item = QTableWidgetItem(str(""))
else:
item = QTableWidgetItem(str(records[i][j]))
item.setToolTip(str(records[i][j])) # 鼠标悬停提示气泡,方便显示过长内容
table.setItem(i, j, item) # 设置i行j列的内容