Overview

Storage engines are MySQL components that handle the SQL operations for different table types.

InnoDB is the default and most general-purpose storage engine, and Oracle recommends using it for tables except for specialized use cases.

In MySQL 8.0 the The CREATE TABLE statement created InnoDB tables by default.

Reference: storage-engines

            SHOW ENGINES
        

What is a Transaction?

A transaction is a logical unit of work that contains one or more SQL statements.

A transaction is an atomic unit.

The effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).

Reference: Introduction to Transactions

Overview

Understanding SQL data types is essential for several reasons:

• Data Integrity: Choosing the correct data type ensures that the data stored in the database is accurate and consistent.
• Performance Optimization: Proper data types can significantly improve query performance and overall database efficiency.
• Storage Efficiency: Using appropriate data types helps in optimizing the storage space, reducing the amount of disk space required.
• Data Validation: Data types provide a built-in validation mechanism, ensuring that only valid data is stored in the database.
• Enhanced Querying: Understanding data types allows for more effective querying and manipulation of data.

MySQL supports SQL data types in several categories:

numeric types, date and time types, string (character and byte) types, spatial types, and the JSON data type.

Reference: Data Types

Numeric Data Types - Overview

Data Type	Storage Required	Minimum Value	Maximum Value
TINYINT	1 byte	-128	127
SMALLINT	2 bytes	-32,768	32,767
MEDIUMINT	3 bytes	-8,388,608	8,388,607
INT, INTEGER	4 bytes	-2,147,483,648	2,147,483,647
BIGINT	8 bytes	-9,223,372,036,854,775,808	9,223,372,036,854,775,807
FLOAT(p)	4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53	-3.402823466E+38	3.402823466E+38
FLOAT	4 bytes	-3.402823466E+38	3.402823466E+38
DOUBLE [PRECISION], REAL	8 bytes	-1.7976931348623157E+308	1.7976931348623157E+308
DECIMAL(M,D), NUMERIC(M,D)	Varies; see following discussion	Depends on M and D	Depends on M and D
BIT(M)	approximately (M+7)/8 bytes	0	2^M - 1

Reference: Numeric Type Storage Requirements

Numeric Data Types - Examples

            CREATE DATABASE exampleDB;

            USE exampleDB;

            CREATE TABLE numeric_data_types_example (
                id INT AUTO_INCREMENT PRIMARY KEY,
                tinyint_column TINYINT,
                smallint_column SMALLINT,
                mediumint_column MEDIUMINT,
                int_column INT,
                bigint_column BIGINT,
                float_column FLOAT(7,4),
                double_column DOUBLE(15,8),
                decimal_column DECIMAL(10,2)
            );

            INSERT INTO numeric_data_types_example (
                tinyint_column,
                smallint_column,
                mediumint_column,
                int_column,
                bigint_column,
                float_column,
                double_column,
                decimal_column
            ) VALUES
                (127, 32767, 8388607, 2147483647, 9223372036854775807, 123.4567, 12345.67890123, 12345.67),
                (-128, -32768, -8388608, -2147483648, -9223372036854775808, -123.4567, -12345.67890123, -12345.67);

            SELECT * FROM numeric_data_types_example;
        

The CHAR Data Type

CHAR(M)

Can store fixed length strings.

The length is specified by M (defaults to 1).

The length can be any value from 0 to 255.

If a value entered is with length < M, then
the string is right padded with spaces.

Examples:

                    first_name CHAR(4),
                

Defines that first_name column will contains 4 character strings.

The VARCHAR Data Type

VARCHAR(M)

Can store variable length strings.

The length is specified by M - cannot by empty!

The length can be any value from 0 to 65,535.

VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

Examples:

                    short_notes VARCHAR(50000),
                

Defines that short_notes column will contains 50000 character strings.

The real size taken on disk will be 50000+2 bytes.

CHAR<->VARCHAR differences

BLOB and TEXT Data Types

BLOBBinary Large OBject

Stores large amounts of binary data, such as images, PDF or other types of files.

BLOB values are treated as binary strings - they have no character set!

TEXT

TEXT values are treated as character strings - they have a character set!

Reference: The BLOB and TEXT Types

ENUM

ENUM (from ENUMeration)

Defines a list of predefined values. Like: ('yes', 'no')

The value entered must be selected from that list (or it can be NULL)

The elements listed in the column specification are assigned index numbers, beginning with 1

Pros:

Compact data storage in situations where a column has a limited set of possible values.

The strings you specify as input values are automatically encoded as numbers

Reference: ENUM

Example

                smoker ENUM('yes','no')
            

The DATE Data Type

A date in YYYY-MM-DD format

Values range: between 1000-01-01 and 9999-12-31.

A date can be entered in several formats.

'YYYY-MM-DD' or 'YY-MM-DD', like '2016-03-28' or '16-03-28'

Any punctuation character may be used as the delimiter between date parts.

'2016/03/28' or '16/03/28'

The TIME Date Type

A time in HH:MM:SS format

Values range: between -838:59:59 UTC and 838:59:59 UTC.

Can represent a time, or time interval.

Can be inserted As a string in 'D HH:MM:SS' format. You can also use one of the following “relaxed” syntaxes: 'HH:MM:SS', 'HH:MM', 'D HH:MM', 'D HH', or 'SS'. Here D represents days and can have a value from 0 to 34.

The DATETIME Date Type

A date and time in YYYY-MM-DD HH:MM:SS format

Values range: between 1000-01-01 00:00:00 and 9999-12-31 23:59:59.

A DATETIME can be entered in several formats.

'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS', like '2016-03-28 09:54:10' or '16-03-28 09:54:10'

Any punctuation character may be used as the delimiter between date parts.

'2016/03/28 09.54.10' or '16/03/28 09.54.10'

The date and time parts can be separated by T rather than a space.

'2016-03-28 09:54:10' and '2016-03-28T09:54:10' are equivalent.

The TIMESTAMP Date Type

A date and time in YYYY-MM-DD HH:MM:SS format

Values range: between 1970-01-01 00:00:01 UTC and 2038-01-19 03:14:07 UTC.

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval

By default, the current time zone for each connection is the server time.

A TIMESTAMP value can be inserted in MYSQL the same way as a DATETIME value.

The DATETIME vs TIMESTAMP in MySQL

DATETIME

• Format: YYYY-MM-DD HH:MM:SS
• Range: from 1000-01-01 00:00:00 to 9999-12-31 23:59:59
• Storage: 8 bytes
• Time zone independent, stores the date and time exactly as inserted
• Does not change with server time zone changes
• Use case: Suitable for historical records or specific date and time values

TIMESTAMP

• Format: YYYY-MM-DD HH:MM:SS
• Range: from 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC
• Storage: 4 bytes
• Time zone aware, automatically converts to UTC for storage and back to the current time zone for retrieval
• Changes with server or connection time zone changes
• Supports automatic initialization and updating with CURRENT_TIMESTAMP
• Use case: Suitable for log entries, tracking events, or any time zone sensitive data

Reference

Date and Time Data Types

Syntax

            SELECT [select_option]
            {select_expression}
            [
                FROM table_reference
                    JOIN
                    ON
                    WHERE
                    GROUP BY
                    HAVING
                    ORDER BY
                    LIMIT
                    INTO
            ]
        

Reference: SELECT Statement

WHERE clause requires at least one condition. If multiple conditions are needed, connect them using AND or OR logic operators.

Comparison Functions and Operators to use in WHERE Clause

Example

            SELECT first_name, order_date
                FROM customers.customers as customers, orders
                    WHERE customers.id=orders.customer_id
        

Note, that it is good practice to prefix table names with DB name, like: customers.customer

The syntax

            INSERT INTO table_name [(column, ...)]
            VALUES (value, ...)[, (...), ...];
        

Column list is optional!

If not present, you should list in VALUES values for all columns, following the order in table definition.

You can insert more than 1 row at a time, by using a list of values arrays.

Examples

Without specifying columns

            INSERT INTO artist
            VALUES (0,'Robert', 'Smith');
        
            Query OK, 1 row affected
        

But:

            INSERT INTO artist
            VALUES ('Madonna');
        
            (1136, u"Column count doesn't match value count at row 1")
        

With explicit column specification

            INSERT INTO artist(lname)
            VALUES ('Piazzolla');
        
            Query OK, 1 row affected
        

Examples

Insert more than 1 row at a time

            INSERT INTO artist( lname, fname )
            VALUES ( 'Schnittke', 'Alfred' ),
                ( 'Pärt', 'Arvo' ),
                ( 'Shostakovich', 'Dmitri' );
        
            Query OK, 3 rows affected
        

The Syntax

            DELETE FROM table_name
            [WHERE where_condition]
            [ORDER BY ...]
            [LIMIT row_count]
        

The conditions in the optional WHERE clause identify which rows to delete. With no WHERE clause, all rows are deleted.

The LIMIT clause places a limit on the number of rows that can be deleted.

If the ORDER BY clause is specified, the rows are deleted in the order that is specified.

Examples

            DELETE FROM artist WHERE
                artist_id BETWEEN 9 AND 13;
        
            Query OK, 5 rows affected
        

The Syntax

            UPDATE table_name
            SET column1=value, column2=value2,...
            [WHERE where_clause]
            [ORDER BY ...]
            [LIMIT row_count]
        

The WHERE clause, if given, specifies the conditions that identify which rows to update

With no WHERE clause, all rows are updated!

If the ORDER BY clause is specified, the rows are updated in the order that is specified.

The LIMIT clause places a limit on the number of rows that can be updated.

Examples

        UPDATE artist
        SET fname='Test', lname='Test'
        WHERE artist_id >= 14;
    
        Query OK, 2 rows affected
    

Python DB-API

Python Database API is a set of standards recommended by a Special Interest Group for database module standardization

Standard Python distribution has in-built support for SQLite database connectivity by the sqlite3 module

Other RDBMS products (Oracle, PostgreSQL, MySQL,...) also have DB-API compliant drivers

A database driver is a software that allows an application (like Python program) to connect and interact with a database system

MySQL Connector/Python

Reference: MySQL Connector/Python Developer Guide

Note, that MySQL’s official documentation uses the term connector instead of driver.

Installation:

            # make sure you're in the venv
            pip install mysql-connector-python
        

MySQL Connector - Create connection

            import mysql.connector

            # Establish a connection to the MySQL server
            db = mysql.connector.connect(
                host="localhost",
                user="test",
                password="test1234",
                database="test"
            )
        

MySQL Connector - Create table

            import mysql.connector

            # Establish a connection to the MySQL server
            db = mysql.connector.connect(
                ...
            )

            # Create a cursor object to interact with the database
            cursor = db.cursor()

            # SQL query to create a 'students' table
            create_table_query = """
            CREATE TABLE students (
                student_id INT AUTO_INCREMENT PRIMARY KEY,
                student_name VARCHAR(50),
                student_age INT,
                student_grade TINYINT CHECK (student_grade >= 2 AND student_grade <= 6)
            )
            """

            # Execute the query to create the table
            cursor.execute(create_table_query)

            # Commit changes to the database
            db.commit()

            # Close the cursor and database connection
            cursor.close()
            db.close()
        

MySQL Connector - Insert

            import mysql.connector

            # Establish a connection to the MySQL server
            db = mysql.connector.connect(
                ...
            )

            # Create a cursor object to interact with the database
            cursor = db.cursor()

            # Data to be inserted into the table
            student_data = [
                ("Alice", 20, 4),
                ("Bob", 22, 2),
                ("Charlie", 21, 5),
                ("Diana", 23, 6)
            ]

            # SQL query to insert data into the 'students' table
            insert_query = """
            INSERT INTO students (student_name, student_age, student_grade)
            VALUES (%s, %s, %s)
            """

            # Execute the query to insert data
            cursor.executemany(insert_query, student_data)

            # Commit changes to the database
            db.commit()

            # Close connection
            db.close()
        

In the provided code, %s is a placeholder used in SQL queries to indicate where the values should be inserted. When cursor.executemany(insert_query, student_data) is executed, the placeholders %s are replaced with the actual values from student_data for each record to be inserted. This is part of the parameterized query execution, which helps prevent SQL injection attacks

MySQL Connector - Fetch rows

            import mysql.connector

            # Establish a connection to the MySQL server
            db = mysql.connector.connect(
                ...
            )

            # Create a cursor object to interact with the database
            cursor = db.cursor()


            ### Example: Fetching all rows
            cursor.execute("SELECT * FROM students")
            rows = cursor.fetchall()
            for row in rows:
                print(row)

            ### Example:  Fetch a single row
            cursor.execute("SELECT * FROM students")

            row1 = cursor.fetchone()
            row2 = cursor.fetchone()
            print(row1)
            print(row2)

            db.close()
        

Project Overview

Let's build a simple Python project (SimpleUserManagment) that demonstrates how to interact with a MySQL database using the mysql-connector-python library. This example will cover basic CRUD (Create, Read, Update, Delete) operations.

Let's start with project structure:

                SimpleUserManagment/
                ├── db
                │   ├── config.py
                │   ├── __init__.py
                │   └── operations.py
                └── main.py
            

Make sure you have mysql-connector-python package installed:

                pip install mysql-connector-python
            

Create the Database and Table

You need to have a MySQL server running and create a database and table for your project:

                CREATE DATABASE testdb;
                USE testdb;

                CREATE TABLE users (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    name VARCHAR(255) NOT NULL,
                    email VARCHAR(255) NOT NULL UNIQUE
                );

            

Configuration File

Create a db_config.py file to store your database configuration:

                # db_config.py

                db_config = {
                    'user': 'yourusername',
                    'password': 'yourpassword',
                    'host': '127.0.0.1',
                    'database': 'testdb',
                }

            

Create Database Operations Module (db/operations.py)

Create an operations.py file in the db directory for database operations:

                # db/operations.py

                import mysql.connector
                from mysql.connector import Error
                from .config import db_config

                class MySQLDB:
                    def __init__(self):
                        self.connection = self.create_connection()

                    def create_connection(self):
                        connection = None
                        try:
                            connection = mysql.connector.connect(**db_config)
                            if connection.is_connected():
                                print("Connected to MySQL database")
                        except Error as e:
                            print(f"Error: '{e}'")
                        return connection

                    def create_user(self, name, email):
                        cursor = self.connection.cursor()
                        query = "INSERT INTO users (name, email) VALUES (%s, %s)"
                        cursor.execute(query, (name, email))
                        self.connection.commit()
                        print("User created successfully")

                    def get_users(self):
                        cursor = self.connection.cursor()
                        query = "SELECT * FROM users"
                        cursor.execute(query)
                        users = cursor.fetchall()
                        for user in users:
                            print(user)

                    def update_user_email(self, user_id, new_email):
                        cursor = self.connection.cursor()
                        query = "UPDATE users SET email = %s WHERE id = %s"
                        cursor.execute(query, (new_email, user_id))
                        self.connection.commit()
                        print("User email updated successfully")

                    def delete_user(self, user_id):
                        cursor = self.connection.cursor()
                        query = "DELETE FROM users WHERE id = %s"
                        cursor.execute(query, (user_id,))
                        self.connection.commit()
                        print("User deleted successfully")

                    def close_connection(self):
                        if self.connection.is_connected():
                            self.connection.close()
                            print("MySQL connection is closed")


            

Create Main Python File (main.py)

Create a main.py file for the main logic of the project:

                # main.py

                from db.operations import MySQLDB

                def main():
                    db = MySQLDB()

                    # Create a user
                    db.create_user("Ivan Ivanov", "ivan.ivanov@example.com")

                    # Read users
                    print("Users:")
                    db.get_users()

                    # Update a user's email
                    user_id = int(input("Enter user ID to update: "))
                    db.update_user_email(user_id, "ivan.newemail@example.com")

                    # Read users again to see the update
                    print("Updated Users:")
                    db.get_users()

                    # Delete a user
                    user_id = int(input("Enter user ID to delete: "))
                    db.delete_user(user_id)

                    # Read users again to see the deletion
                    print("Users after deletion:")
                    db.get_users()

                    # Close the connection
                    db.close_connection()

                if __name__ == "__main__":
                    main()