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](https://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx). You should consider parameterising your query. — David Rushton, Jan 08 '16 at 13:58

score 5 · Accepted Answer · answered Jan 08 '16 at 14:38

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!

@Sajeetharan - That's a different question. [RTFM](https://code.google.com/p/pyodbc/wiki/Cursor) and then [ask a new question](http://stackoverflow.com/questions/ask) if necessary. — Gord Thompson, Jan 08 '16 at 18:21

score 1 · Answer 2 · answered Jan 08 '16 at 13:24

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 08 '16 at 13:24

Shnugo

66,100
9
53
114

1

@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? – Shnugo Jan 08 '16 at 13:39
both are valid in sql server, i dont think its a problem with the query, btw i din't – Sajeetharan Jan 08 '16 at 13:43
@Sajeetharan, Look [here, section "fetchall"](https://code.google.com/p/pyodbc/wiki/Cursor). 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). – Shnugo Jan 08 '16 at 13:48
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. – David Rushton Jan 08 '16 at 14:00
@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 – Shnugo Jan 08 '16 at 14:30

score 0 · Answer 3 · edited May 23 '17 at 12:23

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 '17 at 12:23

Community

1
1

answered Jan 08 '16 at 14:32

Bryan

17,112
7
57
80

Get MSSQL table column names using pyodbc in python

3 Answers3

Linked