psycopg2 issues with inserting a prepared JSONB object into postgres [duplicate]

Question

I have a JSONB object that I want to insert into my database that also contains single quote characters. The example below is just trimmed to highlight my issue:

[{"name": "Moody's Analytics BBB Corp Bd ETF"}]

I have tried just dumping that JSON object to a string and inserting it, but the single ' wont allow that to work and when I try to execute (where detail is the JSON object):

cur.execute("INSERT INTO schmea.table (id, detail) VALUES (?,?)", (sec_id, detail)

I get the following syntax error:

psycopg2.errors.SyntaxError: syntax error at or near ","
LINE 1: INSERT INTO schema.table (id, detail) VALUES (?,?)
                                                       ^

All of the solutions I've found so far suggest turning the JSON object into a string using json.dumps() but that wont work in my situation because of the single ' character in the name.

Any help is appreciated here.

Edit: Was told that the proper bind is %s not ? but that changes the error to:

psycopg2.ProgrammingError: can't adapt type 'dict'

edit 2:

SOLUTION:

Using the correct %s binds and using json.dumps(detail) fixed the issue.

cur.execute("INSERT INTO schmea.table (id, detail) VALUES (%s,%s)", (sec_id, json.dumps(detail))

Thanks for your help!

As in many things the docs have the answer JSON.

Adrian Klaver
– Adrian Klaver

2020-12-03 19:45:42 +00:00
Commented Dec 3, 2020 at 19:45 — Adrian Klaver
– Adrian Klaver, Commented Dec 3, 2020 at 19:45

Jonathan Jacobson · Accepted Answer · 2020-12-03 19:28:22Z

2

The way to pass bind variables to psycopg2 is with %s, not ?.

answered Dec 3, 2020 at 19:28

Jonathan Jacobson

1,52811 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Christopher Carlisle Over a year ago

I got it mixed with pyodbc. But in changing to %s i get the error ``` psycopg2.ProgrammingError: can't adapt type 'dict' ```

Jonathan Jacobson Over a year ago

Try sending it a JSON or just a plain string. PostgreSQL knows how to implicitly convert a text to jsonb. Or even use explicit conversion: %s::jsonb.

Christopher Carlisle Over a year ago

Your answer got me half way there. After using the correct bind using json.dumps(detail) solved my issue. Thanks for the help!

Collectives™ on Stack Overflow

psycopg2 issues with inserting a prepared JSONB object into postgres [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related