How do I get sqlalchemy.create_engine with mysqlconnector to connect using mysql_native_password?

Question

I'm working with pandas and sqlalchemy, and would like to load a DataFrame into a MySQL database. I'm currently using this code snippet:

db_connection = sqlalchemy.create_engine('mysql+mysqlconnector://user:pwd@hostname/db_name')

some_data_ref.to_sql(con=db_connection, name='db_table_name', if_exists='replace')

sqlalchemy, pandas have been imported prior to this.

My MySQL backend is 8.x, which I know uses caching_sha2_password. If I were to connect to the database using mysql.connector.connect and I want to use the mysql_native_password method, I know that I should specify auth_plugin = mysql_native_password like so:

mysql.connector.connect(user=user, password=pw, host=host, database=db, auth_plugin='mysql_native_password')

My question: Is there a way to force mysql_native_password authentication with sqlalchemy.create_engine('mysql+mysqlconnector://...)?

Any advice on this would be much appreciated...

Ilja Everilä · Accepted Answer · 2018-08-10 15:24:05Z

13

You could use connect_args:

db_connection = sqlalchemy.create_engine(
    'mysql+mysqlconnector://user:pwd@hostname/db_name',
    connect_args={'auth_plugin': 'mysql_native_password'})

or the URL query:

db_connection = sqlalchemy.create_engine(
    'mysql+mysqlconnector://user:pwd@hostname/db_name?auth_plugin=mysql_native_password')

answered Aug 10, 2018 at 15:24

Ilja Everilä

53.4k9 gold badges138 silver badges142 bronze badges

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

3 Comments

Caleb Jay Over a year ago

Any idea why I'd get an got an unexpected keyword argument 'auth_plugin' error when following these instructions?

Ilja Everilä Over a year ago

Newer version of mysqlconnector or different driver altogether?

Amir Almusawi Over a year ago

What made this answer work for me with mysql 5.7 was to drop pymsql and then pip3 install mysqlclient

Collectives™ on Stack Overflow

How do I get sqlalchemy.create_engine with mysqlconnector to connect using mysql_native_password?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related