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
– user3896886, Commented Mar 23, 2018 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
– Konstantin, Commented Mar 24, 2018 at 20:33

Konstantin · Accepted Answer · 2018-03-24 20:32:49Z

1

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('-----')

edited Mar 24, 2018 at 20:32

answered Mar 22, 2018 at 10:44

Konstantin

2,6802 gold badges29 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

to_sql error: Unknown object type numpy.ndarray during describe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related