Skip to content
mbedded.ninja

Databases

Authors:
Geoffrey Hunter
Geoffrey Hunter
Published On:
Jul 14, 2020
Last Updated:
Jul 1, 2022

Common Data Types

Text Based

VARCHAR lets you store a variable length string, with a settable maximum length. It is likely the most commonly used data type for strings. Only the size of the string plus a small amount of overhead (1 byte if the string is less than 255 bytes, 2 bytes if it is 255 bytes or more) is stored in the table.1

MySQL

A record is the definition for a row in a table.

Creating A Database

A single SQL instance can have one or more databases.

CREATE DATABASE IF NOT EXISTS my_database;

Creating A Table

CREATE TABLE people(id INT PRIMARY KEY, name VARCHAR(255))

If you only want to create a table if it doesn’t already exist, you can use IF NOT EXISTS:

CREATE TABLE IF NOT EXISTS people(id INT PRIMARY KEY, name VARCHAR(255))

Changing A Position Of A Column

MySQL/MariaDB are somewhat unique in that you can both specify the insertion point of a column and change the position of an existing column. Many other databases such as PostgreSQL do not let you do this, and new columns are always added to the end of the table. This does not matter so much for code-driven front ends which can render the columns in whatever order they choose, but this ability to insert a column at a point of your choosing is really useful if you rely on generic database viewing tools such as DBeaver.

To change the position of an existing column, by inserting after an existing column:

ALTER TABLE tbl_name MODIFY COLUMN new_col_name column_definition AFTER existing_col_name;

If you want to insert the column as the first column, instead of AFTER <existing_col_name> use FIRST:

ALTER TABLE tbl_name MODIFY COLUMN new_col_name column_definition FIRST;

More info at https://mariadb.com/kb/en/alter-table/.

Checking If A Column Exists

You can use the SHOW COLUMNS command to check if a column exists in a table:

SHOW COLUMNS FROM table_name LIKE 'column_name'

For example, in Python:

cur.execute(f'SHOW COLUMNS FROM table_name LIKE \'column_name\'')
results = cur.fetchall()
if len(results) == 1:
    # Column column_name exists in table, do something here.
    pass

Inserting A Record

To insert a record into a table, use the SQL INSERT command. Let’s say we wanted to add a person called josh to the people table:

INSERT INTO people(name)
VALUES('josh');

To do the same thing from Python:

cur.execute('INSERT INTO people(name) VALUES(%s);', ('josh',))
conn.commit()

Updating A Record

The SQL command UPDATE is used to update existing records (table rows). Suppose we wanted to update the age of all people named ben to 11:

UPDATE people SET age=11 WHERE name=ben

To do the same thing in Python:

cur.execute(f'UPDATE people SET age=%s WHERE name=%s', (age, name, ))
conn.commit()

Timestamps

The TIMESTAMP data type holds temporal data in the form of a date and time. It can be used

ALTER TABLE my_table_name ADD COLUMN my_timestamp_column TIMESTAMP

There is special behaviour defined for the first column of this type that you add to a table, it automatically gets the following properties applied to it:

  • DEFAULT CURRENT_TIMESTAMP
  • ON UPDATE CURRENT_TIMESTAMP

This means that the column records will automatically default to the current datetime when it is first added. It also means that when any other data in the record is changed, the column will update to the current datetime (so it acts like a “last modified” field).

See https://mariadb.com/kb/en/timestamp/ for more info.

Debug Info

You can get some debug info on the last foreign key failure by running the command:

SHOW ENGINE INNODB STATUS

MariaDB

MariaDB is a fully open-source, GPL licenced database engine that is designed to be a drop-in replacement for MySQL. Whilst MySQL supports Windows, Linux and Mac OS, MariaDB only supports Windows and Linux (no Mac OS support)2.

PostgreSQL

PostgreSQL is a object-orientated database. The main feature of a object-orientated database is that tables can inherit from other tables, which means they automatically get all the data columns of the table they inherit from (similar to object inheritance in object-orientated programming languages).

Inheritance is defined when creating tables with the INHERITS clause with the CREATE TABLE statement:

CREATE TABLE people (
    name            text,
    age             int
);


CREATE TABLE student (
    student_id      text,
) INHERITS (people);

If a table already exists, you can inheritance via the INHERIT variant of the ALTER TABLE statement.

PostgreSQL does not let you easily alter the position of a column in a table — to do so you have to recreate the table with the desired column order.

Working With PostgreSQL In Python

One of the most popular PostgreSQL libraries for Python is psycopg2. To install it on Ubuntu you will also need the libpg-dev package on your system:

Terminal window
sudo apt install libpq-dev
pip install psycopg2

Microsoft SQL Server

Microsoft SQL Server is a proprietary relational database engine developed by Microsoft. Microsoft Azure SQL Database is a cloud-based version of SQL Server, provided on Microsoft Azure. It is one of the supported database engines by DBeaver.

Data Types

SQL Server offers both datetime and datetime2 data types for storing date and time values. datetime2 is an extension of datetime which has a larger date range, a larger default fractional precision, and optional user-specified precision.3

Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

Data in the database is stored in two different types of files, primary data files with the .mdf extension and secondary data files with the .ndf extension. Logs are stored in files with the .ldf extension.4

Communicating with SQL Server from Python

The pyodbc library is a popular library for communicating with SQL Server from Python.

Terminal window
pip install pyodbc

You also have to install the Microsoft ODBC Driver for SQL Server from here. Drivers are available for Windows, Linux and macOS. This driver is used by pyodbc to communicate with the database.

Footnotes

  1. Brian Morrison II (2022, Sep 30). Blog > Engineering > MySQL data types: VARCHAR and CHAR. PlanetScale. Retrieved 2025-02-24, from https://planetscale.com/blog/mysql-data-types-varchar-and-char. ↩

  2. Vishwash Vishwakarma (2020, May 22). Difference Between MySQL and MariaDB. GeeksforGeeks. Retrieved 2022-07-18, from https://www.geeksforgeeks.org/difference-between-mysql-and-mariadb/. ↩

  3. Microsoft (2024, Nov 23). Learn > SQL > SQL Server > datetime2 (Transact-SQL). Retrieved 2025-02-24, from https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-ver16. ↩

  4. Wikipedia (2025, Feb 15). Microsoft SQL Server. Retrieved 2025-02-24, from https://en.wikipedia.org/wiki/Microsoft_SQL_Server. ↩