👀 Views: 1 💬 Answers: 1 📅 Created: 2025-06-04

sqlalchemy postgresql jsonb Python

I'm reviewing some code and Quick question that's been bugging me - I'm working on a project and hit a roadblock. I'm working with an scenario when trying to insert a record into a PostgreSQL database using SQLAlchemy with a JSONB field. The model looks something like this: ```python from sqlalchemy import create_engine, Column, Integer, JSON from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker Base = declarative_base() class MyModel(Base): __tablename__ = 'my_table' id = Column(Integer, primary_key=True) data = Column(JSON) engine = create_engine('postgresql+psycopg2://user:password@localhost/mydb') Session = sessionmaker(bind=engine) session = Session() ``` When I try to insert a new record like this: ```python new_record = MyModel(data={'key': 'value', 'nested': {'array': [1, 2, 3]}}) session.add(new_record) session.commit() ``` I'm getting the following behavior: ``` TypeError: Object of type dict is not JSON serializable ``` I checked the documentation but it seems that SQLAlchemy should handle the serialization automatically when using the JSON type. I also verified that the `psycopg2` driver is up to date. I've tried different variations of the `data` field, including converting to a string and using json.dumps, but nothing seems to work. Here's how I tried converting it: ```python import json new_record = MyModel(data=json.dumps({'key': 'value', 'nested': {'array': [1, 2, 3]}})) session.add(new_record) session.commit() ``` This leads to a different behavior related to the type expected by the database. I am using SQLAlchemy version 1.4.22 and PostgreSQL 13. Can anyone guide to understand why this is happening and how to resolve it while still using the JSONB field as intended? My development environment is Ubuntu. What's the best practice here? I'm working in a Windows 11 environment. How would you solve this? I'd be grateful for any help. I'm developing on Ubuntu 20.04 with Python. Any feedback is welcome!