在构造函数中使用定义的字段 - Python

Question

我有一个类，下面的代码是用来从一个Linux服务器上的Python网页应用连接到远程SQL服务器的。我在init构造函数里定义并设置了一个游标（cursor），想在整个类里都能使用它。请问我该怎么做？我之前是用Java的，不太理解Python中字段的作用范围和保护级别。

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

Answer 1

把光标改成自定义的光标

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

Answer 2

这个问题的答案很简单：在Python中，“方法”其实就是普通的函数，而局部变量也只是普通的局部变量。要设置或访问实例的属性，你必须使用当前的实例，这个实例会作为函数的第一个参数传入（通常叫做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

现在，使用一个共享的游标来进行所有操作是比较脆弱的，最好为每个操作创建一个新的游标。而且，由于游标是可迭代的，你可能想返回游标本身，让调用的代码去遍历它，这样可能会节省一些内存……

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

哦，对了：使用mixCased这种命名方式在Python中并不常见，我们更喜欢all_lower这种写法；）