SQLite Database in Python
SQLite is a lightweight, serverless, self-contained SQL database engine that is very easy to set up and use with Python. It is an embedded database that doesn’t require a server process, making it ideal for small applications, mobile apps, or embedded devices.
In Python, the sqlite3 module is used to interact with SQLite databases.
1. Setting Up SQLite in Python
To use SQLite, you need to import the sqlite3 module which comes pre-installed with Python. Here's how you can set up and interact with an SQLite database in Python.
Creating a Database Connection
import sqlite3
# Create a connection to a database file (if it doesn't exist, it will be created)
conn = sqlite3.connect('example.db')
# Create a cursor object to interact with the database
cursor = conn.cursor()
The sqlite3.connect() function connects to the SQLite database. If the database file doesn’t exist, it will be created.
The cursor is used to execute SQL commands.
2. Creating a Table in SQLite
Now, let's create a table in the SQLite database.
# Create a table
cursor.execute('''CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER NOT NULL)''')
# Commit the changes to the database
conn.commit()
This command creates a table called users with columns id, name, and age.
The PRIMARY KEY constraint ensures that the id column will have unique values.
conn.commit() commits the transaction to the database.
3. Inserting Data into the Table
To insert data into the table, you can use the INSERT INTO SQL statement.
# Insert data into the table
cursor.execute("INSERT INTO users (name, age) VALUES ('Alice', 30)")
cursor.execute("INSERT INTO users (name, age) VALUES ('Bob', 25)")
cursor.execute("INSERT INTO users (name, age) VALUES ('Charlie', 35)")
# Commit the changes to the database
conn.commit()
The INSERT INTO statement is used to add data to the users table.
Data is provided as a tuple: (name, age).
4. Fetching Data from the Table
You can retrieve data using the SELECT SQL statement.
# Retrieve all users from the table
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
# Print fetched rows
for row in rows:
print(row)
The SELECT * FROM users command retrieves all rows from the users table.
fetchall() returns a list of all rows from the query.
You can loop through the rows and print the result.
5. Updating Data in the Table
To update data in the table, use the UPDATE statement.
# Update a user's age
cursor.execute("UPDATE users SET age = 28 WHERE name = 'Bob'")
# Commit the changes
conn.commit()
The UPDATE statement modifies the age column for the user with the name 'Bob'.
6. Deleting Data from the Table
To delete data from the table, use the DELETE statement.
# Delete a user from the table
cursor.execute("DELETE FROM users WHERE name = 'Charlie'")
# Commit the changes
conn.commit()
The DELETE FROM users WHERE name = 'Charlie' command deletes the row where the name is 'Charlie'.
7. Handling Exceptions
SQLite operations can throw errors, so it's good practice to handle exceptions.
try:
cursor.execute("INSERT INTO users (name, age) VALUES ('David', 'twenty')")
conn.commit()
except sqlite3.Error as e:
print(f"Error occurred: {e}")
The sqlite3.Error is a base class for all SQLite exceptions. It helps in catching errors like data type mismatches, constraint violations, etc.
8. Closing the Connection
Once you’ve finished working with the database, it’s important to close the connection.
# Close the connection to the database
conn.close()
Always close the connection to release resources.
Complete Example
Here is a complete example that demonstrates all the steps above:
import sqlite3
# 1. Connect to SQLite database
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# 2. Create a table
cursor.execute('''CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER NOT NULL)''')
# 3. Insert data into the table
cursor.execute("INSERT INTO users (name, age) VALUES ('Alice', 30)")
cursor.execute("INSERT INTO users (name, age) VALUES ('Bob', 25)")
cursor.execute("INSERT INTO users (name, age) VALUES ('Charlie', 35)")
conn.commit()
# 4. Fetch data from the table
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
print(row)
# 5. Update data
cursor.execute("UPDATE users SET age = 28 WHERE name = 'Bob'")
conn.commit()
# 6. Delete data
cursor.execute("DELETE FROM users WHERE name = 'Charlie'")
conn.commit()
# 7. Fetch updated data
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
print("\nUpdated Table:")
for row in rows:
print(row)
# 8. Close the connection
conn.close()
Conclusion
SQLite in Python is a very useful and lightweight solution for database management, particularly for small to medium-sized applications. It is easy to set up, and you don’t need to install or configure a database server. You can perform all the basic operations like create, read, update, and delete (CRUD) using Python’s sqlite3 module.
Let me know if you need further explanation or examples! 😊
0 Comments