Lesson · 40 min · Free
Python SQLite Integration
Python SQLite Integration 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-f
Python SQLite Integration
In the realm of pharmaceutical research, managing and analyzing experimental data efficiently is paramount. While complex relational database management systems (RDBMS) like PostgreSQL or MySQL are powerful, they often require dedicated server setups and administration. For many common tasks, especially during early-stage research, data prototyping, or when developing desktop applications, a lightweight, serverless, and file-based database solution is highly desirable. This is where SQLite shines. SQLite is an embedded SQL database engine. Unlike other SQL databases, it doesn't have a separate server process. It reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views is contained in a single disk file. This simplicity makes it an excellent choice for Python applications where a full-blown RDBMS might be overkill, but structured data storage is still needed. Python has built-in support for SQLite through its sqlite3 module, making integration straightforward. This module provides a SQL interface compliant with the DB-API 2.0 specification, allowing you to connect to a database, create tables, insert data, query data, and manage transactions directly from your Python scripts. For pharmaceutical researchers, this means you can easily store and retrieve data from drug screening experiments, patient cohorts, or compound libraries without leaving your Python environment.
Connecting, Creating, and Querying a SQLite Database
The first step in using SQLite with Python is to establish a connection to a database file. If the specified file does not exist, SQLite will create it automatically. Once connected, you can create a cursor object, which allows you to execute SQL commands. Let's walk through an example where we create a database to store information about experimental compounds. import sqlite3 # Connect to a SQLite database (or create it if it doesn't exist) # The database will be saved as 'pharmaceutical_data.db' conn = sqlite3.connect('pharmaceutical_data.db') cursor = conn.cursor() # Create a table for compounds if it doesn't already exist # We'll store CompoundID, Name, MolecularWeight, and Solubility cursor.execute(''' CREATE TABLE IF NOT EXISTS compounds ( CompoundID TEXT PRIMARY KEY, Name TEXT NOT NULL, MolecularWeight REAL, Solubility_mg_mL REAL ) ''') # Insert some sample data compounds_data = [ ('CMPD001', 'Aspirin', 180.16, 3.3), ('CMPD002', 'Paracetamol', 151.16, 14.0), ('CMPD003', 'Ibuprofen', 206.29, 0.021), ('CMPD004', 'Metformin', 129.16, 300.0) ] # Use executemany for efficient insertion of multiple rows cursor.executemany("INSERT OR IGNORE INTO compounds VALUES (?, ?, ?, ?)", compounds_data) # Commit the changes to the database conn.commit() # Query data from the table print("All compounds:") cursor.execute("SELECT * FROM compounds") rows = cursor.fetchall() for row in rows: print(row) print("\nCompounds with Molecular Weight less than 200:") cursor.execute("SELECT Name, MolecularWeight FROM compounds WHERE MolecularWeight In the example above, we first connect to or create a database file. We then define a SQL CREATE TABLE statement to set up our compounds table with appropriate data types. The PRIMARY KEY constraint ensures uniqueness for CompoundID , and NOT NULL ensures that a compound always has a name. We use INSERT OR IGNORE to prevent errors if we run the script multiple times and try to insert the same primary key. Finally, we execute SELECT queries to retrieve data based on different criteria. It's crucial to call conn.commit() to save any changes made to the database file and conn.close() to properly close the connection. Let's consider another scenario where we might want to update existing data or delete records. This is equally straightforward using SQL UPDATE and DELETE statements. import sqlite3 # Connect to the existing database conn = sqlite3.connect('pharmaceutical_data.db') cursor = conn.cursor() # Update the solubility of a specific compound print("Updating Solubility for Ibuprofen...") cursor.execute("UPDATE compounds SET Solubility_mg_mL = ? WHERE Name = ?", (0.025, 'Ibuprofen')) conn.commit() print("Update complete.") # Verify the update print("\nIbuprofen's updated data:") cursor.execute("SELECT * FROM compounds WHERE Name = 'Ibuprofen'") print(cursor.fetchone()) # Delete a compound record print("\nDeleting Metformin...") cursor.execute("DELETE FROM compounds WHERE Name = 'Metformin'") conn.commit() print("Deletion complete.") # Verify deletion print("\nAll compounds after deletion:") cursor.execute("SELECT * FROM compounds") rows_after_delete = cursor.fetchall() for row in rows_after_delete: print(row) # Close the connection conn.close() This second code snippet demonstrates how to modify and remove data. We update Ibuprofen's solubility and then delete the record for Metformin. Remember that conn.commit() is essential after any INSERT , UPDATE , or DELETE operation to make the changes permanent in the database file.
Key Takeaways
SQLite is a serverless, file-based database , ideal for lightweight applications and data prototyping in pharmaceutical research. Python's built-in sqlite3 module provides a standard DB-API 2.0 interface for interacting with SQLite databases. You connect to a database using sqlite3.connect() , which creates the file if it doesn't exist. A cursor object is used to execute SQL commands like CREATE TABLE , INSERT , SELECT , UPDATE , and DELETE . Always call conn.commit() after making changes (INSERT, UPDATE, DELETE) to save them, and conn.close() to properly terminate the connection. Parameterized queries (using ? as placeholders) are crucial for preventing SQL injection vulnerabilities.
Practice Exercise
Imagine you are conducting a high-throughput screening experiment and have collected data on the inhibitory concentration (IC50) of several compounds against a specific enzyme. Your data looks like this: CompoundID | TargetEnzyme | IC50_nM -----------|--------------|-------- CMPD001 | EnzymeA | 150 CMPD002 | EnzymeA | 75 CMPD005 | EnzymeB | 200 CMPD006 | EnzymeA | 120 CMPD007 | EnzymeB | 50 Write a Python script using sqlite3 to: Create a new SQLite database named screening_results.db . Create a table named ic50_data with columns for CompoundID (TEXT, PRIMARY KEY), TargetEnzyme (TEXT), and IC50_nM (REAL). Insert the provided sample data into the ic50_data table. Query and print all compounds that have an IC50_nM value less than 100. Update the IC50_nM for 'CMPD001' against 'EnzymeA' to 130 nM. Query and print all compounds targeting 'EnzymeA'. Close the database connection.
Watch the full lesson — free
This topic is part of Python for Pharmaceutical Research, a complete AI-narrated video course. Press play once and watch the entire lecture like a movie.
Start the course free →