21

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

Improve this question

4 Answers 4

Reset to default
40

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

As stated, the alternative is to use INSERT INTO People (userName, password, confirmPassword, <all column names you want to insert values for) VALUES (?, ?, <as many question marks as you have values for>). But that gets a bit verbose. Using NULL to have SQLite insert the default value instead is easier.
@MartijnPieters is there to make <as many question marks as you have values for> a variable , for example maybe from len(params) ?
@taiko of course, that’s just string formatting; ','.join(['?'] * len(params) and interpolating the result into your query string.
@MartijnPieters :) aaah of course, can't see the wood for the trees. Thank you very much
@Simao using NULL means sqlite will autoimcrement. See the SQLite documentation on auto incrementing: If no ROWID is specified on the insert, or if the specified ROWID has a value of NULL, then an appropriate ROWID is created automatically.
|
5

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

Improve this answer

2 Comments

i like this method better than using '?' incase a column gets moved. however i was getting an issue doing this using a column with autoincrement giving me an error stating table has more columns than values i supplied.
to fix this i used c.execute("INSERT INTO table('col1', 'col2', 'col4') VALUES(?,?,?)", (var1, var2, var3))
1

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

Improve this answer

Comments

0

I actually found a quite simple way to fix this! Without using "?" as place holders or param key words... Simply make your string a "formated" string by passing in "f" before the quotes. However note this is not good practice, just a quick shortcut if your not publishing this application. This allows you to pass in your variable when placing "{}"

cur = connection.cursor()
        cur.execute(f"INSERT INTO bills VALUES ({bills})")
        connection.commit()
        connection.close()
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.