Python sqlite3.OperationalError：没有这样的列：
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21142531/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sqlite3.OperationalError: no such column:
提问by Josh Kerbel
This is a very basic question and I know there are security issues with this code and it should be using parameterized entries among other issues - it is a work in progress. I am attempting to set build a user registration module for a project. I have set up a table with with the first column serving as an ID with a primary key constraint but when I run the code, I get the following error and am not sure why - (if it relates to the p_ID column):
这是一个非常基本的问题，我知道此代码存在安全问题，它应该在其他问题中使用参数化条目 - 这是一项正在进行的工作。我正在尝试为项目设置构建用户注册模块。我已经设置了一个表，其中第一列作为带有主键约束的 ID，但是当我运行代码时，我收到以下错误并且不知道为什么 - （如果它与 p_ID 列有关）：
Traceback (most recent call last):
File "user.py", line 72, in <module>
userSignUp()
File "user.py", line 68, in userSignUp
c.execute("INSERT INTO People VALUES(userName, password, confirmPassword, firstName,lastName, companyName, email, phoneNumber,addressLine1, addressLine2, addressLine3, zipCode, province, country, regDate)")
sqlite3.OperationalError: no such column: userName
The code is:
代码是：
import sqlite3
import datetime
path = "/Users/workhorse/thinkful/"
db = "apartment.db"
def checkAndCreateDB():
#not checking for some reason
#fullPath = os.path.join(path, db)
#if os.path.exists(fullPath):
# print "Database Exists"
#else:
connection = sqlite3.connect(db)
print "Creating database"
createUserRegTable()
def createUserRegTable():
with sqlite3.connect(db) as connection:
c = connection.cursor()
c.execute("""CREATE TABLE People
(p_ID INTEGER PRIMARY KEY AUTOINCREMENT,
userName TEXT NOT NULL UNIQUE,
password TEXT NOT NULL,
confirmPassword TEXT NOT NULL,
firstName TEXT NOT NULL,
lastName TEXT NOT NULL,
companyName TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
phoneNumber TEXT NOT NULL,
addressLine1 TEXT NOT NULL,
addressLine2 TEXT,
addressLine3 TEXT,
zipCode TEXT NOT NULL,
province TEXT NOT NULL,
country TEXT NOT NULL,
regDate DATE NOT NULL)
""")
print "table made"
def userSignIn():
pass
def userSignUp():
userName = raw_input("Enter a user name: ")
password = raw_input("Enter a password: ")
confirmPassword = raw_input("Confirm Your Password: ")
firstName = raw_input("Enter your first name: ")
lastName = raw_input("Enter your last name: ")
companyName = raw_input("Enter your company name: ")
email = raw_input("Enter your email: ")
phoneNumber = raw_input("Enter your phone number: ")
addressLine1 = raw_input("Enter your address: ")
addressLine2 = raw_input("Enter second line of your address (Not Required): ")
addressLine3 = raw_input("Enter third line of your address (Not Required): ")
zipCode = raw_input("Enter your zip code: ")
province = raw_input("Enter your state or province: ")
country = raw_input("Enter your country: ")
regDate = datetime.date.today()
print regDate
#userInfo = (userName, password, confirmPassword, firstName,lastName, companyName, email, phoneNumber,addressLine1,
#addressLine2, addressLine3, zipCode, province, country, regDate)
with sqlite3.connect(db) as connection:
c = connection.cursor()
c.execute("INSERT INTO People VALUES(userName, password, confirmPassword, firstName,lastName, companyName, email, phoneNumber,addressLine1, addressLine2, addressLine3, zipCode, province, country, regDate)")
checkAndCreateDB()
userSignUp()
Much thanks
非常感谢
采纳答案by Martijn Pieters
If you want to insert Python values into a SQL database, just naming the Python variables in the SQL statement is not enough. The SQL database instead thinks you wanted to insert values taken from the table or another query instead.
如果要将 Python 值插入 SQL 数据库，仅在 SQL 语句中命名 Python 变量是不够的。相反，SQL 数据库认为您想要插入从表或其他查询中获取的值。
Use SQL parametersinstead, and pass in the actual values:
改用SQL 参数，并传入实际值：
params = (userName, password, confirmPassword, firstName, lastName,
companyName, email, phoneNumber, addressLine1, addressLine2,
addressLine3, zipCode, province, country, regDate)
c.execute("INSERT INTO People VALUES (NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", params)
The NULLvalue is for the p_IDprimary key column; the alternative is to name all the columns you want to insert values for, or pass in Noneas the value for an additional parameter.
该NULL值用于p_ID主键列；另一种方法是命名要为其插入值的所有列，或None作为附加参数的值传入。
回答by J.Boss
Martijn's answer is great but works if you know a certain value is going to be NULL. But if any variable could be NULL, I used a slightly different implementation.
Martijn 的回答很好，但如果您知道某个值将是 NULL，则它会起作用。但是如果任何变量可能是 NULL，我使用了一个稍微不同的实现。
Set any variable to None. If you browse your db the value will be null, and it will display as None in your python console as they are equivalent.
将任何变量设置为无。如果您浏览您的数据库，该值将为空，并且它会在您的 Python 控制台中显示为 None，因为它们是等效的。
See section 11.13.5.1 in the documentation (https://docs.python.org/2/library/sqlite3.html):
请参阅文档中的第 11.13.5.1 节（https://docs.python.org/2/library/sqlite3.html）：
Python type None = SQLite type NULL
Python 类型无 = SQLite 类型 NULL
回答by hygull
As I was not having any important data in db.sqlite3.
因为我在db.sqlite3 中没有任何重要数据。
I fixed my problem by deleting the db.sqlite3and running the following command.
我通过删除db.sqlite3并运行以下命令来解决我的问题。
python manage.py migrate
