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



  • Avatar
    Candido Dessanti

    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;
    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

    Comment actions Permalink
  • Avatar
    David Livingston

    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. [quote="candido.dessanti, post:2, topic:2338"] sqlalchemy.null() [/quote]

    Comment actions Permalink

Please sign in to leave a comment.