how to create sql connection using mysql connector to write to database via df.to_sql()

Issue

Is it possible to create engine using mySql connector instead of sqlalchemy? Most of the time people use sqlalchemy to create an engine and then use it to write to db using to_sql.

   engine = create_engine("mysql+pymysql://{user}:{pw}@localhost/{db}"
                                   .format(user="user",
                                           pw="Password",
                                           db="dbname"))
   df.to_sql(con=engine, name='Summary', if_exists='replace', index=False)

Solution

It is not possible. df.to_sql expects a SQLAlchemy engine as the connection:

con: sqlalchemy.engine.(Engine or Connection) or sqlite3.Connection

Using SQLAlchemy makes it possible to use any DB supported by that
library. Legacy support is provided for sqlite3.Connection objects.

You can use MySQL Connector as the connection dialect of the engine if you wish:

engine = create_engine("mysql+mysqlconnector://{user}:{pw}@localhost/{db}"
              .format(user="user",
                      pw="Password",
                      db="dbname"))

Answered By – snakecharmerb

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0