27

I am newbie in Python Flask. In my project we are creating db object using below code. 

    app = Flask(__name__)  
    app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'  
    db = SQLAlchemy(app)

I want to get cursor object from db. Can someone please help me on it. I know using connection object we can get cursor. But can we get cursor from db object which is created by above way? Thanks.

Karl Knechtel
  • 62,466
  • 11
  • 102
  • 153
user3462649
  • 1,459
  • 3
  • 13
  • 18
  • 2
    I guess the question is: why would you want a cursor? I think you should perhaps have a look at Flask-SQLAlchemy and SQLAlchemy documentation on how to use them. – Ilja Everilä Apr 13 '18 at 04:10
  • I wanted to get column names of sql select query. In my project, I don't know column names for query which is being performed. I want to get it for all select queries. To know column names of select queries, I had to use cursor (https://stackoverflow.com/questions/9752372/how-do-i-get-the-column-names-from-a-row-returned-from-an-adodbapi-query). Since, my project had already db object as discussed above, I had to get corsor object form it. Finally solved the issue. Posted answer below. thanks – user3462649 Apr 13 '18 at 11:54
  • 1
    So it's an [XY problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) and the answer depends on if you're using [the Core](http://docs.sqlalchemy.org/en/latest/core/selectable.html#sqlalchemy.sql.expression.FromClause.columns) or [`Query` objects](http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.column_descriptions), unless what you're really talking about is using textual SQL, in which case you might need a cursor. But you've not provided any context. – Ilja Everilä Apr 13 '18 at 12:02
  • Though you probably don't need a cursor, if all you need are the labels/keys in the result: [`ResultProxy.keys()`](http://docs.sqlalchemy.org/en/latest/core/connections.html#sqlalchemy.engine.ResultProxy.keys). – Ilja Everilä Apr 13 '18 at 12:19

4 Answers4

59

Finally got answer from Flask documentation, we can get cursor from db object using,

from sqlalchemy import create_engine
engine = create_engine('your_connection_string')
connection = engine.raw_connection()
cursor = connection.cursor()
Quba
  • 4,776
  • 7
  • 34
  • 60
user3462649
  • 1,459
  • 3
  • 13
  • 18
  • 17
    I would suggest editing this response to make it more complete. Yes, the answer is to use raw_connection() if you want to do this. However, some context about why this is highly inadvisable would be useful. Also, best practices for closing the connection after the transaction. And how the raw connection could affect your sql-alchemy session overall. – Matt L. Apr 13 '18 at 13:56
  • 1
    What to do, if I have only session?... Fake execute? – Nikolay Fominyh Dec 29 '20 at 12:18
13

It's ok in this way.

db = SQLAlchemy(app)
session = db.session()
cursor = session.execute(sql).cursor
Unbranded Manchester
  • 980
  • 16
  • 30
Sucas Venior
  • 161
  • 1
  • 3
2

create cursor from sqlalchemy orm session

  1. from sqlalchemy.orm import sessionmaker
  2. engine=create_engine(url)
  3. session=sessionmaker(bind= engine)()
  4. curs=session.connection().connection.cursor()
abdelrahman
  • 46
  • 5
-5

You don't have a cursor in Flask SQLAlchemy because it's an ORM. Methods exist to perform actions on the database directly, unlike packages like SQLite. Check the documentation for more info on these.

To execute SQL queries directly, you'd have to run them through the terminal directly through whichever RDBMS you're using (MySQL, PostgreSQL, etc.)

Mangohero1
  • 1,832
  • 2
  • 12
  • 20
  • 5
    SQLAlchemy has an ORM in it, but the ORM is built on top of several lower level layers that give more direct access to the database(s). See https://flask.palletsprojects.com/en/1.1.x/patterns/sqlalchemy/ – Mark Kortink Aug 04 '19 at 23:00