python 3.x - Getting raw psycopg2 cursor object from a SQLAlchemy session object

Question

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.

question from:https://stackoverflow.com/questions/65920713/getting-raw-psycopg2-cursor-object-from-a-sqlalchemy-session-object

Answer 1

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()