0

I'm creating a Python program to execute multiple pieces of SQL code against a Teradata box. I'm currently having an issue with the pyODBC code not dropping a table when the table exists. There's a simple if/else statement that governs this, but it doesn't seem to be executing the code at all.

I've tried redoing the cursor pointing at the Teradata box and taking the code out of the if/else statement, but unfortunately, neither of these things seem to have worked. The table still exists.

def saveYourTable(Source, UserName, Password):
    print("UPLOADING DATA TO TERADATA TABLE")
    eiwconnection = py.connect('DSN='+CONN+
                               ';UID='+ID+';PWD='+PW)
    eiwcursor = eiwconnection.cursor()

    vSQL = """
    Select count(*) from dbc.tables where
    databasename = 'DB' and TableName = 'Table'
    """

    Counter = int()
    eiwcursor.execute(vSQL)
    Counter = eiwcursor.fetchall()
    Counter = [int(l[0]) for l in Counter]
    print(Counter)

    if 0 in Counter:
        print("THE TABLE DOES NOT EXIST - MOVING ON")
    else:
        print("DROPPING THE OLD TABLE")
        vSQL = """
        Drop Table DB.Table
        """
        eiwcursor.execute(vSQL)


executor = saveYourTable(CONN, ID, PW)

The expectation is that this code, when executed, will delete the table, if it exists on the box. Unfortunately, that does not seem to be happening with this current bit of code. Any help or recommendations would be appreciated.

Thanks!

A_Pirates_Favorite_Letter
  • 141
  • 1
  • 12
  • Oftentimes, Python SQL users forget the `commit` call after action queries: `eiwconnection.commit()`. – Parfait Apr 11 '19 at 14:29
  • Try adding `autocommit=True` to your `.connect` call. Some databases require that DDL statements be executed in autocommit mode. – Gord Thompson Apr 11 '19 at 14:32
  • Thank you, both! The autocommit=True function worked perfectly! – A_Pirates_Favorite_Letter Apr 11 '19 at 14:42
  • Use `dbc.TablesV` instead of the deprecated `dbc.Tables`. And you should add `AND TableKind = 'T'` to restrict the search to tables, currently you will get views, macros, etc., too. Btw, it's simpler to submit the `drop table` and ignore the *table does not exists error* – dnoeth Apr 11 '19 at 16:03

0 Answers0