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"}})
On Tue, Sep 15, 2020, at 11:50 AM, Mike Bayer wrote:
> this indicates a bindparam() object is being interpreted as a value somewhere
> but I can't reproduce that.
>
> can you provide an MCVE ?
>
>
>
> On Tue, Sep 15, 2020, at 11:33 AM, Massimiliano della Rovere wrote:
>> 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?
>>
>> Il giorno mar 15 set 2020 alle ore 17:13 Mike Bayer
>> <[email protected]> ha scritto:
>>> __
>>> the dictionary is with column names as keys:
>>>
>>>
>>> set_={"data": bindparam("timestamps")}
>>>
>>> hope this helps
>>>
>>>
>>>
>>> On Tue, Sep 15, 2020, at 10:55 AM, Massimiliano della Rovere wrote:
>>>> 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?
>>>>
>>>>
>>>> --
>>>> 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 [email protected].
>>>> To view this discussion on the web visit
>>>> https://groups.google.com/d/msgid/sqlalchemy/CADKhPGT_mze%2BgjD%3DYoNd7tPGtpYMNnreKne%3DgwWMuJMZ0g2-xw%40mail.gmail.com
>>>>
>>>> <https://groups.google.com/d/msgid/sqlalchemy/CADKhPGT_mze%2BgjD%3DYoNd7tPGtpYMNnreKne%3DgwWMuJMZ0g2-xw%40mail.gmail.com?utm_medium=email&utm_source=footer>.
>>>
>>>
>>> --
>>> 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 [email protected].
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy/9b1ce445-b8c7-4209-977d-c41de5153348%40www.fastmail.com
>>>
>>> <https://groups.google.com/d/msgid/sqlalchemy/9b1ce445-b8c7-4209-977d-c41de5153348%40www.fastmail.com?utm_medium=email&utm_source=footer>.
>>
>> --
>> 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 [email protected].
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/sqlalchemy/CADKhPGQkUh3cqws3Tj9i%2BATs0DRXh4pX-U8MyWWHGnen%2Bt2ZRA%40mail.gmail.com
>>
>> <https://groups.google.com/d/msgid/sqlalchemy/CADKhPGQkUh3cqws3Tj9i%2BATs0DRXh4pX-U8MyWWHGnen%2Bt2ZRA%40mail.gmail.com?utm_medium=email&utm_source=footer>.
>
>
> --
> 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 [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/25f4a09c-7ab0-4032-a857-0b3167bac12e%40www.fastmail.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/25f4a09c-7ab0-4032-a857-0b3167bac12e%40www.fastmail.com?utm_medium=email&utm_source=footer>.
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/79cf0fdb-66d5-4552-88ac-b318896f0eff%40www.fastmail.com.
