Pymapd error with FLOAT[] data type

Hi Experts,

I created a table with FLOAT as a column. but I am not able to query FLOAT data from pymapd. Please see the error below.

query = “SELECT * FROM samplevector limit 100”
df = con.select_ipc(query)
Traceback (most recent call last):
File “”, line 1, in
File “/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/connection.py”, line 316, in select_ipc
first_n=first_n
File “/home/ubuntu/anaconda3/lib/python3.7/site-packages/mapd/MapD.py”, line 1560, in sql_execute_df
return self.recv_sql_execute_df()
File “/home/ubuntu/anaconda3/lib/python3.7/site-packages/mapd/MapD.py”, line 1588, in recv_sql_execute_df
raise result.e
mapd.ttypes.TMapDException: TMapDException(error_msg=‘Exception: FLOAT is not supported in Arrow result sets.’)

Best,
Zipeng

Hi @zipeng, sorry to hear you’re having trouble. What version of pymapd and OmniSciDB are you using?

OmniSciDB: 5.1.2 CPU Ubuntu.
pymapd ==0.20.0

Additional info, I am able to use omnisql to query the table with float array, but failed with pymapd.

Best,
Zipepng

Now that I re-look at your question, I see that you are trying to use a FLOAT array. This in fact does not work, due to some Arrow compatibility issues. Currently, the work-around is to use either con.execute() to return the results as a list of tuples, or you can use pandas df = pd.read_sql("select * from...", con) to get a dataframe.

Version 5.2 of OmniSci should be using Arrow 0.16, and we’ll try and get the Arrow compatibility issues straightened out.

unfortunatelly still not working.

df = pd.read_sql(query,con)
Traceback (most recent call last):
File “”, line 1, in
File “/home/ubuntu/anaconda3/lib/python3.7/site-packages/pandas/io/sql.py”, line 410, in read_sql
chunksize=chunksize,
File “/home/ubuntu/anaconda3/lib/python3.7/site-packages/pandas/io/sql.py”, line 1658, in read_query
data = self._fetchall_as_list(cursor)
File “/home/ubuntu/anaconda3/lib/python3.7/site-packages/pandas/io/sql.py”, line 1671, in _fetchall_as_list
result = cur.fetchall()
File “/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/cursor.py”, line 170, in fetchall
return list(self)
File “/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/cursor.py”, line 206, in make_row_results_set
yield tuple(columns[j][i] for j in range(ncols))
File “/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/cursor.py”, line 206, in
yield tuple(columns[j][i] for j in range(ncols))
IndexError: list index out of range

I also tried Cursors.

c.execute(query)
<pymapd.cursor.Cursor object at 0x7f87dfdf8890>

c.rowcount
2

result=c.fetchall()
Traceback (most recent call last):
File “”, line 1, in
File “/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/cursor.py”, line 170, in fetchall
return list(self)
File “/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/cursor.py”, line 206, in make_row_results_set
yield tuple(columns[j][i] for j in range(ncols))
File “/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/cursor.py”, line 206, in
yield tuple(columns[j][i] for j in range(ncols))
IndexError: list index out of range

c.fetchall()

result=c.fetchall()
len(result)
0

seems like the Cursors can not fetch the data. the c.rowcount is correct but no data.

Best,
Zipeg

For this simple example, it seems to work.

In [1]: import pymapd
   ...: import pandas as pd
   ...: 
   ...: con = pymapd.connect(user="admin", password="HyperInteractive", host="localhost")
   ...: 
   ...: con.execute("DROP TABLE IF EXISTS test_lists;")
   ...: con.execute("CREATE TABLE IF NOT EXISTS test_lists \
   ...:             (col1 TEXT, col2 FLOAT[]);")
   ...: 
   ...: row = [("row1", "{1.23,3.66898,15.8765}"),
   ...:        ("row2", "{19874.0,10,3983.3432}")]
   ...: 
   ...: con.load_table_rowwise("test_lists", row)

In [2]: ans = con.execute("select * from test_lists").fetchall()

In [3]: ans
Out[3]: 
[('row1', [1.2300000190734863, 3.6689798831939697, 15.876500129699707]),
 ('row2', [19874.0, 10.0, 3983.34326171875])]

In [4]: df = pd.read_sql("select * from test_lists", con)

In [5]: df
Out[5]: 
   col1                                               col2
0  row1  [1.2300000190734863, 3.6689798831939697, 15.87...
1  row2                  [19874.0, 10.0, 3983.34326171875]

In [6]: 

Does your FLOAT[] column have nulls or any other special values?

Hi Randy,

I tried your code.still getting error:

uri = “mapd://admin:HyperInteractive@localhost:6274/omnisci?protocol=binary”
con = pymapd.connect(uri=uri)
con.execute(“DROP TABLE IF EXISTS test_lists;”)
<pymapd.cursor.Cursor object at 0x7f87dc1d8410>

con.execute(“CREATE TABLE IF NOT EXISTS test_lists (col1 TEXT, col2 FLOAT);”)
<pymapd.cursor.Cursor object at 0x7f87dc1ea750>

row = [(“row1”, “{1.23,3.66898,15.8765}”), (“row2”, “{19874.0,10,3983.3432}”)]
con.load_table_rowwise(“test_lists”, row)
ans = con.execute(“select * from test_lists”).fetchall()
Traceback (most recent call last):
File “”, line 1, in
File “/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/cursor.py”, line 170, in fetchall
return list(self)
File “/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/cursor.py”, line 206, in make_row_results_set
yield tuple(columns[j][i] for j in range(ncols))
File “/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/cursor.py”, line 206, in
yield tuple(columns[j][i] for j in range(ncols))
IndexError: list index out of range

By the way, I have another strange error maybe related.

I am not able to connect with
con = pymapd.connect(user=“admin”, password=“HyperInteractive”, host=“localhost”)

strangly it automaticall try to connect to port 9091.

Could not connect to any of [(‘127.0.0.1’, 9091)]
Traceback (most recent call last):
File “/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/connection.py”, line 160, in init
self._transport.open()
File “/home/ubuntu/anaconda3/lib/python3.7/site-packages/thrift/transport/TTransport.py”, line 153, in open
return self.__trans.open()
File “/home/ubuntu/anaconda3/lib/python3.7/site-packages/thrift/transport/TSocket.py”, line 113, in open
raise TTransportException(TTransportException.NOT_OPEN, msg)
thrift.transport.TTransport.TTransportException: Could not connect to any of [(‘127.0.0.1’, 9091)]

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File “”, line 1, in
File “/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/connection.py”, line 71, in connect
port=port, dbname=dbname, protocol=protocol)
File “/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/connection.py”, line 164, in init
six.raise_from(err, e)
File “”, line 3, in raise_from
pymapd.exceptions.OperationalError: Could not connect to database

Best,
Zipeng

I wonder if you have a path issue of some kind. In version 0.8 of pymapd, we switched the default of port to 6274, so if your code tries to connect to 9091, you might have an install issue.

Hi Randy,

Thanks a lot! You are right. it is something with my Python environment. I cleaned up my Python env and it works.
Really Appreciate your help!

Best,
Zipeng

1 Like