I'm using jsonb column for the 2nd time ever, and a variation of my issue from
August came up again.
1) I thougt it was weird to need to pass types=['something', 'else', 'int',
'float', 'json']. I suggest to also do:
if isinstance(types, str):
types=types.split()
2) I suggested to allow passing not types=[str('json')] but also
types=[pg.Json].
3) I suggested guess_simple_types() should handle Json() and Hstore()
4) I think we need an example of this, probably for types=list() and for
types=dict().
On Fri, Aug 23, 2019 at 11:01:11AM -0500, Justin Pryzby wrote:
> I understand that INSERT and SELECT value (without ::type) might need to be
> specially casted, otherwise they end up as text.
>
> I'd never needed to do this before and was surprised to see that I needed to
> pass a types=['json'], or call json.dumps() to make it work at all.
>
> Specifically, I thought it would work to call pg.Json:
>
> |$ strace -fe sendto -s222 python -c "import pg; d=pg.DB('postgres');
> d.query('CREATE TABLE IF NOT EXISTS j(a json)'); d.query_formatted('INSERT
> INTO j VALUES (%s)', [pg.Json([1])])"
> |Process 23373 attached
> |[pid 23373] +++ exited with 0 +++
> |--- SIGCHLD {si_signo=SIGCHLD, si_code=CLD_EXITED, si_pid=23373,
> si_status=0, si_utime=0, si_stime=0} ---
> |sendto(3, "\0\0\0(\0\3\0\0user\0pryzbyj\0database\0postgres\0\0", 40,
> MSG_NOSIGNAL, NULL, 0) = 40
> |sendto(3, "Q\0\0\0)CREATE TABLE IF NOT EXISTS j(a json)\0", 42,
> MSG_NOSIGNAL, NULL, 0) = 42
> |NOTICE: relation "j" already exists, skipping
> |sendto(3, "P\0\0\0!\0INSERT INTO j VALUES
> ($1)\0\0\0B\0\0\0006\0\0\0\0\0\1\0\0\0$<pg.Json instance at
> 0x7f94a3b43830>\0\1\0\0D\0\0\0\6P\0E\0\0\0\t\0\0\0\0\0S\0\0\0\4", 111,
> MSG_NOSIGNAL, NULL, 0) = 111
> |Traceback (most recent call last):
> | File "<string>", line 1, in <module>
> | File "/usr/lib64/python2.7/site-packages/pg.py", line 1919, in
> query_formatted
> | command, parameters, types, inline))
> | File "/usr/lib64/python2.7/site-packages/pg.py", line 1901, in query
> | return self.db.query(command, args)
> |pg.DataError: ERROR: invalid input syntax for type json
> |DETAIL: Token "<" is invalid.
> |CONTEXT: JSON data, line 1: <...
>
> guess_simple_type DOES handle Bytea; should it also handle Json and Hstore ?
>
> Also, although I knew it existed and read the code and knew how it worked, I'd
> never before had to use types=. On my first attempt, I passed
> types=[pg.Json],
> which did nothing since it's wrong. So I realized I needed to pass ['json'],
> but perhaps it's worth considering handling something like if x==pg.Json
> (equality to the class, not isinstance).
_______________________________________________
PyGreSQL mailing list
[email protected]
https://mail.vex.net/mailman/listinfo/pygresql
