Python Throwing Error After Passing Try - Except Block-CodePudding

I am using this script to create a database and a user in PostgreSQL "cluster" and then creating new schemas that exist in another schema(I am copying just their names).

I am using Python 2.7.5 and here is my script:

import sys
import psycopg2
import getpass

def connect_db(database):
    connection = psycopg2.connect(user="postgres",
                              password="mypass",
                              host="127.0.0.1",
                              port="5432",
                              database=str(database))
    connection.autocommit = True
    cursor = connection.cursor()
    return cursor


def execute_query_2_select(cursor, query):
    try:
        cursor.execute(query)
        output = cursor.fetchall()
        return output
    except Exception as e:
        print(e)


def execute_query_2_create(cursor, query):
    try:
        cursor.execute(query)
    except Exception as e:
        print(e)


def main():
    source_database = str(raw_input("Enter a tns name(DB_UNIQUE_NAME): ")).strip()
    target_database = str(raw_input("Enter a username(OID): ")).strip()
    user_password = str(getpass.getpass("Enter the password of" )).strip()

    try:
        cursor_source = connect_db(source_database)
    except Exception:
        print("Please check your input and try again.\n")
        main()

    query_2_get_schemas = "select schema_name from information_schema.schemata where schema_name not like 'pg_%' and schema_name not in ('public', 'information_schema');"
    schema_template_list = execute_query_2_select(cursor_source, query_2_get_schemas)
    cursor_source.close()

    cursor_postgres = connect_db("postgres")

    execute_query_2_create(cursor_postgres, "create user {x} with encrypted password '{y}';".format(x=target_database, y=user_password))
    execute_query_2_create(cursor_postgres, "create database {x} owner {x};".format(x=target_database))
    cursor_postgres.close()

    cursor_target = connect_db(target_database)

    for i in schema_template_list:
        execute_query_2_create(cursor_target, "CREATE SCHEMA IF NOT EXISTS {x};".format(x=i[0]))
        execute_query_2_create(cursor_target, "GRANT USAGE ON SCHEMA {x} TO {x};".format(x=i[0]))
        execute_query_2_create(cursor_target, "GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA {x} TO {x};".format(x=i[0]))
        execute_query_2_create(cursor_target, "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA {x} to {x};".format(x=i[0]))
        execute_query_2_create(cursor_target, "ALTER DEFAULT PRIVILEGES IN SCHEMA {x} GRANT ALL PRIVILEGES ON TABLES TO {x};".format( x=i[0]))
        execute_query_2_create(cursor_target, "ALTER DEFAULT PRIVILEGES IN SCHEMA {x} GRANT ALL PRIVILEGES ON SEQUENCES TO {x};".format( x=i[0]))
        execute_query_2_create(cursor_target, "ALTER SCHEMA {x} OWNER TO {x};".format(x=i[0]))
        print("Commands for schema {x} are executed!".format(x=i[0]))

    cursor_target.close()

if __name__ == "__main__":
    main()

Because of the exception block

    except Exception:
        print("Please check your input and try again.\n")
        main()

I am sending users to go back and check their inputs if they provide incorrect connection data and enter again. In order to test the script on first attempt I provide incorrect connection data. Then it goes back to where I want it to go. Finally, I provide correct information, but this time I got "UnboundLocalError: local variable 'cursor_source' referenced before assignment". Why? It is defined in the second/later attempt(s).

Output:

Enter a database name for schema pattern: asdaslkdjals
Enter a database/user name to create: asdasd
Enter the password of user asdasd
Please check your input and try again.

Enter a database name for schema pattern: test_source
Enter a database/user name to create: test_target
Enter the password of user test_target
Commands for schema test_schemaxx are executed!
Traceback (most recent call last):
  File "schema.py", line 68, in <module>
    main()
  File "schema.py", line 44, in main
    schema_template_list = execute_query_2_select(cursor_source, query_2_get_schemas)
UnboundLocalError: local variable 'cursor_source' referenced before assignment

CodePudding user response：

This is the flow of your recursive program with the example you gave: first bad input then good one:

you call main
you pass bad inputs in the try
The except is executed and calls main again
1. you pass good inputs
2. the try executes without a problem
3. rest of the function runs and returns
Recursive call ended - back to the first call.
Now the first main keeps running without cursor_source defined (because the try failed).

What you need is basically to put all code after the except inside an else block - that's what you want to be run if there was no error. But it would be easier to simply return in that case:

    try:
        cursor_source = connect_db(source_database)
    except Exception:
        print("Please check your input and try again.\n")
        main()
        return

But it would even be easier to avoid recursion and simply use a loop:

while True:
    source_database = str(raw_input("Enter a tns name(DB_UNIQUE_NAME): ")).strip()
    target_database = str(raw_input("Enter a username(OID): ")).strip()
    user_password = str(getpass.getpass("Enter the password of" )).strip()
    try:
        cursor_source = connect_db(source_database)
    except Exception:
        print("Please check your input and try again.\n")
    else:
        break
    
# rest of function

CodePudding user response：

I'm not 100% on why you're getting unbound local, but calling main from an exception handler is absolutely a bad idea.

Depending on how many times the user has to retry, you could enter into infinite recursion and you're possibly leaving all kinds of half-initialized connections hanging around.

Instead you should do the retry in a while loop so you don't have to exit the main function.

CodePudding user response：

Have You Tried The global Statement? Put the global in a loop for instance :

A = 20
def main():
  print(‘Does Something’)
  input = input(‘ variable a’s new value : ‘)
  #input == 10
main()

It will give the error if you try to access a variable inside a function. but if you do :

A = 20
def main():
  global A
  print(‘Does Something’)
  input = input(‘ variable A’s new value : ‘)
  #input == 10
main()

This Will Stop Giving the error.