insert multiple columns in phoenix: python

Question

I created phoenix table like:

create table movement_record(
id varchar not null,
latitude float,
longitude float,
location varchar,
duration float,
start_time varchar not null,
end_time varchar not null,
CONSTRAINT pk PRIMARY KEY (id, start_time, end_time)
);

I want to write list of lists to above Phoenix table. List of Lists(final_output) data variables are as [[string,float,float,string,float,datetime,datetime],[---],--] I am tring to write data in Phoenix using below code:

    _phoenixdb = phoenixdb
    conn = _phoenixdb.connect(connection_string, autocommit=True)
    cursor = conn.cursor()
    for row in final_output:
        id=row[0]
        latitude=row[1]
        longitude=row[2]
        ignition_on=row[3]
        location=row[4]
        duration=row[5]
        start_time=row[6]
        end_time=row[7]
        sql = "UPSERT INTO movement_record VALUES (?,?,?,?,?,?,?,?)", (truck_id,latitude,longitude,ignition_on,location,duration,start_time,end_time)
        cursor.execute(sql)

I am getting error:

Traceback (most recent call last):
  File "main.py", line 61, in output_to_phoenix
    cursor.execute(sql)
  File "/usr/local/lib/python2.7/dist-packages/phoenixdb/cursor.py", line 201, in execute
    operation, first_frame_max_size=self.itersize)
  File "/usr/local/lib/python2.7/dist-packages/phoenixdb/avatica.py", line 399, in prepare_and_execute
    request.sql = sql
TypeError: ('UPSERT INTO movement_record VALUES (?,?,?,?,?,?,?,?)', (u'SRT1', -23.333999633789062, has type tuple, but expected one of: bytes, unicode

It looks like obvious solution, but I am not able to figure out what I am missing here. Help will be appreciated. Thanks.

Answer 1

Well, the first parameter cursor.execute is a string and the second one is a tuple of parameters. I'd suggest to change the code like this:

sql = "UPSERT INTO movement_record VALUES (?,?,?,?,?,?,?,?)"
params = (truck_id,latitude,longitude,ignition_on,location,duration,start_time,end_time)
cursor.execute(sql, params)

If you want to keep your pre-constructed arguments tuple, you can do this:

cursor.execute(*sql)

That will expand the tuple and pass the items as individual parameters.