Error Fetching Data: Shape of passed values is (1, 100), indices imply (2, 100)

[arpa007]

I am connecting to a SQL Server Table from Linux box. For the set of queries I have tried thus far, the meta information shows up just fine but queries always have this error:

I am using superset 0.22.1 and pandas 0.20.3 came bundled with the distribution.

2018-01-14 16:27:27,276:INFO:root:Fetching cursor description
2018-01-14 16:27:27,572:ERROR:root:Shape of passed values is (1, 100), indices imply (2, 100)
Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/superset/sql_lab.py", line 94, in get_sql_results
    ctask, query_id, return_results, store_results, user_name)
  File "/usr/local/lib/python2.7/dist-packages/superset/sql_lab.py", line 226, in execute_sql
    pd.DataFrame(list(data), columns=column_names))
  File "/usr/local/lib/python2.7/dist-packages/pandas/core/frame.py", line 330, in __init__
    copy=copy)
  File "/usr/local/lib/python2.7/dist-packages/pandas/core/frame.py", line 483, in _init_ndarray
    return create_block_manager_from_blocks([values], [columns, index])
  File "/usr/local/lib/python2.7/dist-packages/pandas/core/internals.py", line 4303, in create_block_manager_from_blocks
    construction_error(tot_items, blocks[0].shape[1:], axes, e)
  File "/usr/local/lib/python2.7/dist-packages/pandas/core/internals.py", line 4280, in construction_error
    passed, implied))
ValueError: Shape of passed values is (1, 100), indices imply (2, 100)

[arpa007]

something weird for sure is going on in sql_lab.py:226

i had a bit of my code snippet in here:
222 column_names = (
223 [col[0] for col in cursor_description] if cursor_description else [])
224 column_names = dedup(column_names)
225
226 mydata = pd.DataFrame(data, columns=column_names)
227 logging.info(mydata)
228
229 cdf = dataframe.SupersetDataFrame(
230 pd.DataFrame(list(data)), columns=column_names)

and sure enough the problem is now with mydata. for whatever reason pandas DataFrame seems to be having a problem with the data.

Surprisingly, when I use hard coded data everything works magically:
mydata = [(24, 2), (26, 3), (26, 9), (1081, 2), (1357, 10), (1783, 2), (1784, 2), (1785, 2), (1786, 2), (1788, 2), (1789, 2), (1790, 2), (1792, 2), (1801, 2), (1803, 2), (1804, 2), (1805, 2), (1806, 2), (1813, 2), (1814, 2), (1821, 2), (1822, 2), (1823, 2), (1824, 2), (1832, 2), (1834, 2), (1835, 2), (1836, 2), (1837, 2), (1838, 2), (1839, 2), (1841, 2), (1844, 2), (1845, 2), (1847, 2), (1848, 2), (1849, 2), (1850, 2), (1863, 2), (1864, 2), (1865, 2), (1866, 2), (1882, 2), (1883, 2), (1900, 2), (1911, 2), (1925, 2), (1992, 2), (2006, 2), (2038, 2), (2040, 2), (2056, 2), (2059, 2), (2071, 2), (2081, 2), (2102, 2), (2147, 2), (2161, 2), (2170, 2), (2179, 2), (2189, 2), (2200, 2), (2221, 2), (2244, 2), (2245, 2), (2246, 2), (3081, 10), (4278, 3), (4281, 4), (4282, 4), (4284, 1), (4326, 2), (4364, 2), (4372, 2), (4376, 2), (4407, 2), (4429, 2), (4435, 2), (4443, 2), (4447, 2), (4448, 2), (4465, 2), (4480, 2), (4488, 2), (4515, 2), (4527, 2), (4557, 2), (4585, 2), (4595, 2), (4624, 2), (4677, 2), (4679, 2), (4709, 2), (4721, 2), (4730, 2), (4758, 2), (4760, 2), (4772, 2), (4785, 2), (4790, 2)]
227
228 mydata = pd.DataFrame(mydata, columns=column_names)
229 logging.info(mydata)

Not sure what really is going in.

[arpa007]

interestingly, if i do this

import numpy as np

and then in line 230
cdf = ..(pandas.DataFrame(np.array(data), columns=column_names))

this works just fine. so instead of list i am using np.array and issue seems to be resolved

[bvanvooren]

@arpa007 - Are you able to explore MSSQL tables (via Tables/Slices)? I'm on a fresh install of 0.22.1, and I'm able to add a MSSQL table, but zero columns appear on the "List Columns" tab while editing the table.

I am able to verify connectivity to the SQL Server via sqlcmd on the Superset host (and fetch data from the table I'm using), and when I add the SQL Server as a datasource and click the "Test Connection" button, it correctly enumerates the tables. I am also able to interact with MySQL/PostgreSQL tables without issue.

I was thinking that issue might have to do with a general problem detecting MSSQL columns. On the other hand, in SQL Lab, when I select a table, the table definition appears correctly in the left-hand column, but the query fails with the "Shape of passed values..." error that is the subject of this issue.

Incidentally, it appears that this issue might be related to #3934

My config:

numpy==1.14.0
pandas==0.20.3
pyodbc==4.0.22
SQLAlchemy==1.1.9
superset==0.22.1

SQL Server 2008 R2
MS ODBC Driver for Linux 13.1

Running on a Docker image based on Ubuntu 16.04

[arpa007]

yes my workaround fixed the problem.
go to sql_lab.py and in line 226 make a call to numpy.array instead of list. i figured there is some problem with the return value a tuples from sql server. numpy converting it all to a 2d array works well.

[xrmx]

@arpa007 If you need to change the code the issue is not fixed

[bvanvooren]

I am also experiencing this issue, but the other symptom I mentioned above had a different cause. I opened issue #4271 with details.

[darylerwin]

Not sure what I did .. but now I get this error trying to preview or run a query in SQL Lab. It was working ...

2018-02-12 01:48:59,238:INFO:root:Fetching cursor description
2018-02-12 01:48:59,239:ERROR:root:Shape of passed values is (1, 100), indices imply (16, 100)
Traceback (most recent call last):
  File "/home/derwin/venv/local/lib/python2.7/site-packages/superset/sql_lab.py", line 94, in get_sql_results
    ctask, query_id, return_results, store_results, user_name)
  File "/home/derwin/venv/local/lib/python2.7/site-packages/superset/sql_lab.py", line 226, in execute_sql
    pd.DataFrame(list(data), columns=column_names))
  File "/home/derwin/venv/local/lib/python2.7/site-packages/pandas/core/frame.py", line 330, in __init__
    copy=copy)
  File "/home/derwin/venv/local/lib/python2.7/site-packages/pandas/core/frame.py", line 483, in _init_ndarray
    return create_block_manager_from_blocks([values], [columns, index])
  File "/home/derwin/venv/local/lib/python2.7/site-packages/pandas/core/internals.py", line 4303, in create_block_manager_from_blocks
    construction_error(tot_items, blocks[0].shape[1:], axes, e)
  File "/home/derwin/venv/local/lib/python2.7/site-packages/pandas/core/internals.py", line 4280, in construction_error
    passed, implied))
ValueError: Shape of passed values is (1, 100), indices imply (16, 100)
2018-02-12 01:48:59,247:DEBUG:root:[stats_logger] (incr) error_sqllab_unhandled
2018-02-12 01:48:59,345:ERROR:root:Shape of passed values is (1, 100), indices imply (16, 100)
Traceback (most recent call last):
  File "/home/derwin/venv/local/lib/python2.7/site-packages/superset/views/core.py", line 2188, in sql_json
    query_id=query_id, return_results=True)
  File "/home/derwin/venv/local/lib/python2.7/site-packages/celery/local.py", line 191, in __call__
    return self._get_current_object()(*a, **kw)
  File "/home/derwin/venv/local/lib/python2.7/site-packages/celery/app/task.py", line 380, in __call__
    return self.run(*args, **kwargs)
  File "/home/derwin/venv/local/lib/python2.7/site-packages/superset/sql_lab.py", line 94, in get_sql_results
    ctask, query_id, return_results, store_results, user_name)
  File "/home/derwin/venv/local/lib/python2.7/site-packages/superset/sql_lab.py", line 226, in execute_sql
    pd.DataFrame(list(data), columns=column_names))
  File "/home/derwin/venv/local/lib/python2.7/site-packages/pandas/core/frame.py", line 330, in __init__
    copy=copy)
  File "/home/derwin/venv/local/lib/python2.7/site-packages/pandas/core/frame.py", line 483, in _init_ndarray
    return create_block_manager_from_blocks([values], [columns, index])
  File "/home/derwin/venv/local/lib/python2.7/site-packages/pandas/core/internals.py", line 4303, in create_block_manager_from_blocks
    construction_error(tot_items, blocks[0].shape[1:], axes, e)
  File "/home/derwin/venv/local/lib/python2.7/site-packages/pandas/core/internals.py", line 4280, in construction_error
    passed, implied))
ValueError: Shape of passed values is (1, 100), indices imply (16, 100)

[UPDATE] Grabbing the latest version of sql_lab.py gets around this issue.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.