Databases

Caution

This page is in notes format, and may not be of the same quality as other pages on this site.

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)¹.

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:

sudo apt install libpq-dev
pip install psycopg2

Footnotes

1. 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/. ↩