Table of Contents
- Introduction
- Prerequisites
- Installation
- Creating a Database
- Creating Tables
- Inserting Data
- Querying Data
- Updating Data
- Deleting Data
- Conclusion
Introduction
In this tutorial, we will explore SQLite, a lightweight and embedded relational database management system, and how to use it with Python. By the end of this tutorial, you will understand the basics of SQLite and be able to perform common CRUD operations (Create, Read, Update, Delete) using Python.
Prerequisites
Before starting with this tutorial, it is recommended to have a basic understanding of Python programming language. Familiarity with SQL and databases would also be beneficial, but not mandatory.
Installation
SQLite comes bundled with Python, so no additional installation is required. Make sure you have Python installed on your system. You can verify the installation by opening a terminal or command prompt and running the following command:
python
python --version
If Python is successfully installed, the version number will be displayed.
Creating a Database
To get started with SQLite, we need to create a database file. SQLite databases are stored as files with the extension .db or .sqlite. In Python, we can create a SQLite database by importing the sqlite3 module and using the connect() function to establish a connection to the database file. Let’s create a database called “example.db” using the following code:
```python
import sqlite3
conn = sqlite3.connect('example.db')
``` The `connect()` function returns a connection object that we can use to interact with the database.
Creating Tables
After creating a database, we need to create tables to store our data. Tables in SQLite are created using SQL statements. Let’s create a table called “users” with columns for id, name, and email:
```python
import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# SQL statement to create a table
create_table_query = '''
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT,
email TEXT
)
'''
# Execute the SQL statement
cursor.execute(create_table_query)
conn.commit()
conn.close()
``` In the above code, we first establish a connection to the database using the `connect()` function. We then create a cursor object using the `cursor()` method of the connection object. The cursor is used to execute SQL statements. We define the SQL statement to create the "users" table and store it in the `create_table_query` variable. We execute the SQL statement using the `execute()` method of the cursor object and then commit the changes using the `commit()` method of the connection object. Finally, we close the connection to the database.
Inserting Data
Once we have created the table, we can insert data into it. Here’s an example: ```python import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# SQL statement to insert data
insert_data_query = '''
INSERT INTO users (name, email)
VALUES ('John Doe', '[email protected]')
'''
# Execute the SQL statement
cursor.execute(insert_data_query)
conn.commit()
conn.close()
``` In the above code, we use the `INSERT INTO` statement to insert a new record into the "users" table. We specify the columns we want to insert data into and provide the corresponding values.
Querying Data
To retrieve data from the database, we can use the SELECT statement. Here’s an example:
```python
import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# SQL statement to query data
select_data_query = '''
SELECT * FROM users
'''
# Execute the SQL statement
cursor.execute(select_data_query)
# Fetch all rows
rows = cursor.fetchall()
# Print the rows
for row in rows:
print(row)
conn.close()
``` In the above code, we execute the `SELECT` statement to retrieve all rows from the "users" table. We then use the `fetchall()` method of the cursor object to fetch all the rows returned by the query. We iterate over the rows and print them.
Updating Data
To update existing data in the database, we can use the UPDATE statement. Here’s an example:
```python
import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# SQL statement to update data
update_data_query = '''
UPDATE users
SET email = '[email protected]'
WHERE id = 1
'''
# Execute the SQL statement
cursor.execute(update_data_query)
conn.commit()
conn.close()
``` In the above code, we use the `UPDATE` statement to update the email address of the user with `id` 1 in the "users" table. We specify the column we want to update (`email`) and provide the new value (`[email protected]`). We also include a `WHERE` clause to specify which row(s) to update based on a condition (`id = 1` in this case).
Deleting Data
To delete data from the database, we can use the DELETE statement. Here’s an example:
```python
import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# SQL statement to delete data
delete_data_query = '''
DELETE FROM users
WHERE id = 1
'''
# Execute the SQL statement
cursor.execute(delete_data_query)
conn.commit()
conn.close()
``` In the above code, we use the `DELETE FROM` statement to delete the user with `id` 1 from the "users" table. We include a `WHERE` clause to specify which row(s) to delete based on a condition (`id = 1` in this case).
Conclusion
In this tutorial, we covered the basics of SQLite in Python. We learned how to create a database, create tables, insert, query, update, and delete data using SQL statements in Python. SQLite is a powerful and easy-to-use database management system that can be used in various Python projects. With this knowledge, you can start building your own applications that interact with SQLite databases.