Pymapd : How to insert/update NULL or None values? using executemany method!

Programming & Data Ecosystem Tools Python
#1

Hi Team,

Is it possible to insert or update a particular column value to null using executemany API. I’ve tried passing “None” as value but throws bellow error. Below are my sample code snippets and error. Any suggestions would be appreciated.

#-------------------------------
# Code Snippet > "executemany"
#-------------------------------

logging.info("test: executemany")

wake_and_connect_to_mapd()  

test_data_list = []

test_data_list.append( {'id' : 4, 'name' : 'Tech', 'articles' : 1, 'createdAt' : '2020-04-02 01:02:23' } )
test_data_list.append( {'id' : 5, 'name' : None, 'articles' : 8, 'createdAt' : '2020-04-02 01:02:23' } )
test_data_list.append( {'id' : 6, 'name' : 'Wes', 'articles' : 0, 'createdAt' : '2020-04-02 01:02:23' } )

logging.info(test_data_list)

cur = mapdcur.executemany("INSERT INTO test_rp ( id, name, articles, createdAt) VALUES ( :id, :name, :articles, :createdAt)", test_data_list)
logging.info(list(cur))
logging.info("test: end")

return

#-------------------------------
# Error Response from Omnisci
#-------------------------------

2020-04-20 23:25:59 [{'id': 11, 'name': 'Tech', 'articles': 1, 'createdAt': '2020-04-02 01:02:23'}, {'id': 12, 'name': None, 'articles': 8, 'createdAt': '2020-04-02 01:02:23'}, {'id': 13, 'name': 'Wes', 'articles': 0, 'createdAt': '2020-04-02 01:02:23'}, {'id': 14, 'name': None, 'articles': None, 'createdAt': '2020-04-02 01:02:23'}]
Traceback (most recent call last):
  File "/Users/developer/David/projects/rungroupdata/pythonScripts/Omnisci-Import.py", line 794, in <module>
    main()
  File "/Users/developer/David/projects/rungroupdata/pythonScripts/Omnisci-Import.py", line 686, in main
    cur = mapdcur.executemany("INSERT INTO test_rp ( id, name, articles, createdAt) VALUES ( :id, :name, :articles, :createdAt)", test_data_list)
  File "/Users/developer/opt/anaconda3/lib/python3.7/site-packages/pymapd/cursor.py", line 144, in executemany
    in parameters]
  File "/Users/developer/opt/anaconda3/lib/python3.7/site-packages/pymapd/cursor.py", line 143, in <listcomp>
    results = [list(self.execute(operation, params)) for params
  File "/Users/developer/opt/anaconda3/lib/python3.7/site-packages/pymapd/cursor.py", line 111, in execute
    operation = str(_bind_parameters(operation, parameters))
  File "/Users/developer/opt/anaconda3/lib/python3.7/site-packages/pymapd/_parsers.py", line 226, in _bind_parameters
    .compile(compile_kwargs={"literal_binds": True}))
  File "<string>", line 1, in <lambda>
  File "/Users/developer/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 468, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "/Users/developer/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 474, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "/Users/developer/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 589, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "/Users/developer/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 319, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/Users/developer/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 350, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/Users/developer/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/visitors.py", line 92, in _compiler_dispatch
    return meth(self, **kw)
  File "/Users/developer/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 940, in visit_textclause
    do_bindparam, self.post_process_text(textclause.text)
  File "/Users/developer/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 929, in do_bindparam
    return self.process(textclause._bindparams[name], **kw)
  File "/Users/developer/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 350, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/Users/developer/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/visitors.py", line 92, in _compiler_dispatch
    return meth(self, **kw)
  File "/Users/developer/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 1506, in visit_bindparam
    "renderable value not allowed here." % bindparam.key
sqlalchemy.exc.CompileError: Bind parameter 'name' without a renderable value not allowed here.
Exception: Unexpected character: b'<'
#2

Hi @DavidLivingston,

I looked at the code in the driver that’s raising the exception you got when trying to use a None as a NULL.

  if literal_binds or (within_columns_clause and self.ansi_bind_rules):
        if bindparam.value is None and bindparam.callable is None:
            raise exc.CompileError(
                "Bind parameter '%s' without a "
                "renderable value not allowed here." % bindparam.key
            )
        return self.render_literal_bindparam(
            bindparam, within_columns_clause=True, **kwargs
        )

so to insert nulls woith executemany method that is using sqlalchemy to manage bind variables I managed to use NULL using the method sqlalchemy.null() instead of None.

Here is my code

import pymapd
import sqlalchemy

from pymapd import connect
uri = "mapd://admin:HyperInteractive@localhost:6274/omnisci?protocol=binary"

test_data_list = []

test_data_list.append( {'id' : 3, 'name' : sqlalchemy.null(), 'articles' : 1, 'createdAt' : '2020-04-02 01:02:23' } )
test_data_list.append( {'id' : 4, 'name' : 'NULL', 'articles' : 1, 'createdAt' : '2020-04-02 01:02:23' } )
test_data_list.append( {'id' : 2, 'name' : 'John', 'articles' : sqlalchemy.null(), 'createdAt' : '2020-04-02 01:02:23' } )
test_data_list.append( {'id' : 1, 'name' : 'Andy', 'articles' : 1, 'createdAt' : sqlalchemy.null()} )
con = connect(uri=uri)
cur = con.cursor()
cur_res = cur.executemany("INSERT INTO test_rp ( id, name, articles, createdAt) VALUES ( :id, :name, :articles, :createdAt)", test_data_list)

This is the result of the target table

omnisql> select * from test_rp;
id|name|articles|createdat
3|NULL|1|2020-04-02
4|NULL|1|2020-04-02
2|John|NULL|2020-04-02
1|Andy|1|NULL
4 rows returned.
Execution time: 254 ms, Total time: 254 ms

As you noticed, I also used a ‘NULL’ for strings, and it’s translated as NULL.

I’m not sure this is the right way to use the driver, but it is working; maybe someone else more experienced by me will end up with a better solution

#3

Hi @candido.dessanti,
Thanks for sharing the snippet and suggestion on sqlalchemy.null(), It works for me too. Btw passing ‘NULL’ value will only works for string data type, other than that it will fail.