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...

Samsara

Answer 1

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')

Floppy

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

Newer version of mysqlconnector or different driver altogether? – Mccreery

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