Google-cloud-python: Implement DB-API 2.0 driver for Spanner

[tseaver]

Per PEP 249.

• connect() module-scope function would return an instance implementing the Connection interface.
• Each SpannerConnection class would hold a Session instance.
• SpannerConnection and SpannerCursor would work together to map the DB-API semantics onto snapshots, batches, transactions, etc.

Note that until the back-end supports DML, there is no feasible way to do a proper DB-API mapping.

[anantb]

This would be important for us to migrate to Spanner. We use Google Cloud (GKE) but for databases, we still use Amazon Aurora. All of our database access is through sqlalchemy so whenever spanner becomes compatible with sqlalchemy, we'd love to migrate to Spanner.

[tseaver]

(Expanding on my note in the description)

I'm pretty sure that it is premature to tackle the DB-API mapping before the back-end adds support for DML: I can't see a huge demand for DB-API for query-only usecases, and we won't be DB-API compliant without e.g. working commit() and rollback() methods on the connection object.

OTOH, the implementation of Cursor.execute{,many} won't actually change when they add DML, so I guess we could start the parts which don't depend on writes.

[anantb]

This sounds awesome. DB-API mapping won't be useful for any real usage until DML is supported but I think, we would be able to at least test the query-only use case.

[lukesneeringer]

In an effort to get my project management to be a bit more sane, I am going to try something else in lieu of single issues that represent large projects.

I have referenced this on a wiki page devoted to our significant feature backlog. Discussion of this particular feature is still welcome here, although I am going to close this issue until such time as this is something we take on.

[jared-duo]

Am I in the Right Place?

@lukesneeringer

I have referenced this on a wiki page devoted to our significant feature backlog.

This link appears to be dead. Is this issue being tracked anywhere else or just here? I see it under the 'Feature Requests' project, but that just links back here.

Actual Question/Ping

It appears this feature was last looked at near Spanner's initial release, and AFAICT Spanner now supports DML.

Just was wondering what the status of this was now or if I am mistaken as far as the DML support level needed to implement a DB-API 2.0 for Python. (In the end interested in this as a step towards supporting SQLAlchemy as per #3023.

Thanks for any reply.

[smacpher-myst]

Hello,

@jared-duo's previous comment sums up my questions / goals well but I figured I'd ping the thread as well. Is there an update on the status of this?

I noticed that https://github.com/google/python-spanner-orm exists, however am also keen on using SQLAlchemy with Cloud Spanner.

Thanks in advance!

EDIT
I also noticed that the python-spanner-django project implements a DB-API 2.0 for Spanner. Could that be ported over to satisfy this ticket and unblock https://github.com/googleapis/google-cloud-python/issues/3023?