Cursor Vs Connection in Python

Question

Which one is more efficient than others in Python. My requirement is to have one connection until we close the application.

I have two classes one is to make and get connection/cursor and using which I am able to get and fetch the data in my Service. Following MVC in python :)

One DBConnection class

import pyodbc

class Connection:
    def getconnection(self):
        conn =  pyodbc.connect('Driver={SQL Server};Server=.\SQLEXPRESS;Database=School;Trusted_Connection=yes;')
        print("Connection Established")
        #cursor = conn.cursor()
        return conn

    def getcursor(self):
        conn = pyodbc.connect('Driver={SQL Server};Server=.\SQLEXPRESS;Database=School;Trusted_Connection=yes;')
        print("Connection Established")
        cursor = conn.cursor()
        return cursor

and one Service Class

import Connection
import pyodbc

class StudentDataService:

    connection = Connection.Connection().getconnection()
    cursor = Connection.Connection().getcursor()

    def getstudentdata(self):
        print("In method getStudentdata()")
        try:
            row = self.connection.execute('select * from StudentGrade')
            studentList = list(row)
            return studentList
        except pyodbc.DatabaseError as err:
            print("Error Occurred while fetching Student Records", err)
            return None
        finally:
            self.connection.close()

    def getcursorstudentdata(self):
        print("In method getcursorstudentdata()")
        try:
            row = self.cursor.execute('select * from StudentGrade')
            studentList = list(row)
            return studentList
        except pyodbc.DatabaseError as err:
            print("Error Occurred while fetching Student Records", err)
            return None
        finally:
            self.cursor.close()


stu = StudentDataService()
print(stu.getstudentdata())
print("++++++++++++++++++++++++++++++++")
print(stu.getcursorstudentdata())

And both are giving me results

Connection Established
Connection Established
In method getStudentdata()
[(1, 2021, 2, Decimal('4.00')), (2, 2030, 2, Decimal('3.50')),... ]
++++++++++++++++++++++++++++++++
In method getcursorstudentdata()
[(1, 2021, 2, Decimal('4.00')), (2, 2030, 2, Decimal('3.50')),... ]

So my confusion is, which one to use?

Answer 1

In pyodbc, connection.execute is just a convenience for creating a cursor and performing cursor.execute. This is covered in the documentation at:

https://github.com/mkleehammer/pyodbc/wiki/Connection#execute

execute()

This function is not part of the Python DB API.

Creates a new Cursor object, calls its execute method, and returns the new cursor.

num_products = cnxn.execute("SELECT COUNT(*) FROM product")

See Cursor.execute() for more details. This is a convenience method that is not part of the DB API. Since a new Cursor is allocated by each call, this should not be used if more than one SQL statement needs to be executed on the connection.

If in doubt, just use Cursor.execute.