需要自动增加值，但为什么？

Question

我正在尝试使用python将一些数据插入MySQL表。根据我的理解，当您将数据插入到具有自动递增列的表中时，不需要给出所述列的值。但是，我遇到了一个问题，我没有足够的值来插入数据。我做错了什么？我看过这个网站，w3schools，MySQL文档，看起来我做的一切都是对的。我收到错误：mysql.connector.errors.DataError: 1136 (21S01): Column count doesn't match value count at row 1

...
HAND_HISTORY_DB_TABLES['Hands'] = (
    "CREATE TABLE `Hands` ("
    "   `HandID` int NOT NULL AUTO_INCREMENT PRIMARY KEY,"
    "   `HandNumber` int UNIQUE,"
    "   `HandDate` date NOT NULL,"
    "   `HandResults` float"
    ") ENGINE=InnoDB")
...
            hand_insert = "INSERT INTO Hands "\
                          "SELECT %s, %s, %s " \
                          "FROM Hands " \
                          "WHERE NOT EXISTS " \
                          "(SELECT NULL, HandNumber, HandDate, HandResults " \
                          "FROM Hands " \
                          "WHERE HandNumber = %s AND HandDate = %s AND HandResults = %s)"
            cursor.execute(hand_insert, (self.hand_number, self.date, self.result,
                                         self.hand_number, self.date, self.result))
...

恢复原始问题，如下答案解决问题

Answer 1

注释中建议的解决方案是显式地写入目标表的列名。因此：

...
HAND_HISTORY_DB_TABLES['Hands'] = (
    "CREATE TABLE `Hands` ("
    "   `HandID` int NOT NULL AUTO_INCREMENT PRIMARY KEY,"
    "   `HandNumber` int UNIQUE,"
    "   `HandDate` date NOT NULL,"
    "   `HandResults` float"
    ") ENGINE=InnoDB")
...
            hand_insert = "INSERT INTO Hands (HandNumber, HandDate, HandResults) "\
                          "SELECT %s, %s, %s " \
                          "FROM Hands " \
                          "WHERE NOT EXISTS " \
                          "(SELECT NULL, HandNumber, HandDate, HandResults " \
                          "FROM Hands " \
                          "WHERE HandNumber = %s AND HandDate = %s AND HandResults = %s)"
            cursor.execute(hand_insert, (self.hand_number, self.date, self.result,
                                         self.hand_number, self.date, self.result))
...

上一次尝试失败，因为Hands表有4列HandID, HandNumber, HandDate, HandResults。使用INSERT INTO hands SELECT ...，您直接告诉查询插入表Hands中的所有4列，但是在SELECT ..语句之后的INSERT中选择的字段只有3个