SQLite offers a lightweight and beginner-friendly solution. Combined with the power of Python, it becomes a versatile tool for data persistence. In this blog post, we will explore the basics of SQLite with Python and walk through examples of various operations, making it easy for beginners to get started.
We will understand the use of SQLite with Python with basic CRUD(Create Read Update Delete) operations.
What is SQLite?
SQLite is a self-contained, serverless, and file-based relational database engine. It provides a simple way to store and retrieve data without the need for a separate database server. SQLite databases are widely used due to their simplicity, portability, and compatibility with various platforms.
Why SQLite With Python:
- This is because we don’t have to install anything externally to use this with Python. This comes preinstalled with Python.
- This is light weight ,beginner friendly and easy to start with.
Setting Up SQLite with Python:
To begin working in SQLite with Python, follow these steps:
- Import the
sqlite3
module into your Python script. - Establish a connection to an SQLite database file using the
connect()
function. - Create a cursor object using the
cursor()
method to execute SQL statements and interact with the database.
Creating a Table:
Let’s start by creating a table to store information about users. Here’s an example:
import sqlite3
# Establish a connection to the database
conn = sqlite3.connect('mydatabase.db')
# Create a cursor object
cursor = conn.cursor()
# Create the users table
cursor.execute('''CREATE TABLE users
(id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER,
email TEXT NOT NULL)''')
# Commit the changes and close the connection
conn.commit()
conn.close()
Inserting Data:
To add data to the table, we can use the INSERT INTO
statement. Here’s an example:
# Establish a connection to the database
conn = sqlite3.connect('mydatabase.db')
# Create a cursor object
cursor = conn.cursor()
# Insert a new user
cursor.execute("INSERT INTO users (name, age, email) VALUES (?, ?, ?)",
('John Doe', 25, '[email protected]'))
# Commit the changes and close the connection
conn.commit()
conn.close()
Retrieving Data:
To retrieve data from the table, we can use the SELECT
statement. Here’s an example:
# Establish a connection to the database
conn = sqlite3.connect('mydatabase.db')
# Create a cursor object
cursor = conn.cursor()
# Retrieve all users
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
# Display the retrieved data
for row in rows:
print("ID:", row[0])
print("Name:", row[1])
print("Age:", row[2])
print("Email:", row[3])
print("-------------------")
# Close the connection
conn.close()
Updating Data:
To update existing records in the table, we can use the UPDATE
statement. Here’s an example:
# Establish a connection to the database
conn = sqlite3.connect('mydatabase.db')
# Create a cursor object
cursor = conn.cursor()
# Update the age of a user
cursor.execute("UPDATE users SET age = ? WHERE name = ?",
(30, 'John Doe'))
# Commit the changes and close the connection
conn.commit()
conn.close()
Deleting Data:
To delete records from the table, we can use the DELETE FROM
statement. Here’s an example:
# Establish a connection to the database
conn = sqlite3.connect('mydatabase.db')
# Create a cursor object
cursor = conn.cursor()
# Delete a user
cursor.execute("DELETE FROM users WHERE name = ?", ('John Doe',))
# Commit the changes and close the connection
conn.commit()
conn.close()
Conclusion:
In this beginner-friendly guide, we explored the basics of working with SQLite in Python. We learned how to set up SQLite, create a table, insert data, retrieve data, update records, and delete records. By combining SQLite’s simplicity with Python’s versatility, you can efficiently store and manage data for your projects. Remember to customize the examples according to your specific use cases and expand your knowledge by exploring more advanced SQLite features.
Now, armed with the fundamentals, you can dive deeper into the world of SQLite and Python. Happy coding!