Google Cloud SQL - Can I directly access database with SQLAlchemy - not locally

Question

I'm trying to directly access Google Cloud SQL and create there table. I want to use as little services as possible (keep it simple), therefore I really don't want to use Cloud SDK whatever. I want to use something similar, that I saw here. I tried to replicate it, but I ended up with error.

AttributeError: module 'socket' has no attribute 'AF_UNIX'

For all this I'm using Python with sqlalchemy & pymysql

I really don't know how to debug it since I'm using it first few hours, but I think that problem could be with URL or environmental variables (app.yamp file, which I created). I think that I already have installed all dependencies which I need

db_user = os.environ.get("db_user")
db_pass = os.environ.get("db_pass")
db_name = os.environ.get("db_name ")
cloud_sql_connection_name = os.environ.get("cloud_sql_connection_name ")

db = sqlalchemy.create_engine(
    # Equivalent URL:
    # mysql+pymysql://<db_user>:<db_pass>@/<db_name>?unix_socket=/cloudsql/<cloud_sql_instance_name>
    sqlalchemy.engine.url.URL(
        drivername='mysql+pymysql',
        username=db_user,
        password=db_pass,
        database=db_name,
        query={
            'unix_socket': '/cloudsql/{}'.format(cloud_sql_connection_name)
        }
     ),
     pool_size=5,
     max_overflow=2,
     pool_timeout=30,
     pool_recycle=1800,
)

with db.connect() as conn:
     conn.execute(
         "CREATE TABLE IF NOT EXISTS votes "
         "( vote_id SERIAL NOT NULL, time_cast timestamp NOT NULL, "
         "candidate CHAR(6) NOT NULL, PRIMARY KEY (vote_id) );"
)

I do not use db_user etc. as real values. These are just examples. It should pass successfully and create an table in Google SQL

score 2 · Accepted Answer · answered Jun 19 '19 at 22:52

2

Can I directly access database with SQLAlchemy - not locally

You are specifying a unix socket /cloudsql/{}. This requires that you set up the Cloud SQL Proxy on your local machine.

To access Cloud SQL directly, you will need to specify the Public IP address for Cloud SQL. In your call to the function sqlalchemy.engine.url.URL, specify the host and port parameters and remove the query parameter.

answered Jun 19 '19 at 22:52

John Hanley

74,467
6
95
159

Thank you for your respond! It worked, but I wasn't able to connect. I specified host with my public IP from Cloud SQL and port (3306) from documentation and it gave me another error 2003, "Can't connect to MySQL server on '35.226.112.xxx' (timed out)". Does this problem relate to my previous configuration? Do you know how to fix it? – Blank Jun 19 '19 at 23:14
Did you add your IP address in the Cloud SQL Set Connectivity dialog? – John Hanley Jun 19 '19 at 23:18
I'm afraid not. I'm very disoriented in Google utilities. Do I need to? I though that I don't need to add my computer IP somewhere. I would like this little program, which I'm creating to run on all computers. **EDIT**: Meaning I set my public IP address from Cloud SQL as host. I meant my computer IP that I need to setup somewhere... – Blank Jun 19 '19 at 23:24
If you want to connect to Cloud SQL then you either need to enable all networks or just your computer. Otherwise, connection attempts will be blocked. That is why Cloud SQL Proxy exists. – John Hanley Jun 19 '19 at 23:32
Alright. I wanted just simple connect. Thank you for your answers. I'll rework it. ^^ – Blank Jun 19 '19 at 23:36

Google Cloud SQL - Can I directly access database with SQLAlchemy - not locally

1 Answers1