Getting raw psycopg2 cursor object from a SQLAlchemy session object

Viewed 944

I am creating a session object using this

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

maiden_engine = create_engine(connection_url)
session = sessionmaker(maiden_engine)
connector = session()

Now for a certain use case, I want to get pyscopg2 cursor object from this connector object, is there a way this conversion can be achieved?

This is how you normally create a cursor object

import psycopg2
conn = psycopg2.connect(host, port, database, user, password, sslmode)
cursor = conn.cursor()

Note that this conversaion HAS to be made from connector object in the last line of first code snippet, I can not use maiden_engine or anything else.

1 Answers

In your case the connector variable is a <class 'sqlalchemy.orm.session.Session'> object. Session objects have a .bind attribute which returns the <class 'sqlalchemy.engine.base.Engine'> that is associated with the session.

Engine objects have a .raw_connection() method that returns (a proxy to) the raw DBAPI connection, and calling .cursor() on that returns a raw DBAPI Cursor object. Hence,

crsr = connector.bind.raw_connection().cursor()
Related