Pandas SQL Integration

Python for Data Science

Pandas SQL Integration

In the realm of pharmacy and biotech, data is often stored and managed in relational databases. These databases, powered by SQL (Structured Query Language), are indispensable for maintaining patient records, clinical trial data, drug inventory, and research findings. While SQL is excellent for data storage and retrieval, Python's Pandas library provides a robust toolkit for data manipulation, analysis, and visualization. The ability to seamlessly integrate Pandas with SQL databases is a powerful skill, allowing you to extract raw data, transform it using Pandas' extensive functionalities, and then potentially load the processed data back into a database or use it for further analysis. Pandas provides functions to read data from SQL tables directly into DataFrames and to write DataFrames back into SQL tables. This integration is typically achieved using SQLAlchemy, a Python SQL toolkit and Object Relational Mapper (ORM), which Pandas uses under the hood to connect to various database systems like SQLite, PostgreSQL, MySQL, and SQL Server. For this lesson, we will focus on using SQLite, a file-based database, due to its simplicity and lack of external server requirements, making it ideal for demonstration and local development. The principles, however, extend to other database systems. To get started, you'll need the pandas library and a database driver. For SQLite, the driver is usually built into Python. For other databases, you might need to install additional packages (e.g., psycopg2 for PostgreSQL, mysql-connector-python for MySQL). The core functions we'll explore are pd.read_sql_table() , pd.read_sql_query() , and DataFrame.to_sql() .

Connecting to a Database and Reading Data

The first step is to establish a connection to your SQL database. This is typically done using sqlalchemy.create_engine() . Once connected, you can use pd.read_sql_table() to read an entire table or pd.read_sql_query() to execute a custom SQL query and load its results into a Pandas DataFrame. The latter is particularly useful for filtering, joining, or aggregating data directly within the database before bringing it into Python, which can be more efficient for large datasets. import pandas as pd from sqlalchemy import create_engine # Create a SQLite in-memory database for demonstration # In a real scenario, this would be a file path or connection string to an external DB engine = create_engine('sqlite:///:memory:') # Create a dummy DataFrame to populate our database data = { 'patient_id': [101, 102, 103, 104, 105], 'drug_name': ['Aspirin', 'Paracetamol', 'Ibuprofen', 'Metformin', 'Aspirin'], 'dosage_mg': [100, 500, 400, 850, 100], 'treatment_duration_days': [30, 7, 14, 90, 30] } df_medications = pd.DataFrame(data) # Write the DataFrame to a SQL table named 'medications' # if_exists='replace' will overwrite the table if it already exists # index=False prevents writing the DataFrame index as a column in the SQL table df_medications.to_sql('medications', con=engine, if_exists='replace', index=False) print("Original DataFrame (df_medications):") print(df_medications) print("\n--- Reading data from SQL table ---") # Read the entire 'medications' table into a new DataFrame df_from_table = pd.read_sql_table('medications', con=engine) print("\nDataFrame read using pd.read_sql_table:") print(df_from_table) # Read specific data using a SQL query query = "SELECT patient_id, drug_name FROM medications WHERE dosage_mg > 200" df_from_query = pd.read_sql_query(query, con=engine) print("\nDataFrame read using pd.read_sql_query (Dosage > 200mg):") print(df_from_query)

Writing DataFrames to SQL Tables

The DataFrame.to_sql() method is your primary tool for writing Pandas DataFrames into SQL tables. It offers several parameters to control how the data is written: name : The name of the SQL table. con : The SQLAlchemy engine or connection object. if_exists : How to behave if the table already exists. Options are 'fail' (default), 'replace' , or 'append' . For pharmacy/biotech data, exercise extreme caution with 'replace' as it can lead to irreversible data loss. 'append' is often used for adding new records. index : Boolean, whether to write the DataFrame index as a column. Usually set to False unless the index is meaningful. dtype : A dictionary mapping column names to SQLAlchemy data types, allowing for precise control over column types in the database. Consider a scenario where you've analyzed clinical trial data in Pandas, perhaps calculated new metrics or cleaned up erroneous entries. You might want to store this processed data back into a database for reporting or further use by other systems. import pandas as pd from sqlalchemy import create_engine, Integer, String, Float # Re-establish connection for clarity, or continue with existing engine engine = create_engine('sqlite:///:memory:') # Create a dummy medications table first (as shown in previous example) data = { 'patient_id': [101, 102, 103, 104, 105], 'drug_name': ['Aspirin', 'Paracetamol', 'Ibuprofen', 'Metformin', 'Aspirin'], 'dosage_mg': [100, 500, 400, 850, 100], 'treatment_duration_days': [30, 7, 14, 90, 30] } df_medications_initial = pd.DataFrame(data) df_medications_initial.to_sql('medications', con=engine, if_exists='replace', index=False) # Suppose we processed some data and now have a new DataFrame # e.g., adding a 'cost_per_day' column and filtering for long-term treatments df_processed_treatments = pd.DataFrame({ 'patient_id': [101, 104, 105], 'drug_name': ['Aspirin', 'Metformin', 'Aspirin'], 'cost_per_day_usd': [0.50, 2.10, 0.45], 'treatment_duration_days': [30, 90, 30] }) print("Processed DataFrame (df_processed_treatments):") print(df_processed_treatments) # Define specific SQL data types for better database schema management # This is optional but good practice, especially for sensitive data dtype_mapping = { 'patient_id': Integer, 'drug_name': String(50), 'cost_per_day_usd': Float, 'treatment_duration_days': Integer } # Write the processed DataFrame to a new SQL table 'long_term_treatments' df_processed_treatments.to_sql( 'long_term_treatments', con=engine, if_exists='replace', # Or 'append' if adding new records to an existing table index=False, dtype=dtype_mapping ) print("\n--- Reading data from the new SQL table (long_term_treatments) ---") df_read_processed = pd.read_sql_table('long_term_treatments', con=engine) print(df_read_processed) # Example of appending new data new_patient_data = pd.DataFrame({ 'patient_id': [106], 'drug_name': ['Amoxicillin'], 'dosage_mg': [250], 'treatment_duration_days': [10] }) # Append new patient data to the 'medications' table new_patient_data.to_sql('medications', con=engine, if_exists='append', index=False) print("\n--- Medications table after appending new data ---") df_medications_updated = pd.read_sql_table('medications', con=engine) print(df_medications_updated)

Key Takeaways

Pandas integrates seamlessly with SQL databases using SQLAlchemy, enabling efficient data transfer. pd.read_sql_table() is used to load an entire SQL table into a Pandas DataFrame. pd.read_sql_query() allows execution of custom SQL queries, providing flexibility for data extraction and pre-processing directly in the database. DataFrame.to_sql() writes a Pandas DataFrame to a SQL table, with options to create, replace, or append data. Always be cautious with if_exists='replace' when writing to databases, especially in production environments, to prevent accidental data loss. Specifying dtype in to_sql() is good practice for managing database schema and ensuring data integrity.

Practice Exercise

Imagine you have a database of clinical trial participants, and you need to analyze their age distribution for a specific drug. Create a Pandas DataFrame with columns: participant_id (integer), drug_administered (string), and age_years (integer). Populate it with at least 5 rows of dummy data, ensuring at least two participants received the same drug. Write this DataFrame to an in-memory SQLite database as a table named 'clinical_data' . Then, write a SQL query to retrieve all participants who received 'DrugX' (or a drug of your choice from your dummy data) and are older than 40 years. Load the results of this query into a new Pandas DataFrame and print it.