Can SQLAlchemy be used with Google Cloud SQL?

Question

I've looked over Google Cloud SQL's documentation and various searches, but I can't find out whether it is possible to use SQLAlchemy with Google Cloud SQL, and if so, what the connection URI should be.

I'm looking to use the Flask-SQLAlchemy extension and need the connection string like so: mysql://username:password@server/db

I saw the Django example, but it appears the configuration uses a different style than the connection string. https://developers.google.com/cloud-sql/docs/django

Google Cloud SQL documentation: https://developers.google.com/cloud-sql/docs/developers_guide_python

Zapata

Answer 1

Update

Google Cloud SQL now supports direct access, so the MySQLdb dialect can now be used. The recommended connection via the mysql dialect is using the URL format:

mysql+mysqldb://root@/<dbname>?unix_socket=/cloudsql/<projectid>:<instancename>

mysql+gaerdbms has been deprecated in SQLAlchemy since version 1.0

I'm leaving the original answer below in case others still find it helpful.

---

For those who visit this question later (and don't want to read through all the comments), SQLAlchemy now supports Google Cloud SQL as of version 0.7.8 using the connection string / dialect (see: docs):

mysql+gaerdbms:///<dbname>

E.g.:

create_engine('mysql+gaerdbms:///mydb', connect_args={"instance":"myinstance"})

---

I have proposed an update to the mysql+gaerdmbs:// dialect to support both of Google Cloud SQL APIs (rdbms_apiproxy and rdbms_googleapi) for connecting to Cloud SQL from a non-Google App Engine production instance (ex. your development workstation). The change will also modify the connection string slightly by including the project and instance as part of the string, and not require being passed separately via connect_args.

E.g.

mysql+gaerdbms:///<dbname>?instance=<project:instance>

This will also make it easier to use Cloud SQL with Flask-SQLAlchemy or other extension where you don't explicitly make the create_engine() call.

If you are having trouble connecting to Google Cloud SQL from your development workstation, you might want to take a look at my answer here - https://mcmap.net/q/428971/-unable-to-connect-to-google-cloud-sql-from-development-server-using-sqlalchemy.

Zapata

FYI for anyone else who stumbles across this answer: This method is no longer recommended by SQLAlchemy; use the MySQLdb dialect instead: doc link – Tugboat

Answer 2

Yes,

If you find any bugs in SA+Cloud SQL, please let me know. I wrote the dialect code that was integrated into SQLAlchemy. There's a bit of silly business about how Cloud SQL bubbles up exceptions, so there might be some loose ends there.

Mane

Answer 3

For those who prefer PyMySQL over MySQLdb (which is suggested in the accepted answer), the SQLAlchemy connection strings are:

For Production

mysql+pymysql://<USER>:<PASSWORD>@/<DATABASE_NAME>?unix_socket=/cloudsql/<PUT-SQL-INSTANCE-CONNECTION-NAME-HERE>

Please make sure to

1. Add the SQL instance to your app.yaml:

   beta_settings:
       cloud_sql_instances: <PUT-SQL-INSTANCE-CONNECTION-NAME-HERE>
   

2. Enable the SQL Admin API as it seems to be necessary:

   https://console.developers.google.com/apis/api/sqladmin.googleapis.com/overview

For Local Development

mysql+pymysql://<USER>:<PASSWORD>@localhost:3306/<DATABASE_NAME>

given that you started the Cloud SQL Proxy with:

cloud_sql_proxy -instances=<PUT-SQL-INSTANCE-CONNECTION-NAME-HERE>=tcp:3306

Deandra

This is what currently still works for using pymysql. I can't validate using the SQLAlchemy docs suggestion of using mysqldb as that didn't work for me. – Nephridium

Answer 4

it is doable, though I haven't used Flask at all so I'm not sure about establishing the connection through that. I got it working through Pyramid and submitted a patch to SQLAlchemy (possibly to the wrong repo) here:

https://bitbucket.org/sqlalchemy/sqlalchemy/pull-request/2/added-a-dialect-for-google-app-engines

That has since been replaced and accepted into SQLAlchemy as

http://www.sqlalchemy.org/trac/ticket/2484

I don't think it's made it way to a release though.

There are some issues with Google SQL throwing different exceptions so we had issues with things like deploying a database automatically. You also need to disable connection pooling using NullPool as mentioned in the second patch.

We've since moved to using the datastore through NDB so I haven't followed the progess of these fixes for a while..

Housefly

would you share the reasons for decision to move to NDB? – Civics

Basically we believe it to be a better fit with what we are doing. We started using SQLAlchemy because we knew SQL/RDBMS and we thought it to be at least one of the best Python ORMS out there (after some limited research). But ultimately the complexity of mapping an RDBMS to objects in your code just seems a waste of effort without a really good reason to go there when you can simply store those objects directly to a no-sql datastore. – Housefly

I was looking at using SQL for a project that has a large focus on reporting, as the NoSQL nature of GAE DataStore (db/ndb) isn't as easy to work with without a lot of de-normalization and map reduce. It would be great if there was something like Apache Hive for GAE, but I'm getting off topic now. I'll look into the changeset of the SQLAlchemy issue and go from there. I may revisit using the DataStore and work through the complications. I know it's definitely possible (all of Google works this way), just more complicated for our team. – Zapata

would that not be BigQuery? ;) developers.google.com/bigquery/docs/overview – Housefly

It was considered (and still is), but the project currently has a requirement for the reports to reflect the current data, and BigQuery would require us to push a CSV every so often of the changes, and are limited to how often you can do a push (2 per minute - developers.google.com/bigquery/docs/quota-policy). I may reconsider it based on how CloudSQL works out, it already appears to have some issues/limitations with pooling like you mentioned. I think I might just look into using a Task Queue to queue up exporting data from the DataStore (using NDB api) over to BigQuery. – Zapata

I'll set the task queue to rate limit 1 every 30 seconds. I'm still not 100% sure I'll be able to accomplish the types of queries I'll need (there's lot of views based on user permissions, date windows, etc). I'm not sure if BigQuery's join limitations will impede this, but I could probably de-normalize on the NDB export to BigQuery to handle these. – Zapata

@Housefly - I've circled you on Google+ if you want to continue this discussion offline. I think it's getting too off topics for the SO question. – Zapata

I just committed the above mentioned patch in ticket 2484 to 0.7.8 and 0.8. 0.7.8 isn't released yet but you can get it from the snapshots near the bottom of the download page. – Barbera

@Sean-Lynch - I'm not advocating datastore over rdbms in any way, and I haven't used BigQuery, just thought it might be "something like Apache Hive for GAE". For a project with a large focus on reporting I would guess that SQL would be ideally suited. – Housefly

Answer 5

PostgreSQL, pg8000 and flask_sqlalchemy

Adding information in case someone is on the lookout how to use flask_sqlalchemy with PostgreSQL: Using pg8000 as driver, the working connection string is

postgres+pg8000://<db_user>:<db_pass>@/<db_name>

Tissue