Table of Contents
- Introduction
- Prerequisites
- Installing SQLite
- Creating a Database
- Creating a Table
- Inserting Data
- Querying Data
- Updating Data
- Deleting Data
- Conclusion
Introduction
In this tutorial, we will learn how to use Python with SQLite, a lightweight database management system, to store and query data. We will cover the basic concepts of SQLite, including creating a database, creating tables, inserting, querying, updating, and deleting data. By the end of this tutorial, you will have a solid understanding of how to work with SQLite databases using Python.
Prerequisites
Before starting this tutorial, you should have a basic understanding of Python programming. Familiarity with SQL concepts will also be beneficial, although not required.
You will need to have Python installed on your system. To check if Python is installed, open a terminal or command prompt and run the following command:
bash
python --version
If Python is not installed, you can download and install it from the official Python website: python.org.
Installing SQLite
SQLite is a self-contained, serverless, and zero-configuration database engine, which means it is included in the Python standard library by default. There is no need to install anything extra to use SQLite with Python.
Creating a Database
To create a new SQLite database using Python, we need to import the sqlite3 module and establish a connection to the database. If the database does not exist, SQLite will create it for us. We can use the connect() function to create a connection to a database file.
Here is an example of creating a new database called mydatabase.db:
```python
import sqlite3
conn = sqlite3.connect('mydatabase.db')
``` In this example, the `connect()` function accepts the name of the database file as an argument and returns a connection object. If the file does not exist, it will be created in the current directory. You can provide the full path to the file if you want to store it in a specific location.
Creating a Table
After creating the database, we can create a table to store our data. Tables consist of columns and rows, where columns define the types of data that can be inserted and rows represent individual records.
To create a table, we need to execute a SQL CREATE TABLE statement. We can use the execute() method of the connection object to execute SQL statements.
Here is an example of creating a table called employees:
```python
import sqlite3
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()
cursor.execute('''
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER
)
''')
conn.commit()
``` In this example, we first create a cursor object using the `cursor()` method of the connection object. The cursor allows us to execute SQL statements and retrieve data. We then execute the `CREATE TABLE` statement to define the structure of the `employees` table. The `id` column is specified as the primary key, which means it will uniquely identify each record. The `name` and `age` columns are of type `TEXT` and `INTEGER`, respectively.
After executing the CREATE TABLE statement, we need to call the commit() method of the connection object to save the changes to the database.
Inserting Data
Once we have created the table, we can insert data into it using the SQL INSERT statement. We can use the execute() method of the cursor object to execute SQL statements.
Here is an example of inserting a record into the employees table:
```python
import sqlite3
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()
cursor.execute('''
INSERT INTO employees (name, age)
VALUES (?, ?)
''', ('John Doe', 30))
conn.commit()
``` In this example, we execute the `INSERT INTO` statement to insert a record into the `employees` table. We use placeholders `?` to represent the values that will be inserted. The actual values are passed as a tuple as the second argument to the `execute()` method.
After inserting the data, we need to call the commit() method of the connection object to save the changes to the database.
Querying Data
To retrieve data from the database, we can use the SQL SELECT statement. We can use the execute() method of the cursor object to execute SQL statements.
Here is an example of querying all records from the employees table:
```python
import sqlite3
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()
cursor.execute('SELECT * FROM employees')
rows = cursor.fetchall()
for row in rows:
print(row)
conn.commit()
``` In this example, we execute the `SELECT` statement to retrieve all records from the `employees` table. The `fetchall()` method of the cursor object returns a list of tuples, where each tuple represents a record. We can then iterate over the list and print each record.
After querying the data, we need to call the commit() method of the connection object to save the changes to the database.
Updating Data
To update existing data in the database, we can use the SQL UPDATE statement. We can use the execute() method of the cursor object to execute SQL statements.
Here is an example of updating a record in the employees table:
```python
import sqlite3
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()
cursor.execute('''
UPDATE employees
SET age = ?
WHERE name = ?
''', (35, 'John Doe'))
conn.commit()
``` In this example, we execute the `UPDATE` statement to update the `age` of the employee named 'John Doe'. We use placeholders `?` to represent the values that will be updated. The actual values are passed as a tuple as the second argument to the `execute()` method.
After updating the data, we need to call the commit() method of the connection object to save the changes to the database.
Deleting Data
To delete data from the database, we can use the SQL DELETE statement. We can use the execute() method of the cursor object to execute SQL statements.
Here is an example of deleting a record from the employees table:
```python
import sqlite3
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()
cursor.execute('''
DELETE FROM employees
WHERE name = ?
''', ('John Doe',))
conn.commit()
``` In this example, we execute the `DELETE FROM` statement to delete the employee record with the name 'John Doe'. We use a placeholder `?` to represent the value that will be deleted. The actual value is passed as a tuple as the second argument to the `execute()` method.
After deleting the data, we need to call the commit() method of the connection object to save the changes to the database.
Conclusion
In this tutorial, we have learned how to use Python with SQLite to store and query data. We covered the basic concepts of SQLite, including creating a database, creating tables, inserting, querying, updating, and deleting data. You should now have a solid understanding of how to work with SQLite databases using Python.
SQLite is a powerful and flexible tool for storing and managing data, and Python provides a simple and convenient way to interact with it. By combining the expressive power of SQL with the versatility of Python, you can build robust applications that leverage the capabilities of a relational database system.
Remember to always close the connection to the database when you are done working with it using the close() method of the connection object:
python
conn.close()
Happy coding!