MySQL - Data to Decision

A true and tested relational database released in 1995.
- Originally from Swedish company MySQL AB.
- Later acquired by Sun who were then acquired by Oracle.
Freeware version and full enterprise versions for many platforms.
Lately also supports native JSON and NoSQL document store.
Easily installed directly in Windows, Mac, Linux, etc. or using Docker, Homebrew, etc.

Instructor’s database¶

An example of accessing an online, editable database.
Some VPN connections block the chosen port, e.g., NMBU’s network.

# Make a connection object 'connection' for the MySQL database 'u492297623_student1' on port 3306 at server 145.14.151.151 with username 'u492297623_stud1'
# and password given by instructor.
import mysql.connector
PWD = open('../../../No_sync/password_khliland','r').read()

# Establishing a connection to the MySQL database
connection = mysql.connector.connect(
    host='195.35.53.47', #'srv1748.hstgr.io',
    port=3306,
    user='u492297623_stud1',
    password=PWD,
    database='u492297623_student1',
    connection_timeout=10
)

# Creating a cursor object to execute SQL queries
cursor = connection.cursor()

Revealing structure¶

If the tables of a database and their structure is given in advance, this can be queried.

# Executing a SQL query to retrieve all the tables present in the database
query = "SHOW TABLES;"
cursor.execute(query)

# Fetching all the rows from the result set
tabs = cursor.fetchall()

# Printing the retrieved data
for row in tabs:
    print(row)

# Executing a SQL query to describe the table 'students' present in the database
query = "DESCRIBE students;"
cursor.execute(query)

# Fetching all the rows from the result set
studs = cursor.fetchall()

# Printing the retrieved data
for row in studs:
    print(row)

Extract data¶

# Executing the SQL query to select all data from the 'students' table
query = "SELECT * FROM students;"
cursor.execute(query)

# Fetching all the rows from the result set
result = cursor.fetchall()

# Printing the retrieved data
for row in result:
    print(row)

Example of the power of AI tools¶

Instructor wrote “Select only students whose name starts with ‘J’” with Copilot activated.
Copilot automatically generated the query.
Instructor corrected ‘name’ to ‘first_name’
It worked!

# Select only students whose name starts with 'J'
query = "SELECT * FROM students WHERE first_name LIKE 'J%';"
cursor.execute(query)

# Fetching all the rows from the result set
result = cursor.fetchall()

# Printing the retrieved data
for row in result:
    print(row)

Add new data¶

If the primary key is duplicated, this will return an error

# Insert a new row into the first_test table with the values 3, 'Pete', 'Data Science'
query = "INSERT INTO students VALUES (3, 'Pete', 'Data Science');"
# This can result in an error if the primary key already exists
cursor.execute(query)

# Increment the main_key and insert new data
query = "INSERT INTO students SELECT COALESCE(MAX(main_key) + 1, 1), 'Anita', 'Plant Science' FROM students;"
cursor.execute(query)
# (AUTO_INCREMENT can be set at the table creation to avoid having to do this)

Removing data¶

Delete a single record or data that follows a pattern.

# Delete all rows from the students table where main_key > 2
query = "DELETE FROM students WHERE main_key > 2;"
cursor.execute(query)

Commiting changes¶

All changes to the data using mysql.connector.cursor are local until committed.

# Push data from the cursor to the database
connection.commit()

# Closing the cursor and the database connection
cursor.close()
connection.close()

Exercise¶

Make a Python function that takes an SQL query as input, opens a connection, executes the statement, closes the connection and returns any results.
Assume that password, username, database name, etc. are stored in a dictionary inside the function. Make another version of the function that takes a query and the dictionary as input and returns any results.
Test both functions with SELECT and INSERT INTO statements.