using .read_sql_query() with run_transaction()

crdb_read_sql_query_trans.py

import pandas as pd
import sqlalchemy as sa
from sqlalchemy_cockroachdb.transaction import run_transaction

connection_url = "cockroachdb+psycopg2://root@localhost:26257/defaultdb"
connection_url = "postgresql+psycopg2://scott:[email protected]/test"
engine = sa.create_engine(connection_url)


def read_sql_qry_trans(qry, engine_):

    def callback(conn):
        return pd.read_sql_query(qry, conn)

    return run_transaction(engine_, callback)


sql = """\
SELECT 1 AS id, 'foo' AS txt
UNION ALL
SELECT 2 AS id, 'bar' AS txt
"""
df = read_sql_qry_trans(sql, engine)
print(df)
"""
   id  txt
0   1  foo
1   2  bar
"""