How to connect MySQL database using Python+SQLAlchemy remotely?

Question

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.

Answer 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. 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.

Answer 2

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:

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

Answer 3

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:passwd@127.0.0.1:3307/

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

Answer 4

I Tried this to connect with mysql db of xampp server

from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://usrnme:passwd@hstnme/dbname")

Answer 5

If you are using Python 3.x you can use:

pip install mysql-connector-python

Then:

import sqlalchemy as db
engine = db.create_engine("mysql+mysqlconnector://username:password@hostname:port/dbname")