如何使用Python+SQLAlchemy远程连接MySQL数据库?

2021-12-28 00:00:00 python sqlalchemy tcp ssh-tunnel mysql

我无法远程访问 MySQL.我使用SSH隧道，想用Python+SQLALchemy连接数据库MySQL.

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

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

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

但是，当我想使用 Python+SQLAchemy 连接到数据库时，我找不到像 —protocol=TCP 这样的选项否则，我只能连接到本地 MySQL 数据库.请告诉我，有没有办法使用 SQLAlchemy 做到这一点.

解决方案

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

<块引用>

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

后来:

<块引用>

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

<小时>

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

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

例如使用 PyMySQL，在我为此目的设置的测试系统(MariaDB 10.0.12、SQLAlchemy 0.9.8 和 PyMySQL 0.6.2)上，我得到了以下结果:<预><代码>>>>引擎 = 创建引擎("mysql+pymysql://sylvain:passwd@localhost/db?host=localhost?port=3306")#^^^^^^^^^^^^^^^^^^^^^^^^^^# 强制 TCP 套接字.注意`?`的两种用法# 通常 URL 选项应该使用 `?` 和 `&`# 在那之后.但这在这里不起作用(错误?)>>>conn = engine.connect()>>>conn.execute("SELECT host FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID = CONNECTION_ID()").fetchall()[('本地主机:54164'，)]# 使用 127.0.0.1 而不是 localhost 的结果相同:>>>引擎 = 创建引擎("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()[('本地主机:54164'，)]# 或者，使用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()[('本地主机:54353'，)]

如您所见，两者都将使用 TCP 连接(我知道这是因为主机名后面的端口号).另一方面:

<预><代码>>>>引擎 = 创建引擎("mysql+pymysql://sylvain:passwd@localhost/db?unix_socket=/path/to/mysql.sock")#^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^# 指定mysql.sock中的路径# `unix_socket` 选项将强制# UNIX 套接字的使用>>>conn = engine.connect()>>>conn.execute("SELECT host FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID = CONNECTION_ID()").fetchall()[('本地主机',)]# 使用 127.0.0.1 而不是 localhost 的结果相同:>>>引擎 = 创建引擎("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()[('本地主机',)]# 或者，使用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()[('本地主机',)]

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

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

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

I get access to remote database through SSH-tunnel.

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.

解决方案

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:

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

And later:

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.

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.

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.

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',)]

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',)]

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

相关文章