python - 'pyodbc.Cursor' 对象没有属性 'index'

Question

每当我尝试FROM INFORMATION_SCHEMA.TABLES 在 Pyqt5 应用程序中使用 pandasModel 类在 qtableView 中显示 sql 表名称列表时，我都会收到此错误'pyodbc.Cursor ' object has no attribute 'index'。我正在使用 mssql server 2019。我尝试了命令sql_conn.execute和 pd。read_sql_query. 如何用熊猫模型处理这个问题？提前致谢！

主文件

from PyQt5 import QtCore, QtGui, QtWidgets
import pyodbc
import pandas as pd

class PandasModel(QtCore.QAbstractTableModel): 
    def __init__(self, df = pd.DataFrame(), parent=None): 
        QtCore.QAbstractTableModel.__init__(self, parent=parent)
        self._df = df

    def headerData(self, section, orientation, role=QtCore.Qt.DisplayRole):
        if role != QtCore.Qt.DisplayRole:
            return QtCore.QVariant()

        if orientation == QtCore.Qt.Horizontal:
            try:
                return self._df.columns.tolist()[section]
            except (IndexError, ):
                return QtCore.QVariant()
        elif orientation == QtCore.Qt.Vertical:
            try:
                return self._df.index.tolist()[section]
            except (IndexError, ):
                return QtCore.QVariant()

    def data(self, index, role=QtCore.Qt.DisplayRole):
        if role != QtCore.Qt.DisplayRole:
            return QtCore.QVariant()

        if not index.isValid():
            return QtCore.QVariant()

        return QtCore.QVariant(str(self._df.iloc[index.row(), index.column()]))

    def setData(self, index, value, role):
        row = self._df.index[index.row()]
        col = self._df.columns[index.column()]
        if hasattr(value, 'toPyObject'):
            value = value.toPyObject()
        else:
            dtype = self._df[col].dtype
            if dtype != object:
                value = None if value == '' else dtype.type(value)
        self._df.set_value(row, col, value)
        return True

    def rowCount(self, parent=QtCore.QModelIndex()): 
        return len(self._df.index)

    def columnCount(self, parent=QtCore.QModelIndex()): 
        return len(self._df.columns)

    def sort(self, column, order):
        colname = self._df.columns.tolist()[column]
        self.layoutAboutToBeChanged.emit()
        self._df.sort_values(colname, ascending= order == QtCore.Qt.AscendingOrder, inplace=True)
        self._df.reset_index(inplace=True, drop=True)
        self.layoutChanged.emit()

class Widget(QtWidgets.QWidget):
    def __init__(self, parent=None):
        QtWidgets.QWidget.__init__(self, parent=None)
        vLayout = QtWidgets.QVBoxLayout(self)
        hLayout = QtWidgets.QHBoxLayout()
        self.pathLE = QtWidgets.QLineEdit(self)
        hLayout.addWidget(self.pathLE)
        self.loadBtn = QtWidgets.QPushButton("load", self)
        hLayout.addWidget(self.loadBtn)
        vLayout.addLayout(hLayout)
        self.tableView = QtWidgets.QTableView(self)
        vLayout.addWidget(self.tableView)
        self.loadBtn.clicked.connect(self.loadData)
        self.tableView.setSortingEnabled(True)

    def loadData(self):
        dbName="AccountDatabase"
        server = '-----'
        database = ''
        username = 'admin'
        password = 'admin#'
        sql_conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=' +
                          server+';DATABASE='+database+';UID='+username+';PWD=' + password)

        query_string= "use " + dbName +" "+ "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'"
    
        df = sql_conn.execute(query_string)
        # df = pd.read_sql_query(query_string, sql_conn)
        # sql_conn.commint()
        # records = sqlSend.fetchall()
        model = PandasModel(df)
        self.tableView.setModel(model)

if __name__ == "__main__":
    import sys
    app = QtWidgets.QApplication(sys.argv)
    w = Widget()
    w.show()
    sys.exit(app.exec_())

标签： pythonsql-serverpandaspypyodbc

Answer 1

这现在对我有用：

def loadData(self):
            dbName="AccountDatabase"
            server = '-----'
            database = ''
            username = 'admin'
            password = 'admin#'
            sql_conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=' +
                              server+';DATABASE='+database+';UID='+username+';PWD=' + password)
    
            query_string="""SELECT * FROM """ +dbName+""".INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'"""
        
           
            df = pd.read_sql_query(query_string, sql_conn)
            model = PandasModel(df)
            self.tableView.setModel(model)