Python - Postgres array to dict/json/array

Question

So...

I have to pull data from postgres database using python ant return the results (JSON formatted) to a web page via an AJAX call

I have to live with a nasty constraint in that I have no access to python SQL libraries (it's a black box appliance with limited libraries) so I have to shell out to OS and parse the results.

A PITA but no real problem so far

Today's problem though is with returned Postgres array types

A typical value returned as a string would be, and yes, some of the elements do contain colons..

{foo,"bar bar",baz:qux,999}

Ideally I'd like to convert to

'values':['foo','bar bar","baz:qux","999"]

Any good ideas or do I need to spend some time on regex101.com

Answer 1

Something like this:

In [24]: strs="""{foo,"bar bar",baz:qux,999}"""

In [27]: [x.strip('"{}') for x in strs.split(",")]
Out[27]: ['foo', 'bar bar', 'baz:qux', '999']

Answer 2

I'd use a pure-python library like pure-python library like py-postgresql from http://python.projects.pgfoundry.org/ that doesn't require any additional native code, personally.

If necessary you could even hack it to include it in-line in a single script, rather than as multiple files.