在python中使用pyodbc获取MSSQL表列名 [英] Get MSSQL table column names using pyodbc in python

问题描述

我正在尝试使用 pyodbc 获取 mssql 表列名，并收到错误消息

I am trying to get the mssql table column names using pyodbc, and getting an error saying

ProgrammingError: No results.  Previous SQL was not a query.

这是我的代码:

class get_Fields:
   def GET(self,r):
          web.header('Access-Control-Allow-Origin',      '*')
          web.header('Access-Control-Allow-Credentials', 'true')
          fields = []
          datasetname = web.input().datasetName
          tablename = web.input().tableName
          cnxn = pyodbc.connect(connection_string)
          cursor = cnxn.cursor()
          query =  "USE" + "[" +datasetname+ "]" + "SELECT COLUMN_NAME,* FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = " + "'"+ tablename + "'"
          cursor.execute(query)
          DF = DataFrame(cursor.fetchall())
          columns = [column[0] for column in cursor.description]
          return json.dumps(columns)

如何解决这个问题?

推荐答案

您可以通过使用 pyodbc 的一些内置方法来避免这种情况.例如，而不是:

You can avoid this by using some of pyodbc's built in methods. For example, instead of:

    query =  "USE" + "[" +datasetname+ "]" + "SELECT COLUMN_NAME,* FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = " + "'"+ tablename + "'"
    cursor.execute(query)
    DF = DataFrame(cursor.fetchall())

试试:

    column_data = cursor.columns(table=tablename, catalog=datasetname, schema='dbo').fetchall()
    print(column_data)

这将返回列名称(和其他列元数据).我相信列名是每行的第四个元素.这也消除了对 SQL 注入的非常有效的担忧.然后，您可以弄清楚如何从结果数据构建您的 DataFrame.

That will return the column names (and other column metadata). I believe the column name is the fourth element per row. This also relieves the very valid concerns about SQL injection. You can then figure out how to build your DataFrame from the resulting data.

祝你好运！

这篇关于在python中使用pyodbc获取MSSQL表列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！