Problem with storing data in sqlite with Python/Flask-SQLAlchemy code

Question

I'm trying to store a user and password in a table I created with sqlite db. It seems that pwd column doesn't exist, while if I check the table in sqlite it does contain the column.

The error returns the following:

sqlalchemy.exc.OperationalError

OperationalError: (sqlite3.OperationalError) table user has no column named pwd
[SQL: INSERT INTO user (id, username, pwd) VALUES (?, ?, ?)]
[parameters: (1, 'ita_itf', '$2b$12$j5NPKCgv8DsFnOJtHE2xjOyXMoGSFCz89Gf/Oy2Hs.FfEx/0BuOY2')]
(Background on this error at: http://sqlalche.me/e/e3q8)

ERROR DETAILS:

File "/home/itad/DICP_evo_dev/venv/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute


    def do_executemany(self, cursor, statement, parameters, context=None):

        cursor.executemany(statement, parameters)

    def do_execute(self, cursor, statement, parameters, context=None):

        cursor.execute(statement, parameters)

    def do_execute_no_params(self, cursor, statement, context=None):

        cursor.execute(statement)

    def is_disconnect(self, e, connection, cursor):

this is the User class I created:

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///data-users.sqlite'
bcrypt = Bcrypt(app)
db = SQLAlchemy(app)

class User(db.Model):

    __tablename__ = "user"
    id = db.Column(db.Integer(), primary_key = True, autoincrement=True)
    username = db.Column(db.String(64), unique = True)
    pwd = db.Column(db.String(128))

    def __init__(self,id,username,pwd):
        self.id=id
        self.username = username
        self.pwd = bcrypt.generate_password_hash(pwd)

This is the code where I manually insert the password with a python GUI in my app:

 from store_user_db import User, db
        
        DICP_FTP_DESTINATION_PSW=self.submit_pwd()

        user = User(id=001,username="ita_itf", pwd=DICP_FTP_DESTINATION_PSW)
        db.session.add(user)
        db.session.commit()

Here I check the user table if exist and the schema, I also tried to run sqlite>SELECT * FROM user but it doesn't return any result

itad@itad-virtual-machine:~/DICP_evo_dev$ sqlite3 data-users.sqlite
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> .schema user
CREATE TABLE user (
    id INTEGER NOT NULL, 
    username VARCHAR, 
    pwd VARCHAR, 
    PRIMARY KEY (id), 
    UNIQUE (username)
);

Maybe it's easier if I show you the screenshots, the commented part is the one I used to create the table, then I commented it because you only need to create it once.

PYTHON CODE

SQLITE FROM COMMAND PROMPT

I didn't put that in the code but I previously did create the db and the table with the function table() then create_all() etc.. Then I comment that out. indeed if I type sqlite> .schema user, it shows the table, should be there in the db no?

score 0 · Answer 1 · answered Sep 03 '20 at 14:08

0

i think issue is with the declaration of user variable 2 times in models

class User(db.Model):

    __tablename__ = "user"
    id = db.Column(db.Integer(), primary_key = True, autoincrement=True)
    username = db.Column(db.String(64), unique = True)
    pwd = db.Column(db.String(128))

    def __init__(self,id,username,pwd):
        db.Model.__init__(self, id=id, username=username, pwd=pwd)

this will solve the problem.

answered Sep 03 '20 at 14:08

sahasrara62

10,069
3
29
44

Nope, is not that, same error. I'm afraid the code is ok but somehow I'm messing up with loading the db or some sqlite configuration, I dont know. – marcello86 Sep 03 '20 at 14:30
Do you believe me when I say that I have been stuck on this issue for 2 consecutive weeks? – marcello86 Sep 03 '20 at 14:33
well i can belive, btw what else you can do is create databases using the `db.create_all()` in flask shell, also maybe change your python version from 2.7 to 3.7 or 3.8 , that would help – sahasrara62 Sep 03 '20 at 14:39
No my gosh I wont do any migration this is a huge application for my work!!! It would be an even bigger mess!!! – marcello86 Sep 03 '20 at 14:50
I didn't put that in the code but I previously did create the db and the table with the function table() then create_all() etc.. Then I comment that out. indeed if I type sqlite> .schema user, it shows the table, should be there in the db no? – marcello86 Sep 03 '20 at 14:50
yes it will show, or you can do a little hack, from your model generate sql script and then run it in db shell, this way there is symc with python model and table, plus you can see database content using sqlite3 visualization tool to see if data is begin inserted or not, also from flask shell try to add data and then see if it added or not – sahasrara62 Sep 03 '20 at 15:13
yes so I ran this https://i.stack.imgur.com/X7P6v.png Now? I cannot store the password manually in the sqlite shell command prompt because the application must encrypt the password through python. The python code has to do that – marcello86 Sep 03 '20 at 15:16
yes application will handle it, as you are adding the password through shell in same way as from application `userR=user(id=1, username='1',pwd='wweq') db.session.add(userR) db.session.commit()` also may be you need to [change the datatype](https://stackoverflow.com/questions/5881169/what-column-type-length-should-i-use-for-storing-a-bcrypt-hashed-password-in-a-d) of `pwd` from `varchar` to `largebinary` – sahasrara62 Sep 03 '20 at 15:20
yes writing the pw from my application and pressing a button. the it passes the pwd – marcello86 Sep 03 '20 at 15:30
Is possible that I have messd up the db somehow or is not being re-initialized? Maybe I can try to drop the table and the db then remake it again? How do I drop the table from sqlite command prompt? – marcello86 Sep 04 '20 at 09:04
instead of droping db, why not create a testing db and then do all the testing there – sahasrara62 Sep 04 '20 at 17:24

score 0 · Answer 2 · answered Sep 03 '20 at 14:34

0

Seems like your table is not being created, try running 'python' (to run REPL), import the class that has the db object and run 'db.create_all()' (This will create tables in DB according to your models)

answered Sep 03 '20 at 14:34

Richard Fogaca Nienkotter

135
1
3
12

This means the table is created or not in the db? https://i.stack.imgur.com/X7P6v.png – marcello86 Sep 03 '20 at 15:13
Yes the table is created in your DB. It takes the tables and attributes you defined in your models file and creates it in your database, – Richard Fogaca Nienkotter Sep 03 '20 at 18:37
Then how is it possible that when I try to store the password tells me that pwd column in the table doesn't exist? Is possible that I have messd up the db somehow or is not being re-initialized? Maybe I can try to drop the table and the db then remake it again? – marcello86 Sep 04 '20 at 09:03
How do I drop the table from sqlite command prompt? – marcello86 Sep 04 '20 at 09:04

score 0 · Answer 3 · answered Sep 04 '20 at 09:41

Looking at the code where I create the table and then I create the User class, there might be some issue because in the User class I'm using User(db.Model) while when I create the table just before I'm using engine and Metadata() function? Indeed when I create the table there's no reference to db.

Screenshot below, code on the left:

https://i.stack.imgur.com/LMwhO.jpg

Problem with storing data in sqlite with Python/Flask-SQLAlchemy code

3 Answers3