Unit 2: Generate Code

Structured Query Language (SQL)

SQL is a standard language for storing, manipulating and retrieving data in databases [W3Schools, 2019].

SQL is the the most popular language for working with databases despite being over 50 years old. It is everywhere and every major IT platform uses it. Why? Because it is powerful and effective.

SQLite

There are many different flavours of SQL. Most of these work by setting up an SQL server separate from the computer running the program. We will be using SQLite as our SQL database. It is a lightweight and fast database management system, which can run off a file stored on your local machine.

SQL Tutorials

Below are links to tutorials to refresh your knowledge on the SQL we will use in this course.

Retrieving Data

Basic Queries

• SELECT Statement

• Aliases

• SELECT DISTINCT Statement

• WHERE Clause

• AND, OR, NOT Operators

• NULL Values

• LIMIT Clause (use the MySQL syntax)

• ORDER BY Keyword

• Arithmetic Operators

• LIKE Operator

• Wildcard Characters

• BETWEEN Operator

Aggregation

• MIN() and MAX() Functions

• COUNT(), AVG() and SUM() Functions

• GROUP BY Statement

• HAVING Clause

Joins

• JOIN

• INNER JOIN Keyword

• LEFT JOIN Keyword

• RIGHT JOIN Keyword

• FULL OUTER JOIN Keyword

• Self Join

Sub-queries

• IN Operator

• EXISTS Operator

Create Tables and Modify Data

Database Management

• CREATE DATABASE Statement

• DROP DATABASE Statement

• CREATE TABLE Statement

• DROP TABLE Statement

• ALTER TABLE Statement

• NOT NULL Constraint

• UNIQUE Constraint

• PRIMARY KEY Constraint

• FORIEGN KEY Constraint

• AUTO INCREMENT Field

Data Management

• INSERT INTO Statement UPDATE Statement DELETE Statement

SQL & Python

This section gives a brief overview of using SQLite in Python. W3schools give more details in this blog.

To work with SQLite in Python we will use the sqlite3 module. It is part of the Python standard Library, so it is already installed.

Setup

There are three steps to setting up your Python code to use sqlite3:

1. Import the module

This is your usual import command at the top of your Python file.

import sqlite3

2. Connect to the database

You need to create a connection object. The connection object is used to make changes to the database file. Generate a connection object using the sqlite3.connect method and assign it to a variable.

import sqlite3

connection = sqlite3.connect("database_file.db")

3. Create a cursor

Finally you need to create a cursor object. The cursor object is used to run SQL queries on the database via the connection object.

import sqlite3

connection = sqlite3.connect("database_file.db")
cursor = connection.cursor()

Now you are set to access your SQLite database with Python.

Queries

There are two steps to running a query in using sqlite3 and Python.

1. Execute the SQL query statement

The SQL query statement is provided as a string. Below is an example of how I format the code using a multiline string. It makes it easier to read, and, therefore, increases maintainability.

cursor.execute(
    """
    SELECT name, phone_num
    FROM customers
    """
)

2. Fetch the results

Once the query has been executed, you need to fetch the results that are stored in the cursor. You have three fetching options:

1. fetchall() - returns all the results

2. fetchone() - returns the next result

3. fetchmany(size) - returns the size number of rows

cursor.execute(
    """
    SELECT name, phone_num
    FROM Customers
    """
)

reults = cursor.fetchall()

INSERT, CREATE, UPDATE, DELETE

All SQL statements that involve making changes to the database (INSERT, CREATE, UPDATE and DELETE) also have two steps, but they are slightly different.

Execute the SQL statement

The first step in as the same. The cursor needs to be used to execute the SQL statement.

import sqlite3

cursor.execute(
    """
    INSERT INTO Customers (name, phone_num)
    VALUES ("John", "0434123456")
    """
)

Commit changes

The changes to the database will not be permanent until they have been committed to the database file. You can commit numerous execute commands to the database in one go. To make the commitment we use the connection’s commit() method.

import sqlite3

cursor.execute(
    """
    INSERT INTO Customers (name, phone_num)
    VALUES ("John", "0434123456")
    """
)

connection.commit()

Using variables

To avoid SQL injection attacks, the correct way to insert variables values into your SQL statements is to use the parameter substitution method. This method inserts a placeholder in the SQL statement, and the values are then passes as a second argument to the execute() method. We will be using dictionaries to do this, although lists can also be used.

Queries

Below is an example of a parameterised query statement.

import sqlite3

cursor.execute(
    """
    SELECT name, phone_num
    FROM Customers
    WHERE name = :name
    """,
    {
        "name": "John"
    }
)

reults = cursor.fetchall()

INSERT, CREATE, UPDATE, DELETE

Below is an example of a parameterised insert statement

import sqlite3

cursor.execute(
    """
    INSERT INTO Customers (name, phone_num)
    VALUES (:name, :phone)
    """,
    {
        "name":"John",
        "phone":"0434123456"
    }
)

connection.commit()