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 PyGreSQL@Vex.Net https://mail.vex.net/mailman/listinfo/pygresql