Python Database Connectivity
In the previous lesson, we learned about Object-Oriented Programming (OOP) and how Classes and Objects help organize Python applications. Today, we will learn about Database Connectivity, which allows Python programs to store, retrieve, update, and manage data using databases.
Database Connectivity is very important because most real-world applications rely on databases to store information permanently.
What is a Database?
A Database is an organized collection of data stored electronically.
Databases help applications store information such as:
- User accounts
- Student records
- Banking information
- Product details
- Employee data
Why Database Connectivity is Important
Database Connectivity helps applications:
- Store data permanently
- Retrieve information quickly
- Manage large amounts of data
- Support multi-user systems
- Build real-world software applications
Most websites and applications use databases.
Types of Databases
Some common databases include:
Database| Description
MySQL| Popular relational database
SQLite| Lightweight built-in database
PostgreSQL| Advanced open-source database
MongoDB| NoSQL database
Oracle Database| Enterprise database system
SQLite in Python
Python provides built-in support for SQLite using the "sqlite3" module.
SQLite is:
- Lightweight
- Easy to use
- Serverless
- Good for beginners
Importing sqlite3 Module
Example
import sqlite3
Creating a Database Connection
The "connect()" function creates or opens a database.
Example
import sqlite3
connection = sqlite3.connect("students.db")
If the database does not exist, Python creates it automatically.
Creating a Cursor Object
A Cursor is used to execute SQL commands.
Example
cursor = connection.cursor()
Creating a Table
SQL commands are executed using the "execute()" method.
Example
cursor.execute("""
CREATE TABLE students (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER
)
""")
This creates a table named "students".
Inserting Data into a Table
Example
cursor.execute("""
INSERT INTO students (name, age)
VALUES ('Augustine', 21)
""")
Saving Changes
The "commit()" method saves changes permanently.
Example
connection.commit()
Retrieving Data from a Table
The "SELECT" statement retrieves records.
Example
cursor.execute("SELECT * FROM students")
records = cursor.fetchall()
print(records)
Output
[(1, 'Augustine', 21)]
Looping Through Database Records
Example
cursor.execute("SELECT * FROM students")
for row in cursor.fetchall():
print(row)
Updating Database Records
The "UPDATE" statement modifies existing records.
Example
cursor.execute("""
UPDATE students
SET age = 22
WHERE name = 'Augustine'
""")
connection.commit()
Deleting Records
The "DELETE" statement removes records.
Example
cursor.execute("""
DELETE FROM students
WHERE name = 'Augustine'
""")
connection.commit()
Closing the Database Connection
The "close()" method closes the database connection.
Example
connection.close()
Closing connections helps save system resources.
Complete SQLite Example
Example
import sqlite3
connection = sqlite3.connect("students.db")
cursor = connection.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER
)
""")
cursor.execute("""
INSERT INTO students (name, age)
VALUES ('Augustine', 21)
""")
connection.commit()
cursor.execute("SELECT * FROM students")
for row in cursor.fetchall():
print(row)
connection.close()
Database Exception Handling
Database operations may produce errors.
Example
try:
connection = sqlite3.connect("students.db")
print("Database connected")
except sqlite3.Error:
print("Database error occurred")
Advantages of Database Connectivity
Database Connectivity helps applications:
- Manage large amounts of data
- Store records permanently
- Improve data organization
- Support real-time systems
- Build scalable applications
Most enterprise applications rely heavily on databases.
Real-World Applications of Databases
Databases are used in:
- Banking Systems
- School Management Systems
- E-Commerce Platforms
- Hospital Systems
- Social Media Platforms
- Mobile Applications
Conclusion
Database Connectivity is an essential concept in Python programming because it allows applications to store and manage data efficiently.
Understanding how to connect Python with databases helps developers build powerful real-world applications.
Practice creating databases, inserting records, and retrieving data to strengthen your Python database programming skills.
Top comments (0)