Python DatabaseError with SQL Server

Question

I've written a python script to loop through some tables in Microsoft SQL Server. 90% of the time, the same query can be used, but the other 10% (estimating) I have to use a different query because of some different headers.

Spyder is showing a

DatabaseError: Execution failed on sql

and goes on to say that my column names are invalid. I would like to use a try/except to get through this, but it says that

NameError: 'DatabaseError' is not defined

I tried using except Exception as e and that still didn't work.

Script looks something like this

query1 = """
        SELECT count(distinct column1) [Col1]
        ,SUM(CASE WHEN column2 <> '' THEN 1 ELSE 0 END)[Col2]
        ,SUM(CASE WHEN colum3 <> '' THEN 1 ELSE 0  END)[Col3]
        FROM {query_table} with (nolock)
        """
query2 = """
        SELECT count(distinct column_1) [Col1]
        ,SUM(CASE WHEN column_2 <> '' THEN 1 ELSE 0 END)[Col2]
        ,SUM(CASE WHEN column3 <> '' THEN 1 ELSE 0  END)[Col3]
        FROM {query_table} with (nolock)
        """     

for table in processed_df['Table Name']:
    row_df = processed_df[processed_df['Table Name'] == table]
    try:
       query_results = pd.read_sql_query(query1.format(query_table = table), conn)
  
    except DatabaseError:
       query_results = pd.read_sql_query(query2.format(query_table = table), conn)

Edit

The answer given below was correct, and I'd like to give bonus points to Erik who solved it despite me having some typos.

That said, I wanted to show another option I found in case it can help someone else.

    try:
       # Try thing 1
  
    except pyodbc.Error: 
       # Try thing 2
    
    except:
       # If all else fails, do thing 3

Not shown in my code was the fact that I was using pyodbc. The error I was getting said DatabaseError, but stating that didn't help.

I switched to pyodbc.Error, and that allowed me to add a final except statement for just in case. Hopefully this helps someone else!

I will fix that. That's not the case in my script though. Thank you for catching that. — pkpto39
– pkpto39, Commented Sep 4, 2021 at 0:23
I also notice that in your except: section, you are calling exactly the same command as in the try, so if the try throws an exception, the except will most likely throw the same one. When you tried except Exception as e: instead of except DatabaseError:, did that change how it failed? — Eric McKeeth
– Eric McKeeth, Commented Sep 4, 2021 at 0:31

Eric McKeeth · Accepted Answer · 2021-09-04 00:58:03Z

1

Assuming that query1 and query2 are different in your real code, You probably want to change your try block to:

    try:
       query_results = pd.read_sql_query(query1.format(query_table = table), conn)
  
    except:
       query_results = pd.read_sql_query(query2.format(query_table = table), conn)

As written in your question, both calls use query1, so any time you get an exception in the try part, the except part will generally throw an identical exception to the one you just caught.

answered Sep 4, 2021 at 0:58

Eric McKeeth

3575 silver badges11 bronze badges

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

Collectives™ on Stack Overflow

Python DatabaseError with SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related