Snippets Django / Cursor

SQL via Cursor

By Marcelo Fernandes Jul 12, 2017

Django Cursors:

Whenever you want to make a request on your database in which the django does not have an easy ORM model to serve you desired feature (A Model.objects.all() for example), you are no longer left with other possibilities but use the cursor.

cursor is served by django.db.connection, which is an Object that represents the default database connection. In order to use the cursor, you might call the connection.cursor(), which will give you a cursor object.

The cursor object has three major methods: cursor.execute(sql, [params])for executing queries, cursor.fetchone() and cursor.fetchall() for retrieving results.

Basics:


from django.db import connection

def my_custom_sql(self):
    with connection.cursor() as cursor:
        cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])
        cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
        row = cursor.fetchone()

    return row

Remember that connection.cursor() represents an active database connection,
therefore if you are not using 'with connection.cursor()...' you have to explicitly close it using cursor.close(). If you don't close it explicitly, you may go away with it, but django follows up the PEP-249 which states that Connection objects must have a .close() method. Sometimes this method is implemented inside the cursor object itself and not in the connection function, so the 'final' programmer user won't need to worry about that, but just keep in head that this is good practice.
Besides that, According to psycopg2 FAQ, their cursors are lightweight, objects but will cache the data being returned from queries you made using the cursor object. And it will be using a memory space proportional to the query response size, which could waste some memory

Multiple Databases:

As django allows you to connect through multiple databases, it also supports a connection object that handles multiple database connections. It's called connections, note the 's' in the end.


from django.db import connections
cursor = connections['my_db_alias'].cursor()
# Your code here...


Notes


References:


Django Docs