CODE WITH MARTIN

Databases

This chapter shows how to install and use MySQL in Python.


Contents


Introduction

A database product in simple terms, is just another program that handles receiving and serving data in a really really fast way. Databases are designed to handle millions upon millions of rows of data and allow you to search and produce calculations on that data in a blink of an eye.

We're going to get our hands completely dirty as we're going to be installing a database product and then learning how to interact and develop things in the database product using Python.

Just like programming languages, there are many different types of databases all designed to solve different problems. We're going to be using one that will allow you to transfer everything you learn to any other database product that you may encounter in the future and is the best and most suitable database for general business applications.

We will be using a database product called MySQL, which is freely available to download and install and comes with a good set of tools to later work with the database faster than just with Python code alone.

The topic of databases is extremely deep. So deep in fact that you can have a career focused entirely on database administration. Usually large companies have the role of DBA - Database Administrator, but smaller companies expect developers to take on the work of looking after their databases. We will cover databases to a point of what I consider to be enough for you to survive in both environments.

Installing MySQL

You can find the MySQL Community Edition (free version) located here: Download Page (Windows) or Download Page (MacOS/Linux)

Click the Download button at the top of the list on the download page, which should show a smaller file size than the download below it. Either one will work, but the larger sized download isn't really required and won't affect anything anyway.

Once the installer has downloaded, run it. As you work through the intaller steps, make sure the Developer Default install type is selected. Keep all options on every page of the installer default to the settings it suggests.

When you get to the step that prompts you for a 'root password', just use something simple you can remember. Since this is just a local development database, I've just set my root password to "password".

When the install finishes (it cant take up to 5 minutes), you will see a terminal window open - you can simply close this. You will also see the MySQL Workbench program opened, you can close this too. Later on, you can find this tool from your start menu anytime by searching for MySQL Workbench.

Connecting to MySQL

With MySQL installed and running, we should first test that we can connect to it using Python. To work with MySQL from Python, we need to install a package named "mysql-connector-python", so run the command "pip install mysql-connector-python" from a terminal window to install that.

Now let's write the code required to connect to MySQL. Create a new Python code file named 'mysqlconnect.py' and put the following code in:

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password"
)

info = db.get_server_info()
print("Connected to MySQL version", info)

db.close()

Before running the code, make sure you change the password on line 6 to match the password you set when setting the root password during the MySQL installation.

Save the code file and then run the code, you should see something like this in the terminal output:

Connected to MySQL version 8.0.30

This means that you have successfully connected to your installation of MySQL on your machine.

If instead you see the following error in the terminal:

mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

This means that the password you entered on line 6 does not match what you set when installing MySQL. Correct it, and try again.

On line 3 of the code example, we are creating a new variable named 'db' which is the result of the 'connect' function. We pass parameters to this function that describe the target server name (localhost is your computer) and the user credentials that will be used to login to MySQL. The user 'root' is the master account in MySQL that has privileges to do anything we want.

On line 9, we're fetching the version of MySQL that we are connected to and printing this to the terminal on line 10 which confirms the connection is working ok. This isn't really required but doesn't hurt to see that information to give us feedback on successfully connecting. If the connect function on line 3 did fail, it would instead raise an error and crash the program. It would be a good idea to use a try/except block to gracefully handle this but for our simple example, what we have is ok.

And finally, on line 12, we're closing the connection to MySQL when we are done working with it.

We now need to talk a little bit about how databases work to get an understanding of how we're going to use them.

Database Basics

A database is a catalog of many tables where a single table looks something like a spreadsheet. A table has named columns and rows containing values for each column. Each column also has a type, which describes to MySQL what kind of data will be kept in each column.

MySQL can contain multiple databases allowing you to group tables in different databases.

Databases receive commands in the form of text. We call this text SQL which stands for 'Structured Query Language'. SQL is an industry standard language that we use with databases to express what we want the database to do, such as retrieving data from tables or inserting new data in to tables. SQL is something that can take years to master but the basics are really simple. It's also a language you can add to your CV/resume.

Let's begin looking at some simple SQL commands and how we execute these commands in MySQL.

Listing Databases

We mentioned that MySQL can store many different databases. Let's first look at how we can send an SQL command to MySQL to query what databases exist. Create a new Python code file named 'mysqldatabases.py' and put the following code in it:

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password2"
)

info = db.get_server_info()
print("Connected to MySQL version", info)

cursor = db.cursor()

cursor.execute("SHOW DATABASES")

for x in cursor:
    print(x)

cursor.close()
db.close()

Save the file, and run it. You should see something similar to the following in the terminal output (don't worry if the list is less or more):

Connected to MySQL version 8.0.30
('information_schema',)
('mycompany',)
('mysql',)
('performance_schema',)
('sakila',)
('sys',)
('world',)

There's a couple of new things that have been added to the code example that we need to highlight. First, note line 12 we use the 'db' object to execute a function named 'cursor'. This function returns an object that allows us to issue commands to MySQL and work with the results from the commands.

Line 14 is also special as the function 'execute' on the cursor object, is what issues an SQL query to MySQL. The SQL we're sending to MySQL is "SHOW DATABASES". This command asks MySQL for a list of the created databases.

After executing the SQL query on line 14, we need to read the results. The cursor object contains the results and acts like an array that we can iterate with a for loop to print the names of the databases to the terminal. We do this on lines 16 and 17.

And finally on line 19, just like we do with the 'db' variable on line 20, we must close the cursor object when we're finished using it.

Now we're going to look at how we can create our very own database.

Creating Databases

Let's start creating a database named "mycompany" using a new SQL query command. Create a new Python source file named 'mysqlcreatedb.py' and put the following code in it:

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password"
)

cursor = db.cursor()

cursor.execute("CREATE DATABASE mycompany")

cursor.close()
db.close()

Save the file and run this code once to create the database. Nothing will display in the terminal which indicates everything worked. You can run the previous example code in 'Listing Databases' to see if your new database now appears in the list if you like.

If you try to run this code a second time, you will be issuing another 'CREATE DATABASE mycompany' to MySQL, but we already created the database in a previous run. This will result in an error which will display in the terminal like the following:

mysql.connector.errors.DatabaseError: 1007 (HY000): Can't create database 'mycompany'; database exists

This is simply telling us that the SQL command failed because we already have a database named 'mycompany' in MySQL.

It's now time to start creating tables in our new database.

Creating Tables

Inside our database, we're going to create a table to store a list of customers. What we have to consider when creating tables is what kind of data will be contained in each column. Each column has a data type, much like how your variables in Python have types like integers and strings.

Having the correct data types in a table allows MySQL to be very efficient at working with the data. For example, if you're going to store text in a column, what is the maximum length of the text? Same with numbers, if you're going to store a number, is it a small number that only needs a few values? Or is it potentially a very large number that can be in the trillions.

Understanding the data types for columns is a slight learning curve and I suggest you research the MySQL data types at some point and decide what types you may want to use in future table designs. We're going to be using some basic ones to help get you started.

Let's see what the SQL query looks like to create a table named 'customer' in our database (notice how the name of the table is not plural). Create new Python source file named 'mysqlcreatetable.py' and put the following code in it:

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password",
  database="mycompany"
)

cursor = db.cursor()

cursor.execute("CREATE TABLE customer (Name VARCHAR(255), Address VARCHAR(255), Email VARCHAR(255), PhoneNumber VARCHAR(20))")

cursor.close()
db.close()

Now save the code file.

One of the most important changes in this example is the introduction of a new parameter in the 'connect' function on line 3. We have added the parameter 'database="mycompany"' on line 7 which tells MySQL to start using our database when we connect.

The SQL query "CREATE TABLE" on line 12 shows how we describe 4 columns (comma separated names enclosed in brackets) with the names 'Name', 'Address', 'Email', and 'PhoneNumber'. Each column also has a data type after the name. The data type 'VARCHAR(255)' is like a string in Python. The number in brackets after the data type name is the maximum size the string can be in length. Phone numbers are usually quite small so we've set the max size for that column to 20.

Fun Fact: If you're wondering why we've used the length 255 for most of the columns, this is to do with how MySQL stores how large the columns can be. In computer memory terms, 255 is the maximum value that can be counted using 1 byte of memory. If we specified 256, we over step that 1 byte and 2 bytes would be required. If we set it to something smaller than 255 such as 200, it would still consume 1 byte. So we simply set this value to the maximum value that fits 1 byte. Also note that 255 is not a limit, you can set this higher if you need to.

Running this code once will create the table with no messages in the terminal output. Like when we created the database, running this code more than once will throw an error because the table already exists. If you want to see a list of tables in the current database, you can use the SQL statement "SHOW TABLES", similar to how we did earlier in the 'List Databases' example.

Altering Tables

A problem we might face after creating a table is wanting to add more columns to it. Let's look at how we can add a column to the table we just created.

An important column you will want on all of our tables is a unique ID to represent the row - we call this the Primary Key of the table. We can ask MySQL to auto increment this unique ID per row for us every time data is inserted in the table. Let's see how we can add this special primary key column with the name 'ID' to the existing customer table.

Create a new Python source code file named 'mysqlaltertable.py' and put the following code in it:

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password",
  database="mycompany"
)

cursor = db.cursor()

cursor.execute("ALTER TABLE customer ADD COLUMN ID INT AUTO_INCREMENT PRIMARY KEY FIRST")

cursor.close()
db.close()

Save the file.

Here we can see a new "ALTER TABLE" SQL query command that lets us alter an existing table. Directly after the "ADD COLUMN" words, you begin to see the column being defined as an "INT" data type. The "AUTO_INCREMENT" and "PRIMARY KEY" are also specified to tell MySQL to treat this column as an automatic incrementing value and that this column is the primary key of the table. Finally, the "FIRST" keyword means we want this column to be the first column in the table.

In the future, you will just ensure that this primary column is included in your initial "CREATE TABLE" statements to save you having to add it after. We use "ALTER TABLE" here just to demonstrate how tables can be altered after creating them.

We finally have our own table now. Let's start inserting some data in to it.

Inserting Data

Inserting data in to a table is yet another SQL query command. Create a new Python file named 'mysqlinsert.py' and place the following code in it:

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password",
  database="mycompany"
)

cursor = db.cursor()

sql = "INSERT INTO customer (Name, Address, Email, PhoneNumber) VALUES (%s, %s, %s, %s)"
val = ("Martin", "37 Milky Way", "martin@mycompany.com", "012345678")
cursor.execute(sql, val)

db.commit()

print(cursor.rowcount, "record inserted.")

cursor.close()
db.close()

Save the file and then run it. You should see "1 record inserted." in the terminal output.

Line 12 shows our new "INSERT INTO" SQL query stored in a variable named 'sql'. In the SQL query, we first specify what columns we want to insert data in to for. Each column name is separated by a comma in brackets. The "VALUES" keyword then follows and another set of brackets with a placeholder "%s" as the values to insert. The actual values to be inserted are done by defining a tuple on Line 13 and then passing this tuple containing the values along with the SQL query variable to the execute function on Line 14.

Finally, on line 16, we ask MySQL to commit the operation which finally writes the data in to the table.

Once we write a new row in to a table, we usually want to know what ID MySQL assigned to it in its ID column. This is so that if our software wanted to work with that row after inserting it, well, we have the ID value of it.

To get the ID, we simply access it after the commit function using the cursor object. The following example shows how we get and print the new row ID in the terminal (use this code to replace the code in the 'mysqlinsert.py' file):

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password",
  database="mycompany"
)

cursor = db.cursor()

sql = "INSERT INTO customer (Name, Address, Email, PhoneNumber) VALUES (%s, %s, %s, %s)"
val = ("Martin", "37 Milky Way", "martin@mycompany.com", "012345678")
cursor.execute(sql, val)

db.commit()

print(cursor.rowcount, "record inserted with ID", cursor.lastrowid)

cursor.close()
db.close()

Be aware that running this code with this change means that we now have executed two "INSERT INTO" SQL queries on the database. Which means that our table now has 2 rows inside it, or more, if you ran the example multiple times.

Inserting Multiple Rows

We've seen how to insert a single row in to our table, so now we'll insert multiple rows and set our table with a lot of data to allow us to learn the different ways we can retrieve it.

The only difference from a single insert is using a list of tuples for the values, and changing the execute function name we use on the cursor object. Create a new Python file named 'mysqlinsertmultiple.py' and place the following code in it:

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password",
  database="mycompany"
)

cursor = db.cursor()

sql = "INSERT INTO customer (Name, Address, Email, PhoneNumber) VALUES (%s, %s, %s, %s)"

val = [
    ("Bill", "1 Milky Way", "bill@mycompany.com", "09147365"),
    ("Chris", "2 Milky Way", "chris@mycompany.com", "02467894"),
    ("Emily", "3 Milky Way", "emily@mycompany.com", "05437984")
]

cursor.executemany(sql, val)
db.commit()

print(cursor.rowcount, "rows inserted.")

cursor.close()
db.close()

Save and run the code file.

Line 14 is the creation of our list of tuples that hold the values we want to insert in to the table - 1 tuple is 1 row. Line 20 uses the new 'executemany' function that causes our SQL query to execute multiple times for each tuple in the list. And to show you what exactly MySQL inserted after committing our data to the table, we print the inserted row count on line 23 to the terminal.

Feel free to create as many rows as you like and vary the data values ready for the next exercise.

Retrieving Data

Retrieving data from databases is the bread and butter of database products. We have the power to pull data from multiple tables at once, we can filter, sort, generate or calculate new pieces of data on top of existing data and so much more. But as with anything complex, we start simple.

The most quick and dirty way to grab data from a table is asking for all of it. The following code shows the new SELECT SQL query command in action and how we can print the results of the query to the terminal.

Create a new Python code file named 'mysqlselect.py' and put the following code in it:

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password",
  database="mycompany"
)

cursor = db.cursor()

cursor.execute("SELECT * FROM customer")

result = cursor.fetchall()

for x in result:
    print(x)

cursor.close()
db.close()

Save and run the code file.

The SQL query on line 12 "SELECT * FROM customer" tells MySQL that we want to select all columns and rows from the customer table. The SELECT statement is made up by telling MySQL what columns we want to bring back from the database followed by the table to go looking for data in.

The column names come directly after the SELECT word in the query text. If we wanted just the name and email from the customer table, we would write "SELECT Name, Email FROM customer" (column names are separated by a comma), and if you just wanted the phone number column we could write "SELECT PhoneNumber FROM customer". In our example, the asterisk symbol '*' after the SELECT word means to just bring back all columns found in the table.

The table name that we want to fetch the data from is specified directly after the keyword FROM, which in the example, we specify 'FROM customer' which indicates the customer table.

On line 14, we use the cursor object to execute the function 'fetchall' to bring all data from the database over in to our programs memory. The 'fetchall' function returns a list object whose items are tuples that represent each row of the result. The items in each tuple represent each column in the table.

Line 16 and 17 shows how we iterate over the list contained in the 'result' variable. Your terminal will display all the rows contained in the database table. My terminal output looks like this (yours will probably differ):

(1, 'Martin', '37 Milky Way', 'martin@mycompany.com', '012345678')
(2, 'Martin', '37 Milky Way', 'martin@mycompany.com', '012345678')
(3, 'Martin', '37 Milky Way', 'martin@mycompany.com', '012345678')
(4, 'Martin', '37 Milky Way', 'martin@mycompany.com', '012345678')
(5, 'Martin', '37 Milky Way', 'martin@mycompany.com', '012345678')
(6, 'Bill', '1 Milky Way', 'bill@mycompany.com', '09147365')
(7, 'Chris', '2 Milky Way', 'chris@mycompany.com', '02467894')
(8, 'Emily', '3 Milky Way', 'emily@mycompany.com', '05437984')

You can see how in each tuple, there are 5 values, one for each column in the table.

Now we're going to demonstrate a lot of varying SELECT SQL query like statements. I suggest you try replacing the SQL query in the 'mysqlselect.py' code file with the example SQL queries below and see what kind of output you get in the terminal after running the code file with the change. Make sure you save the code file after each change to the SQL query.


Select all columns and rows from the customer table:
SELECT * FROM customer

Select 3 columns from all rows in the customer table:
SELECT ID, Email FROM customer

Select 2 columns ordered by the column 'Name':
SELECT ID, Name FROM customer ORDER BY Name

Select 2 columns ordered by the column 'Name' in descending order:
SELECT ID, Name FROM customer ORDER BY Name DESC

Select 2 columns ordered by the column 'Name' and then by the column 'Address':
SELECT ID, Name FROM customer ORDER BY Name, Address

Select all rows where the column 'Name' is 'Bill':
SELECT * FROM customer WHERE Name = 'Bill'

Select all columns and a maximum of 3 rows from the customer table':
SELECT * FROM customer LIMIT 3

Select all unique names from the 'Name' column:
SELECT DISTINCT Name FROM customer

Select the column 'Name' from rows where the 'Name' column begin with the letter 'E':
SELECT Name FROM customer WHERE Name LIKE 'E%'

Select the column 'Name' from rows where the 'Address' column contains the word 'Milky':
SELECT Name FROM customer WHERE Address LIKE '%Milky%'

Select the column 'Name' from rows where the 'Address' column ends with the word 'Way':
SELECT Name FROM customer WHERE Address LIKE '%Way'

Select all columns and rows where the 'ID' column is less than 3:
SELECT * FROM customer WHERE ID < 3

Select all columns and rows where the 'ID' column is greater than 3:
SELECT * FROM customer WHERE ID > 3

Select all columns and rows where the 'ID' column is greater than or equal to 3:
SELECT * FROM customer WHERE ID >= 3

Select all columns and rows where the 'ID' column is 3 and the address contains the word 'Milky':
SELECT * FROM customer WHERE ID = 3 AND Address LIKE '%Milky%'

Select all columns and rows where the 'ID' column is not equal to 3:
SELECT * FROM customer WHERE ID <> 3

Select all columns and rows where the 'Name' column is not equal to 'Bill':
SELECT * FROM customer WHERE NOT Name = 'Bill'

Select all columns and rows where the 'ID' column is equal to 1 or 2:
SELECT * FROM customer WHERE ID = 1 OR ID = 2


That's a lot of ways to get data! We've missed a few other operators out too that you may want to research yourself, they are MIN, MAX, AVG, SUM, BETWEEN and IN.

Notice how in some of the examples in the 'WHERE' part of a SQL, the conditions can be made up of multiple tests on the data using the 'AND' and 'OR' SQL keywords. Multiple conditions can be stacked up as many times as you need to be more specific about what data you want.

Updating Data

Updating rows is another SQL query statement that begins with "UPDATE". Here's an example update query:

UPDATE customer SET Name='Marty' WHERE ID = 2

First we specify the table we want to modify directly after the keyword 'UPDATE', then the keyword 'SET' followed by a list of columns and their new values. Like 'SELECT' queries the UPDATE query supports a WHERE condition to allow us to target only specific rows to update. So in this example, only the row with an ID column value of 2 will get updated.

Create a new Python code file named 'mysqlupdate.py' and place the following code in it:

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password",
  database="mycompany"
)

cursor = db.cursor()

sql = "UPDATE customer SET Name='Marty' WHERE ID = 2"

cursor.execute(sql)
db.commit()

print(cursor.rowcount, "row(s) updated.")

cursor.close()
db.close()

If you save and run this, you will see the text "1 row(s) updated." produced from the print function on line 17. After executing the SQL query on line 14, the cursor contains a count of how many rows were actually updated which is shown on line 17 where we access it using 'cursor.rowcount'.

Multiple columns can be specified if you want to update more than 1 column, the query would look like this:

UPDATE customer SET Name='Martin', PhoneNumber='0000' WHERE ID = 2

More columns can be added by putting a comma between the column name and value pairs.

Note: If you don't include a WHERE condition to target specific rows to update, MySQL will update all rows in the target table.

Deleting Data

Our final exercise on dealing with our table data is deleting rows. That's easily done with the "DELETE FROM" SQL statement.

Create a new Python code file named 'mysqldelete.py' and place the following code in it:

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password",
  database="mycompany"
)

cursor = db.cursor()

sql = "DELETE FROM customer WHERE Name = 'Martin'"

cursor.execute(sql)

db.commit()

print(cursor.rowcount, "record(s) deleted")

cursor.close()
db.close()

Save and run the code file. The terminal output will display how many rows the SQL delete query removed from the table.

It's very important to note that a "DELETE FROM" statement has a "WHERE" condition attached to it. If that "WHERE" condition was not present in the statement, a query like "DELETE FROM customer" will delete all data in the table, so be careful! Our delete statement in the example above only deletes rows where the column 'Name' is equal to 'Martin', so we're safe knowing that we have targeted a specific set of rows.

Final Notes

We've gone over a lot but everything we've seen so far are very essential parts of working with databases. We have yet to explore some slightly more advanced topics, but these will come later in the series when we need to use them. Knowing how to create tables, insert, select, and delete is a very good base and will serve us fine for the next parts in the backend series of tutorials.