I'd like to store Python dicts containing lists as HSTORE object in a PostgreSQL database using SQLAlchemy. Following my table class.
from sqlalchemy.dialects.postgresql import HSTORE
from sqlalchemy import Column, String
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Data(Base):
id = Column(String, primary_key=True)
data = Column(HSTORE)
I insert my items in the following matter.
data = Data(
id='fancy_id',
data={
'foo': ['bar', 'bar']
},
)
db.Session.add(scan)
db.Session.commit()
This causes a ROLLBACK to happen and the following exception raises.
sqlalchemy.exc.DataError: (psycopg2.errors.ArraySubscriptError) wrong number of array subscripts
[SQL: INSERT INTO data (id, data) VALUES (%(id)s, %(data)s)]
[parameters: {'id': 'fancy_id', 'data': {'foo': ['bar', 'bar']}}]
However, the insertion works without the list.
data = Data(
id='fancy_id',
data={
'foo': 'bar'
},
)
I followed the PostgreSQL SQLAlchemy 1.4 documentation and can't spot a note indicating this limitation. Am I missing something? How can I store Python lists inside PostgreSQL HSTORE objects via SQLAlchemy?
Thanks in advance.