sqlite3.OperationalError:没有这样的列: [英] sqlite3.OperationalError: no such column:

问题描述

这是一个非常基本的问题，我知道此代码存在安全问题，它应该使用参数化条目以及其他问题 - 这是一项正在进行的工作.我正在尝试为项目设置构建用户注册模块.我已经设置了一个表，其中第一列作为带有主键约束的 ID，但是当我运行代码时，我收到以下错误并且不知道为什么 -(如果它与 p_ID 列有关):

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):

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

代码是:

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()

非常感谢

推荐答案

如果要将 Python 值插入到 SQL 数据库中，仅在 SQL 语句中命名 Python 变量是不够.SQL 数据库反而认为您想插入从表或其他查询中获取的值.

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.

改用 SQL 参数，并传入实际值:

Use SQL parameters instead, and pass in the actual values:

params = (userName, password, confirmPassword, firstName, lastName,
          companyName, email, phoneNumber, addressLine1, addressLine2, 
          addressLine3, zipCode, province, country, regDate)

c.execute("INSERT INTO People VALUES (NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", params)

NULL 值用于 p_ID 主键列；另一种方法是命名您要为其插入值的所有列，或传入 None 作为附加参数的值.

The NULL value is for the p_ID primary key column; the alternative is to name all the columns you want to insert values for, or pass in None as the value for an additional parameter.

这篇关于sqlite3.OperationalError:没有这样的列:的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！