Lesson · 40 min · Free
Python SQLite Basics
Python SQLite Basics body { font-family: sans-serif; line-height: 1.6; margin: 20px; } h1, h2 { color: #2c3e50; } pre { background-color: #ecf0f1; padding: 15px; border-radius: 5px; overflow-x: auto; } code { font-family
Python SQLite Basics
Welcome to this lesson on Python SQLite Basics. As future professionals in pharmacy and biotechnology, you will frequently encounter scenarios where managing and analyzing data is paramount. Whether it's patient demographics, experimental results, drug interaction profiles, or clinical trial data, the ability to store, retrieve, and manipulate structured information efficiently is a critical skill. While large-scale applications might use more robust database systems, SQLite offers an incredibly powerful, serverless, and file-based database solution perfect for local data storage, small to medium-sized applications, and rapid prototyping in scientific and clinical contexts. SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured SQL database engine. It is the most widely deployed database engine in the world. Crucially, it doesn't require a separate server process; the entire database is stored in a single file on your disk. This makes it exceptionally easy to integrate into Python applications without complex setup or dependencies. Python's standard library includes the sqlite3 module, providing a straightforward interface to work with SQLite databases. In this lesson, we will cover the fundamental operations: connecting to a database, creating tables, inserting data, querying data, updating records, and deleting records. Understanding these basics will empower you to manage structured data effectively for various tasks, from organizing research data to building simple data management tools for laboratory use.
Connecting to a Database and Basic Operations
The first step in working with SQLite in Python is to establish a connection to a database. If the specified database file does not exist, SQLite will automatically create it. Once connected, you obtain a Connection object, which allows you to create Cursor objects. A cursor is essential for executing SQL commands and fetching results. Remember to always commit your changes to save them to the database file and close the connection when you're done to release resources. import sqlite3 # Connect to a database (or create it if it doesn't exist) # The database will be saved in the file 'pharmacy_data.db' conn = sqlite3.connect('pharmacy_data.db') # Create a cursor object cursor = conn.cursor() # Create a table for drug inventory # We define columns for drug_id (primary key), name, dosage, and quantity cursor.execute(''' CREATE TABLE IF NOT EXISTS drugs ( drug_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, dosage TEXT, quantity INTEGER NOT NULL ) ''') # Insert some sample data cursor.execute("INSERT INTO drugs (name, dosage, quantity) VALUES ('Amoxicillin', '500mg', 150)") cursor.execute("INSERT INTO drugs (name, dosage, quantity) VALUES ('Metformin', '850mg', 200)") cursor.execute("INSERT INTO drugs (name, dosage, quantity) VALUES ('Lisinopril', '10mg', 100)") # Commit the changes to the database conn.commit() # Query data print("All drugs in inventory:") cursor.execute("SELECT * FROM drugs") rows = cursor.fetchall() # Fetches all rows from the last executed query for row in rows: print(row) # Query a specific drug print("\nSearching for 'Metformin':") cursor.execute("SELECT * FROM drugs WHERE name = 'Metformin'") metformin_data = cursor.fetchone() # Fetches a single row print(metformin_data) # Update data cursor.execute("UPDATE drugs SET quantity = 180 WHERE name = 'Amoxicillin'") conn.commit() print("\nUpdated Amoxicillin quantity:") cursor.execute("SELECT * FROM drugs WHERE name = 'Amoxicillin'") print(cursor.fetchone()) # Delete data cursor.execute("DELETE FROM drugs WHERE name = 'Lisinopril'") conn.commit() print("\nDrugs after deleting Lisinopril:") cursor.execute("SELECT * FROM drugs") for row in cursor.fetchall(): print(row) # Close the connection conn.close() print("\nDatabase connection closed.") In the example above, we demonstrated the full lifecycle of basic database interaction. Notice the use of PRIMARY KEY AUTOINCREMENT for drug_id , which automatically assigns a unique, increasing integer to each new record. TEXT NOT NULL ensures that the name column must contain text and cannot be empty. After performing operations like INSERT , UPDATE , or DELETE , it's crucial to call conn.commit() to make these changes permanent in the database file. If you forget to commit, your changes will not be saved. Using parameterized queries is a best practice for inserting and updating data. This approach helps prevent SQL injection vulnerabilities and correctly handles special characters in your data. Instead of concatenating strings directly into your SQL query, you use placeholders (typically ? ) and pass the values as a tuple to the execute() method. import sqlite3 conn = sqlite3.connect('pharmacy_data.db') cursor = conn.cursor() # Insert data using parameterized query new_drug_name = "Aspirin" new_dosage = "325mg" new_quantity = 300 cursor.execute("INSERT INTO drugs (name, dosage, quantity) VALUES (?, ?, ?)", (new_drug_name, new_dosage, new_quantity)) conn.commit() print(f"\nInserted new drug: {new_drug_name}") # Update data using parameterized query update_drug_name = "Aspirin" new_quantity_value = 280 cursor.execute("UPDATE drugs SET quantity = ? WHERE name = ?", (new_quantity_value, update_drug_name)) conn.commit() print(f"Updated quantity for {update_drug_name} to {new_quantity_value}") # Verify the update cursor.execute("SELECT * FROM drugs WHERE name = ?", (update_drug_name,)) print(cursor.fetchone()) conn.close()
Key Takeaways
SQLite is a serverless, file-based database ideal for local storage and small to medium applications. The sqlite3 module is part of Python's standard library. Use sqlite3.connect('database_name.db') to establish a connection. A Cursor object is used to execute SQL commands. Always call conn.commit() to save changes to the database file. Always call conn.close() to release database resources. Use parameterized queries (e.g., INSERT INTO ... VALUES (?, ?, ?) ) to prevent SQL injection and handle data safely. cursor.execute() for executing SQL commands, cursor.fetchone() for one row, and cursor.fetchall() for all rows.
Practice Exercise
Imagine you are managing a small biotech lab and need to track samples. Create a new Python script that: Connects to a new SQLite database named lab_samples.db . Creates a table named samples with columns for: sample_id (INTEGER PRIMARY KEY AUTOINCREMENT) sample_name (TEXT NOT NULL) collection_date (TEXT - you can store dates as YYYY-MM-DD strings for simplicity) storage_location (TEXT) status (TEXT - e.g., 'analyzed', 'pending', 'discarded') Inserts at least three different sample records into the table using parameterized queries. Updates the status of one of your samples from 'pending' to 'analyzed'. Queries and prints all samples that have a status of 'analyzed'. Deletes one sample from the database. Closes the database connection.
Watch the full lesson — free
This topic is part of Python Programming - Basics, a complete AI-narrated video course. Press play once and watch the entire lecture like a movie.
Start the course free →