Mirror Mirror Study Session 7

Learned how to actually work with basic MySQL statements in Python, getting hands-on practice with the flow of querying, updating, and inserting data. Building on previously learned content, also practiced reading CSV files and organizing them into a database, as well as the reverse process of extracting DB content into a file.

Basic MySQL-related commands covered included SHOW DATABASES, CREATE DATABASE, USE, CREATE TABLE, SHOW TABLES, DESCRIBE, CREATE USER, and GRANT ALL PRIVILEGES. Before the hands-on practice, DB connection packages such as mysqlclient and psycopg2 were also checked as preparation items.

When working with a DB in Python, the following flow was key.

• Connect to the DB with CON = MySQL.db.connect(...)
• Create a cursor for executing and storing SQL with C = CON.cursor()
• Perform INSERT/UPDATE with C.execute(...) and %s placeholders
• Save changes with CON.commit()
• Retrieve query results with fetchall() and output them to a file

When inserting new records into a table, CSV data was read and processed, then inserted in the form INSERT INTO ... VALUES (%s, %s, %s, %s, %s);. For querying, data retrieved with SELECT * FROM table_name WHERE condition; was saved using csv.writer. In the update exercise, records matching a condition were updated like UPDATE Suppliers SET Cost=%s, Purchase_Date=%s WHERE Supplier_Name=%s;.

In the advanced portion, learned how to modify and create an actual company DB using statements, and additionally picked up object-oriented programming concepts. Learned that creating classes and repeatedly generating objects from them reduces duplication, and found the analogy comparing a class to a fish-shaped waffle (bungeoppang) mold particularly memorable.

Through the study session, felt the learning effect firsthand by directly pulling needed data from an actual company DB. After extracting withdrawal request fee data needed by the CRM team, set a goal to study more deeply so as to be able to independently pull out and use whatever data the team needs going forward.