Introduction to SQLite
SQLite is a C library that provides a lightweight disk-based database. It doesn’t require a separate server process, making it ideal for embedded applications, development servers, or local data storage.
Python introduces several enhancements and optimizations, making it more appealing for developers to implement database operations efficiently. This tutorial covers the CRUD (Create, Read, Update, Delete) operations in Python 3 using the built-in sqlite3 module. By the end of this tutorial, you’ll have a solid foundation for managing SQLite databases in Python.
Ensure you have Python 3 installed. Verify this by running python --version in your terminal. If you don’t have it, download it from the official Python website.
Creating a Database and Table
To perform any CRUD operation, first, we need to create a database and table. Here’s how:
import sqlite3
def create_connection(db_file):
""" create a database connection to a SQLite database """
conn = None
try:
conn = sqlite3.connect(db_file)
print(f'Successful connection to {db_file}')
except sqlite3.Error as e:
print(e)
finally:
if conn:
conn.close()
if __name__ == '__main__':
create_connection(r'test.db')
This function creates a connection to an SQLite database or creates the database if it doesn’t exist. Notice the use of r before the database name, indicating a raw string to handle any escape characters.
Creating a Table
Once the database is up, let’s create a table named users.
def create_table(db_file):
create_users_table_sql = '''
CREATE TABLE IF NOT EXISTS users (
id integer PRIMARY KEY,
name text NOT NULL,
email text NOT NULL UNIQUE
);
'''
conn = sqlite3.connect(db_file)
try:
c = conn.cursor()
c.execute(create_users_table_sql)
conn.commit()
print("Table created successfully.")
except sqlite3.Error as e:
print(e)
finally:
conn.close()
Now, let’s add some data to our table.
CREATE: Adding Data
To insert data:
def add_user(db_file, user):
sql = ''' INSERT INTO users(name, email)
VALUES(?,?) '''
conn = sqlite3.connect(db_file)
cur = conn.cursor()
cur.execute(sql, user)
conn.commit()
return cur.lastrowid
Continuing, we need to read data.
READ: Fetching Data
For reading data:
def select_all_users(db_file):
conn = sqlite3.connect(db_file)
cur = conn.cursor()
cur.execute("SELECT * FROM users")
rows = cur.fetchall()
for row in rows:
print(row)
Up until now, we have implemented CREATE and READ. Next, let’s tackle UPDATE.
UPDATE: Modifying Data
To update a user’s email:
def update_user(db_file, user):
sql = ''' UPDATE users
SET email = ?
WHERE name = ?'''
conn = sqlite3.connect(db_file)
cur = conn.cursor()
cur.execute(sql, user)
conn.commit()
Lastly, we DEAL with the deletion of data.
DELETE: Removing Data
To delete a user:
def delete_user(db_file, name):
sql = 'DELETE FROM users WHERE name = ?'
conn = sqlite3.connect(db_file)
cur = conn.cursor()
cur.execute(sql, (name,))
conn.commit()
Bringing It All Together
By now, you should be comfortable with the basic CRUD operations using the sqlite3 module in Python 3.11. As a best practice, remember to handle exceptions and close your database connections properly. Implement proper error handling and data validation in your real-world applications.
Conclusion
In this tutorial, we’ve explored how to perform CRUD operations using the sqlite3 module in Python 3. These basic operations form the backbone of database management and serve as a solid foundation for building more complex data-driven applications. Happy coding!
