René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Interpret None as NULL in inserts","articleBody":"In tables with `NULL` default values, insert behavior is inconsistent:\r\n\r\nIf a dictionary-style insert is performed using a dictionary without a key corresponding to the default-null field, the operation proceeds. In the case of a dictionary-style insert where the default-null field is present and set to `None`, or in the case of a list-style insert with an appropriately given positional `None`,  the operation fails. \r\n\r\nHowever, on the fetch side, records are returned with `None` as a present and populated value.\r\n\r\n```\r\n\u003e\u003e\u003e print(OtherNull.definition)\r\n\r\n    otherid: integer\r\n    ---\r\n    otherthing=NULL: varchar(255)\r\n    \r\n\u003e\u003e\u003e OtherNull().insert1({'otherid': 1})\r\n\u003e\u003e\u003e OtherNull().insert1({'otherid': 2, 'otherthing': None})\r\nTraceback (most recent call last):\r\n  File \"\u003cconsole\u003e\", line 1, in \u003cmodule\u003e\r\n  File \"/Users/chris/Workspace/mlims-pipeline.ve/lib/python3.6/site-packages/datajoint/base_relation.py\", line 137, in insert1\r\n    self.insert((row,), **kwargs)\r\n  File \"/Users/chris/Workspace/mlims-pipeline.ve/lib/python3.6/site-packages/datajoint/base_relation.py\", line 278, in insert\r\n    itertools.chain.from_iterable((v for v in r['values'] if v is not None) for r in rows)))\r\n  File \"/Users/chris/Workspace/mlims-pipeline.ve/lib/python3.6/site-packages/datajoint/connection.py\", line 133, in query\r\n    cur.execute(query, args)\r\n  File \"/Users/chris/Workspace/mlims-pipeline.ve/lib/python3.6/site-packages/pymysql/cursors.py\", line 163, in execute\r\n    query = self.mogrify(query, args)\r\n  File \"/Users/chris/Workspace/mlims-pipeline.ve/lib/python3.6/site-packages/pymysql/cursors.py\", line 142, in mogrify\r\n    query = query % self._escape_args(args, conn)\r\nTypeError: not enough arguments for format string\r\n\u003e\u003e\u003e OtherNull().insert1((2, None,))\r\nTraceback (most recent call last):\r\n  File \"\u003cconsole\u003e\", line 1, in \u003cmodule\u003e\r\n  File \"/Users/chris/Workspace/mlims-pipeline.ve/lib/python3.6/site-packages/datajoint/base_relation.py\", line 137, in insert1\r\n    self.insert((row,), **kwargs)\r\n  File \"/Users/chris/Workspace/mlims-pipeline.ve/lib/python3.6/site-packages/datajoint/base_relation.py\", line 278, in insert\r\n    itertools.chain.from_iterable((v for v in r['values'] if v is not None) for r in rows)))\r\n  File \"/Users/chris/Workspace/mlims-pipeline.ve/lib/python3.6/site-packages/datajoint/connection.py\", line 133, in query\r\n    cur.execute(query, args)\r\n  File \"/Users/chris/Workspace/mlims-pipeline.ve/lib/python3.6/site-packages/pymysql/cursors.py\", line 163, in execute\r\n    query = self.mogrify(query, args)\r\n  File \"/Users/chris/Workspace/mlims-pipeline.ve/lib/python3.6/site-packages/pymysql/cursors.py\", line 142, in mogrify\r\n    query = query % self._escape_args(args, conn)\r\nTypeError: not enough arguments for format string\r\n```\r\non the fetch side, None is used as a substitute:\r\n```\r\n\u003e\u003e\u003e OtherNull()\r\n*otherid    otherthing    \r\n+---------+ +------------+\r\n1           None          \r\n (1 tuples)\r\n\r\n\u003e\u003e\u003e OtherNull().fetch()\r\narray([(1, None)], dtype=[('otherid', '\u003ci8'), ('otherthing', 'O')])\r\n\u003e\u003e\u003e OtherNull().fetch1()\r\nOrderedDict([('otherid', 1), ('otherthing', None)])\r\n```\r\n","author":{"url":"https://github.com/ixcat","@type":"Person","name":"ixcat"},"datePublished":"2018-04-25T02:37:34.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":4},"url":"https://github.com/467/datajoint-python/issues/467"}