Database CRUD operations using Python

Database operations are the backbone of any application that deals with data storage and retrieval. Python offers a plethora of frameworks that simplify CRUD (Create, Read, Update, Delete) operations, enabling developers to efficiently interact with databases. In this blog, we’ll explore how to perform CRUD operations using popular Python frameworks, complete with code examples for each operation.

Table of Contents

• Frameworks for Database CRUD Operations:
  • SQLite
  • SQLAlchemy:
  • Django ORM:
  • Peewee:
• Conclusion:
• FAQ

Frameworks for Database CRUD Operations:

SQLite

SQLite is a software library that provides a relational database management system (RDBMS). One of its key features is that it’s a self-contained, serverless, and zero-configuration database engine. Unlike other database systems like MySQL, PostgreSQL, or Oracle, SQLite doesn’t require a separate server process to operate, making it ideal for embedded systems and small to medium-sized applications. The sqlite3 library in Python offers an SQL interface that aligns with the DB-API 2.0 standard outlined in PEP 249. The sqlite3module is part of the standard library for Python 2.5 and above.

Establish a connection to SQLite database

import sqlite3
# connect to database it is exists or create it
conn = sqlite3.connect('example.db')

Create a table

def create_table():
  c = conn.cursor()
  c.execute('''CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)''')
  conn.commit()

Insert data into table

def insert_data(name, email):
  c = conn.cursor()
  c.execute("INSERT INTO users (name, email) VALUES (?, ?)", (name, email))
  conn.commit()

Read data from table

def read_data():
  c = conn.cursor()
  c.execute("SELECT * FROM users")
  rows = c.fetchall()
  for row in rows:
    print(row)

Update data in table

def update_data(new_name, id):
  c = conn.cursor()
  c.execute("UPDATE users SET name=? WHERE id=?", (new_name, id))
  conn.commit()

Delete data from table

def delete_data(id):
  c = conn.cursor()
  c.execute("DELETE FROM users WHERE id=?", (id,))
  conn.commit()

Main function to execute CRUD operations

def main():
  # Create table
  create_table()
  # Insert data
  insert_data('John Doe', 'john@example.com')
  insert_data('Jane Doe', 'jane@example.com')
  
  # Read data
  print("Before Update:")
  read_data()
  
  # Update data
  update_data('John Updated', 1)
  
  # Read data
  print("After Update:")
  read_data()
  
  # Delete data
  delete_data(1)
  
  # Read data
  print("After Delete:")
  read_data()
  
  # Close the connection
  conn.close()
  
if __name__ == "__main__":
  main()

SQLAlchemy:

SQLAlchemy, a powerful ORM (Object Relational Mapping) library, simplifies database interactions by representing database tables as Python classes. Let’s dive into code examples for each CRUD operation:

• Create Operation

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    username = Column(String, unique=True)
    email = Column(String, unique=True)

engine = create_engine('sqlite:///users.db')
Session = sessionmaker(bind=engine)
session = Session()

new_user = User(username='john_doe', email='john@example.com')
session.add(new_user)
session.commit()

• Read Operation:

users = session.query(User).all()
for user in users:
    print(user.id, user.username, user.email)

• Update Operation:

user = session.query(User).filter_by(username='john_doe').first()
user.email = 'new_email@example.com'
session.commit()

• Delete Opertion

user_to_delete = session.query(User).filter_by(username='john_doe').first()
session.delete(user_to_delete)
session.commit()

Django ORM:

Django’s built-in ORM provides a convenient way to perform CRUD operations. Here’s how you can achieve each operation:

• Create Operation

from myapp.models import User

new_user = User(username='jane_doe', email='jane@example.com')
new_user.save()

• Read Operation:

users = User.objects.all()
for user in users:
    print(user.username, user.email)

• Update Operation:

user = User.objects.get(username='jane_doe')
user.email = 'updated_email@example.com'
user.save()

• Delete Opertion

user_to_delete = User.objects.get(username='jane_doe')
user_to_delete.delete()

Peewee:

Peewee, a lightweight ORM, simplifies CRUD operations with a concise syntax:

• Create Operation

from peewee import Model, CharField, SqliteDatabase

db = SqliteDatabase('my_database.db')

class User(Model):
    username = CharField(unique=True)
    email = CharField(unique=True)

db.connect()
db.create_tables([User])

new_user = User(username='jimmy', email='jimmy@example.com')
new_user.save()

• Read Operation:

users = User.select()
for user in users:
    print(user.username, user.email)

• Update Operation:

user = User.get(username='jimmy')
user.email = 'new_email@example.com'
user.save()

• Delete Opertion

user_to_delete = User.get(username='jimmy')
user_to_delete.delete_instance()

Conclusion:

Python frameworks like SQLAlchemy, Django ORM, and Peewee offer powerful tools to simplify CRUD operations when working with databases. Understanding how to perform these operations using these frameworks is essential for any developer aiming to build robust and efficient applications that interact seamlessly with databases. By following the provided code examples, you can master the art of performing CRUD operations and elevate your skills in database-driven application development.

FAQ

Q. What are CRUD operations?

A. CRUD stands for Create, Read, Update, and Delete. These are the basic operations that you can perform on a database.

Q. Which libraries in Python can be used for CRUD operations?

A. Libraries for CRUD operations:

1. SQLite: Built into Python standard library.
2. SQLAlchemy: An Object Relational Mapping (ORM) library.
3. MySQL Connector: For MySQL databases.
4. psycopg2: For PostgreSQL databases.
5. PyMongo: For MongoDB databases.

Q. What are some common errors and how can I troubleshoot them?

A. Common Errors:

1. OperationalError: Usually means that there’s a syntax error in your SQL command.
2. IntegrityError: Occurs when you’re inserting data that doesn’t adhere to constraints (like unique constraints).
3. DataError: Occurs when you’re trying to insert data of the wrong type.
4. Troubleshooting: Always check your SQL syntax, ensure your database connection is active, and make sure you’re using the correct data types.