MySQL

MySQL#

  • 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.

# 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='145.14.151.151',
    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)

('students',)

# 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)

('main_key', 'int(11)', 'NO', 'PRI', None, '')
('first_name', 'text', 'NO', '', None, '')
('study_program', 'text', 'NO', '', None, '')

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)

(1, 'John', 'Industrial Economics')
(2, 'Sandra', 'Building Physics')

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)

(1, 'John', 'Industrial Economics')

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#

  1. Make a Python function that takes an SQL query as input, opens a connection, executes the statement, closes the connection and returns any results.

  2. Assume that password, username, database name, etc. are stored in a dictionary. Make another version of the function that takes a query and the dictionary as input and returns any results.

  3. Test both functions with SELECT and INSERT INTO statements.

See also

Resources

Contents