2

I am using pyodbc to create or drop table. And I wrote a function to drop table if table has already existed. Please look at my syntax below

try:
    cur.execute('Drop table {}'.format(table_name))
except ProgrammingError:
    cur.execute(create_table)

However, I got error message :

<ipython-input-79-fe0fe29a1e8e> in upload_to_SQL(file_name, skiprows, table_name)
 26     try:
 27         cur.execute('Drop table {}'.format(table_name))
--->28  except ProgrammingError:
 29         cur.execute(create_table)
 30 

NameError: name 'ProgrammingError' is not defined

I confirm that ProgrammingError is the error message if I drop a table didn't exist in the sql server. Anyone have idea how to revise this?

Brendan Abel
  • 35,343
  • 14
  • 88
  • 118
Zed Fang
  • 823
  • 2
  • 13
  • 24

2 Answers2

3

This exception you're getting is a NameError, because ProgrammingError isn't defined in the current scope. Exceptions are objects just like everything else in python, and you have to import or reference them from the correct places.

You probably want to do

try:
    cur.execute('Drop table {}'.format(table_name))
except pyodbc.ProgrammingError:
    cur.execute(create_table)
Brendan Abel
  • 35,343
  • 14
  • 88
  • 118
3

As @Brendan Abel suggested, your exception ProgrammingError is out of scope. Make an import like :

from pyodbc import ProgrammingError

or

pyodbc.ProgrammingError. Also would be nice to change your query a bit:

"DROP TABLE IF EXISTS {}".format(self.table_name)

Here is a list exceptions from pyodbc docs.

Anthony
  • 421
  • 1
  • 5
  • 13
  • Thank you Anthony! I believe **Drop Table if exists** is not available in SQL server. – Zed Fang Mar 16 '18 at 21:07
  • @Zed Fang, that's my mistake. Here is syntax for SQL server:(https://stackoverflow.com/questions/7887011/how-to-drop-a-table-if-it-exists-in-sql-server#7887033) – Anthony Mar 16 '18 at 21:17