如何使用Python + SQLAlchemy远程连接MySQL数据库? [英] How to connect MySQL database using Python+SQLAlchemy remotely?

问题描述

我在远程访问MySQL时遇到困难.我使用SSH隧道，并希望使用Python + SQLALchemy连接数据库MySQL.

I am having difficulty accessing MySQL remotely. I use SSH tunnel and want to connect the database MySQL using Python+SQLALchemy.

当我在控制台中使用MySQL客户端并指定"ptotocol=TCP"时，一切都很好！ 我使用命令:

When i use MySQL-client in my console and specify "ptotocol=TCP", then everything is fine! I use command:

mysql -h localhost —protocol=TCP -u USER -p

我可以通过SSH隧道访问远程数据库.

I get access to remote database through SSH-tunnel.

但是，当我想使用Python + SQLAchemy连接到数据库时，找不到类似—protocol=TCP的选项 否则，我只能连接到本地MySQL数据库. 请告诉我，有没有一种方法可以使用SQLAlchemy.

However, when I want to connect to the database using the Python+SQLAchemy I can't find such option like —protocol=TCP Otherwise, i have only connect to local MySQL Databases. Tell me please, is there a way to do it using SQLAlchemy.

推荐答案

此问题的经典答案是使用127.0.0.1或主机的 IP 或主机名而不是特殊名称" localhost.从文档:

The classic answer to this issue is to use 127.0.0.1 or the IP of the host or the host name instead of the "special name" localhost. From the documentation:

在Unix上

[...]到 localhost 的连接默认情况下是使用Unix套接字文件进行的

[...] connections on Unix to localhost are made using a Unix socket file by default

后来:

在Unix上，MySQL程序特别对待主机名 localhost ，与其他基于网络的程序相比，它可能与您期望的方式有所不同.为了连接到本地主机，MySQL程序尝试使用Unix套接字文件连接到本地服务器.即使给--port或-P选项指定了端口号，也会发生这种情况.为了确保客户端与本地服务器建立TCP/IP连接，请使用--host或-h将主机名值指定为127.0.0.1，或者指定本地服务器的IP地址或名称.

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.

---

但是，这种简单的技巧似乎不适用于您的情况，因此您必须以某种方式 force 使用TCP套接字.正如您自己解释的那样，在命令行上调用mysql时，可以使用--protocol tcp选项.

---

However, this simple trick doesn't appear to work in your case, so you have to somehow force the use of a TCP socket. As you explained it yourself, when invoking mysql on the command line, you use the --protocol tcp option.

如此处所述，您可以通过SQLAlchemy传递使用connect_args关键字参数作为URL选项或与驱动程序相关的选项(如果有).

As explained here, from SQLAlchemy, you can pass the relevant options (if any) to your driver either as URL options or using the connect_args keyword argument.

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

For example using PyMySQL, on a test system I've setup for that purpose (MariaDB 10.0.12, SQLAlchemy 0.9.8 and PyMySQL 0.6.2) I got the following results:

>>> 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连接(我知道这是因为主机名后面的端口号).另一方面:

As you noticed, both will use a TCP connection (I know that because of the port number after the hostname). On the other hand:

>>> 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套接字.

No port after the hostname: this is an UNIX socket.

这篇关于如何使用Python + SQLAlchemy远程连接MySQL数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！