Menu
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.
566
Finally, you have the hostname or IP address of the database and the database name. These data are all you need to establish a connection. The port is optional, but SQLAlchemy is smart enough to know the MySQL database resides at port 3306. Finally, you create the connection object and invoke the connect method.
SQLAlchemy supports MySQL starting with version 5.0. 2 through modern releases, as well as all modern versions of MariaDB.
Follow the procedure below to install SQLAlchemy and start accessing MySQL through Python objects. Use the pip utility to install the SQLAlchemy toolkit: Be sure to import the module with the following: You can now connect with a connection string. Use the create_engine function to create an Engine for working with MySQL data.
Follow the procedure below to install SQLAlchemy and start accessing MySQL through Python objects. Use the pip utility to install the SQLAlchemy toolkit: Be sure to import the module with the following:
host: this is your remote MySQL server, you can either use its IP address or domain name. database: the name of the database you want to access, you can leave it empty if you only want to connect to the MySQL server. user: this is the name of the user that you'll be creating for remote accesses, we'll see in a minute how to create one.
After successful connection with the database using the create_engine () function, we can execute different SQL queries using sqlalchemy functions to extract data from the database in use using the connection.execute () function:
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:[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:
>>> 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.
162
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
