Hackforge Academy

Explanation

PostgreSQL is a powerful open-source relational database management system commonly used for production applications. Python applications can connect to PostgreSQL using database drivers such as psycopg. The driver allows Python programs to establish database connections, execute SQL queries, retrieve results, and perform CRUD operations. PostgreSQL is widely used with Python backend technologies such as FastAPI, Flask, and Django. Before connecting, the PostgreSQL server should be running and the required Python driver should be installed.

Code:

# Install PostgreSQL driver
# pip install psycopg

import psycopg

print('PostgreSQL driver is ready')

Explanation

A PostgreSQL connection can be created using the psycopg.connect() function. The connection requires details such as the database host, port, database name, username, and password. Once the connection is established, a cursor can be created to execute SQL statements. After completing database operations, the cursor and connection should be closed properly. In real applications, database credentials should be stored in environment variables instead of directly writing passwords in source code.

Code:

import psycopg

connection = psycopg.connect(
    host='localhost',
    port=5432,
    dbname='company',
    user='postgres',
    password='password'
)

print('Connected to PostgreSQL successfully')

connection.close()

Explanation

After connecting to PostgreSQL, a cursor can execute SQL statements. The CREATE TABLE statement creates a database table, while INSERT adds records. PostgreSQL transactions should be committed using connection.commit() after successful INSERT, UPDATE, or DELETE operations. Parameterized queries use placeholders instead of directly concatenating values into SQL strings. This approach improves security and helps prevent SQL injection attacks.

Code:

import psycopg

connection = psycopg.connect(
    host='localhost',
    port=5432,
    dbname='company',
    user='postgres',
    password='password'
)

cursor = connection.cursor()

cursor.execute('''
CREATE TABLE IF NOT EXISTS employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary NUMERIC(10, 2)
)
''')

cursor.execute(
    'INSERT INTO employees (name, salary) VALUES (%s, %s)',
    ('John', 50000)
)

connection.commit()

print('Table created and employee inserted')

cursor.close()
connection.close()

Explanation

The SELECT statement retrieves data from PostgreSQL tables. After executing a SELECT query, fetchone() can retrieve a single record, fetchmany() can retrieve a specified number of records, and fetchall() can retrieve all matching records. The returned records can be processed using normal Python loops. Data retrieval is commonly used in reports, dashboards, APIs, and business applications.

Code:

import psycopg

connection = psycopg.connect(
    host='localhost',
    port=5432,
    dbname='company',
    user='postgres',
    password='password'
)

cursor = connection.cursor()

cursor.execute(
    'SELECT id, name, salary FROM employees'
)

employees = cursor.fetchall()

for employee in employees:
    print(employee)

cursor.close()
connection.close()

Explanation

CRUD stands for Create, Read, Update, and Delete. These operations are the foundation of database-driven applications. Python can perform CRUD operations in PostgreSQL using parameterized SQL queries. INSERT creates records, SELECT retrieves records, UPDATE modifies existing records, and DELETE removes records. The commit() method saves changes permanently. In production applications, database operations should also include proper exception handling, transaction management, connection pooling, and secure credential management.

Code:

import psycopg

connection = psycopg.connect(
    host='localhost',
    port=5432,
    dbname='company',
    user='postgres',
    password='password'
)

cursor = connection.cursor()

# Create
cursor.execute(
    'INSERT INTO employees (name, salary) VALUES (%s, %s)',
    ('Alice', 60000)
)

# Read
cursor.execute('SELECT * FROM employees')
print('Employees:', cursor.fetchall())

# Update
cursor.execute(
    'UPDATE employees SET salary = %s WHERE name = %s',
    (65000, 'Alice')
)

# Delete
cursor.execute(
    'DELETE FROM employees WHERE name = %s',
    ('Alice',)
)

connection.commit()

print('CRUD operations completed')

cursor.close()
connection.close()