无法在没有密码的情况下以root身份连接 sqlalchemy

Question

我在用Python连接MySQL时遇到了问题，不能不提供密码。

我可以通过命令行的mysql客户端，以root用户身份连接MySQL服务器，而且不需要密码：

# mysql -u root -h localhost

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 5.5.37-0ubuntu0.12.04.1 (Ubuntu)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

但是当我尝试执行一个Python脚本时：

# cat tomas.py
import sqlalchemy
engine = sqlalchemy.create_engine("mysql://root:@localhost:3306",echo=True)
connection = engine.connect()

我收到了这个错误：

# python tomas.py
Traceback (most recent call last):
  File "tomas.py", line 3, in <module>
    connection = engine.connect()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1709,     in connect
    return self._connection_cls(self, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 59, in     __init__
    self.__connection = connection or engine.raw_connection()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1778,     in raw_connection
    return self.pool.unique_connection()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 273, in     unique_connection
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 626, in     _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 433, in     checkout
    rec = pool._do_get()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 945, in     _do_get
    return self._create_connection()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 278, in     _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 404, in     __init__
    self.connection = self.__connect()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 527, in     __connect
    connection = self.__pool._creator()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/strategies.py", line     95, in connect
    connection_invalidated=invalidated
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 185, in     raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/strategies.py", line     89, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 376,     in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 187, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
sqlalchemy.exc.OperationalError: (OperationalError) (1045, "Access denied for user     'root'@'localhost' (using password: NO)") None None



mysql> show grants;
+--------------------------------------------------------------------------------------    --------------------------------------------------+
| Grants for root@localhost                                                                                                                  |
+--------------------------------------------------------------------------------------    --------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD     '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                               |
+--------------------------------------------------------------------------------------    --------------------------------------------------+
2 rows in set (0.00 sec)

有没有什么建议？

附注：我不能修改连接字符串，因为它是写死在我使用的脚本里的。

Answer 1

你可以试试这个：

在 MySQL 控制台中：

use mysql;
update user set password=null where User='root' and Host='localhost';
flush privileges;

如果你想要更改它：

use mysql;
update user set password=password('mynewpassword') where User='root';
flush privileges;