Get Mssql Table Column Names Using Pyodbc In Python

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. Here is my code: class get_

Solution 1:

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())

Try:

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

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.

Good luck!

Solution 2:

Your line

query =  "USE" + "[" +datasetname+ "]" + "SELECT COLUMN_NAME,*...

Will produce something like

USE[databasename]SELECT ...

In SSMS this would work, but I'd suggest to look on proper spacing and to separate the USE-statement with a semicolon:

query =  "USE " + "[" +datasetname+ "]; " + "SELECT COLUMN_NAME,*...

Solution 3:

1. Set the database context using the Database attribute when building the connection string

2. Use parameters any time you are passing user input (especially from HTTP requests!) to a WHERE clause.

These changes eliminate the need for dynamic SQL, which can be insecure and difficult to maintain.