I am trying to use the pandas function pd.read_sql to read records that have been created, added, and flushed in a SQLAlchemy session, but not committed. So I want to create an object in a SQLAlchemy session and query it with pandas before calling commit. Using pandas 0.22.0 and SQLAlchemy 1.1.10.
I have tried setting the isolation_level on create_engine, and various other ways of setting the isolation level to 'READ UNCOMMITTED', but this does not seem to work. Minimal example below:
# Import packages
import pandas as pd
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
# Set up an example ORM
Base = declarative_base()
class Record(Base):
__tablename__ = 'records'
id = Column(Integer, primary_key=True)
foo = Column(String(255))
# Create a session and engine:
database='foobar'
user=''
password = ''
host = 'localhost'
port = '5432'
connection_string = f"postgresql+psycopg2://{user}:{password}@{host}:{port}/{database}"
engine = create_engine(connection_string, encoding = 'utf8', convert_unicode = True,
isolation_level='READ_UNCOMMITTED'
)
session = sessionmaker()
session.configure(bind=engine)
db = session()
# Set up the example record:
Record.__table__.create(bind=engine)
record = Record(foo='bar')
db.add(record)
db.flush()
# Attempt to query:
records = pd.read_sql('select * from records', db.get_bind())
assert records.empty
I am looking for a solution that will cause the above code to throw an AssertionError on the last line. records.empty currently evaluates to true.
