How do you work with SQLite databases in Python?
Working with SQLite databases in Python is straightforward using the built-in `sqlite3` module. Below are the steps to connect to an SQLite database, create a table, insert data, retrieve data, and close the connection:
1. Import the sqlite3 module:
2. Connect to the SQLite database:
3. Create a cursor object:
4. Create a table:
5. Insert data into the table:
6. Retrieve data from the table:
7. Update data in the table:
8. Delete data from the table:
9. Close the connection:
Remember to handle exceptions using `try` and `except` blocks when working with databases to ensure your code runs smoothly even in case of errors.
1. Import the sqlite3 module:
import sqlite3
2. Connect to the SQLite database:
# If the database does not exist, it will be created
conn = sqlite3.connect('example.db')
3. Create a cursor object:
cursor = conn.cursor()
4. Create a table:
cursor.execute('''CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER)''')
5. Insert data into the table:
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 30))
conn.commit() # Commit the changes
6. Retrieve data from the table:
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
print(row)
7. Update data in the table:
cursor.execute("UPDATE users SET age = 31 WHERE name = 'Alice'")
conn.commit() # Commit the changes
8. Delete data from the table:
cursor.execute("DELETE FROM users WHERE name = 'Alice'")
conn.commit() # Commit the changes
9. Close the connection:
conn.close()
Remember to handle exceptions using `try` and `except` blocks when working with databases to ensure your code runs smoothly even in case of errors.
