Python Forum
Need help on a psycopg2 question
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Need help on a psycopg2 question
#1
I have posted my question in stackoverflow but hasn't got any help yet.

https://stackoverflow.com/questions/5318...ng-python3

I need to sync any Postgres table from the latest copy of our production database (source) to a developer database without wiping out its testing data. The below simplified code works for most tables, but not for those with jsonb fields, due to the psycopg2.ProgrammingError: can't adapt type 'dict'

    import psycopg2
    from psycopg2 import sql

    tb = "table_to_be_copied"

    ##############################
    # load data from source DB                                                                                                                          
    ##############################

    conn_source = psycopg2.connect(host='source_localhost',
                             dbname=postgres,
                             user='xyz',
                             port=source_port)

    cursor_source = conn_source.cursor()
    cursor_source.execute(
        sql.SQL("SELECT * from {}").format(sql.Identifier(tb))
    )

    # obtain column names on the fly for any given table
    colnames = tuple([desc[0] for desc in cursor_source.description])        

    # jsonb's type code is 3802. This will help the program determine on the fly
    # which columns are in jsonb.
    typecodes = tuple([desc[1] for desc in cursor_source.description])

    # obtain production data to be synced
    rows = cursor_source.fetchall()

    cursor_source.close()
    conn_source.close()

    ##############################
    # upsert data into destination DB
    ##############################

    conn_dest = psycopg2.connect(host='dest_localhost',
                                 dbname='postgres',
                                 user='xyz',
                                 port=dest_port)

    cursor_dest = conn_dest.cursor()

    for row in rows:
            cursor_dest.execute(
                    sql.SQL("INSERT INTO {} ({}) VALUES ({}) \                                                                                          
                    ON CONFLICT (id) DO UPDATE SET ({}) = ({})").format(
                            sql.Identifier(tb),
                            sql.SQL(', ').join(map(sql.Identifier, colnames)),
                            sql.SQL(', ').join(sql.Placeholder() * len(colnames)),
                            sql.SQL(', ').join(map(sql.Identifier, colnames)),
                            sql.SQL(', ').join(sql.Placeholder() * len(colnames))),
                    row * 2)

    conn_dest.commit()
    cursor_dest.close()
    conn_dest.close()

    print ("Sync done")
It'd be terrific if you could help answer this question. If not, I would still greatly appreciate if you can vote for my post at stackoverflow so to attract more reads. Thanks!
Reply
#2
The entire traceback is below:

Error:
Traceback (most recent call last): File "sync_db.py", line 59, in <module> main() File "sync_db.py", line 53, in main row * 2) psycopg2.ProgrammingError: can't adapt type 'dict'
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  How psycopg2 autocommit works ? johntay 3 10,203 Oct-08-2021, 11:22 AM
Last Post: Larz60+
  Psycopg2 doesn't work with python2 MedianykEugene 3 2,888 Aug-10-2021, 07:00 AM
Last Post: ndc85430
  PostgreSQL psycopg2.errors.DuplicateColumn: column specified more than once rajnish_nationfirst 2 3,691 Jun-21-2020, 08:17 AM
Last Post: ibreeden
  tornado psycopg2 Nikosznb 1 2,282 Feb-23-2020, 10:49 PM
Last Post: scidam
  Frequency and timing of psycopg2 commits acecase 0 1,953 Nov-01-2019, 05:50 PM
Last Post: acecase
  Create table with psycopg2 on postgreSQL DB yhecohen 2 3,272 Aug-23-2019, 05:56 AM
Last Post: massimo_m
  psycopg2 insert error Wonder_women 0 2,644 Jun-10-2019, 11:56 AM
Last Post: Wonder_women
  import psycopg2 issue bhuvneshdogra 1 2,956 Dec-27-2018, 04:03 PM
Last Post: Larz60+
  Error when Inserting CSV file values into a postgreSQL using psycopg2 klllmmm 8 12,997 Oct-07-2016, 06:48 PM
Last Post: klllmmm
  Error when using copy_expert(sql, file) in psycopg2 to insert data from several CSV klllmmm 5 29,096 Oct-03-2016, 01:13 AM
Last Post: klllmmm

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020