Introduction:
Hello developers Python is a high-level, interpreted programming language that emphasizes code readability and simplicity and user friendly crud modules. It was created by Guido van Rossum and first released in 1991. Python supports multiple programming paradigms, including procedural, object-oriented, and functional programming and simply uses crud.lets create a Python CRUD application lets get started.
CRUD:
Start programming, one of the most common tasks developers face involves creating, reading, updating, and deleting records. Known as CRUD operations, these are the four basic functions of persistent storage. In this post, we’ll explore how you can perform CRUD operations using Python with SQLite as a database convenient to use pycharm or spyder idle.
Step 1(Setting up Environment):
First of all , we need to install the SQLite library, which is a built-in Python library so no installation is required. For other databases such as PostgreSQL or MySQL, you might need to install a specific driver.
python --versionThe above code is used for checking the python version
Create a database:
To start, let’s import SQLite and connect to a database
import sqlite3 conn = sqlite3.connect('users.db')
import sqlite3 import from the database
#devil.db refer as a database name Step 2(Creating records):
In Python, the creation process begins with a simple SQL command. Let’s create a table named devil:
Now, let’s insert some records into the user table:
import sqlite3
# Connect to the database
conn = sqlite3.connect('users.db')
c = conn.cursor()
# Function to create a new user
def create_user(name, email):
c.execute("INSERT INTO users (name, email) VALUES (?, ?)", (name, email))
conn.commit()
print("User created successfully!")
# Usage example
create_user("John Doe", "john@example.com")
# Close the connection
conn.close()Step 3 (Reading the users details from the table)
Reading data is accomplished using the SELECT statement:
import sqlite3
# Connect to the database
conn = sqlite3.connect('users.db')
c = conn.cursor()
# Function to retrieve all users
def get_all_users():
c.execute("SELECT * FROM users")
rows = c.fetchall()
for row in rows:
print("ID:", row[0])
print("Name:", row[1])
print("Email:", row[2])
print("--------------------")
# Usage example
get_all_users()
# Close the connection
conn.close()The below screenshot represent the fetched data,
Step 4 (Updating the users details)
To update a record, use the UPDATE statement:
import sqlite3
# Connect to the database
conn = sqlite3.connect('users.db')
c = conn.cursor()
# Function to update a user's email
def update_user_email(user_id, email):
c.execute("UPDATE users SET email = ? WHERE id = ?", (email, user_id))
conn.commit()
print("User email updated successfully!")
# Usage example
update_user_email(1, "john.doe@example.com")
# Close the connection
conn.close()The screenshot shows the output of updated user details
The above screenshot refers to the updated details for the particular user based on their id.
Step 5(Deleting a particular user details with their ID)
Finally, to delete a record, use the DELETE statement:
import sqlite3
# Connect to the database
conn = sqlite3.connect('users.db')
c = conn.cursor()
# Function to delete a user
def delete_user(user_id):
c.execute("DELETE FROM users WHERE id = ?", (user_id,))
conn.commit()
print("User deleted successfully!")
# Usage example
delete_user(2)
# Close the connection
conn.close()The below picture indicates the deletion process.
Full code:
import sqlite3
# Connect to the database
conn = sqlite3.connect('users.db')
c = conn.cursor()
# Create table
c.execute('''CREATE TABLE IF NOT EXISTS users
(id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT NOT NULL)''')
# Function to create a new user
def create_user(name, email):
c.execute("INSERT INTO users (name, email) VALUES (?, ?)", (name, email))
conn.commit()
print("User created successfully!")
# Function to retrieve all users
def get_all_users():
c.execute("SELECT * FROM users")
rows = c.fetchall()
for row in rows:
print("ID:", row[0])
print("Name:", row[1])
print("Email:", row[2])
print("--------------------")
# Function to update a user's email
def update_user_email(user_id, email):
c.execute("UPDATE users SET email = ? WHERE id = ?", (email, user_id))
conn.commit()
print("User email updated successfully!")
# Function to delete a user
def delete_user(user_id):
c.execute("DELETE FROM users WHERE id = ?", (user_id,))
conn.commit()
print("User deleted successfully!")
# Usage example
create_user("John Doe", "john@example.com")
create_user("Jane Smith", "jane@example.com")
print("All Users:")
get_all_users()
update_user_email(1, "john.doe@example.com")
print("All Users:")
get_all_users()
delete_user(2)
print("All Users:")
get_all_users()
# Close the connection
conn.close()Conclusion
CRUD operations are fundamental in any system that interacts with a database. Python, combined with SQLite, offers a simple and efficient way to perform these operations. It’s also crucial to remember to always close your database connection after performing operations to prevent memory leaks and other potential issues so use to close data base for each operation.
Python’s simplicity and the ubiquity of SQL databases make them an excellent pair for handling CRUD operations. However, when scaling to larger applications, you might want to consider using an ORM (Object-Relational Mapping) tool, like SQLAlchemy, to make your database interactions more Pythonic, safe, and efficient to use.
Whether you’re developing a small project or a large scale application, understanding CRUD operations is essential.
Happy coding!
