2

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.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
saadi
  • 646
  • 6
  • 29
  • 1
    Perhaps `crsr = connector.bind.raw_connection().cursor()` …? – Gord Thompson Jan 27 '21 at 14:21
  • hi @GordThompson hi thanks, it works! Can you please post it in an answer with how you reached this solution so I can accept it? Also if u think the question is worthy, pls upvote for others to find it easily. – saadi Jan 28 '21 at 04:50

1 Answers1

5

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()
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Using a `scoped_session`, not sure why but I had better luck using `crsr = session.connection().connection.cursor()` from [here](https://stackoverflow.com/a/24267777/4653485). – Jérôme Jul 17 '23 at 13:47