CODEWITHSUNDEEP
pythonmysqlpython-3.xormsqlalchemy
Ethan Hill
Ethan Hill

Reputation: 488

SQLAlchemy - session returns values, cursor does not

Whenever I connect to my database with a session, it returns the expected values. However, cursor commands do not appear to return any values.

Here is my boilerplate:

import pprint, sqlalchemy as db
from sqlalchemy.orm import sessionmaker
from models import Event

database = 'sandbox_test'
engine = db.create_engine('mysql+mysqlconnector://user:pass!!@localhost:3306/'+database+'?charset=utf8')

Here is my 'session' block:

Session = sessionmaker(bind=engine)
session = Session()
results = session.query(Event).first()
pprint.pprint(results.emp_id)

It returns firstname_lastname.

Here is my 'cursor' block:

conn = engine.raw_connection()
curs = conn.cursor()
curs.execute('select * from events limit 100;',multi=True)
results = curs.fetchone()
pprint.pprint(results)

It returns None.

How have I formatted the cursor block incorrectly?

Upvotes: 1

Views: 615

Answers (1)

snakecharmerb
snakecharmerb

Reputation: 55670

You need to iterate over the cursor, then iterate over that result, like this:

conn = engine.raw_connection()
curs = conn.cursor()

for res in curs.execute("""SELECT * FROM test;SELECT * FROM test;""", multi=True):
    for row in res:
        print(row)

This is based on the MySQL Connector docs here. For raw connection actions you need to refer to the specific DB-API driver docs as the precise API and calling conventions tend to vary back driver and RDBMS. The SQLAlchemy docs only provide general guidance in these cases.

Upvotes: 1

Related Questions