sqlalchemy连接mysql_如何使用Python+SQLAlchemy远程连接MySQL数据库？

这个问题的经典答案是使用主机的127.0.0.1或IP或主机名而不是“特殊名称”localhost。从documentation：[...] connections on Unix to localhost are made using a Unix socket file by default

之后：On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file. This occurs even if a --port or -P option is given to specify a port number. To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a host name value of 127.0.0.1, or the IP address or name of the local server.

然而，这个简单的技巧在您的情况下似乎不起作用，因此您必须以某种方式强制使用TCP套接字。正如您自己所解释的，在命令行上调用mysql时，使用--protocol tcp选项。

如here所述，从SQLAlchemy中，可以使用connect_args关键字参数将相关选项(如果有的话)作为URL选项或传递给驱动程序。

例如，使用PyMySQL，在为此目的而设置的测试系统(MariaDB 10.0.12、SQLAlchemy 0.9.8和PyMySQL 0.6.2)上，我得到了以下结果：>>> engine = create_engine(

"mysql+pymysql://sylvain:passwd@localhost/db?host=localhost?port=3306")

# ^^^^^^^^^^^^^^^^^^^^^^^^^^

# Force TCP socket. Notice the two uses of `?`

# Normally URL options should use `?` and `&`

# after that. But that doesn't work here (bug?)

>>> conn = engine.connect()

>>> conn.execute("SELECT host FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID = CONNECTION_ID()").fetchall()

[('localhost:54164',)]

# Same result by using 127.0.0.1 instead of localhost:

>>> engine = create_engine(

"mysql+pymysql://sylvain:passwd@127.0.0.1/db?host=localhost?port=3306")

>>> conn = engine.connect()

>>> conn.execute("SELECT host FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID = CONNECTION_ID()").fetchall()

[('localhost:54164',)]

# Alternatively, using connect_args:

>>> engine = create_engine("mysql+pymysql://sylvain:passwd@localhost/db",

connect_args= dict(host='localhost', port=3306))

>>> conn = engine.connect()

>>> conn.execute("SELECT host FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID = CONNECTION_ID()").fetchall()

[('localhost:54353',)]

正如您所注意到的，两者都将使用TCP连接(我知道这是因为主机名后面的端口号)。另一方面：>>> engine = create_engine(

"mysql+pymysql://sylvain:passwd@localhost/db?unix_socket=/path/to/mysql.sock")

# ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

# Specify the path to mysql.sock in

# the `unix_socket` option will force

# usage of a UNIX socket

>>> conn = engine.connect()

>>> conn.execute("SELECT host FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID = CONNECTION_ID()").fetchall()

[('localhost',)]

# Same result by using 127.0.0.1 instead of localhost:

>>> engine = create_engine(

"mysql+pymysql://sylvain:passwd@127.0.0.1/db?unix_socket=/path/to/mysql.sock")

>>> conn = engine.connect()

>>> conn.execute("SELECT host FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID = CONNECTION_ID()").fetchall()

[('localhost',)]

# Alternatively, using connect_args:

>>> engine = create_engine("mysql+pymysql://sylvain:passwd@localhost/db",

connect_args= dict(unix_socket="/path/to/mysql.sock"))

>>> conn = engine.connect()

>>> conn.execute("SELECT host FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID = CONNECTION_ID()").fetchall()

[('localhost',)]

主机名后没有端口：这是一个UNIX套接字。