Cannot Automate Dropping of Tables via Jupyter with Postgres-CodePudding

So I am trying to automate the dropping of TABLES in my Postgresql table

I ensure the connections are working, I have done multiple queries previously, Now I want to be able to list out my Tables from my Database, and in my case I have over 20 tables that need to be dropped and manually doing them one by one is going to take time.... SO I came up with a way to automate this but I keep getting this error

I tried this:

import psycopg2

connection = psycopg2.connect(
    host='localhost',
    port='5433',
    database='rdb',
    user='ruser',
    password='pass'
)
cursor = connection.cursor()
# table_name = 'orders_part'
query = '''
SELECT COUNT(*) FROM information_schema.tables 
WHERE table_catalog = 'retail_db' 
    AND table_schema = 'public' 
'''

query2 = '''
SELECT table_name FROM information_schema.tables 
WHERE table_catalog = 'retail_db' 
    AND table_schema = 'public' 
'''

query3 = '''
DROP TABLE {table_name}; 
'''

cursor.execute(query)
data = cursor.fetchall()
res= data[0][0]

cursor.execute(query2)
data1 = cursor.fetchall()
# print(data[0][0])


tables=[]

for i,j in enumerate(data1):
    tables.append(j[0])

# for i in tables:
#     print(i)
    
print('DROPPING THE TABLES\n')
for i in range(0,res):
    for dd in tables:    
        cursor.execute(
            query3.format(
                table_name= dd
            ), ()
        )
connection.commit()
# cursor.close()
# connection.close()

When I execute this I keep getting back this error:

 ---------------------------------------------------------------------------
UndefinedTable                            Traceback (most recent call last)
Input In [147], in <cell line: 51>()
     51 for i in range(0,res):
     52     for dd in tables:    
---> 53         cursor.execute(
     54             query3.format(
     55                 table_name= dd
     56             ), ()
     57         )
     58 connection.commit()

UndefinedTable: table "orders_part_201410" does not exist

I have confirmed the tables, They are all present, so I dont understand why it keeps saying table not present

Please Help

CodePudding user response：

It clearly throws up an error "orders_part_201410" does not exists. Does it really exists ? If so, necessary permissions and table synonymn if any used matches ? Please check that once.

CodePudding user response：

Alright, so I was able to fix the issue by editing the section of query3 i.e query3 = ''' DROP TABLE {table_name}; '''

TO query3 = ''' DROP TABLE IF EXISTS {table_name}; '''

This changed the whole game and all the Tables got dropped.... I hope this helps anyone and if there is a better way to do it please drop your answers.