How use Postgre's HSTORE type in Flask application?

Question

I'm a beginner in Flask & I tried to make one of my table column as HSTORE in my model. But I got an error.

Can anyone point to me how I can use HSTORE type in flask app.

I have checked too many links and can't find anything helpful for me.

In SQLAlchemy manual, they documented that PostgreSQL HSTORE type as well as hstore literals are supported.

https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#hstore-type

In my model, I have added the below code.

from sqlalchemy.dialects import postgresql

#.........
class Submission(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    form_id = db.Column(db.Integer, db.ForeignKey('form.id'))
    origin = db.Column(db.String())
    form_data = db.Column(db.Hstore()) # I have also tried HSTORE(), hstore() 
    timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)

But when I try to run my flask app, I got the below error.

AttributeError: 'SQLAlchemy' object has no attribute 'Hstore'

I'm using the latest version of Flask - 1.1.1.

score 2 · Accepted Answer · answered Nov 07 '19 at 06:00

In SQLAlchemy HSTORE type as well as hstore literals are supported, But you have to manually import the hstore extension using

from sqlalchemy.dialects.postgresql import HSTORE
from sqlalchemy.ext.mutable import MutableDict

Afterwards you should describe the column name and column type with hstore

form_data = db.Column(MutableDict.as_mutable(HSTORE))

Python Dictionaries should be stored inside the columns using MutableDict in Flask (sqlalchemy)

So your code need to be changed as

from sqlalchemy.dialects import postgresql
from sqlalchemy.dialects.postgresql import HSTORE
from sqlalchemy.ext.mutable import MutableDict

#.........
class Submission(db.Model):
 id = db.Column(db.Integer, primary_key=True)
 form_id = db.Column(db.Integer, db.ForeignKey('form.id'))
 origin = db.Column(db.String())
 form_data = db.Column(MutableDict.as_mutable(HSTORE))
 timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)

Also remember to create Hstore extension for your database as follows

login in psql postgres from command prompt

sudo -u postgres psql

Assuming you have created the required database under postgres role, lets say db_test is the database

Connect to db_test

postgres=# \c db_test

Now you should be connected to db_test Create hstore extension

db_test=# CREATE EXTENSION hstore;

hstore extension should be successfully created, List out the extensions installed on your database using

db_test=# \dx

Hope this will solve your problem

How use Postgre's HSTORE type in Flask application?

1 Answers1