MySql, OperationalError, access denied when non-alphanumeric characters in password.

Issue #1564 resolved
Former user created an issue

I was doing something like this:

mysql = create_engine('mysql://user:%restofthepassword@host:3306/db')
result = mysql.execute("select * from some_table")

And I used to get following error:

(...)
connection = self.contextual_connect(close_with_result=True)
  File "C:\install\Python26\lib\site-packages\sqlalchemy-0.5.5-py2.6.egg\sqlalchemy\engine\base.py", line 1229, in contextual_connect
    return self.Connection(self, self.pool.connect(), close_with_result=close_with_result, **kwargs)
  File "C:\install\Python26\lib\site-packages\sqlalchemy-0.5.5-py2.6.egg\sqlalchemy\pool.py", line 142, in connect
    return _ConnectionFairy(self).checkout()
  File "C:\install\Python26\lib\site-packages\sqlalchemy-0.5.5-py2.6.egg\sqlalchemy\pool.py", line 304, in __init__
    rec = self._connection_record = pool.get()
  File "C:\install\Python26\lib\site-packages\sqlalchemy-0.5.5-py2.6.egg\sqlalchemy\pool.py", line 161, in get
    return self.do_get()
  File "C:\install\Python26\lib\site-packages\sqlalchemy-0.5.5-py2.6.egg\sqlalchemy\pool.py", line 639, in do_get
    con = self.create_connection()
  File "C:\install\Python26\lib\site-packages\sqlalchemy-0.5.5-py2.6.egg\sqlalchemy\pool.py", line 122, in create_connection
    return _ConnectionRecord(self)
  File "C:\install\Python26\lib\site-packages\sqlalchemy-0.5.5-py2.6.egg\sqlalchemy\pool.py", line 198, in __init__
    self.connection = self.__connect()
  File "C:\install\Python26\lib\site-packages\sqlalchemy-0.5.5-py2.6.egg\sqlalchemy\pool.py", line 261, in __connect
    connection = self.__pool._creator()
  File "C:\install\Python26\lib\site-packages\sqlalchemy-0.5.5-py2.6.egg\sqlalchemy\engine\strategies.py", line 80, in connect
    raise exc.DBAPIError.instance(None, None, e)
sqlalchemy.exc.OperationalError: (OperationalError) (1045, "Access denied for user 'user'@'host' (using password: YES)") None None

I had no problem connecting with this password either using console or php before. I was also trying to connect using MySqldb (mysql-python) directly and it worked. Then I find somewhere that somebody had similar problem with '$' in the password. So I removed '%' from password and it started to work normally.

Sorry if the problem is solved or I'm doing something wrong, but I spend few hours looking for a solution of this problems and was quite surprised to discover that it is caused by some character in password.

Comments (3)

  1. Former user Account Deleted

    Forget to mentioned about my environment: - windows xp - python 2.6 - sqlalchemy-0.5.5 - mysqldb 1.2.3rc1

  2. Mike Bayer repo owner

    the password field to create_engine() needs to be URL escaped if any special characters are present. see http://stackoverflow.com/questions/1423804/writing-a-connection-string-when-password-contains-special-characters for some discussion on this.

    alternatively you can specify a URL object directly:

    from sqlalchemy.engine.url import URL
    
    create_engine(URL('mysql', username='user', password='password', database='mydatabase', host='localhost'))
    
  3. Log in to comment