Python SQLite: Basics of Working with SQLite Databases
Introduction to Python SQLite
SQLite is a lightweight, disk-based database that doesn't require a separate server process. Python comes with built-in support for SQLite through the sqlite3 module, which allows us to create, manipulate, and query SQLite databases easily.
Following tutorial provides a foundational understanding of SQLite in Python, which is useful for developing lightweight, database-driven applications.
Example 1: Connecting to an SQLite Database
This example demonstrates how to connect to an SQLite database. If the database does not exist, it will be created.
Code:
import sqlite3
# Connect to an SQLite database (or create it if it doesn't exist)
connection = sqlite3.connect('sql_data.db') # Creates or opens a file named 'sql_data.db'
# Create a cursor object to interact with the database
cursor = connection.cursor()
# Close the connection
connection.close()
Explanation:
- Connection: 'sqlite3.connect('example.db')' opens a connection to the SQLite database file named 'example.db'. If the file does not exist, it creates a new one.
- Cursor: The 'cursor' object is used to execute SQL queries and retrieve data.
- Closing the Connection: 'connection.close()' closes the connection to the database, which is a good practice to free up resources.
Example 2: Creating a Table
This example shows how to create a table named 'users' in the SQLite database.
Code:
import sqlite3
# Connect to the SQLite database
connection = sqlite3.connect('sql_data.db')
cursor = connection.cursor()
# Create a table named 'users'
cursor.execute('''CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER NOT NULL
)''')
# Commit the changes to save them
connection.commit()
# Close the connection
connection.close()
Explanation:
- SQL Command: The 'CREATE TABLE SQL' command is used to create a new table named users with three columns: 'id', 'name', and 'age'.
- IF NOT EXISTS: Ensures the table is only created if it does not already exist, preventing errors.
- Commit: 'connection.commit()' saves the changes made to the database.
Example 3: Inserting Data into a Table
This example demonstrates how to insert data into the 'users' table.
Code:
import sqlite3
# Connect to the SQLite database
connection = sqlite3.connect('sql_data.db')
cursor = connection.cursor()
# Insert data into the 'users' table
cursor.execute("INSERT INTO users (name, age) VALUES ('Martyn Shyam', 30)")
cursor.execute("INSERT INTO users (name, age) VALUES ('Praveen Juliet', 25)")
# Commit the changes to save them
connection.commit()
# Close the connection
connection.close()
Explanation:
- INSERT Statement: The ‘INSERT INTO’ SQL statement is used to insert new rows of data into the ‘users’ table.
- Values: Data values are directly provided in the SQL query for simplicity.
- Multiple Inserts: Multiple ‘execute’ calls can be used to insert several rows.
Example 4: Querying Data from a Table
This example shows how to retrieve data from the 'users' table.
Code:
import sqlite3
# Connect to the SQLite database
connection = sqlite3.connect('sql_data.db')
cursor = connection.cursor()
# Query data from the 'users' table
cursor.execute("SELECT * FROM users")
# Fetch all rows from the executed query
rows = cursor.fetchall()
# Print the results
for row in rows:
print(row)
# Close the connection
connection.close()
Output:
(1, 'Martyn Shyam', 30) (2, 'Praveen Juliet', 25)
Explanation:
- SELECT Statement: The 'SELECT * FROM users' SQL command retrieves all columns ('*') from the 'users' table.
- Fetching Data: 'cursor.fetchall()' fetches all the results of the executed query as a list of tuples.
- Iteration: A 'for' loop is used to print each row retrieved from the table.
Example 5: Updating Data in a Table
This example demonstrates how to update existing data in the 'users' table.
Code:
import sqlite3
# Connect to the SQLite database
connection = sqlite3.connect('sql_data.db')
cursor = connection.cursor()
# Update data in the 'users' table
cursor.execute("UPDATE users SET age = 34 WHERE name = 'Praveen Juliet'")
# Commit the changes to save them
connection.commit()
# Close the connection
connection.close()
Explanation:
- UPDATE Statement: The 'UPDATE' SQL command modifies the data in the table. It changes the age of the user named "Praveen Juliet" to 34.
- WHERE Clause: The ‘WHERE’ clause specifies which rows should be updated.
Example 6: Deleting Data from a Table
This example shows how to delete data from the 'users' table.
Code:
import sqlite3
# Connect to the SQLite database
connection = sqlite3.connect('sql_data.db')
cursor = connection.cursor()
# Delete data from the 'users' table
cursor.execute("DELETE FROM users WHERE name = 'Praveen Juliet'")
# Commit the changes to save them
connection.commit()
# Close the connection
connection.close()
Explanation:
- DELETE Statement: The 'DELETE' SQL command removes rows from the table. Here, it deletes the row where the name is "Praveen Juliet".
- WHERE Clause: Ensures only the specified rows are deleted.
Example 7: Using Parameters to Prevent SQL Injection
This example shows how to use parameters to prevent SQL injection attacks.
Code:
import sqlite3
# Connect to the SQLite database
connection = sqlite3.connect('sql_data.db')
cursor = connection.cursor()
# Insert data using parameters to prevent SQL injection
name = "Gurutz Irene"
age = 40
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", (name, age))
# Commit the changes to save them
connection.commit()
# Close the connection
connection.close()
Explanation:
- Parameters: Use placeholders ('?') and pass data as a tuple to safely insert data into the table.
- SQL Injection Prevention: This method prevents malicious input from executing harmful SQL commands.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/python/python-sqlite-databases-with-examples.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics