Get MSSQL table column names using pyodbc in python

Question

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

how to solve this?

Are you passing a valid table name? If the table doesn't exist in the db an empty result set would be returned. I'm sure you know this already but for the benefit of others... this kind of dynamic SQL leaves you wide open to SQL Injection Attacks. You should consider parameterising your query. — David Rushton
– David Rushton, Commented Jan 8, 2016 at 13:58

FlipperPA · Accepted Answer · 2016-01-08 14:38:00Z

5

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!

answered Jan 8, 2016 at 14:38

FlipperPA

14.4k5 gold badges46 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sajeetharan Over a year ago

do you know a way to get the tables of a particular db?

Gord Thompson Over a year ago

@Sajeetharan - That's a different question. RTFM and then ask a new question if necessary.

Gottfried Lesigang · Accepted Answer · 2016-01-08 13:24:32Z

1

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,*...

answered Jan 8, 2016 at 13:24

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

5 Comments

Gottfried Lesigang Over a year ago

@Sajeetharan, The statement USE [myDB]; SELECT COLUMN_NAME, * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='SomeTable';is definitly a valid one in SQL-Server... Try to get the content of "query" and execute it directly against your SQL-Server... Does it work? Did you vote this down? If yes: Why?

Sajeetharan Over a year ago

both are valid in sql server, i dont think its a problem with the query, btw i din't

Gottfried Lesigang Over a year ago

@Sajeetharan, Look here, section "fetchall". Your Programming error is thrown, if the statement does not return data. Maybe your tablename does not exist within the database specified. That's why I suggested to execute the statement directly (or you might use the profiler to see what statements are executed).

David Rushton Over a year ago

Shnugo - I've upvoted your answer. Although it doesn't fix the original issue I, like you, felt the downvote was unfair. You raise a valid point.

Gottfried Lesigang Over a year ago

@Sajeetharan, I am not a pythoneer... But I think, that the problem occurs somewhere else. I'd use the SQL Profiler to monitor what's going on

Community · Accepted Answer · 2017-05-23 12:23:17Z

0

Set the database context using the Database attribute when building the connection string
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.

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered Jan 8, 2016 at 14:32

Bryan

17.7k7 gold badges59 silver badges81 bronze badges

Collectives™ on Stack Overflow

Get MSSQL table column names using pyodbc in python

3 Answers 3

2 Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related