时间戳

首页 > 解决方案 > 如何继续添加、检索和比较 MySQL 数据库中的数据？我正在尝试做一个登录平台

python - 如何继续添加、检索和比较 MySQL 数据库中的数据？我正在尝试做一个登录平台

问题描述

我正在尝试制作一个登录系统。

  1. 用户注册。
  2. 用户名和密码存储在数据库中
  3. 用户尝试登录
  4. 如果成功，代码给出“登录！” 信息。

这是代码：

import mysql.connector as msqltor

db = msqltor.connect(
    host='localhost',
    user='root',
    passwd='qwerty',
    database='test_db'
)

my_cursor = db.cursor()

def signup():
    username = input("Enter username: ")
    password = input("Enter password: ")

    Q2 = "INSERT INTO credentials(username, password) VALUES (%s, %s)", (username, password)
    my_cursor.execute(Q2)
    db.commit()


def login():
    username = input("Enter username: ")
    password = input("Enter password: ")
    credentials_tuple = (username, password)

    Q3 = "SELECT * FROM credentials where username=%s"
    my_cursor.execute(Q3, (username))

    if credentials_tuple == my_cursor:
        print("Logged in!")
    else:
        print("Error")

Q1 = '''CREATE TABLE credentials(
    username varchar(25),
    password varchar(25)
    )'''

message = '''Welcome to login: \n
1. Signup \n
2. Login \n
'''

user_input = int(input(message))

if user_input == 1:
    signup()
elif user_input == 2:
    login()

# my_cursor.execute(Q1)

我收到此错误消息：

  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/mysql/connector/connection_cext.py", line 486, in cmd_query
    query = query.encode('utf-8')
AttributeError: 'tuple' object has no attribute 'encode'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "login.py", line 47, in <module>
    signup()
  File "login.py", line 17, in signup
    my_cursor.execute(Q2)
  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/mysql/connector/cursor_cext.py", line 266, in execute
    raw_as_string=self._raw_as_string)
  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/mysql/connector/connection_cext.py", line 499, in cmd_query
    errno=2055, values=(addr, 'Connection not available.'))
mysql.connector.errors.OperationalError: 2055: Lost connection to MySQL server at 'localhost:3306', system error: Connection not available.

查看错误，似乎没有连接到 MySQL 数据库。但是，我认为这不是问题，因为我已确保在代码开头进行连接。

标签： pythonmysql

解决方案

这些行是问题所在：

Q2 = "INSERT INTO credentials(username, password) VALUES (%s, %s)", (username, password)
my_cursor.execute(Q2)

您想像这样将查询和值传递给游标的execute方法

Q2 = "INSERT INTO credentials(username, password) VALUES (%s, %s)"
my_cursor.execute(Q2, (username, password))

你可以这样做：

Q2 = "INSERT INTO credentials(username, password) VALUES (%s, %s)", (username, password)
my_cursor.execute(*Q2)

但它并不常见，所以我会避免它。

发生的事情是

Q2 = "INSERT INTO credentials(username, password) VALUES (%s, %s)", (username, password)

创建一个tuple, 并将mycursor.execute此元组视为它的第一个参数，而不是将其拆分为查询和值部分，这会导致

AttributeError: 'tuple' object has no attribute 'encode'

在登录功能中，这段代码有问题：

Q3 = "SELECT * FROM credentials where username=%s"
my_cursor.execute(Q3, (username))

if credentials_tuple == my_cursor:

传递给的 values 参数mycursor.execute必须是 a tuple，如下所示：

my_cursor.execute(Q3, (username,))  # <- note the comma

然后您必须获取执行结果以进行比较：

if credentials_tuple == my_cursor.fetchone():

推荐阅读