Show columns from table in PostgreSQL

Check out columns sand their data types in specified table. Hidden under advanced options is database schema selector, allowing easier querying in large databases.

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

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()

Put into recipe table name of which columns you are looking and run it. Results will appear below the code.

# 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 attname AS col, atttypid::regtype AS datatype
                    FROM pg_attribute
                    WHERE attrelid = %s::regclass 
                    AND attnum > 0
                    AND NOT attisdropped
                    ORDER BY attnum;""", 
                    ("products",))
        # 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("Columns of products:")
        for column in cur.fetchall():
            print(f"{column}")

Choosing advanced option reveals database schema field. Public is the default schema for PostgreSQL databases.

# 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 attname AS col, atttypid::regtype AS datatype
                    FROM pg_attribute
                    WHERE attrelid = %s::regclass 
                    AND attnum > 0
                    AND NOT attisdropped
                    ORDER BY attnum;""", 
                    ("public.products",))
        # 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("Columns of public.products:")
        for column in cur.fetchall():
            print(f"{column}")

Conclusions

Showing table columns is required to write correct queries using that table. Use this recipe every time you need a refresher on how your database is layout.

Recipes used in the postgresql-python-show-columns.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-show-columns.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 😊