Connect to MySQL on GCE using Google Colaboratory

Viewed 371

I am trying to access MySQL on GCE VM instance through Google Colaboratory though, unfortunately, it does not work properly but does work in the local VSCode environment.

While executing TestExec.py, it shows SSH Connected so it seems that ssh connection is successfully done though, however, it seems to be stuck on MySQL connection.

Output on Google Colab:

Output on Google Colab

Please help with the solutions/tips?

    sqlList = []
    sqlList.append("select * from table name;")
    HOST = 'ComputeEngine PublicIP'
    PORT = 22
    USER = 'username'
    DBUSER = 'username for db'
    KEY_FILE = 'private key file path'
    DBNAME = 'dbname'
    DBPORT = 3306
    SSH_BASTION_ADDRESS = HOST
    SSH_PORT = PORT
    SSH_USER = USER
    SSH_PKEY_PATH = KEY_FILE
    MYSQL_HOST = HOST
    MYSQL_PORT = 3306
    MYSQL_USER = DBUSER
    MYSQL_PASS = 'MySQL Login PW'
    MYSQL_DB = DBNAME

    with SSHTunnelForwarder(
        (SSH_BASTION_ADDRESS, SSH_PORT),
        ssh_pkey=SSH_PKEY_PATH,
        ssh_username=SSH_USER,
        # ssh_password=PASSPHRASE,
        remote_bind_address=('localhost', MYSQL_PORT),
        local_bind_address=('localhost', MYSQL_PORT)
    ) as ssh:
        print("SSH Connected")
        print(ssh.local_bind_port)
        try:
            connection = mysql.connector.connect(
                host='localhost',
                port = ssh.local_bind_port,
                user=MYSQL_USER,
                passwd=MYSQL_PASS,
                db=MYSQL_DB,
                charset='utf8'
            )
            print(connection.is_connected())
            print("DB Connected")

            cur = connection.cursor()
            sql = "use dbname"
            cur.execute(sql)
            # rows = cur.fetchall()
            # for row in rows:
            #     print(row)

            for i in range(len(sqlList)):
                print(sqlList[i])
                sql = str(sqlList[i])
            # sql = 'create table test (id int, content varchar(32))'
                cur.execute(sql)

            rows = cur.fetchall()
            for row in rows:
                print(row)
        except mysql.connector.Error as err:
            print("Something went wrong: {}".format(err))
            connection.rollback()
            raise err
        finally:
            cur.close()
            connection.commit()
            connection.close()
1 Answers

It seemed the error occurred because of the connection. I have changed the method of connection.

import pymysql.cursors
connection = pymysql.connect(
  host='localhost', 
  user='user', 
  password='password!', 
  db='dbname', 
  charset='utf8mb4', 
  cursorclass=pymysql.cursors.DictCursor
)
Related