Python Script to Create an Employee Table in a Cloud Database

[Trupti81]

Introduction:
Creating an employee table in a Cloud PostgreSQL database using Python helps in organizing and storing employee information in a structured way. By using libraries like psycopg2, Python can easily connect to the database and execute SQL commands. This method is commonly used in modern applications that rely on cloud-based data storage and backend systems.

Body:

Write python code to create employee table as below

import psycopg2
import os
from dotenv import load_dotenv

# Load environment variables from .env file
load_dotenv(dotenv_path='../.env')

def create_employee_table():
    try:
        # Connect to the PostgreSQL database
        conn = psycopg2.connect(os.getenv('DB_URL'))
        cur = conn.cursor()

        # SQL to create employee table
        create_table_sql = """
        CREATE TABLE IF NOT EXISTS employee (
            id SERIAL PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            gender VARCHAR(2),
            salary DECIMAL(10, 2),
            currency VARCHAR(3)
        );
        """

        # Execute the SQL
        cur.execute(create_table_sql)
        conn.commit()

        print("Employee table created successfully!")

    except Exception as e:
        print(f"Error creating table: {e}")

    finally:
        if 'cur' in locals():
            cur.close()
        if 'conn' in locals():
            conn.close()

if __name__ == "__main__":
    create_employee_table()

Execute the file in terminal as follows

Check if the table created in PostgreSQL admin console

Summary:
To achieve this, you first connect to the Cloud PostgreSQL database using Python with the required credentials. Then, you execute a SQL statement to create the employee table with necessary columns. Once the table is created, it can be used to securely store and manage employee data.