Flask app 1 - word count with PostgreSQL and Flask-SQLAlchemy

In this page, we'll list possible errors and fixes when we run / deploy our app.

Github source : akadrone-flask

This is about postgresql, and it happens when we run gunicorn.

$ gunicorn -w 1 -b 127.0.0.1:5000 aka:app
...
    conn = _connect(dsn, connection_factory=connection_factory, async=async)
OperationalError: (psycopg2.OperationalError) FATAL:  role "sfvue" does not exist

Actually, the postgresql config in aka.py was like this:

app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://localhost/wordcount_dev'

So, we need "username:password" in the following format:

dialect+driver://username:password@host:port/database

In our case, it should look like this:

app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://postgres:postgres@localhost/wordcount_dev'

We may want to create a user. In my case, it's 'sfvue'.

[sfvue@sf ~]$ sudo -u postgres createuser --interactive
[sudo] password for sfvue: 
Enter name of role to add: sfvue
Shall the new role be a superuser? (y/n) y
[sfvue@sf ~]$ sudo -u postgres psql
psql (9.2.15)
Type "help" for help.

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication | {}
 sfvue     | Superuser, Create role, Create DB              | {}

postgres=# 

When we try to run python manage.py runserver:

(venv)[sfvue@sf akadrone]$ python manage.py runserver
...
ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'dict' [SQL: 'SELECT results.id AS results_id, results.url AS results_url, results.result_all AS results_result_all, results.result_no_stop_words AS results_result_no_stop_words \nFROM results \nWHERE results.id = %(id_1)s \n LIMIT %(param_1)s'] [parameters: {'id_1': {'error': ['Unable to add item to database.']}, 'param_1': 1}]
...

The error was caused by incomplete db setup. So, we need the followings:

$ python manage.py db init
$ python manage.py db migrate
$ python manage.py db upgrade

(venv)[sfvue@sf akadrone]$ sudo -u postgres psql
[sudo] password for sfvue: 
psql (9.2.15)
Type "help" for help.

postgres=# \c wordcount_dev
You are now connected to database "wordcount_dev" as user "postgres".
wordcount_dev=# \dt
              List of relations
 Schema |      Name       | Type  |  Owner   
--------+-----------------+-------+----------
 public | alembic_version | table | postgres
 public | results         | table | postgres
(2 rows)

wordcount_dev=# \d results
                                     Table "public.results"
        Column        |       Type        |                      Modifiers                       
----------------------+-------------------+------------------------------------------------------
 id                   | integer           | not null default nextval('results_id_seq'::regclass)
 url                  | character varying | 
 result_all           | json              | 
 result_no_stop_words | json              | 
Indexes:
    "results_pkey" PRIMARY KEY, btree (id)

wordcount_dev-# \q