sqlite3.OperationalError: table users has no column named username

Question

Hello I cant figure out the solution for that problem. I searched on google but got no answer. I'm new to db. so maybe its a dumb question :).

class Users:
    def __init__(self, tablename="users", userId="userId", password="password", username="username"):
        self.__tablename = tablename
        self.__userId = userId
        self.__password = password
        self.__username = username
        conn = sqlite3.connect('test.db')
        print("open database successfully")
        str = "CREATE TABLE IF NOT EXISTS " +  tablename + "(" + self.__userId + " " + " INTEGER PRIMARY KEY AUTOINCREMENT ,"
        str += " " + self.__password +  "TEXT   NOT NULL ,"
        str += " " + self.__username +  "TEXT   NOT NULL )"
        conn.execute(str)
        print("Table created successfully")
        conn.commit()
        conn.close()
    def insert_user(self, username, password):

        conn = sqlite3.connect('test.db')
        str_insert = "INSERT INTO " + self.__tablename + " (" + self.__username +"," + self.__password + ") VALUES (" +  "'" +username + "'" + "," + "'" +password +"'" +");"
        print(str_insert)
        conn.execute(str_insert)
        conn.commit()
        conn.close()
        print("Record created successfully")


u = Users()
u.insert_user("[email protected]", "123456")

Answer 1

Problem is here :

str = "CREATE TABLE IF NOT EXISTS " +  tablename + "(" + self.__userId + " " + " INTEGER PRIMARY KEY AUTOINCREMENT ,"
str += " " + self.__password +  "TEXT   NOT NULL ,"
str += " " + self.__username +  "TEXT   NOT NULL )"

Indeed, concatenate "username" and "TEXT NOT NULL" will give "usernameTEXT NOT NULL", without space between "username" and "TEXT"

You can see this if you execute the following query :

conn.execute("SELECT * from sqlite_master").fetchall()

This gives you :

[
    ('table', 'users', 'users', 2, 'CREATE TABLE users(userId  INTEGER PRIMARY KEY AUTOINCREMENT , passwordTEXT   NOT NULL , usernameTEXT   NOT NULL )'), 
    ('table', 'sqlite_sequence', 'sqlite_sequence', 3, 'CREATE TABLE sqlite_sequence(name,seq)')
]

Which is clearly a mess.

When you work with sqlite3, a good practice is to use the following syntax, which will avoid you a lot of space mistakes :

conn.execute(
    f"CREATE TABLE IF NOT EXISTS {tablename}"
    f"({self.__userId} INTEGER PRIMARY KEY AUTOINCREMENT,"
    f"{self.__password} TEXT NOT NULL, {self.__username} TEXT NOT NULL)"
)