0

I am attempting to execute some basic SQL via Python using the teradatasql module. The code appears to run and the SQL is executed: however, the execution of the Python itself ends with an error on the end of the code reproduced below. Currently, I need to run additional data preprocessing steps using pandas on the output of the SQL, but the larger program will not continue past the Operational Error (not even via a try/except block excepting the teradatsql.OperationalError). Therefore even though the SQL executes fine with this issue, I need to resolve it.

Any suggestions? Thank you!

Error:

teradatasql.OperationalError: 1 is not a valid connection pool handle

Code:

import teradatasql
import os
def refresh_table():
    usr = ****1
    with open(f'C:\\Users\\{usr}\\Documents\\my_td_password.txt', 'r') as my_pwd_f:
        pw = my_pwd_f.read()
    with teradatasql.connect(host = '*******2'
    , user = usr
    , password = pw
    ,  ) as con:
        with con.cursor() as cur:
            with open('C:\\Users\\****1\\Documents\\test.sql', 'r') as my_sql:
                sql_script = my_sql.read()
                for sql_block in sql_script.split(';'):
                    try:
                        cur.execute(sql_block)
                        print("Block executed")
                    except ValueError:
                        print("Failure to execute block: ValueError")
                    finally:
                        print(sql_block)
                my_sql.close()
                print("SQL file closed")
        con.close()
        print("Connection closed")

refresh_table()
dapperAF
  • 19
  • 4
  • 1
    You are explicitly closing the connection inside the `with` block. Take out the `con.close()` – Fred Sep 09 '21 at 16:14
  • So the connection does not need to be closed explicitly? Currently the indentation aligns with the connect block – dapperAF Sep 09 '21 at 17:04
  • What step is throwing the error? – Andrew Sep 09 '21 at 17:59
  • 1
    Correct. When `with` is used to create an object, exiting the block will dispose of it automatically. Just as you didn't explicitly close or dispose of the cursor. It's the "double close" that is giving you this error. – Fred Sep 09 '21 at 19:38

1 Answers1

1

Fixed by removing con.close() from the end - as Fred pointed out, the with block implicitly closes the connection when it finishes execution

https://stackoverflow.com/users/11552426/fred

dapperAF
  • 19
  • 4