# Import packages

# For data manipulation
import pandas as pd

# For working with SQLite databases
import sqlite3

Introduction

As the CGM dataset grows larger, efficient and reliable data management becomes more important. Moving from CSV files to a database allows for better scalability, consistency, and query capabilities. This part of the series explains how to use Python and SQLite to create a database, load formatted data, and validate results interactively in Jupyter Notebook with SQL Magic.

Why SQLite?

SQLite is a serverless SQL database that integrates seamlessly with Python. This simplicity makes it ideal for local data management and rapid deployment. By pairing SQLite with the pandas library and SQL Magic, you can efficiently handle large datasets with minimal setup.

Steps to Build the SQLite Database

The program performs the following key steps:

• Connect to SQLite: The program connects to the SQLite database file. If the file does not exist, SQLite automatically creates it.

  # Create connection to the SQLite database (if the database doesn't exist, it will be created)
  %sql sqlite:///NewDatabase.db

• Load Data into a DataFrame: Data is loaded into a pandas DataFrame from a CSV file, enabling preliminary checks and transformations before insertion.

  # Load the dataset
  df0 = pd.read_csv("New Data.csv")

• Replace Existing Table: Using the if_exists="replace" parameter ensures that the NEW_DATA table is replaced during each run. This allows corrections and updates to the dataset without risk of duplication.

  # Create a table called NEW_DATA in the database (replace the table if it already exists) and load the data from the dataframe into the table
  df0.to_sql("NEW_DATA", con=sqlite3.connect('NewDatabase.db'), if_exists='replace', index=False, method="multi")

  • index=False: Prevents the DataFrame index from being written as a column.
  • method="multi": Batches multiple rows into a single SQL `INSERT` statement, improving performance when loading larger datasets. When I first created the the database I currently use, there were several thousand records to load.

• Basic Data Checks: After loading the data, the program validates the schema and record count to ensure accuracy.

  # Display column names and data types for the NEW_DATA table
  %sql PRAGMA table_info(NEW_DATA)
  
  # Get the record count for NEW_DATA
  %sql SELECT COUNT(*) FROM NEW_DATA
  
  # Display the first 5 rows of the NEW_DATA table
  %sql SELECT * FROM NEW_DATA LIMIT 5

Benefits of This Approach

• Scalability: By switching to a database, larger datasets are easier to query and manage.
• Error Correction: Replacing the table each run enables correction of formatting or data issues without affecting the underlying structure.
• Interactive Analysis: SQL Magic allows for quick insights directly in Jupyter, combining the flexibility of SQL with the power of Python.

Conclusion

Using SQLite as a database solution for managing CGM data provides a robust and efficient way to handle a growing dataset. The combination of Python and SQL Magic simplifies data setup, validation, and interaction. Replacing the table with each run addresses potential errors proactively, ensuring the dataset remains clean and reliable.

This approach establishes a strong foundation for advanced tasks, such as appending and processing new records.

Python code at GitHub

Continue to Working with CGM Data: Part 3 – Cleaning and Processing New Data with Python and SQLite

Frequently Asked Questions

Why is the if_exists="replace" parameter used, and are there alternative approaches for updating tables?
The if_exists="replace" parameter ensures that the table is recreated with each run, which helps in correcting data issues without retaining outdated or incorrect records.
Alternative approaches:

• if_exists="append": Adds new data to the existing table, suitable for incremental updates.
• if_exists="fail": Stops the process if the table already exists, useful for preventing unintended overwrites.

How does SQL Magic differ from executing SQL queries in a standalone SQLite client?
SQL Magic allows SQL queries to be embedded directly within Jupyter Notebook cells, making it easy to switch between Python and SQL.
This integration:

• Enables seamless use of Python for data manipulation alongside SQL queries.
• Simplifies workflows by combining code, queries, and output in one environment.
• Eliminates the need to switch between separate SQL client tools.

What are the limitations of using SQLite for managing CGM datasets?
While SQLite is lightweight and easy to use, it has some limitations:

• Scalability: It’s not designed for high-volume, concurrent operations.
• Network Access: It’s limited to local use and not optimized for distributed environments.
• Data Size: Performance can degrade with very large datasets (multi-gigabyte range).
• Features: Lacks advanced features like stored procedures or user-defined functions.

What transformations can be performed in pandas before inserting data into SQLite?
Data transformations in pandas before insertion might include:

• Converting date and time columns into a consistent datetime format.
• Renaming or reordering columns to match the database schema.
• Removing or imputing missing values.
• Filtering records based on specific criteria.
• Aggregating data to reduce redundancy.

Can multiple tables be managed in the same SQLite database?
Yes, SQLite supports multiple tables in a single database. You can create and query additional tables with unique names. Use SQL Magic to manage them interactively or programmatically with pandas for data insertion.