Re: [Trac] How to store a multi object list of dictionary into a table using postgresql

Skip to first unread message

Jun Omae

Jan 8, 2020, 1:45:50 AM1/8/20
On Wed, Jan 8, 2020 at 2:38 PM Velu Narasimman <> wrote:
> Hi,
> I am using MenusPlugin for displaying trac menus as submenus. At one point of implementation we are trying to store a list of dictionary of data that is generated by this menus plugin into postgresql database. Please find the list of dictionary sample below.
> [{'parent_name': u'top', 'href': u'/auth/dashboard', 'name': u'new_dash', 'active': False, 'visited': True, 'enabled': True, u'order': u'-9999999', 'label': <Fragment>}, {'parent_name': u'top', 'href': u'/myprojects', 'name': u'allprojects', 'active': True, 'visited': True, 'enabled': True, u'order': u'-9999998', 'label': <Fragment>}, {'parent_name': u'top', 'href': u'#', 'name': u'alltimesheet', 'children': <Element "ul">, 'active': False, 'visited': True, 'enabled': True, u'order': u'-9999997', 'label': <Fragment>}, {'parent_name': u'top', 'href': u'/myteam', 'name': u'myteam', 'children': <Element "ul">, 'active': False, 'visited': True, 'enabled': True, u'order': u'-9999996', 'label': <Fragment>}, {'name': u'timesheet', 'parent_name': u'top', 'enabled': True, 'label': <Fragment>, 'children': <Element "ul">, 'href': '#', 'active': False, 'visited': True, u'order': u'4'}, {'parent_name': 'top', 'name': u'admin', 'enabled': False, 'label': <Fragment>, 'href': '#', 'active': False, 'visited': True, u'order': u'999999'}, {'name': u'username', 'parent_name': u'top', 'enabled': True, 'label': <Fragment>, 'children': <Element "ul">, 'href': '#', 'active': False, 'visited': True, u'order': u'9999999'}]
> My aim is to store this whole list of dictionary into a table as is. But as you can see some Genshi <fragment> and <Element "ul"> are present in this, following two methods are failing.
> 1. Pickle object
> 2. Json
> Method 1: (Pickle)
> -------------------------
> pickle_data = cPickle.dumps(menu_dict, -1) # menu_dict is the data that you can see above.
> # constructed pickle successfully in this step.
> binary_menu = psycopg2.Binary(pickle_data) # converting pickle to Binary is also done successfully.
> # And I hope I can store this data to db as is but for a try I am unpickling the # binary data back to it's original form using the below statement
> # and that doesn't work!
> cPickle.loads(str(binary_menu)) # this throws some errors as shown below.
> Below is the error that I ended up with the last line in above chunk
> Trac detected an internal error:
> UnpicklingError: invalid load key, '''.

psycopg2.Binary is wrapper class for binary in PostgreSQL and to
generate binary literal.
That instance is unable to use to cPickle.loads.

>>> import cPickle
>>> data = cPickle.dumps({'key':42})
>>> data
>>> psycopg2.Binary(data)
<psycopg2._psycopg.Binary object at 0x2abca30>
>>> print(str(psycopg2.Binary(data)))
>>> str(psycopg2.Binary(data))

A buffer instance in psycopg2 is retrieved from a binary column.
It is able to cPickle.loads from the buffer instance.

>>> from trac.env import Environment
>>> env = Environment('/var/lib/trac/1.0-postgres')
>>> rows = env.db_query('SELECT %s', (psycopg2.Binary(data),))
>>> rows[0][0]
<read-only buffer for 0x2df81d0, size 21, offset 0 at 0x2ebdc70>
>>> str(rows[0][0])
>>> cPickle.loads(str(rows[0][0]))
{'key': 42}

Jun Omae <> (大前 潤)

Velu Narasimman

Jan 14, 2020, 1:35:03 AM1/14/20
to Trac Users
Thank you so much! it worked! :)


Jan 28, 2020, 2:27:31 PM1/28/20
to Trac Development

On Monday, January 13, 2020 at 10:35:03 PM UTC-8, Velu Narasimman wrote:
Thank you so much! it worked! :) 

Topic moved to Trac Development mailing list.

Reply all
Reply to author
0 new messages