Using fields defined in constructor - Python

Question

I have a class as below that I'm using to connect to a remote SQL server instance from a linux server python web app. I define and set cursor in the init constructor and wish to use it throughout the class. How do I do this? I come form a java background and don't understand the scope and protection levels of Python fields.

import pyodbc

class SQLSeverConnection():


    def __init__(self, DSN, user, password, database):
        connectionString = 'DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' % (DSN, user, password, database)
        connection = pyodbc.connect(connectionString)
        cursor = connection.cursor()

    def getColumnData(self, columnName, tableName):

        cursor.execute('SELECT ' columnName ' FROM ' tableName ' ORDER BY timestamp')
        data = cursor.fetchall()

        return data

    def getColumnTitles(self, tableName):
        cursor.execute('select column_name,* from information_schema.columns where table_name = 'tableName' order by ordinal_position')

        columns = cursor.fetchall()

        return columns

    def getTableNames(self):
        cursor.execute('SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE''')

        tables = cursor.fetchall()

        return tables

bruno desthuilliers · Answer 1 · 2014-07-18T10:01:55.703

The answer is simple: Python's "methods" are really plain functions, and local variables are plain local variables. To set / access instance attributes, you must use the current instance, which is passed as first argument to the function (and by convention named self):

class SQLSeverConnection():


    def __init__(self, DSN, user, password, database):
        connectionString = 'DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' % (DSN, user, password, database)
        self.connection = pyodbc.connect(connectionString)
        self.cursor = connection.cursor()

    def getColumnData(self, columnName, tableName):

        self.cursor.execute('SELECT ' columnName ' FROM ' tableName ' ORDER BY timestamp')
        data = self.cursor.fetchall()

        return data

    def getColumnTitles(self, tableName):
        self.cursor.execute('select column_name,* from information_schema.columns where table_name = 'tableName' order by ordinal_position')

        columns = self.cursor.fetchall()

        return columns

    def getTableNames(self):
        BASE_TABLE ='BASE_TABLE'
        self.cursor.execute('SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'')

        tables = self.cursor.fetchall()

        return tables

Now using a single shared cursor for all operations is brittle, you'd better instanciate a new cursor for each operation. Also, since a cursor is an iterable, you may want to return the cursor itself and let client code iterate over it, it might save some memory...

class SQLSeverConnection(object):

    def __init__(self, DSN, user, password, database):
        connectionString = 'DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' % (DSN, user, password, database)
        self.connection = pyodbc.connect(connectionString)

   def getCursor(self):
       return self.connection.cursor()  

    def getColumnData(self, columnName, tableName):
        cursor = self.getCursor() 
        cursor.execute('SELECT ' columnName ' FROM ' tableName ' ORDER BY timestamp')
        return cursor

    # etc

Oh and yes: using mixCased is not pythonic, we prefer all_lower ;)

Thanks for the swift response, I'm still getting errors on the self.cursor.execute() statements - Expected:) — Mark Corrigan, Jul 18 '14 at 10:09
@PadraicCunningham: getters are unpythonic (because useless - we do have computed attributes) when all the do is returning a plain attribute. In the above case, `getCursor()` is not returning a plain attribute of the `SQLServerConnection` instance... — bruno desthuilliers, Jul 18 '14 at 10:37
@brunodesthuilliers, can you not just create a `self.cursor =...` in the init method and call self.cursor where needed? — Padraic Cunningham, Jul 18 '14 at 10:41
@PadraicCunningham Yes you can but it means you have a single shared cursor for all operations on a given SQLServerConnection instance. If you try to execute another statement while iterating over the results of a previous SELECT statement you'll be in trouble. — bruno desthuilliers, Jul 18 '14 at 11:45

score 0 · Answer 2 · answered Jul 18 '14 at 09:53

change cursor to self.cursor

def __init__(self, DSN, user, password, database):
    connectionString = 'DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' % (DSN, user, password, database)
    connection = pyodbc.connect(connectionString)
    self.cursor = connection.cursor()

def getColumnData(self, columnName, tableName):

    self.cursor.execute('SELECT ' columnName ' FROM ' tableName ' ORDER BY timestamp')
    data = self.cursor.fetchall()

    return data

Using fields defined in constructor - Python

2 Answers2