Lesson · 40 min · Free
Pandas for SQL Databases
Pandas for SQL Databases 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-fa
Pandas for SQL Databases
Welcome to this module on "Pandas for SQL Databases," an essential skill for pharmacy and biotech professionals working with data. In fields like clinical trials, pharmacovigilance, and genomics, data is often stored in relational databases (SQL databases). While SQL is the primary language for querying these databases, Pandas provides a powerful and flexible Pythonic interface to interact with SQL, allowing for seamless data retrieval, manipulation, and analysis within your Python environment. This lesson will cover how to establish connections to various SQL databases, execute queries to fetch data directly into Pandas DataFrames, and how to write DataFrames back to a SQL database. Understanding these operations is crucial for building robust data pipelines and performing advanced analytics without constantly switching between SQL clients and Python scripts.
Connecting and Querying SQL Databases with Pandas
The primary library for connecting Python to SQL databases is SQLAlchemy , which Pandas leverages under the hood. While you can use other database-specific connectors (like psycopg2 for PostgreSQL or mysql-connector-python for MySQL), SQLAlchemy provides a consistent interface across different database systems. Pandas' read_sql_query() and read_sql_table() functions are your main tools for fetching data. First, you'll need to install the necessary libraries. For this example, we'll use sqlite3 , which is built into Python and good for local testing, but the principles apply to other databases. For real-world scenarios, you'd install SQLAlchemy and a database-specific driver (e.g., psycopg2 for PostgreSQL). import pandas as pd import sqlite3 # 1. Create a connection to an SQLite database (or connect to an existing one) # For other databases, the connection string would look different: # e.g., 'postgresql://user:password@host:port/database_name' # e.g., 'mysql+mysqlconnector://user:password@host:port/database_name' conn = sqlite3.connect('pharmaceutical_data.db') # 2. Create a dummy table and insert some data for demonstration # In a real scenario, this table would already exist. try: conn.execute(''' CREATE TABLE IF NOT EXISTS Patients ( PatientID INTEGER PRIMARY KEY, Age INTEGER, Gender TEXT, Diagnosis TEXT, Treatment TEXT ); ''') conn.execute("INSERT INTO Patients (PatientID, Age, Gender, Diagnosis, Treatment) VALUES (101, 45, 'Female', 'Hypertension', 'Drug A')") conn.execute("INSERT INTO Patients (PatientID, Age, Gender, Diagnosis, Treatment) VALUES (102, 62, 'Male', 'Diabetes', 'Drug B')") conn.execute("INSERT INTO Patients (PatientID, Age, Gender, Diagnosis, Treatment) VALUES (103, 30, 'Female', 'Asthma', 'Drug C')") conn.execute("INSERT INTO Patients (PatientID, Age, Gender, Diagnosis, Treatment) VALUES (104, 58, 'Male', 'Hypertension', 'Drug A')") conn.commit() print("Table 'Patients' created and data inserted successfully.") except sqlite3.OperationalError as e: print(f"Error creating table or inserting data: {e}") # 3. Use pandas.read_sql_query() to fetch data based on a SQL query query = "SELECT PatientID, Age, Diagnosis FROM Patients WHERE Age > 50;" df_patients_older = pd.read_sql_query(query, conn) print("\nPatients older than 50:") print(df_patients_older) # 4. Use pandas.read_sql_table() to fetch an entire table (requires SQLAlchemy engine) # For sqlite3, read_sql_table often works directly with the connection object, # but for other databases, an SQLAlchemy engine is preferred. # For simplicity with sqlite3, we'll demonstrate read_sql_query for now. # If using SQLAlchemy, you'd do: # from sqlalchemy import create_engine # engine = create_engine('sqlite:///pharmaceutical_data.db') # df_all_patients = pd.read_sql_table('Patients', engine) # Let's use read_sql_query for the full table for consistency with sqlite3 df_all_patients = pd.read_sql_query("SELECT * FROM Patients;", conn) print("\nAll Patients data:") print(df_all_patients) # Close the connection conn.close() pd.read_sql_query() is highly flexible as it allows you to pass any valid SQL SELECT statement. This means you can perform complex joins, aggregations, and filtering directly in SQL before loading the results into a DataFrame, which can be more efficient for very large datasets as it minimizes the data transferred to Python. pd.read_sql_table() is simpler if you just want to load an entire table without specific filtering.
Writing DataFrames to SQL Databases
Just as easily as you can read data from SQL into Pandas, you can also write or append DataFrames back to SQL databases using the to_sql() method. This is invaluable for saving processed data, exporting results of analyses, or updating database tables. import pandas as pd import sqlite3 conn = sqlite3.connect('pharmaceutical_data.db') # Create a new DataFrame to write to the database new_patients_data = { 'PatientID': [105, 106], 'Age': [28, 70], 'Gender': ['Male', 'Female'], 'Diagnosis': ['Allergy', 'Osteoporosis'], 'Treatment': ['Drug D', 'Drug E'] } df_new_patients = pd.DataFrame(new_patients_data) print("New patients DataFrame to be added:") print(df_new_patients) # Write the DataFrame to a new table or append to an existing one # if_exists='fail': Do nothing if table exists (default) # if_exists='replace': Drop the table before inserting new values # if_exists='append': Insert new values into existing table df_new_patients.to_sql('Patients', conn, if_exists='append', index=False) print("\nDataFrame successfully appended to 'Patients' table.") # Verify by reading the updated table df_updated_patients = pd.read_sql_query("SELECT * FROM Patients;", conn) print("\nUpdated Patients table:") print(df_updated_patients) # Close the connection conn.close() The if_exists parameter in to_sql() is critical. Use 'append' to add new rows, 'replace' to overwrite the entire table, or 'fail' to prevent any action if the table already exists. The index=False argument is important if you don't want Pandas to write the DataFrame's index as a column in your SQL table. For more complex operations like updating specific rows or performing upserts (insert if not exists, update if exists), you might need to use SQLAlchemy's ORM capabilities or execute raw SQL UPDATE statements via the connection object, as to_sql() is primarily for bulk inserts.
Key Takeaways
Pandas provides powerful functions ( read_sql_query() , read_sql_table() , to_sql() ) for interacting with SQL databases. sqlite3 is excellent for local, file-based databases and testing; for production, use specific drivers and SQLAlchemy. read_sql_query() allows for flexible data retrieval using custom SQL SELECT statements. to_sql() enables writing Pandas DataFrames back to SQL databases, with options for handling existing tables. Understanding database connection strings and the if_exists parameter are crucial for effective database interaction. Integrating Pandas with SQL streamlines data workflows in pharmaceutical and biotech research, connecting raw database data with Python's analytical power.
Practice Exercise
Using the pharmaceutical_data.db from the examples above, write Python code that performs the following: Connects to the database. Creates a new Pandas DataFrame containing hypothetical adverse event data (e.g., EventID , PatientID , AdverseEvent , Severity ). Writes this DataFrame to a new table named AdverseEvents in the database. Ensure that if the table already exists, it is replaced with your new data. Reads all data from the newly created AdverseEvents table back into a new Pandas DataFrame and prints it. Closes the database connection.
Watch the full lesson — free
This topic is part of Python for Data Science, a complete AI-narrated video course. Press play once and watch the entire lecture like a movie.
Start the course free →