How to connect a MySQL database using Python + SQLAlchemy remotely?

Question

I find it difficult to access MySQL remotely. I am using an SSH tunnel and want to connect a MySQL database using Python + SQLALchemy.

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

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

I am accessing a remote database through an SSH tunnel.

However, when I want to connect to the database using Python + SQLAchemy, I cannot find an option such as —protocol=TCP . Otherwise, I only connect to local MySQL databases. Please tell me if there is a way to do this using SQLAlchemy.

Answer 1

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

[...] Unix connections to localhost are made using the default Unix socket file

And later:

On Unix, MySQL programs handle the localhost host name specifically , which may be different than expected compared to other network programs. To connect to localhost, MySQL programs try to connect to the local server using a Unix socket file. This happens even if the -port or -P option is specified to specify the port number. For the client to establish a TCP / IP connection with the local server, use -host or -h to specify the host name value 127.0.0.1 or the IP address or name of the local server.

---

However, this simple trick does not work in your case, so you need to somehow force the use of a TCP socket. As you yourself explained, when you call mysql on the command line, you use the --protocol tcp option.

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

For example, using PyMySQL, in the test system I installed for this 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 due to the port number after the host name). 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',)] 

There is no port after the host name: this is a UNIX socket.

Answer 2

In my setup (I use mysql-python), using 127.0.0.1 instead of localhost in MySQL SQLAlchemy, url works. The full url that I use for this particular scenario (tunnel with local port 3307):

 mysql:/user: passwd@127.0.0.1 :3307/ 

I am using SQLAlchemy 1.0.5, but I think it doesn't really matter ...