Python Forum

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!

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'