pandas dataframe to mysql db error database flavor mysql is not supported

Question

I have a table in pandas dataframe df.

   product_id_x    product_id_y    count       date
0   288472           288473          1     2016-11-08 04:02:07
1   288473           2933696         1     2016-11-08 04:02:07
2   288473           85694162        1     2016-11-08 04:02:07

i want to save this table in mysql database.

i am using MySQLdb package.

import MySQLdb
conn = MySQLdb.connect(host="xxx.xxx.xx.xx", user="name", passwd="pwd", db="dbname")

df.to_sql(con = conn, name = 'sample_insert', if_exists = 'append', flavor = 'mysql', index = False)

i used this query to put it in my db.

but i am getting error.

ValueError: database flavor mysql is not supported

my datatype is str for all the columns.

type(df['product_id_x'][0]) = str
type(df['product_id_y'][0]) = str
type(df['count'][0])        = str
type(df['date'][0])         = str

i don't want to use sqlalchemy or other packages, can anyone tell what's the error here. Thanks in advance

Marine · Accepted Answer · 2017-02-10 13:05:17Z

11

The flavor 'mysql' is deprecated in pandas version 0.19. You have to use the engine from sqlalchemy to create the connection with the database.

from sqlalchemy import create_engine
engine = create_engine("mysql+mysqldb://USER:"+'PASSWORD'+"@localhost/DATABASE")
df.to_sql(con=engine, if_exists='append', index=False)

When defining your engine, you need to specify your user, password, host and database. In your specific case, this should look like:

engine = create_engine("mysql+mysqldb://name:[email protected]/dbname")

answered Feb 10, 2017 at 13:05

Marine

4085 silver badges11 bronze badges

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

2 Comments

jgomo3 Over a year ago

to_sql require a positional argument of name

Shams Over a year ago

pip install MySql-Python pip install SQLAlchemy

gold_cy · Accepted Answer · 2016-11-28 14:10:53Z

0

It appears the option you are using for flavor no longer exists. A brief look at the docs confirms this:

flavor : ‘sqlite’, default None

DEPRECATED: this parameter will be removed in a future version, as ‘sqlite’ is the only supported option if SQLAlchemy is not installed.

answered Nov 28, 2016 at 14:10

gold_cy

14.2k4 gold badges27 silver badges55 bronze badges

Collectives™ on Stack Overflow

pandas dataframe to mysql db error database flavor mysql is not supported

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related