Python mysql connector ssh

Enable Python to Connect to MySQL via SSH Tunnelling

When that’s run, we’ll then disconnect from MySQL and close the SSH tunnel. You can also use similar code to insert data into a MySQL database using PyMySQL.,Well, thanks to the Python packages sshtunnel and pymysql you can create an SSH connection inside your Python script and connect to a remote MySQL server securely and return your query results in a Pandas dataframe. Here’s how it’s done.,Finally, we need a couple of functions to tidy up when we’re done. The first one disconnects from the MySQL server by using the close() function, while the second one closes the SSH tunnel.,The next function uses PyMySQL to create a connection to the MySQL server. This will be run after the SSH tunnel has been opened. The MySQL port will be mapped to the local_bind_port from the tunnel object and stored in the global connection.

import pandas as pd import pymysql import logging import sshtunnel from sshtunnel import SSHTunnelForwarder 

Answer by Hugh Crawford

Only this worked for me,Paramiko is the best python module to do ssh tunneling. Check out the code here: https://github.com/paramiko/paramiko/blob/master/demos/forward.py,I’m guessing you’ll need port forwarding. I recommend sshtunnel.SSHTunnelForwarder ,Best practice is to parameterize the connection variables. Here is how I have implemented. Works like charm!

import pymysql import paramiko import pandas as pd from paramiko import SSHClient from sshtunnel import SSHTunnelForwarder from os.path import expanduser home = expanduser('~') mypkey = paramiko.RSAKey.from_private_key_file(home + pkeyfilepath) # if you want to use ssh password use - ssh_password='your ssh password', bellow sql_hostname = 'sql_hostname' sql_username = 'sql_username' sql_password = 'sql_password' sql_main_database = 'db_name' sql_port = 3306 ssh_host = 'ssh_hostname' ssh_user = 'ssh_username' ssh_port = 22 sql_ip = '1.1.1.1.1' with SSHTunnelForwarder( (ssh_host, ssh_port), ssh_username=ssh_user, ssh_pkey=mypkey, remote_bind_address=(sql_hostname, sql_port)) as tunnel: conn = pymysql.connect(host='127.0.0.1', user=sql_username, passwd=sql_password, db=sql_main_database, port=tunnel.local_bind_port) query = '''SELECT VERSION();''' data = pd.read_sql_query(query, conn) conn.close() 

Answer by Rocco Houston

I’m using MySqldb with Python 2.7 to allow Python to make connections to another MySQL server,As said in comments this one works perfect. SSH Tunnel for Python MySQLdb connection,Instead of connecting normally like this, how can the connection be made through a SSH tunnel using SSH key pairs? ,Best practice is to parameterize the connection variables. Here is how I have implemented. Works like charm!

I’m using MySqldb with Python 2.7 to allow Python to make connections to another MySQL server

import MySQLdb db = MySQLdb.connect(host="sql.domain.com", user="dev", passwd="*******", db="appdb") 

Answer by Morgan Fisher

Check «Use SSH tunnel» in Connect to new database -> Mysql -> SSH tab ,Start PuTTY and enter your SSH hostname into the «Host name» field,You can set up the SSH tunnelling from the SSH/SSL tab of the PyCharm connection setup dialog:,The «proxy host» should be your SSH hostname (see the options for that at the top of this help page)

If you’re running Python code on your local machine, and you want it to access your MySQL database, you can install the sshtunnel package and then use code like this:

import MySQLdb import sshtunnel sshtunnel.SSH_TIMEOUT = 5.0 sshtunnel.TUNNEL_TIMEOUT = 5.0 with sshtunnel.SSHTunnelForwarder( ('your SSH hostname'), ssh_username='your PythonAnywhere username', ssh_password='the password you use to log in to the PythonAnywhere website', remote_bind_address=('your PythonAnywhere database hostname, eg. yourusername.mysql.pythonanywhere-services.com', 3306) ) as tunnel: connection = MySQLdb.connect( user='your PythonAnywhere database username', passwd='your PythonAnywhere database password', host='127.0.0.1', port=tunnel.local_bind_port, db='your database name, eg yourusername$mydatabase', ) # Do stuff connection.close() 

This example uses the mysqlclient library, which you can install on your machine with

As long as you’re not running a MySQL instance locally, just invoke SSH locally (that is, on your own machine — not on PythonAnywhere) like this, replacing username with your PythonAnywhere username and yoursshhostname with your SSH hostname:

If you are running a MySQL instance locally, then it will probably already be using local port 3306, which means that the ssh command won’t be able to. You can modify your SSH invocation to use any other port — this one would use the local post 3333.

One thing to watch out for, however — some MySQL clients treat the hostname localhost as special, meaning «connect to the local server over a domain socket». What you want to do is force it to connect to your local machine on port 3306, and you can do this by specifying 127.0.0.1 for the host instead of localhost. For example, to use the command-line mysql client you’d invoke it like this:

mysql -h 127.0.0.1 -u username -p 

Or, if you had to use port 3333 because you had a local MySQL server:

mysql -h 127.0.0.1 --port 3333 -u username -p 

Answer by Alma Phillips

To import MySQLdb you need to have MySQL-python installed first. You can substitute Psycopg for MySQLdb to do the same thing for PostgreSQL.,Now we’ll use SSHTunnelForwarder to create a tunnel, connect to the database, do the query and return the results as a Pandas dataframe all in one shot. The connection and the SSH tunnel is closed after the query.,read_sql_query conveniently takes your SQL query as a string, as well as the connection that was just established to the database, and creates a dataframe out of the tabular data.,And once we SSH into the environment we need a username and password to connect to a database called “database”:

Let’s say your database is hosted on 52.xx.xx.xx and you have the following user and private key:

# ssh variableshost = '52.xx.xx.xx'localhost = '127.0.0.1'ssh_username = 'ubuntu'ssh_private_key = '/path/to/key.pem'

And once we SSH into the environment we need a username and password to connect to a database called “database”:

# database variablesuser='user'password='verysecurepassword'database='database'

To t u rn all of this into one function that you can call from your Python code we can use the sshtunnel package which you can get using pip:

Then import SSHTunnelForwarder, MySQLdb, as well as pandas:

from sshtunnel import SSHTunnelForwarderimport MySQLdb as dbimport pandas as pd

Now we’ll use SSHTunnelForwarder to create a tunnel, connect to the database, do the query and return the results as a Pandas dataframe all in one shot. The connection and the SSH tunnel is closed after the query.

def query(q): with SSHTunnelForwarder( (host, 22), ssh_username=ssh_username, ssh_private_key=ssh_private_key, remote_bind_address=(localhost, 3306) ) as server: conn = db.connect(host=localhost, port=server.local_bind_port, user=user, passwd=password, db=database) return pd.read_sql_query(q, conn)

Now you can simply query your database:

df = query('select * from table limit 1000')

Answer by Mohammad Patterson

import pymysql import paramiko import pandas as pd from paramiko import SSHClient from sshtunnel import SSHTunnelForwarder from os.path import expanduser home = expanduser('~') mypkey = paramiko.RSAKey.from_private_key_file(home + pkeyfilepath) # if you want to use ssh password use - ssh_password='your ssh password', bellow sql_hostname = 'sql_hostname' sql_username = 'sql_username' sql_password = 'sql_password' sql_main_database = 'db_name' sql_port = 3306 ssh_host = 'ssh_hostname' ssh_user = 'ssh_username' ssh_port = 22 sql_ip = '1.1.1.1.1' with SSHTunnelForwarder( (ssh_host, ssh_port), ssh_username=ssh_user, ssh_pkey=mypkey, remote_bind_address=(sql_hostname, sql_port)) as tunnel: conn = pymysql.connect(host='127.0.0.1', user=sql_username, passwd=sql_password, db=sql_main_database, port=tunnel.local_bind_port) query = '''SELECT VERSION();''' data = pd.read_sql_query(query, conn) conn.close() 

Answer by Jianna Hill

I’m really familiar with connection to MySQL databases via direct/standard connections, using pymysql, but this SSH aspect really has me stumped.,Alternatively, the sshtunnel version I tried errors out with: Could not establish connection from (‘127.0.0.1’, 63693) to remote side of the tunnel,Typically, the issue is that the script connects to the ssh properly, but times out during the request to connect to the MySQL database server. (aka, the pymysql part),I’ve Googled/StackOverflowed a bunch about the problem, and have tried out some solutions using paramiko and/or sshtunnel, but nothing is getting me where I want to be: with a full connection to the MySQL database, and the ability to query it and get datasets back.

from sshtunnel import SSHTunnelForwarder import paramiko from paramiko import SSHClient from os.path import expanduser from forward import forward_tunnel import subprocess as sbp 

ssh = SSHClient() ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy()) ssh.connect(ssh_host, ssh_port, ssh_user, pkey=mypkey) conn = pymysql.connect(host=sql_hostname, user=sql_username, passwd=sql_password, port=sql_port, db=sql_main_database) 

with SSHTunnelForwarder( (ssh_host, ssh_port), ssh_username=ssh_user, ssh_pkey=mypkey, remote_bind_address=('127.0.0.1', sql_port)) as server: conn = None conn = pymysql.connect(host=sql_hostname, user=sql_username, passwd=sql_password, db=sql_main_database, port=server.local_bind_port) cur = conn.cursor() query = '''SELECT VERSION();''' cur.execute(query) results = cur.fetchall() print(results) 

EDIT: I made it work! In case this helps anybody else looking to solve a similar problem in the future, here’s the code:

import pymysql import paramiko import pandas as pd from paramiko import SSHClient from sshtunnel import SSHTunnelForwarder from os.path import expanduser home = expanduser('~') mypkey = paramiko.RSAKey.from_private_key_file(home + pkeyfilepath) sql_hostname = 'sql_hostname' sql_username = 'sql_username' sql_password = 'sql_password' sql_main_database = 'db_name' sql_port = 3306 ssh_host = 'ssh_hostname' ssh_user = 'ssh_username' ssh_port = 22 sql_ip = '1.1.1.1.1' with SSHTunnelForwarder( (ssh_host, ssh_port), ssh_username=ssh_user, ssh_pkey=mypkey, remote_bind_address=(sql_hostname, sql_port)) as tunnel: conn = pymysql.connect(host='127.0.0.1', user=sql_username, passwd=sql_password, db=sql_main_database, port=tunnel.local_bind_port) query = '''SELECT VERSION();''' data = pd.read_sql_query(query, conn) conn.close() 

Answer by Jonah Pittman

This tutorial will show you how to connect to a remote machine hosting a MySQL database; the connection will be made using an SSH Tunnel. We will then query the database using Python’s pymysql library .,Next, we will install python’s pymysql package,From the command line, you will need to create a connection to the server that is running the database. Most standard MySQL instances are run on port 3306. If the MySQL instance on your server is running on a Different port, you may need to change that number., A simple code snippet that describes how to connect to a remote database over SSH

ssh -i key.pem -fN -L 3307:localhost:3306 [email protected]_or_ip_address_hosting_the_database 

Источник