Getting ValueError: <Column Name> (NVARCHAR) not a string in pandas.to_sql

Question

I am trying to insert a pandas dataframe in SingleStore. dtype of columns are either string/object. I am getting the below error for this piece of code.

Error :

ValueError: country (NVARCHAR(255)) not a string

Code :

# Insert whole DataFrame into MySQL
data_df.to_sql('gapmindertidy', con = conn1, if_exists = 'append', 
index = False, chunksize = 1000, dtype ={
 'country': sqlalchemy.types.NVARCHAR(length=255),
 'continent': sqlalchemy.types.NVARCHAR(length=255),
 'year': sqlalchemy.types.INTEGER(),
 'metric': sqlalchemy.types.NVARCHAR(length=255),
 'value': sqlalchemy.types.Float(precision=3, asdecimal=True)})

Column Types :

country       object
continent     object
year           int64
metric        object
value        float64
dtype: object

Answer 1

I've met the same problem.

Put engine object instead of connection object into to_sql()

In my example it was like:

engine = sqlalchemy.engine.create_engine(*my_connection_string*, echo=False)
connection = engine.raw_connection()
my_df.to_sql('tablename', con=connection, if_exists='append',
            dtype={'Product': String(32)})

The desicion looks like:

engine = sqlalchemy.engine.create_engine(*my_connection_string*, echo=False)
connection = engine.raw_connection()
my_df.to_sql('tablename', con=engine , if_exists='append',
            dtype={'Product': String(32)})