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:
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:
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:
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:
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:
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:
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:
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.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics