Skip to main content

 

Database Operations in Python

Creating Tables, Insert, Update, Delete, Read and Transaction Control

Python can interact with databases using modules like sqlite3. It allows programmers to perform database operations such as creating tables, inserting records, updating data, deleting data, and reading records.

These operations are commonly called CRUD operations:

  • Create – Create tables and insert data

  • Read – Retrieve data from tables

  • Update – Modify existing records

  • Delete – Remove records from tables

Python also supports Transaction Control, which ensures database operations are completed safely.

1. Creating a Table

A table is created using the CREATE TABLE SQL command.

import sqlite3

conn = sqlite3.connect("college.db")
cursor = conn.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS student(
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT
)
""")

conn.commit()

2. Insert Data

The INSERT statement is used to add records.

cursor.execute("INSERT INTO student (id, name, department) VALUES (1,'Arun','CS')")
conn.commit()

3. Read Data (SELECT)

Used to retrieve data from the table.

cursor.execute("SELECT * FROM student")

rows = cursor.fetchall()
for row in rows:
    print(row)

Output Example:

(1, 'Arun', 'CS')

4. Update Data

Used to modify existing records.

cursor.execute("UPDATE student SET department='IT' WHERE id=1")
conn.commit()

5. Delete Data

Used to remove records from the table.

cursor.execute("DELETE FROM student WHERE id=1")
conn.commit()

6. Transaction Control

Transactions ensure that database operations are safely executed.

Important methods:

MethodPurpose
commit()Save changes permanently
rollback()Undo changes if error occurs
close()Close database connection

Example:

try:
    cursor.execute("INSERT INTO student VALUES (2,'Ravi','ECE')")
    conn.commit()
except:
    conn.rollback()

Basic Python Program (CRUD Example)

import sqlite3

conn = sqlite3.connect("college.db")
cursor = conn.cursor()

# Create table
cursor.execute("CREATE TABLE IF NOT EXISTS student(id INTEGER, name TEXT, dept TEXT)")

# Insert data
cursor.execute("INSERT INTO student VALUES(1,'prasath','CS')")

# Read data
cursor.execute("SELECT * FROM student")
print(cursor.fetchall())

conn.commit()
conn.close()

Output Example

[(1, 'prasath', 'CS')]

Conclusion:
Python database modules allow easy implementation of table creation, data insertion, updating, deleting, reading, and transaction control, which are essential for database-driven applications.

Comments

Post a Comment

Popular posts from this blog

Prasath M

  Data Types in Python In Python, a data type specifies the kind of value a variable can store and determines the operations that can be performed on it. Python is dynamically typed , meaning you do not explicitly declare the data type—Python infers it at runtime. Below is a structured explanation of the primary built-in data types. 1. Numeric Types 1.1 int Represents whole numbers (positive, negative, or zero). x = 10 y = - 5 No decimal point Unlimited precision (only limited by memory) 1.2 float Represents decimal (floating-point) numbers. pi = 3.14 temp = - 2.5 Uses double precision (64-bit IEEE 754) 1.3 complex Represents complex numbers (real + imaginary part). z = 3 + 4j 3 → real part 4j → imaginary part 2. Sequence Types 2.1 str (String) Represents textual data. name = "Prasath" msg = 'Hello' Immutable (cannot be changed after creation) Supports indexing and slicing 2.2 list Ordered, mutabl...
Post a Comment
Read more

prasath M

  Types of Loops in Python  In Python , loops are fundamental control structures that allow a program to execute a block of code repeatedly . They are commonly used for tasks like processing lists, iterating through data, or performing repeated calculations. Python mainly provides two primary types of loops . 1. For Loop The for loop is used to iterate over a sequence such as a list, tuple, string, or a range of numbers. It is typically used when the number of iterations is known . Syntax for variable in sequence : # code block Example for i in range ( 5 ): print ( i ) Output 0 1 2 3 4 Explanation range(5) generates numbers from 0 to 4 . The loop runs once for each value in the sequence. 2. While Loop The while loop repeatedly executes a block of code as long as a condition remains true . It is commonly used when the number of iterations is not predetermined . Syntax while condition : # code block Example i = 1 while i <=...
Post a Comment
Read more
  Date and Time Module in Python  Python provides built-in modules to work with date and time . The most commonly used modules are: datetime module – used to work with dates and times. time module – used for time-related functions such as delays and timestamps. 1. datetime Module The datetime module helps to get the current date, time, and perform date calculations . Importing the module import datetime Example: Display Current Date and Time import datetime now = datetime . datetime . now() print ( "Current Date and Time:" , now ) Output (example): Current Date and Time: 2026-03-09 10:30:15.234567 Example: Display Only Current Date from datetime import date today = date . today() print ( "Today's Date:" , today ) Output: Today's Date: 2026-03-09 Example: Formatting Date and Time from datetime import datetime now = datetime . now() formatted = now . strftime( "%d-%m-%Y %H:%M:%S" ) print ( "Formatt...
Post a Comment
Read more