MYSQL and Python (via ssh)

Question

This may be a repeated question of attempting to run a mysql query on a remote machine using python. Im using pymysql and SSHTunnelForwarder for this.

The mysqldb is located on different server (192.168.10.13 and port 5555).

Im trying to use the following snippet:

with SSHTunnelForwarder(
        (host, ssh_port),
        ssh_username = ssh_user,
        ssh_password = ssh_pass,
        remote_bind_address=('127.0.0.1', 5555)) as server:

    with pymysql.connect("192.168.10.13", user, password, port=server.local_bind_port) as connection:
            cursor = connection.cursor()
            output = cursor.execute("select * from billing_cdr limit 1")
            print output

Is this the correct approach ?

I see the following error:

sshtunnel.BaseSSHTunnelForwarderError: Could not establish session to SSH gateway

Also is there any other recommended library to use ?

abhishek nair · Accepted Answer · 2017-11-02 08:35:37Z

1

Found this to be working after some digging.

with SSHTunnelForwarder(
        ("192.168.10.13", 22),
        ssh_username = ssh_user,
        ssh_password = ssh_pass,
        remote_bind_address=('127.0.0.1', 5555)) as server:

    with pymysql.connect('127.0.0.1', user, password, port=server.local_bind_port) as connection:
            output = connection.execute("select * from db.table limit 1")
            print output

answered Nov 2, 2017 at 8:35

abhishek nair

3451 gold badge6 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

victor_destiny · Accepted Answer · 2017-11-02 08:24:26Z

0

you can do it like this:

conn = MySQLdb.connect(
    host=host,
    port=port,
    user=username,
    passwd=password,
    db=database
    charset='utf8',)
cur = conn.cursor()
cur.execute("select * from billing_cdr limit 1")
rows = cur.fetchall()
for row in rows:
    a=row[0]
    b=row[1]
conn.close()

answered Nov 2, 2017 at 8:24

victor_destiny

112 bronze badges

2 Comments

abhishek nair Over a year ago

I need to establish the connection via ssh only. The server would not allow a standard connect to mysql.

victor_destiny Over a year ago

ok, you can follow this page to do it. blog.imdst.com/python-shi-yong-sshsui-dao-lian-jie-mysql

Collectives™ on Stack Overflow

MYSQL and Python (via ssh)

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related