How to Implement CRUD Operations with SQLite and Python

Posted on by Panther

SQLite is a lightweight, file-based database engine that is widely used in various applications and development scenarios due to its simplicity and ease of use. In this tutorial, we will explore how to implement CRUD (Create, Read, Update, and Delete) operations with SQLite using Python.

Prerequisites

Before we begin, make sure you have the following installed on your system:

  • Python: You can download and install Python from the official website.
  • SQLite: Most Linux distributions come with SQLite pre-installed. For Windows, you can download SQLite from its official website.

Once you have these prerequisites set up, let’s proceed with implementing CRUD operations with SQLite and Python.

Step 1: Creating a Database and Connecting to it

The first step is to create a SQLite database file and establish a connection to it using Python. To do this, follow the steps below:

  1. Open your favorite text editor or IDE and create a new Python file, such as database.py.
  2. Import the sqlite3 module by adding the following line at the top of your file:
import sqlite3
  1. Create a connection to the database by running the following code:
connection = sqlite3.connect('mydatabase.db')

This code establishes a connection to a SQLite database file named mydatabase.db. If the file doesn’t exist, SQLite will create it automatically.

Step 2: Creating a Table

In this step, we will create a table in the database to store our data. For demonstration purposes, let’s imagine we want to create a simple contacts table with the following columns:

  • id: A unique identifier for each contact.
  • name: The contact’s name.
  • email: The contact’s email address.
  • phone: The contact’s phone number.

To create this table, add the following code after establishing the database connection:

cursor = connection.cursor()

create_table_query = '''
CREATE TABLE IF NOT EXISTS contacts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    phone TEXT NOT NULL
);
'''

cursor.execute(create_table_query)

The cursor object is used to execute SQL queries. In the create_table_query variable, we define the SQL statement to create the contacts table if it doesn’t exist already.

Note that we use the IF NOT EXISTS clause in the CREATE TABLE statement to avoid re-creating the table if it already exists.

Step 3: Inserting Data

In this step, we will learn how to insert data into the contacts table. Suppose we have the following contact information:

To insert this data into the table, add the following code:

insert_query = '''
INSERT INTO contacts (name, email, phone)
VALUES (?, ?, ?)
'''

values = ('John Doe', '[email protected]', '123-456-7890')

cursor.execute(insert_query, values)

connection.commit()

In the insert_query variable, we define the SQL statement to insert a new row into the contacts table with the provided values. The ? placeholders are used as parameter markers to safely insert values into the query.

We then define the values to be inserted in the values variable. In this case, we use a tuple to store the name, email, and phone values.

Finally, we execute the query by calling the execute() method on the cursor object and pass the query and values as arguments. After executing the query, we commit the changes to the database using the commit() method of the connection object.

Step 4: Retrieving Data

Now that we have inserted some data into the table, let’s learn how to retrieve it. To retrieve data from the contacts table, add the following code:

select_query = '''
SELECT * FROM contacts
'''

cursor.execute(select_query)

rows = cursor.fetchall()

for row in rows:
    print(row)

In the select_query variable, we define the SQL statement to retrieve all rows from the contacts table.

We then execute the query by calling the execute() method on the cursor object. After executing the query, we call the fetchall() method to retrieve all rows returned by the query.

Finally, we iterate over the rows using a simple for loop and print each row to the console.

Step 5: Updating Data

In this step, we will learn how to update existing data in the contacts table. Suppose we want to update the phone number of the contact with an ID of 1 to “987-654-3210”.

To update the data, add the following code:

update_query = '''
UPDATE contacts
SET phone = ?
WHERE id = ?
'''

values = ('987-654-3210', 1)

cursor.execute(update_query, values)

connection.commit()

In the update_query variable, we define the SQL statement to update the phone column of a row in the contacts table where the id matches a given value.

We then define the new phone number and the ID of the contact to update in the values variable.

After executing the query using the execute() method, we commit the changes to the database using the commit() method of the connection object.

Step 6: Deleting Data

In this step, we will learn how to delete data from the contacts table. Suppose we want to delete the contact with an ID of 1.

To delete the data, add the following code:

delete_query = '''
DELETE FROM contacts
WHERE id = ?
'''

values = (1,)

cursor.execute(delete_query, values)

connection.commit()

In the delete_query variable, we define the SQL statement to delete a row from the contacts table where the id matches a given value.

We then define the ID of the contact to delete in the values variable. Note that we use a comma after the ID value to create a tuple with a single element.

After executing the query using the execute() method, we commit the changes to the database using the commit() method of the connection object.

Step 7: Closing the Connection

Once you have finished working with the database, it’s important to close the connection properly. This ensures that all changes are saved, and the database file is released.

To close the connection, add the following code at the end of your Python file:

connection.close()

This will close the connection to the SQLite database.

Conclusion

In this tutorial, we have explored how to implement CRUD operations with SQLite and Python. We learned how to create a database, establish a connection, create a table, insert data, retrieve data, update data, and delete data.

SQLite provides a simple and efficient way to store and manage data in various applications and scenarios. By combining SQLite with Python, you can build powerful and flexible data storage solutions.

Remember to always handle errors and exceptions properly when working with databases, as they can help you identify and resolve potential issues in your code.

I hope you found this tutorial helpful. Now you have the knowledge to implement CRUD operations with SQLite using Python. Happy coding!

Related Post