Python Database Access | Google Colab

Relational databases are a useful tool for data storage and manipulation. SQL (Structured Query Language) is an industry standard for managing data in relational databases. It is supported in nearly every major programming language including Python with the sqlite3 core Python package. See this reference guide from LearnSQL.com for a summary of SQL commands.

Import Libraries

The first step is to import sqlite3. The pandas library is also used to export, analyze, and import SQL tables. Numpy and Matplotlib are used to create a data array and visualize data, respectively.

Connect to Database

SQLite is a minimal version of SQL. SQLite stores the entire database in one file or in memory, making it a good choice for small projects or for on-device storage with local datahttps://apmonitor.com/dde/index.php/Main/DatabaseAccess#:~:text=SQLite%20is%20a%20minimal%20version,device%20storage%20with%20local%20data.

• :memory: - store the database in temporary memory
• filename - write to the hard drive (e.g. database.db)

Write a data file if the database needs to persist or is also needed by another application. Use the connect function to connect to the database.

Cursor Position

The database cursor position is needed to access the database.

SQL Queries

SQL Queries are commands to create a new table. Below is a list of basic commands for adding or dropping (deleting) tables:

• CREATE TABLE - create a new table
• DROP TABLE - delete table

Delete Table

Drop (delete) table tbl if it exists with command DROP TABLE IF EXISTS. The SQL statement is a string that is processed by cur.execute().

Create Table

Create a new table with name store with CREATE TABLE. The new table can have any of the SQLite datatypes:

• int - Integer numbers
• real - Decimal numbers
• text - Character string
• blob - Binary large object with photos or other files

Each part of the SQL statement is explained in detail:

• cur.execute() - The execute() command processes SQL statements
• id int PRIMARY KEY - The id is the PRIMARY_KEY as a unique integer
• tag text NOT NULL - The tag is a text type that cannot be blank
• val real NOT NULL - The name val is a real type (floating point number)

Commit Changes

The commit() function saves the changes. Until the commit() function runs, other applications cannot access the database updates.

Row Operations

Now that the blank table is created, rows can be inserted, updated, or deleted.

• INSERT INTO - add rows to a table
• SELECT - find rows in a table
• UPDATE - update rows in a table
• DELETE - delete rows in a table

The VALUES (?, ?, ?) allows a tuple to determine the (id, name, val) as variables.

✅ Activity

Insert a new record (row) into t1 with the following values:

• id = 3
• tag = 'FF'
• val = -0.01

After adding the row, commit the change to the database.

Read SQL Table with Pandas

Select all value from table t1 to export to a Pandas DataFrame t1

Write SQL Table with Pandas

Create a new DataFrame table with Pandas. The parameters P, I, D are adjustable values from a PID controller. The FF term is a feedforward element that is optionally added to controllers to reject disturbances.

Write a new table t2 to the database with to_sql(). The if_exists argument has options fail, replace, and append. Use replace to remove any existing table and replace it with the updated table.

Join Tables with Pandas

Pandas joins tables with the df.join() function. Joining tables is also shown in the Pandas Time-Series tutorial.

First, retrieve the t1 table and set the index to id.

Also retrieve table t2 and set the index in the same way.

Join the two tables with an inner join that only includes the id rows that are common between t1 and t2. An outer join includes all rows and inserts NaN for any missing values.

SQL statements are more efficient when dealing with large databases because the source tables do not need to be imported before the JOIN operation. However, the sqlite3 commands are limited, such as no OUTER JOIN.

Join Tables with SQL Statement

• JOIN - join two tables

JOIN combine tables t1 and t2 into a single table t3.

Each part of the SQL statement is explained in more detail:

• SELECT t1.tag,t1.val,t2.desc - The new t3 table has tag and val from t1 and desc from t2
• FROM t1 INNER JOIN t2 - A join only includes the rows that have common key elements
• ON t1.id=t2.id - Join the two tables by matching t1.id and t2.id

Pandas processes the SQL query and returns the result to the new DataFrame t3.

Filter with SQL WHERE

Filters select a subset of rows that meet a set of conditions. The WHERE statement indicates which rows are selected based on a condition.

Table t4 contains a history of P, I, D tuning parameters from a PID controller. A new record (row) is created every time the tuning parameters are changed. Create the new table t4.

Show only the rows with id=0 with WHERE id=0.

Merge Tables

Join t1, t2, and t4 with the common id with an INNER JOIN to create table t5.

Create a plot of the Proportional term P to show how it has changed.

The table merge can also be completed with Pandas with the history of P, I and D shown on a single plot.

✅ Activity

Create a new table t0 that contains 3 SQLite datatypes.

• opID - Operator ID as integer (primary key)
• name - Operator name as text (required)
• photo - Operator profile photo as blob (optional)

New records (rows) will be added later. Use DROP TABLE IF EXISTS t0 to drop the table if it already exists.

Create the table t0 and insert records:

• opID: 1 name: David Stevens
• opID: 2 name: Rebecca Okume
• opID: 3 name: Mike Fisher

Commit the changes once the records are added.

The new table t0 can now to be used to record details of who made the changes to the PID parameters by recording the opID in t4 when a new row is added to the history. It is not required to add a photo or augment t4 for this activity.

Close Database Connection

Close the database connection with cxn.close(). This releases the database so that it prevents a connection after it is closed.

Time‑Series Data

Time‑series data are sequences of measurements recorded over time. When working with sensors and controllers, it is often necessary to record the values at fixed intervals and store them for later analysis or visualization. Time-series data accumulates indefinitely as the process continues to feed data into storage. Two database options include SQLite and InfluxDB that are reviewed below with a list of other available database options.

Logging TCLab Data to SQLite

SQLite stores an entire database in a single file or in memory, which makes it suitable for local time‑series logging. The example below connects to a TCLab device, creates a `history` table with a timestamp and four data columns, and then records the heater outputs `Q1`/`Q2` and temperatures `T1`/`T2` once per second. Each timestamp is the current Unix time in seconds, which allows easy plotting later.

Once data have been collected, the time‑series can be viewed or analyzed with Pandas. Setting the index to the timestamp makes plotting easy.

Writing TCLab Data to InfluxDB

InfluxDB is a purpose‑built time‑series database that optimizes storage and query performance for time‑stamped measurements. Installation instructions are available from InfluxData’s website: InfluxDB Installation Guide. The ``influxdb-client`` Python package communicates with an InfluxDB v2 instance and writes points to a specific bucket. Before running the script below, configure a server or cloud instance and create a bucket, organization, and API token.

The ``Point`` object names the measurement ``tclab`` and attaches four numeric fields. The write API sends each point to the chosen bucket with second‑level precision. To read the data back, query the bucket with the Flux language:

Popular Databases Accessible with Python

The table below lists several widely used databases and the primary Python packages used to interact with them. Each entry indicates the database type (relational, time‑series, document, etc.) and a typical connector. This list is not exhaustive but highlights common options for data acquisition and analysis.

✅ Knowledge Check

1. Which SQL statement is used to add rows to a table in a relational database?

2. What is the main difference between SQLite and other SQL databases?