Python try except to drop or create table in sql server

Question

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 · Accepted Answer · 2018-03-16 20:18:22Z

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)

answered Mar 16, 2018 at 20:18

Brendan Abel

38k16 gold badges101 silver badges126 bronze badges

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

Comments

Anthony · Accepted Answer · 2018-03-16 20:46:10Z

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.

answered Mar 16, 2018 at 20:46

Anthony

4191 gold badge5 silver badges13 bronze badges

2 Comments

Zed Fang Over a year ago

Thank you Anthony! I believe Drop Table if exists is not available in SQL server.

Anthony Over a year ago

@Zed Fang, that's my mistake. Here is syntax for SQL server:(stackoverflow.com/questions/7887011/…)

Collectives™ on Stack Overflow

Python try except to drop or create table in sql server

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related