SQLAlchemy CORE: bindparam with postgresql on_conflict_do_update with a JSONB column

[Massimiliano della Rovere]

Greetings,

I am using SQLAlchemy==1.3.18.

I have an SQLAlchemy "Settings" table with a "data" column defined as:

from sqlalchemy.dialects.postgresql import JSONB

Settings = Table(

    "settings",

    self._metadata,

    # ...

    Column("data", JSONB, nullable=False))

and later in the code this "baked" query:

from sqlalchemy.dialects.postgresql import insert as pinsert, JSONB

query = (

    pinsert(Settings)

    .values({
        "key": "xxx",
        "data": bindparam("timestamps", type_=JSONB)})
    .on_conflict_do_update(
        index_elements=(Settings.columns.key,),
        set_=bindparam("timestamps", type_=JSONB))

The python interpreter throws me the following error raised by sqlalchemy.dialects.postgresql.dml line 227:

        if not isinstance(set_, dict) or not set_:
            raise ValueError("set parameter must be a non-empty dictionary")

it seems that the "set_" parameter of the "on_conflict_do_update" method requires a dict, and thus is not compatible with bindparam.
I have also tried to set the "type_" parameter of bindparam to dict, but I did not solve the problem.

Please, can anybody help me?

[Mike Bayer]

the dictionary is with column names as keys:

set_={"data": bindparam("timestamps")}

hope this helps

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CADKhPGT_mze%2BgjD%3DYoNd7tPGtpYMNnreKne%3DgwWMuJMZ0g2-xw%40mail.gmail.com.

[Massimiliano della Rovere]

Yes, Mike, that solved the problem! I forgot to pass the column name.

Now I get a different error: bindparam is not json-serializable.

StatementError('(builtins.TypeError) Object of type BindParameter is not JSON serializable'),
  

Is the JSONB column not supporting lazy/bindparam-compatible "preparing" of queries?

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/9b1ce445-b8c7-4209-977d-c41de5153348%40www.fastmail.com.

[Mike Bayer]

this indicates a bindparam() object is being interpreted as a value somewhere but I can't reproduce that. 

can you provide an MCVE ?

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CADKhPGQkUh3cqws3Tj9i%2BATs0DRXh4pX-U8MyWWHGnen%2Bt2ZRA%40mail.gmail.com.

[Mike Bayer]

here's one based on your samples of code, works fine on this end, you can use this to see what you are doing differently.

from sqlalchemy import bindparam

from sqlalchemy import Column

from sqlalchemy import create_engine

from sqlalchemy import MetaData

from sqlalchemy import String

from sqlalchemy import Table

from sqlalchemy.dialects.postgresql import insert as pinsert

from sqlalchemy.dialects.postgresql import JSONB

m = MetaData()

Settings = Table(

    "settings",

    m,

    Column("key", String, primary_key=True),

    Column("data", JSONB, nullable=False),

)

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)

m.drop_all(e)

m.create_all(e)

query = (

    pinsert(Settings)

    .values({"key": "xxx", "data": bindparam("timestamps", type_=JSONB)})

    .on_conflict_do_update(

        index_elements=(Settings.columns.key,),

        set_={"data": bindparam("timestamps", type_=JSONB)},

    )

)

with e.begin() as conn:

    conn.execute(query, {"timestamps": {"foo": "bar"}})

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/25f4a09c-7ab0-4032-a857-0b3167bac12e%40www.fastmail.com.

[Massimiliano della Rovere]

I removed all the tables from the DB and recreated them all (Settings among them) with alembic and everything worked.

Thanks for your help, Mike!

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/79cf0fdb-66d5-4552-88ac-b318896f0eff%40www.fastmail.com.