VNSGU-Practical-DHP-A-Oct-2024

#Python Code for Medicine Table Operations
import sqlite3
import csv

# Connect / Create database
conn = sqlite3.connect("medicine.db")
cursor = conn.cursor()

# ------------------ A. Create Table & Insert Records ------------------
cursor.execute("""
    CREATE TABLE IF NOT EXISTS Medicine(
        Med_Id INTEGER PRIMARY KEY,
        Med_Name TEXT,
        Qty INTEGER,
        Rate REAL
    )
""")

# Insert at least 10 records
medicines = [
    (1, "Crocin", 10, 5.0),
    (2, "Cough Syrup", 5, 50.0),
    (3, "Coldact", 20, 2.5),
    (4, "Calpol", 15, 3.0),
    (5, "Cetrizine", 12, 1.5),
    (6, "Paracetamol", 25, 2.0),
    (7, "Disprin", 30, 1.0),
    (8, "Cheston Cold", 8, 12.0),
    (9, "Vitamin C", 18, 4.0),
    (10, "Azithromycin", 10, 15.0)
]

cursor.executemany("INSERT OR IGNORE INTO Medicine VALUES (?, ?, ?, ?)", medicines)
conn.commit()
print("Inserted 10 records successfully.")

# ------------------ B. Add Column Total & Calculate ------------------
try:
    cursor.execute("ALTER TABLE Medicine ADD COLUMN Total REAL")
except:
    pass  # Ignore if column already exists

cursor.execute("UPDATE Medicine SET Total = Qty * Rate")
conn.commit()
print("Total column updated with Qty * Rate.")

# ------------------ C. Display Records with Name starting with 'C' ------------------
print("\nMedicines starting with 'C':")
cursor.execute("SELECT * FROM Medicine WHERE Med_Name LIKE 'C%'")
rows = cursor.fetchall()
for row in rows:
    print(row)

# ------------------ D. Export Table Data to CSV ------------------
cursor.execute("SELECT * FROM Medicine")
all_data = cursor.fetchall()

with open("medicines.csv", "w", newline="") as f:
    writer = csv.writer(f)
    # Write header
    writer.writerow([i[0] for i in cursor.description])
    # Write data
    writer.writerows(all_data)

print("\nData exported to medicines.csv successfully.")

# Close connection
conn.close()