close

How to connect MySQL database using Python+SQLAlchemy remotely?

Hello Guys, How are you all? Hope You all Are Fine. Today We Are Going To learn about How to connect MySQL database using Python+SQLAlchemy remotely in Python. So Here I am Explain to you all the possible Methods here.

Without wasting your time, Let’s start This Article.

Table of Contents

How to connect MySQL database using Python+SQLAlchemy remotely?

  1. How to connect MySQL database using Python+SQLAlchemy remotely?

    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.

  2. connect MySQL database using Python+SQLAlchemy remotely

    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.

Method 1

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.

[…] 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:[email protected]/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:[email protected]/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:[email protected]/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:[email protected]/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.

Method 2

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:/user:[email protected]:3307/

I’m using SQLAlchemy 1.0.5, but I guess that doesn’t matter too much…

Conclusion

It’s all About this issue. Hope all Methods helped you a lot. Comment below Your thoughts and your queries. Also, Comment below which Method worked for you? Thank You.

Also, Read