时间戳

首页 > 解决方案 > 不正确的整数值 mySQL

python - 不正确的整数值 mySQL

问题描述

我收到一个错误，我为 userID_fk 和目标使用了不正确的整数值。对于具有整数作为数据类型的值，如果将其更改为 text 或 varchar，则会出现错误，它将表明已创建站点并且 siteID 将增加，但不会包含其他数据。我希望用户输入他们的用户名，使其与其用户 ID 匹配，并通过 python 和 Tkinter 插入到 userID_fk 中。

以下是我的用户和网站表的结构

用户：

 CREATE TABLE `users` (
 `userID` int(255) NOT NULL AUTO_INCREMENT,
 `userName` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
 `userPassword` varchar(225) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
 `Name` varchar(255) NOT NULL,
 `phoneNum` text NOT NULL,
 `email` varchar(230) NOT NULL,
 `region` text NOT NULL,
 `accessLevel` int(10) NOT NULL,
 PRIMARY KEY (`userID`)
) ENGINE=InnoDB AUTO_INCREMENT=10002 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

网站：

CREATE TABLE `sites` (
 `siteID` int(225) NOT NULL AUTO_INCREMENT,
 `siteName` text CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
 `userID_fk` int(255) NOT NULL,
 `region` text NOT NULL,
 `risklevel` text NOT NULL,
 `siteType` text NOT NULL,
 `target` int(225) NOT NULL,
 PRIMARY KEY (`siteID`),
 KEY `userID_fk` (`userID_fk`),
 CONSTRAINT `sites_ibfk_1` FOREIGN KEY (`userID_fk`) REFERENCES `users` (`userID`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

将站点插入站点表的 Python 代码：

def register_site():
    sitename_info = sitename2.get()
    username2_info = username2.get()
    region_info = region.get()
    risklevel_info = risklevel.get()
    sitetype_info = sitetype.get()
    targetpercent_info = targetpercent.get()
    # Sql code for writing the data that was written in the regsitering page. 
    cursor = cnn.cursor()
    sitequery = "INSERT INTO `sites`(`siteID`, `siteName`, `userID_fk`, `region`, `risklevel`, `siteType`, `target`) VALUES (NULL,%s,%s,%s,%s,%s,%s)"
    sitequery_vals = (sitename_info, username2_info, region_info, risklevel_info, sitetype_info, targetpercent_info)
    cursor.execute(sitequery, sitequery_vals)
    cnn.commit()
    cursor.close()
    cnn.close()
    # removes the values in the entrys once the user selects that the registration was successful
    sitename2_entry.delete(0, END)
    region_entry.delete(0, END)
    risklevel_entry.delete(0, END)
    sitetype_entry.delete(0, END)
    targetpercent_entry.delete(0, END)
    Label(screen10, text = "Site Created", fg = "green", font = ("calibri", 11)).pack()

标签： pythonmysqltkinter

解决方案

如果username2_info是userName，则需要userID从users表中获取：

sitequery = ("INSERT INTO `sites` (`siteName`, `userID_fk`, `region`, `risklevel`, `siteType`, `target`) "
             "SELECT %s, `userID`, %s, %s, %s, %s FROM `users` WHERE `userName` = %s")
sitequery_vals = (sitename_info, region_info, risklevel_info, sitetype_info, targetpercent_info, username2_info)
cursor.execute(sitequery, sitequery_vals)
cnn.commit()

推荐阅读