Forums

MySQL from outside pythonanywhere

Hi All,

I'm trying to migrate some scripts from my Raspberry Pi to pythonanywhere but I still need to do part of it on the Pi. I've looked at the tutorial regarding this but I'm a bit lost.

This is my first dip into Python and MySQL so the learning curve is steep at the moment.

So, a snippet of my code looks like this. It connects to the MySQL DB on the Pi, sniffs for Bluetooth packets and then writes the status and a score. This is doing occupancy detection at home so I can control the heating, etc. So I need to write the status and score to my pythonanywhere database instead of the Pi.

conn = MySQLdb.connect(host="localhost", user = "xxxxxx", passwd = "xxxxxx", db = "xxx$xxx")
        cursor = conn.cursor()
        andyResult = bluetooth.lookup_name('98:9C:57:xx:xx:xx', timeout=10)
        if (andyResult != None):
        status = "In"
                score = score + 10
                cursor.execute("UPDATE occupants SET status = %s, lastin = %s  WHERE occupants.id = '1'", (status, timestamp))
                conn.commit()

The tutorial code looks like this:

sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0

with sshtunnel.SSHTunnelForwarder(
    ('ssh.pythonanywhere.com'),
    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 = mysql.connector.connect(
        user='your PythonAnywhere username', password='your PythonAnywhere database password',
        host='127.0.0.1', port=tunnel.local_bind_port,
        database='your database name, eg yourusername$mydatabase',
    )

Could some kind soul help me with what I need to replace with what? My gut feeling is that I replace my first line with all of the tutorial code and then change my cursor = conn.cursor() to cursor = connection.cursor() Would that be right? Have I missed anything?

Cheers Andy

Yes, that's right -- of course, you'll need to put your PythonAnywhere usernames and passwords into the appropriate parts of the tutorial code.

One thing that might not be obvious -- all of your existing code will need to be indented inside the "with" block, just like the call to assign the connection variable. This is because the SSH tunnel is closed when you exit the "with" block.

Hi

Thank you for that. I actually decided to give that all a go this morning. Took about 2 hours to get my indentation right - as I said, the learning curve is steep!

Anyhow, once I got that all sorted, it worked like a dream!

Cheers Andy

Excellent! Glad you got it working :-)