python - 没有密码 sqlalchemy 无法以 root 身份连接

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>

但是当我尝试执行 pything 脚本时:

# 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;