1

I have been searching SO and the web for days trying to solve this issue. There are plenty of similar questions on SO but none of them seem to be the same issue that I am dealing with currently. I am trying to connect to a remote MySQL database in python to use the pandas.to_sql feature to update tables. I am able to connect with MySQL workbench without any issue. I am also able to connect using the following code:

self.conn = mysql.connector.connect(host= remote_host,user= db_user, password=db_pass)

This allows me to query the database just fine in python. However, in order to use the pandas.to_sql I need to create and engine with sqlalchemy. I have tried multiple different ports and even leaving the port off. Here is the code I have tried:

self.engine = create_engine("mysql+mysqlconnector://db_user:db_pass@remote_host:3306/db_name")

They all give me the following error

ProgrammingError: (mysql.connector.errors.ProgrammingError) 1045 (28000): Access denied for user 'db_user'@'c-67-176-125-82.hsd1.co.comcast.net' (using password: YES) (Background on this error at: http://sqlalche.me/e/f405)

I can use the create_engine to connect to a local database but just not a remote database.

I have tried restarting MySQL on the server, I have granted all privileges to the user and flushed all the privileges on the server. I have even tried using the root user and password with no success.

I double checked the username and password, and of course know they are correct as they are the ones I use in MySQL Workbench, plus when I just use mysql.connector.connect

I even tried uninstalling mysql-connector-python and reinstalling it.

Any help would be greatly appreciated.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Alstar158
  • 71
  • 1
  • 9
  • I would assume this is something you need to speak to the db admins about. It looks like you have restricted privileges. I don't know exactly how MySQL works but I assume the connection string tries to establish a connection that you're not authorised to make – roganjosh Feb 10 '20 at 18:46
  • 1
    Does the actual password contain any characters that might confuse the issue (things like space, `:`, `/`, `@`, `?`, `&`, ...) and could require that the string be URL-encoded? – Gord Thompson Feb 10 '20 at 19:40
  • There are symbols in the password like $,),&,[,{,%,]. The URL-encoding worked! If you want to post answer I will accept! Thank you so much! – Alstar158 Feb 10 '20 at 19:55

1 Answers1

2

If any of the constituent parts of the database URL might contain special characters that would potentially confuse the URL parser it is best to have sqlalchemy.engine.URL.create() construct the URL for you. For example:

import sqlalchemy as sa

connection_url = sa.engine.URL.create(drivername="mysql+pymysql",
                                   username="db_user",
                                   password="my:password",
                                   host="localhost",
                                   port="3307",
                                   database="mydb",
                                   )
print(connection_url)
# mysql+pymysql://db_user:my%3Apassword@localhost:3307/mydb

Notice that the colon character in the password is percent-encoded as %3A.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418