Hi all. When I normally use

psycopg2

, I use a

try/finally

because leaving the context manager doesn’t close the connection. So, I would do things like this:

sql = ...
conn = ...

try:
    with conn:
        with conn.cursor() as curs:
            curs.execute(sql)
            ...
finally:
    conn.close()

Since

psycopg2

specifically notes that the connection doesn’t get closed, I would like to confirm that the

prefect-sqlalchemy SQLAlchemyConnector

does close the connection automatically (as your docs say) when using the context manager like below and therefore, I don’t need to use the

try/finally

. I’m using the

postgresql+psycopg2

driver. Thanks!

with SqlAlchemyConnector.load("MY_BLOCK") as database:
    database.execute("CREATE TABLE IF NOT EXISTS customers (name varchar, address varchar);")
    database.execute_many(
        "INSERT INTO customers (name, address) VALUES (:name, :address);",
        seq_of_parameters=[
            {"name": "Ford", "address": "Highway 42"},
            {"name": "Unknown", "address": "Space"},
            {"name": "Me", "address": "Myway 88"},
        ],
    )
    results = database.fetch_many("SELECT * FROM customers", size=2)
    print(results)
    results = database.fetch_many("SELECT * FROM customers", size=2)
    print(results)

hi @kiran yep, the

SQLAlchemyConnector

should manage your db engine (and any connections it has) when used as a context manager you could check out how that works here

Ah thanks Nate!

hey @kiran - you should be able to use `get_connection` from

SQLAlchemyConnector

to get a connection and get a cursor from that is that what you're looking for?

@Nate hi! That is what I’d like but I get

*AttributeError:* '_trans_ctx' object has no attribute 'cursor'

what if you try

with SqlAlchemyConnector.load("redshift-db") as redshift_db:
    conn = redshift_db.get_connection()
    print(dir(conn))
    cursor = conn.cursor()

Same thing. Cursor isn’t in the list

[
    '__class__',
    '__delattr__',
    '__dict__',
    '__dir__',
    '__doc__',
    '__enter__',
    '__eq__',
    '__exit__',
    '__format__',
    '__ge__',
    '__getattribute__',
    '__gt__',
    '__hash__',
    '__init__',
    '__init_subclass__',
    '__le__',
    '__lt__',
    '__module__',
    '__ne__',
    '__new__',
    '__reduce__',
    '__reduce_ex__',
    '__repr__',
    '__setattr__',
    '__sizeof__',
    '__str__',
    '__subclasshook__',
    '__weakref__',
    'close_with_result',
    'conn',
    'transaction'
]

hmm 👀

I mean… from your messages, it seems like it should have

cursor

, right? Or did I misunderstand

thats the interface I'm used to with db connection objects, but I didn't write

prefect-sqlalchemy

so I'm looking into it now

Ah yeah same. No rush. Thanks! I’ll hardcode the field names for now