mysql操作
首先先创建一个login表并插入数据
import pymysql
#打开数据库连接
conn = pymysql.connect(host="localhost",user = "root",passwd = "chuge325",db = "test")
#获取游标
cursor=conn.cursor()
print(cursor)
#创建user表
cursor.execute('drop table if exists login')
sql1= """CREATE TABLE login(
name VARCHAR(128) NOT NULL,
passwd VARCHAR(256) NOT NULL,
PRIMARY KEY(name)
) ; """
sql2="insert into login values(%s,%s)"
cursor.execute(sql1)
# inser one way 1
cursor.execute("insert into login values('admin','123456')")
# insert one way 2
cursor.execute(sql2,('admin2','123456'))
# insert some way3
cursor.executemany(sql2,[('admin3','123456'),('admin4','123456')])
cursor.close()#先关闭游标
conn.commit()
conn.close()#再关闭数据库连接
print('创建数据表成功')
查询插入内容
import pymysql
#打开数据库连接
conn = pymysql.connect(host="localhost",user = "root",passwd = "chuge325",db = "test")
#获取游标
cur=conn.cursor()
cur.execute("select * from login;")
while 1:
res=cur.fetchone()
if res is None:
#表示已经取完结果集
break
print (res)
cur.close()
conn.commit()
conn.close()
print('sql执行成功')
界面设计
from src.main import Main
import sys
from PyQt5.QtWidgets import *
# 这里自己根据main的位置进行修改
from src.test_learn_ytc.src.main import Main
import pymysql
print(sys.path)
class Login(QWidget):
def __init__(self, parent=None):
#super(Login, self).__init__(parent)
super().__init__()
self.setWindowTitle('用户登录')
#self.setFixedSize(300, 150)
self.center()
self.main=Main()
self.account = QLineEdit()
self.password = QLineEdit()
self.password.setEchoMode(2)
self.button_login = QPushButton('登录')
self.button_login.clicked.connect(self.login)
self.button_register = QPushButton('注册')
self.button_cancel=QPushButton('清空')
self.button_cancel.clicked.connect(self.cancel)
# 布局
grid_layout=QGridLayout()
v_layout=QVBoxLayout()
h_layout=QHBoxLayout()
grid_layout.addWidget(QLabel('账号'),0,0)
grid_layout.addWidget(self.account,0,1)
grid_layout.addWidget(QLabel('密码'),1,0)
grid_layout.addWidget( self.password,1,1)
h_layout.addWidget(self.button_login)
h_layout.addWidget(self.button_register)
h_layout.addWidget(self.button_cancel)
v_layout.addLayout(grid_layout)
v_layout.addLayout(h_layout)
self.setLayout(v_layout)
#窗体功能
def center(self):
screen = QDesktopWidget().screenGeometry()
size = self.geometry()
self.move((screen.width() - size.width()) / 2,
(screen.height() - size.height()) / 2)
# 按钮功能
def login(self):
obj=MysqlSearch()
result=obj.get_userinfo()
name=self.account.text()
pwd=self.password.text()
ulist=[]
plist=[]
for item in result:
ulist.append(item['name'])
plist.append(item['passwd'])
for i in range(len(ulist)):
if name==ulist[i] and pwd==plist[i]:
self.main.show()
self.hide()
break
else:
QMessageBox.information(self,'提示','用户密码错误')
def cancel(self):
# 清空用户输入的用户名和密码
self.account.setText("")
self.password.setText("")
class MysqlSearch(object):
# 数据库操作功能
def __init__(self) :
self.get_conn()
# 获取连接
def get_conn(self):
try:
self.conn = pymysql.connect(host="localhost",user = "root",passwd = "passwd",db = "test")
except pymysql.Error as e:
print('Error: %s' % e)
return None
def close_conn(self):
try:
if self.conn:
self.conn.close()
except pymysql.Error as e:
print('Error: %s' % e)
return None
def get_userinfo(self):
sql = 'SELECT * FROM login'
# 使用cursor()方法获取操作游标
cursor = self.conn.cursor()
cursor.execute(sql)
# 使用fetchall()方法获取全部数据
result=cursor.fetchall()
result=[dict(zip([k[0] for k in cursor.description],row)) for row in result]
cursor.close()
self.close_conn()
return result
if __name__ == '__main__':
app = QApplication(sys.argv)
login = Login()
login.show()
sys.exit(app.exec_())
from PyQt5.QtWidgets import QWidget,QDesktopWidget,QApplication
import sys
class Main(QWidget):
def __init__(self,parent=None) :
super(Main,self).__init__(parent)
self.setFixedSize(500,500)
self.center()
def center(self):
screen = QDesktopWidget().screenGeometry()
size = self.geometry()
self.move((screen.width() - size.width()) / 2,
(screen.height() - size.height()) / 2)
登入login效果