在学习sqlalchemy时，刚连数据库就遇到了报错，各种尝试，百度，谷歌，stackoverflow 都查了个遍，竟然还没解决，那叫一个无语。废话到此为止，上代码：
- 环境：python 3.6, pymysql 0.9.3 sqlalchemy 1.3.15 mariadb 10.2.30-MariaDB(在vm虚拟机)
from sqlalchemy import create_engine
import pymysql
import threading
lock = threading.Lock()
HOSTNAME = '198.23.200.xx'
PORT = '3306'
DATABASE = 'first_sqlalchemy'
USERNAME = 'root'
PASSWORD = 'xxxx'
DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8".format(username=USERNAME,
password=PASSWORD,
host=HOSTNAME, port=PORT,
db=DATABASE)
engine = create_engine(DB_URI, convert_unicode=True, connect_args=dict(use_unicode=True))
conn = engine.connect()
res = conn.execute('select 1')
print(res.fetchone())
conn.close()
运行后报错：
Traceback (most recent call last):
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2285, in _wrap_pool_connect
return fn()
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 303, in unique_connection
return _ConnectionFairy._checkout(self)
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 773, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 492, in checkout
rec = pool._do_get()
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 139, in _do_get
self._dec_overflow()
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 69, in __exit__
exc_value, with_traceback=exc_tb,
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
raise exception
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 136, in _do_get
return self._create_connection()
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 308, in _create_connection
return _ConnectionRecord(self)
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 437, in __init__
self.__connect(first_connect_check=True)
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 657, in __connect
pool.logger.debug("Error on connect(): %s", e)
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 69, in __exit__
exc_value, with_traceback=exc_tb,
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
raise exception
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 652, in __connect
connection = pool._invoke_creator(self)
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py", line 114, in connect
return dialect.connect(*cargs, **cparams)
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 488, in connect
return self.dbapi.connect(*cargs, **cparams)
File "/opt/envs/flask_env/lib/python3.6/site-packages/pymysql/__init__.py", line 94, in Connect
return Connection(*args, **kwargs)
File "/opt/envs/flask_env/lib/python3.6/site-packages/pymysql/connections.py", line 325, in __init__
self.connect()
File "/opt/envs/flask_env/lib/python3.6/site-packages/pymysql/connections.py", line 598, in connect
self._get_server_information()
File "/opt/envs/flask_env/lib/python3.6/site-packages/pymysql/connections.py", line 975, in _get_server_information
packet = self._read_packet()
File "/opt/envs/flask_env/lib/python3.6/site-packages/pymysql/connections.py", line 671, in _read_packet
% (packet_number, self._next_seq_id))
pymysql.err.InternalError: Packet sequence number wrong - got 1 expected 0
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "sqlalchemy_demo.py", line 15, in <module>
conn = engine.connect()
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2218, in connect
return self._connection_cls(self, **kwargs)
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 103, in __init__
else engine.raw_connection()
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2318, in raw_connection
self.pool.unique_connection, _connection
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2289, in _wrap_pool_connect
e, dialect, self
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1555, in _handle_dbapi_exception_noconnection
sqlalchemy_exception, with_traceback=exc_info[2], from_=e
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
raise exception
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2285, in _wrap_pool_connect
return fn()
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 303, in unique_connection
return _ConnectionFairy._checkout(self)
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 773, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 492, in checkout
rec = pool._do_get()
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 139, in _do_get
self._dec_overflow()
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 69, in __exit__
exc_value, with_traceback=exc_tb,
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
raise exception
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 136, in _do_get
return self._create_connection()
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 308, in _create_connection
return _ConnectionRecord(self)
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 437, in __init__
self.__connect(first_connect_check=True)
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 657, in __connect
pool.logger.debug("Error on connect(): %s", e)
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 69, in __exit__
exc_value, with_traceback=exc_tb,
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
raise exception
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 652, in __connect
connection = pool._invoke_creator(self)
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py", line 114, in connect
return dialect.connect(*cargs, **cparams)
File "/opt/envs/flask_env/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 488, in connect
return self.dbapi.connect(*cargs, **cparams)
File "/opt/envs/flask_env/lib/python3.6/site-packages/pymysql/__init__.py", line 94, in Connect
return Connection(*args, **kwargs)
File "/opt/envs/flask_env/lib/python3.6/site-packages/pymysql/connections.py", line 325, in __init__
self.connect()
File "/opt/envs/flask_env/lib/python3.6/site-packages/pymysql/connections.py", line 598, in connect
self._get_server_information()
File "/opt/envs/flask_env/lib/python3.6/site-packages/pymysql/connections.py", line 975, in _get_server_information
packet = self._read_packet()
File "/opt/envs/flask_env/lib/python3.6/site-packages/pymysql/connections.py", line 671, in _read_packet
% (packet_number, self._next_seq_id))
sqlalchemy.exc.InternalError: (pymysql.err.InternalError) Packet sequence number wrong - got 1 expected 0
(Background on this error at: http://sqlalche.me/e/2j85)
然后就是上面的各种查阅资料，这里真是无力吐槽，尤其是国内的csdn这种，经常就一个答案，连事情的前后经过，代码，报错都没有，直接一句文字描述，然后就一答案。 最后仍然没有解决，我就直接用pymysql 去连接：
#数据部分共用的上面的
conn = pymysql.connect(HOSTNAME, USERNAME, PASSWORD, DATABASE)
cur = conn.cursor()
lock.acquire()
cur.execute('select 1;')
lock.release()
print(cur.fetchone())
cur.close()
conn.close()
依然不行，此时我才意识到，数据库可能没有对外网开放，然后ssh上数据库，执行了下面的操作
mysql>use mysql;
mysql>update user set host = '%' where user ='root';
mysql>select host, user from user;
mysql>flush privileges;
#执行上面这些操作，仍然无法连接
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'admin123' WITH GRANT OPTION;
mysql>flush privileges;
#此时报错：ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY' ，但再连接数据库已经可以连接了。
