sqlalchemy drop table issues

Question

I am working on some Python code to create and drop some database tables. The code to create the table is working correctly, however I am having issues with the drop table code. Here is my stack info:

Python 3.8
SQLAlchemy 1.3.23
Postgres 12

have a schema(asia) and a table(sales), that I am trying to drop sales from asia using the following python code:

from sqlalchemy import MetaData
from sqlalchemy import create_engine
from sqlalchemy.engine.url import URL
from sqlalchemy.ext.declarative import declarative_base

# Import log configuration file
import os
import sys
sys.path.append('../..')
from conf import dbconfig
from conf import alchemy_config

# Import log configuration file
sys.path.append('../..')
from conf import alchemy_config


def drop_table(schema_name, table_name):
   # engine = create_engine(URL(**DATABASE))
   # engine = create_engine(alchemy_config.DATABASE_URI_UK, echo=False)
   engine = create_engine(alchemy_config.DATABASE_URI_UK, echo=True)
   connection = engine.connect()

   # base = declarative_base()
   meta = MetaData(schema=schema_name)
   meta.reflect(bind=engine)

   table = meta.tables.get(table_name)
   # table = Table(table_name, metadata)

   if (table is not None):
       print(f'Deleting {table_name} table')
       # base.metadata.drop_all(engine, table, checkfirst=True)
       base.metadata.drop_all(engine)
   else:
       print(f"Table {table_name} could not be found")


def main():
  table_name = 'sales'
  schema_name = 'asia'

  drop_table(schema_name, table_name)


if __name__ == "__main__":
  main()

This code is unable to locate the table and here is the output:

2021-03-01 18:13:06,830 INFO sqlalchemy.engine.base.Engine select version()
2021-03-01 18:13:06,830 INFO sqlalchemy.engine.base.Engine {}
2021-03-01 18:13:06,831 INFO sqlalchemy.engine.base.Engine select current_schema()
2021-03-01 18:13:06,831 INFO sqlalchemy.engine.base.Engine {}
2021-03-01 18:13:06,832 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2021-03-01 18:13:06,832 INFO sqlalchemy.engine.base.Engine {}
2021-03-01 18:13:06,833 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2021-03-01 18:13:06,833 INFO sqlalchemy.engine.base.Engine {}
2021-03-01 18:13:06,833 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2021-03-01 18:13:06,833 INFO sqlalchemy.engine.base.Engine {}
2021-03-01 18:13:06,842 INFO sqlalchemy.engine.base.Engine SELECT c.relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = %(schema)s AND c.relkind in ('r', 'p')
2021-03-01 18:13:06,843 INFO sqlalchemy.engine.base.Engine {'schema': 'asia'}
2021-03-01 18:13:06,845 INFO sqlalchemy.engine.base.Engine 
            SELECT c.oid
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE (n.nspname = %(schema)s)
            AND c.relname = %(table_name)s AND c.relkind in
            ('r', 'v', 'm', 'f', 'p')
        
2021-03-01 18:13:06,846 INFO sqlalchemy.engine.base.Engine {'schema': 'asia', 'table_name': 'sales'}
2021-03-01 18:13:06,847 INFO sqlalchemy.engine.base.Engine 
            SELECT a.attname,
              pg_catalog.format_type(a.atttypid, a.atttypmod),
              (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)
                FROM pg_catalog.pg_attrdef d
               WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum
               AND a.atthasdef)
              AS DEFAULT,
              a.attnotnull, a.attnum, a.attrelid as table_oid,
              pgd.description as comment,
              a.attgenerated as generated
            FROM pg_catalog.pg_attribute a
            LEFT JOIN pg_catalog.pg_description pgd ON (
                pgd.objoid = a.attrelid AND pgd.objsubid = a.attnum)
            WHERE a.attrelid = %(table_oid)s
            AND a.attnum > 0 AND NOT a.attisdropped
            ORDER BY a.attnum
        
2021-03-01 18:13:06,847 INFO sqlalchemy.engine.base.Engine {'table_oid': 17161}
2021-03-01 18:13:06,850 INFO sqlalchemy.engine.base.Engine 
            SELECT t.typname as "name",
               pg_catalog.format_type(t.typbasetype, t.typtypmod) as "attype",
               not t.typnotnull as "nullable",
               t.typdefault as "default",
               pg_catalog.pg_type_is_visible(t.oid) as "visible",
               n.nspname as "schema"
            FROM pg_catalog.pg_type t
               LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
            WHERE t.typtype = 'd'
        
2021-03-01 18:13:06,850 INFO sqlalchemy.engine.base.Engine {}
2021-03-01 18:13:06,851 INFO sqlalchemy.engine.base.Engine 
            SELECT t.typname as "name",
               -- no enum defaults in 8.4 at least
               -- t.typdefault as "default",
               pg_catalog.pg_type_is_visible(t.oid) as "visible",
               n.nspname as "schema",
               e.enumlabel as "label"
            FROM pg_catalog.pg_type t
                 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
                 LEFT JOIN pg_catalog.pg_enum e ON t.oid = e.enumtypid
            WHERE t.typtype = 'e'
        ORDER BY "schema", "name", e.oid
2021-03-01 18:13:06,851 INFO sqlalchemy.engine.base.Engine {}
2021-03-01 18:13:06,854 INFO sqlalchemy.engine.base.Engine 
                SELECT a.attname
                FROM pg_attribute a JOIN (
                    SELECT unnest(ix.indkey) attnum,
                           generate_subscripts(ix.indkey, 1) ord
                    FROM pg_index ix
                    WHERE ix.indrelid = %(table_oid)s AND ix.indisprimary
                    ) k ON a.attnum=k.attnum
                WHERE a.attrelid = %(table_oid)s
                ORDER BY k.ord
            
2021-03-01 18:13:06,854 INFO sqlalchemy.engine.base.Engine {'table_oid': 17161}
2021-03-01 18:13:06,856 INFO sqlalchemy.engine.base.Engine 
        SELECT conname
           FROM  pg_catalog.pg_constraint r
           WHERE r.conrelid = %(table_oid)s AND r.contype = 'p'
           ORDER BY 1
        
2021-03-01 18:13:06,856 INFO sqlalchemy.engine.base.Engine {'table_oid': 17161}
2021-03-01 18:13:06,859 INFO sqlalchemy.engine.base.Engine 
          SELECT r.conname,
                pg_catalog.pg_get_constraintdef(r.oid, true) as condef,
                n.nspname as conschema
          FROM  pg_catalog.pg_constraint r,
                pg_namespace n,
                pg_class c

          WHERE r.conrelid = %(table)s AND
                r.contype = 'f' AND
                c.oid = confrelid AND
                n.oid = c.relnamespace
          ORDER BY 1
        
2021-03-01 18:13:06,859 INFO sqlalchemy.engine.base.Engine {'table': 17161}
2021-03-01 18:13:06,860 INFO sqlalchemy.engine.base.Engine 
              SELECT
                  i.relname as relname,
                  ix.indisunique, ix.indexprs, ix.indpred,
                  a.attname, a.attnum, c.conrelid, ix.indkey::varchar,
                  ix.indoption::varchar, i.reloptions, am.amname,
                  ix.indnkeyatts as indnkeyatts
              FROM
                  pg_class t
                        join pg_index ix on t.oid = ix.indrelid
                        join pg_class i on i.oid = ix.indexrelid
                        left outer join
                            pg_attribute a
                            on t.oid = a.attrelid and a.attnum = ANY(ix.indkey)
                        left outer join
                            pg_constraint c
                            on (ix.indrelid = c.conrelid and
                                ix.indexrelid = c.conindid and
                                c.contype in ('p', 'u', 'x'))
                        left outer join
                            pg_am am
                            on i.relam = am.oid
              WHERE
                  t.relkind IN ('r', 'v', 'f', 'm', 'p')
                  and t.oid = %(table_oid)s
                  and ix.indisprimary = 'f'
              ORDER BY
                  t.relname,
                  i.relname
            
2021-03-01 18:13:06,860 INFO sqlalchemy.engine.base.Engine {'table_oid': 17161}
2021-03-01 18:13:06,863 INFO sqlalchemy.engine.base.Engine 
            SELECT
                cons.conname as name,
                cons.conkey as key,
                a.attnum as col_num,
                a.attname as col_name
            FROM
                pg_catalog.pg_constraint cons
                join pg_attribute a
                  on cons.conrelid = a.attrelid AND
                    a.attnum = ANY(cons.conkey)
            WHERE
                cons.conrelid = %(table_oid)s AND
                cons.contype = 'u'
        
2021-03-01 18:13:06,863 INFO sqlalchemy.engine.base.Engine {'table_oid': 17161}
2021-03-01 18:13:06,864 INFO sqlalchemy.engine.base.Engine 
            SELECT
                cons.conname as name,
                pg_get_constraintdef(cons.oid) as src
            FROM
                pg_catalog.pg_constraint cons
            WHERE
                cons.conrelid = %(table_oid)s AND
                cons.contype = 'c'
        
2021-03-01 18:13:06,864 INFO sqlalchemy.engine.base.Engine {'table_oid': 17161}
2021-03-01 18:13:06,864 INFO sqlalchemy.engine.base.Engine 
            SELECT
                pgd.description as table_comment
            FROM
                pg_catalog.pg_description pgd
            WHERE
                pgd.objsubid = 0 AND
                pgd.objoid = %(table_oid)s
        
2021-03-01 18:13:06,865 INFO sqlalchemy.engine.base.Engine {'table_oid': 17161}
Table sales could not be found

What am I doing wrong? Please let me know.

Thank you!

------------------------ SOLUTION w/ Gord's help ------------------------

import logging
from sqlalchemy import MetaData
from sqlalchemy import create_engine
from sqlalchemy.engine.url import URL
from sqlalchemy.ext.declarative import declarative_base

import os
import sys
sys.path.append('../..')
from conf import dbconfig
from conf import alchemy_config

sys.path.append('../..')
from conf import alchemy_config


def drop_table(schema_name, table_name):
   engine = create_engine(alchemy_config.DATABASE_URI_UK, echo=True)
   connection = engine.connect()

   base = declarative_base()
   meta = MetaData(schema=schema_name)
   meta.reflect(bind=engine)

   table = meta.tables.get(".".join([schema_name, table_name]))

   print(f" Table {table} data type is: {type(table)}")
   if (table is not None):
       print(f'Deleting {table_name} table')
       base.metadata.drop_all(engine, [table], checkfirst=True)
   else:
       print(f"Table {table_name} could not be found")


def main():
  table_name = 'sales'
  schema_name = 'asia'

  drop_table(schema_name, table_name)


if __name__ == "__main__":
  main()

sqlalchemy drop table issues

Answers (1)

Related Questions