Flask sqlalchemy - Minimal Application

Question

I am starting with Flask sqlalchemy, but i don't understand how works the demo of the homepage (here).

This will create the structure:

>>> from yourapplication import db
>>> db.create_all()

But I don't understand where these users are stored.

>>> from yourapplication import User
>>> admin = User('admin', '[email protected]')
>>> guest = User('guest', '[email protected]')
>>> db.session.add(admin)
>>> db.session.add(guest)
>>> db.session.commit()

Because if I do Select * from user;

I will get:

test=# \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | user | table | postgres
(1 row)

test=# select * from user;
 current_user
--------------
 postgres
(1 row)

However the output is the expected:

>>> users = User.query.all()
>>> [<User u'admin'>, <User u'guest'>]

So, what is happening here? the users are stored in cache? memory? Because i don't see any admin or guest in the user table .

you probably are supposed to configure your database connexion somewhere (actually, I just clicked the link, it is like, in the middle of the page. app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db') — njzk2
– njzk2, Commented Feb 26, 2014 at 23:42
@njzk2 'postgresql://postgres:xxx@localhost/test', If I don't set this conf, obviously the program will give an error. — user2990084
– user2990084, Commented Feb 26, 2014 at 23:45
@njzk2 ok, but the problem is not that. I have the expected output, i just want to understand where is stored the user info. — user2990084
– user2990084, Commented Feb 26, 2014 at 23:48
your postgresql table really does not resemble the expected schema. therefore, it is stored somewhere else. are you sure it is not in the sqlite file? — njzk2
– njzk2, Commented Feb 26, 2014 at 23:50

codegeek · Accepted Answer · 2014-02-27 18:32:29Z

5

Flask is using the extremely unfortunate table name user. This is a reserved keyword in the database; an alias for the built-in user function, which is in turn an alias for current_user. That's why you get the column-name current_user when you select * from user; you're really running the pseudo-function current_user.

This is made more confusing by the fact that "user" doesn't use SQL function syntax, it's undecorated, and by the fact that it's rewritten by the parser so there's no function by that name actually defined, hence the confusing outcome:

regress=> explain select * from user;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Function Scan on "current_user"  (cost=0.00..0.01 rows=1 width=64)
(1 row)

If you want to describe or select from the user-defined table named user you must, per the documentation on reserved words, quote it. So you must write:

select * from "user";

or

select * from public."user";

(schema-qualified).

Similarly:

\dt "user"

will show the table structure.

Please report a bug against Flask. It should not be using an SQL-92 reserved word as a relation name.

EDIT: Flask is creating a table called 'user' because the classname is User. In order to avoid this, it is recommended to provide a __tablename__ option while defining the model.

class User(db.Model):
    __tablename__= 'MyUser'
....

This way, you can avoid the conflicts with default conventions of the database.

edited Feb 27, 2014 at 18:32

codegeek

33.5k12 gold badges65 silver badges65 bronze badges

answered Feb 27, 2014 at 1:09

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

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

3 Comments

Craig Ringer Over a year ago

@user2990084 You should report a bug against Flask. user has been an SQL reserved word since SQL-92. It should not be using it. At the very minimum, Flask needs to document the quirks this will cause.

njzk2 Over a year ago

@CraigRinger : the mistake is in the application using User as class name, knowing the class name is in turn used as table name (being always quoted in the library, it poses no problem). (And therefore in the flask example given. Though most people won't see this problem)

codegeek Over a year ago

Ideally, one should overwrite the default table name by using __tablename__ = "xyz" in order to avoid this issue. Alternatively, use a different class name that User

Collectives™ on Stack Overflow

Flask sqlalchemy - Minimal Application

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related