How to write JSON column to MySQL using pandas.to_sql?

Question

I struggled quite a while trying to save into MySQL a table containing JSON columns, using SQLAlchemy and pandas' to_sql.

I got this error

sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) Failed processing pyformat-parameters; Python 'list' cannot be converted to a MySQL type

when trying to do it the standard way below. I have a pandas dataframe (df) where I have two columns containing JSONs ('videos', and 'newsitems').

import sqlalchemy
connection = sqlalchemy.create_engine(f"mysql+mysqlconnector://{user}:{pw}@{host}/{db}")
table_name = 'TABLENAME'
df.to_sql(table_name, 
          connection, 
          if_exists='append', 
          index=False)

I'm getting an error with my json column "scans": ValueError: scans (<class 'sqlalchemy.sql.sqltypes.JSON'>) not a string — Raksha
– Raksha, Commented Jul 31, 2023 at 17:25

MattiH · Accepted Answer · 2022-01-02 09:06:40Z

1

Now I found a response elsewhere, about postgresql - which works as finely with MySQL. Adding the last two lines dtype={...} fixed the issue

import sqlalchemy
connection = sqlalchemy.create_engine(f"mysql+mysqlconnector://{user}:{pw}@{host}/{db}")
table_name = 'TABLENAME'
df.to_sql(table_name, 
          connection, 
          if_exists='append', 
          index=False,
          dtype={'videos': sqlalchemy.types.JSON,
                 'newsitems': sqlalchemy.types.JSON})

The original Q&A that helped me solve the issue was this

Writing JSON column to Postgres using Pandas .to_sql

answered Jan 2, 2022 at 9:06

MattiH

6646 silver badges10 bronze badges

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

Collectives™ on Stack Overflow

How to write JSON column to MySQL using pandas.to_sql?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related