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

[Jun Omae]

On Wed, Jan 8, 2020 at 2:38 PM Velu Narasimman <velava...@gmail.com> 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
"(dp1\nS'key'\np2\nI42\ns."
>>> psycopg2.Binary(data)
<psycopg2._psycopg.Binary object at 0x2abca30>
>>> print(str(psycopg2.Binary(data)))
'(dp1\012S''key''\012p2\012I42\012s.'::bytea
>>> str(psycopg2.Binary(data))
"'(dp1\\012S''key''\\012p2\\012I42\\012s.'::bytea"

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])
"(dp1\nS'key'\np2\nI42\ns."
>>> cPickle.loads(str(rows[0][0]))
{'key': 42}

--
Jun Omae <jun...@gmail.com> (大前 潤)

[Velu Narasimman]

Thank you so much! it worked! :)

[RjOllos]

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.

https://trac.edgewall.org/wiki/MailingList