Python Database Access
Most modern applications need to store and retrieve data. Databases provide a reliable way to manage information such as users, products, orders, and transactions.
Python offers powerful tools for database access, allowing developers to connect to databases, execute SQL queries, and manage data efficiently.
Python supports various database systems including:
- SQLite
- MySQL
- PostgreSQL
- Oracle
- Microsoft SQL Server
- MongoDB
In this tutorial, we'll focus on the fundamentals of database access using SQLite, which is included with Python.
What is Database Access?
Database access refers to the process of:
- Connecting to a database
- Executing SQL commands
- Reading records
- Updating information
- Deleting data
- Managing transactions
Python makes these operations simple through database libraries.
Python Database API (DB-API)
Python follows a standard called DB-API.
Most database modules provide:
connect()
cursor()
execute()
fetchone()
fetchall()
commit()
rollback()
close()This makes switching between databases easier.
SQLite in Python
SQLite is a lightweight, file-based database system.
Advantages:
- No server required
- Easy to use
- Included with Python
- Perfect for learning and small projects
Import SQLite module:
import sqlite3Creating a Database Connection
import sqlite3
conn = sqlite3.connect("company.db")
print("Database Connected")If the database file does not exist, SQLite automatically creates it.
Creating a Cursor Object
A cursor allows Python to execute SQL statements.
cursor = conn.cursor()Creating a Table
import sqlite3
conn = sqlite3.connect("company.db")
cursor = conn.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS employees(
id INTEGER PRIMARY KEY,
name TEXT,
salary REAL
)
""")
conn.commit()
conn.close()Inserting Data
import sqlite3
conn = sqlite3.connect("company.db")
cursor = conn.cursor()
cursor.execute(
"INSERT INTO employees(name, salary) VALUES (?, ?)",
("John", 5000)
)
conn.commit()
conn.close()Inserting Multiple Records
employees = [
("Alice", 6000),
("Bob", 5500),
("David", 7000)
]
cursor.executemany(
"INSERT INTO employees(name, salary) VALUES (?, ?)",
employees
)Retrieving Data
Fetch All Records
cursor.execute("SELECT * FROM employees")
rows = cursor.fetchall()
for row in rows:
print(row)Output
(1, 'John', 5000)
(2, 'Alice', 6000)
(3, 'Bob', 5500)Fetch One Record
cursor.execute("SELECT * FROM employees")
row = cursor.fetchone()
print(row)Updating Records
cursor.execute(
"UPDATE employees SET salary=? WHERE id=?",
(6500, 1)
)
conn.commit()Deleting Records
cursor.execute(
"DELETE FROM employees WHERE id=?",
(1,)
)
conn.commit()Using WHERE Clause
cursor.execute(
"SELECT * FROM employees WHERE salary > ?",
(5500,)
)
records = cursor.fetchall()
for record in records:
print(record)Ordering Results
cursor.execute(
"SELECT * FROM employees ORDER BY salary DESC"
)
rows = cursor.fetchall()Counting Records
cursor.execute(
"SELECT COUNT(*) FROM employees"
)
count = cursor.fetchone()
print(count[0])Using SQL Parameters
Never build SQL queries using string concatenation.
Bad Example:
query = "SELECT * FROM users WHERE id=" + user_idGood Example:
cursor.execute(
"SELECT * FROM users WHERE id=?",
(user_id,)
)This helps prevent SQL Injection attacks.
Transactions
A transaction ensures database consistency.
try:
cursor.execute(
"UPDATE accounts SET balance=balance-100 WHERE id=1"
)
cursor.execute(
"UPDATE accounts SET balance=balance+100 WHERE id=2"
)
conn.commit()
except:
conn.rollback()If an error occurs, changes are undone.
Closing Connections
Always close database resources.
cursor.close()
conn.close()This prevents memory leaks and connection issues.
Complete CRUD Example
CRUD stands for:
- Create
- Read
- Update
- Delete
Create
cursor.execute(
"INSERT INTO employees(name, salary) VALUES (?, ?)",
("Tom", 4500)
)Read
cursor.execute("SELECT * FROM employees")Update
cursor.execute(
"UPDATE employees SET salary=5000 WHERE id=1"
)Delete
cursor.execute(
"DELETE FROM employees WHERE id=1"
)Working with MySQL
For MySQL, install a connector:
pip install mysql-connector-pythonConnection example:
import mysql.connector
conn = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="company"
)Working with PostgreSQL
Install PostgreSQL adapter:
pip install psycopg2Connection example:
import psycopg2
conn = psycopg2.connect(
database="company",
user="postgres",
password="password",
host="localhost"
)Best Practices
Use Parameterized Queries
Protect against SQL injection.
Close Connections
Always release resources.
Use Transactions
Maintain data consistency.
Handle Exceptions
Prevent application crashes.
Normalize Data
Reduce duplication.
Common Database Errors
Database Locked
Occurs when multiple processes access SQLite simultaneously.
Solution:
- Close unused connections
- Commit transactions promptly
Table Not Found
Error:
no such tableSolution:
- Verify table name
- Ensure table creation query was executed
Connection Failed
Check:
- Hostname
- Username
- Password
- Database name
Real-World Applications
Database access is essential for:
- Web applications
- E-commerce systems
- Banking software
- Inventory management
- Customer management systems
- Reporting dashboards
- Mobile applications
Almost every business application uses databases.
Summary
Python provides simple yet powerful tools for database access. Through modules such as SQLite, MySQL Connector, and PostgreSQL adapters, developers can create, read, update, and delete data efficiently.
Understanding database connectivity and SQL operations is a fundamental skill for building real-world Python applications.
Conclusion
Database access is one of the most important aspects of software development. Python's database libraries make it easy to connect to databases, execute queries, and manage data securely.
Whether you're building a small desktop application or a large enterprise system, mastering Python database access will help you create reliable and scalable applications.
0 Comments