How to use readonly transaction

[wusendong]

I want to execute the query from a database in which my service account has no beginOrRollbackReadWriteTransaction permission, and I use this library with pandas via codes like here:

from sqlalchemy import create_engine
import pandas as pd

engine = create_engine( "spanner:///projects/project_id/instances/instance_id/databases/demo")
pd.read_sql("SELECT * from plans", engine)

And I got a message returns: "Caller is missing IAM permission spanner.databases.beginOrRollbackReadWriteTransaction on resource projects/project_id/instances/instance_id/databases/demo"

So how to use this library to execute some read-only SQL? Because I want only a limited number of people to have write access

[skuruppu]

@wusendong just letting you know that @IlyaFaer is looking into how to support this. I don't believe there's a way to trigger the implementation to use read-only SQL in the current implementation. But @IlyaFaer will get back to you with details.

[IlyaFaer]

Yes, it looks like we need to do some changes in code first. Working on it.

[wusendong]

@IlyaFaer glad to hear that 👍

[IlyaFaer]

@wusendong, by default a connection is in non-autocommit mode. However, if your case doesn't require to stay in non-autocommit mode, you probably can initiate a connection manually from the engine, turn it into autocommit mode by writing:
connection.autocommit = True
or by changing isolation level:
connection.set_isolation_level("AUTOCOMMIT")
and then call read_sql(). In this case a single-use ReadOnly transaction going to be used, which should work for your case.

[wusendong]

Good to see your quick implementation, this will help us a lot 👏

[hadim]

I am hitting the same issue here. I have a client application using spanner-sqlalchemy and when it's used by an IAM user with the roles/spanner.databaseReader role it raises an error related to a missing spanner.databases.beginOrRollbackReadWriteTransaction permission. It works well when the user has the roles/spanner.databaseUser role.

The query is quite a standard sqlalchemy one: just getting some rows by their primary indices and calling `query.all() at the end.