to_sql error: Unknown object type numpy.ndarray during describe

Question

I am trying to insert a dataframe into a sql table and I get the following error

ProgrammingError: (pyodbc.ProgrammingError) ('Unknown object type numpy.ndarray during describe', 'HY000')

for the following code

merged.to_sql('pmg.cwc.EmSignals1', engine, chunksize=1000, 
           if_exists='replace', 
           index=False, 
            dtype ={'monthenddate': sqlalchemy.types.NVARCHAR(length=20),                                                                                                       
           'lastweekday': sqlalchemy.types.NVARCHAR(length=20),
           'item': sqlalchemy.types.NVARCHAR(length=20),
           'sols': sqlalchemy.types.NVARCHAR(length=20),
           'value': sqlalchemy.types.NVARCHAR(length=20)})

This is a .head of the dataframe

monthenddate lastweekday item sols value

0 1999-12-31 1999-12-31 value 2W063W1 -0.870225

1 1999-12-31 1999-12-31 value W1YBRK4 0.078154

2 1999-12-31 1999-12-31 value X16W902 -0.072731

3 1999-12-31 1999-12-31 value 2X45X4W 1.278582

4 1999-12-31 1999-12-31 value 23X1XWX 0.293649

I have tried a lot, but cannot figure out the cause of the issue.

Int64Index: 710719 entries, 0 to 710718 Data columns (total 5 columns): monthenddate 710719 non-null object lastweekday 710719 non-null object item 710719 non-null object sedols 710719 non-null object value 710719 non-null float64 dtypes: float64(1), object(4) memory usage: 32.5+ MB None — user3896886, Mar 23 '18 at 16:02
I've added some more code to my answer that you can use to check what are the types of data in your columns. — Konstantin, Mar 24 '18 at 20:33

Konstantin · Answer 1 · 2018-03-24T20:32:49.403

I assume that for some reason, one of you columns contains numpy arrays. You can verify using print(merged.info()).

If that's the case, check the statements, where you assign data to the DataFrame.

Edit: as print(merged.info()) shows several object columns, you still don't know which of them might be numpy arrays. Try this code to dig deeper:

for el in merged.iloc[0, :]:
    print('Checking: {:s}.'.format(str(el)))
    print(isinstance(el, np.generic))
    try:
        print(el.shape)
        print('Is a NumPy array.')
    except:
        print('Is not a NumPy array.')
    finally:
        print('-----')

to_sql error: Unknown object type numpy.ndarray during describe

1 Answers1