Drop table using Python and PostgreSQL

Dropping a table means deleting it for good. Before doing so you might want to back up the table. You can learn about it in this notebook.

This notebook was created with MLJAR Studio

MLJAR Studio is Python code editior with interactive code recipes and local AI assistant.
You have code recipes UI displayed at the top of code cells.

Documentation

All required packages are automatically imported by MLJAR Studio for you so you don't have to worry about them.

# import packages
import psycopg
import os
from dotenv import load_dotenv
from psycopg import sql

Make sure you opened a connection in your notebook. To learn how to do it, check out open and test database connection example notebook.

# load credentials from .env file:
load_dotenv(override=True)

# get the credentials
def create_new_connection():
    try:
        conn = psycopg.connect(
            dbname=os.getenv("POSTGRES_DB_NAME"),
            user=os.getenv("POSTGRES_USERNAME"),
            password=os.getenv("POSTGRES_PASSWORD"),
            host=os.getenv("POSTGRES_HOST"),
            port=os.getenv("POSTGRES_PORT"),
        )
        return conn
    # check for errors
    except psycopg.Error as e:
        raise psycopg.Error(f"""
Error occurred while establishing connection: 
    {e}

Maybe you provided wrong credentials, use define new connection recipe to edit them.
Other option is that database server is down or you dont have the right acces to this database.
            """)

# open new connection:
conn = create_new_connection()

Let's list all tables in out database, you can do so using show all tables recipe. We want to focus on my_new_table table that was created in create new table notebook.

# if connection was used and closed it is reopen here
if conn.closed:
    conn = create_new_connection()

# run query
with conn:
    with conn.cursor() as cur:

        # query db
        try:
            cur.execute("""
                SELECT table_name
                FROM information_schema.tables
                WHERE table_type = 'BASE TABLE'
                AND table_schema NOT IN ('pg_catalog', 'information_schema');
            """)
        # check for errors
        except psycopg.ProgrammingError as e:
            raise psycopg.ProgrammingError(f"""
Problem running query:
    {e}

Did you spell everything correctly?
You can use show tables and columns recipes.
            """)

        # print the results
        print("Tables:")
        for table in cur.fetchall():
            print(f"{table}")

To delete my_new_table table drop table use drop table recipe and provide name of the table to be dropped. Drop options refer to if other related tables should be dropped too. Restricted is the default option and it means that if any other table depends on table we chose to drop, operation will be aborted an we will see an error. Cascade means that foreign keys in other tables that reference out target table will be deleted.

# if connection was used and closed it is reopen here
if conn.closed:
    conn = create_new_connection()

# run query
with conn:
    with conn.cursor() as cur:

        # drop table
        try:
            cur.execute(
                sql.SQL("DROP TABLE {table};")
                    .format(table=sql.Identifier("my_new_table"))
            )
        # check for errors
        except psycopg.ProgrammingError as e:
            raise psycopg.ProgrammingError(f"""
Problem dropping table:
    {e}

Are you sure this table exists?
Mayby some other tables reference this table? Choose CASCADE option then.
            """)

If show all tables is run again we can see that my_new_table is no longer present on the result list.

# if connection was used and closed it is reopen here
if conn.closed:
    conn = create_new_connection()

# run query
with conn:
    with conn.cursor() as cur:

        # query db
        try:
            cur.execute("""
                SELECT table_name
                FROM information_schema.tables
                WHERE table_type = 'BASE TABLE'
                AND table_schema NOT IN ('pg_catalog', 'information_schema');
            """)
        # check for errors
        except psycopg.ProgrammingError as e:
            raise psycopg.ProgrammingError(f"""
Problem running query:
    {e}

Did you spell everything correctly?
You can use show tables and columns recipes.
            """)

        # print the results
        print("Tables:")
        for table in cur.fetchall():
            print(f"{table}")

Conclusions

Dropping table is a destructive operation that should be only be done by users that now what they are doing. This notebook presents the basics of how to approach this task.

Recipes used in the postgresql-python-drop-table.ipynb

All code recipes used in this notebook are listed below. You can click them to check their documentation.

Packages used in the postgresql-python-drop-table.ipynb

List of packages that need to be installed in your Python environment to run this notebook. Please note that MLJAR Studio automatically installs and imports required modules for you.

psycopg>=3.2.1

python-dotenv>=1.0.1

Similar notebooks

List of similar Python notebooks, so you can find more inspiration 😊