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

How to connect MySQL database using Python+SQLAlchemy remotely?

提问by strevg

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

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

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

当我在控制台中使用 MySQL-client 并指定“ ptotocol=TCP”时，一切都很好！我使用命令：

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

I get access to remote database through SSH-tunnel.

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

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

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

采纳答案by Sylvain Leroux

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

这个问题的经典答案是使用127.0.0.1或主机的IP或主机名而不是“特殊名称” localhost。从文档：

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

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

And later:

然后：

On Unix, MySQL programs treat the host name localhostspecially, 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.

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

---

---

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

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

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

正如解释在这里，从SQLAlchemy的，您可以通过相关选项（如果有的话）到你的驱动程序的网址选项或使用connect_args关键字参数。

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:

例如使用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:[email protected]/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:

正如您所注意到的，两者都将使用 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:[email protected]/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.

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

回答by jgbarah

In my setup (I'm using mysql-python) just using 127.0.0.1 instead of localhost in the MySQL SQLAlchemy url works. The complete url I'm using exactly for that scenario (tunnel with local port 3307) is:

在我的设置中（我使用的是 mysql-python），在 MySQL SQLAlchemy url 中只使用 127.0.0.1 而不是 localhost 。我完全用于该场景（具有本地端口 3307 的隧道）的完整 url 是：

mysql:/user:[email protected]:3307/

I'm using SQLAlchemy 1.0.5, but I guess that doesn't matter too much...

我正在使用 SQLAlchemy 1.0.5，但我想这并不重要......

回答by sparrow

This worked for me:

这对我有用：

import pandas as pd
import pymysql
from sqlalchemy import create_engine

cnx = create_engine('mysql+pymysql://<username>:<password>@<host>/<dbname>')    
df = pd.read_sql('SELECT * FROM <table_name>', cnx) #read the entire table

Where credentials are added to mysql database like this:

像这样将凭据添加到 mysql 数据库的地方：

CREATE USER '<username>' IDENTIFIED BY '<password>';
GRANT ALL PRIVILEGES ON *.* TO '<username>' WITH GRANT OPTION;
FLUSH PRIVILEGES;