Python's sqlalchemy on Google CLoud SQL

Question

I'm attempting to connect to a Google Cloud SQL instance with sqlalchemy in order to use the Pandas to_sql function. My instance has an internal IP Address.

I got the following to work:

import MySQLdb

def get_con():
    return MySQLdb.connect(host='<IP Address>', user='root')

However when I use sqlalchemy:

import sqlalchemy

def get_con():
    s = 'mysql://root@<IP Address>'
    engine = sqlalchemy.create_engine(s)
    return engine.connect()

I get the following error:

_mysql_exceptions.OperationalError: (2002, 'Can\'t connect to local MySQL server through socket \'/var/run/mysqld/mysqld.sock\' (2 "No such file or directory")')

A few ideas of what might be going wrong: https://stackoverflow.com/questions/5376427/cant-connect-to-local-mysql-server-through-socket-var-mysql-mysql-sock-38 — arudzinska, Nov 09 '18 at 09:38
This is a reference to running MySQL locally. I’m running it remotely on Cloud SQL! — James Pinkerton, Nov 09 '18 at 15:37

score 3 · Accepted Answer · answered Nov 11 '18 at 20:48

You didn't say what MySQL driver you're using, but I'd guess it's mysql-python which hasn't been updated in four years. I'd recommend using a different driver.

I tried this using mysql-connector-python and this works as expected:

import sqlalchemy

s = "mysql+mysqlconnector://root@<IP Address>/<DB Name>"
engine = sqlalchemy.create_engine(s)
engine.connect()

Same with PyMySQL

import sqlalchemy

s = "mysql+pymysql://root@<IP Address>/<DB Name>"
engine = sqlalchemy.create_engine(s)
engine.connect()

Also make sure you're specifying the database name here, it's missing from your example.

Python's sqlalchemy on Google CLoud SQL

1 Answers1