No such column sqlite ошибка

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

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

Выходит это потому что у тебя «username» в Insert не обёрнут кавычками да и добавление юзера тоже не верно у тебя в таблице «INTEGER, INTEGER, TEXT», а ты заносишь как «INTEGER, TEXT, INTEGER» Вот норм код:

cursor.execute(f"SELECT user_id FROM lun_db WHERE user_id = '{userid}'")
if cursor.fetchone() is None:
    cursor.execute("INSERT INTO lun_db VALUES(?, ?, ?);", (userid,  0, f"{username}"))
    connect.commit()

UPDATE:
Так же и в UPDATE

cursor.execute('UPDATE lun_db SET user_name = ? WHERE user_id = ?', (f"{username}", userid))

Solution 1

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.

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)

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.

Solution 2

Actually it’s a small mistake,
you have to edit it like this:

c.execute("INSERT INTO People VALUES('userName', 'password', 'confirmPassword', 'firstName','lastName', 'companyName', 'email', phoneNumber,'addressLine1', 'addressLine2', 'addressLine3', zipCode, 'province', 'country', 'regDate')")

I hope you understood

Solution 3

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.

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.

See section 11.13.5.1 in the documentation (https://docs.python.org/2/library/sqlite3.html):

Python type None = SQLite type NULL

Related videos on Youtube

I have a table (trackedinfo) inside my database that has the following columns (columns obtained by running PRAGMA table_info(trackedinfo);)

The problem is that even though the column sendok exists, when running a query on the database with that field, it throws an error.

Example queries:

SELECT * FROM trackedinfo WHERE sendok IS NULL;
SELECT sendok FROM trackedinfo;

Error:

SQLITE_ERROR: SQL error or missing database (no such column: sendok)

But, if I run a query selecting all of the fields, it brings me the info regarding sendok:

Here is the CREATE command of the database:

CREATE TABLE trackedinfo
(
    id INTEGER PRIMARY KEY,
    date_time_start TEXT,
    date_time_end TEXT,
    tracked_name TEXT,
    tracked_origin TEXT,
    tracked_maker TEXT,
    tracked_version TEXT,
    tracked_type TEXT,
    sendok TEXT,
    tracked_id TEXT
);

It also happens with the column tracked_id

Info that I got by executing .schema trackedinfo

CREATE TABLE IF NOT EXISTS "trackedinfo" ("id" INTEGER PRIMARY KEY, "date_time_start" TEXT, "date_time_end" TEXT, "tracked_name" TEXT, "tracked_origin" TEXT, "tracked_maker" TEXT, "tracked_version" TEXT, "tracked_type" TEXT, "sendok " TEXT, "tracked_id " TEXT);