Working with MySQL in Python: SQL Operations Guide

1. COMP4332/RMBI4310 Using SQL in Python Prepared by Raymond Wong Presented by Raymond Wong

2. MySQL Connector • We can use MySQL in Python(when we are running the MySQL server). • We should install the package “MySQL Connector” (i.e., “mysql-connector-python”) in Python

3. Including Package • At the beginning of the “Python” script, include the following package. Python import mysql.connector

4. Database Connection • Inside the “Python” script, include the following. Get the database connection and get a database called “university” Python client = mysql.connector.connect(user='comp4332', password='datamining', host='127.0.0.1', database='university') cursor = client.cursor() … client.commit() cursor.close() client.close() Get the cursor of the connection (a structure used for handling SQL operations) Perform some SQL operations Perform a “commit” operation on database “university” Close the cursor Close the database connection

5. Database Connection • For each Python operation related to MySQL, it is better to include the following to handle exceptions related to MySQL Python try: … except mysql.connector.Error as error: print(error) Some operations in Python (including the previous operations just shown and the operations to be shown later)

6. Database Connection • In summary, one simple implementation can be: Python try: client = mysql.connector.connect(user='comp4332', password='datamining', host='127.0.0.1', database='university') cursor = client.cursor() … client.commit() cursor.close() client.close() except mysql.connector.Error as error: print(error)

7. Database Connection • Alternatively, you could use “try… except” for each individual SQL-related operation (rather than a whole block of all SQL-related operations) • In the following, we just focus on SQL-related operations. Thus, the operations handling exceptions will not be shown in the following slides.

8. MySQL Operation • Creating Table • Inserting Records • Updating Records • Querying Records • Removing Records • Dropping Table

9. 1. Creating Table The variable “stringSQL” stores what we should type in SQL. However, “;” is not needed inside this string. Python stringSQL = "create table student (sid char(8), sname varchar(200), byear int, primary key (sid))" cursor.execute(stringSQL)

10. MySQL Operation • Creating Table • Inserting Records • Updating Records • Querying Records • Removing Records • Dropping Table

11. 2. Inserting Records The variable “stringSQL” stores what we should type in SQL. Python stringSQL = "insert into student values ('12345678', 'Raymond', 1998)" cursor.execute(stringSQL)

12. MySQL Operation • Creating Table • Inserting Records • Updating Records • Querying Records • Removing Records • Dropping Table

13. 3. Updating Records The variable “stringSQL” stores what we should type in SQL. Python stringSQL = "update student set byear = 2008 where sid = '12345678'" cursor.execute(stringSQL)

14. MySQL Operation • Creating Table • Inserting Records • Updating Records • Querying Records • Removing Records • Dropping Table

15. 4. Querying Records The variable “stringSQL” stores what we should type in SQL. Python stringSQL = "select * from student where byear = 1998" cursor.execute(stringSQL) for oneStudent in cursor: tempSid = oneStudent[0] tempSname = oneStudent[1] tempByear = oneStudent[2] We could get the i-th column value by specifying with [i] in the variable “oneStudent”

16. Python 4. Querying Records stringSQL = "select * from student where byear = 1998" cursor.execute(stringSQL) recordNo = 0 for oneStudent in cursor: recordNo = recordNo + 1 tempSid = oneStudent[0] tempSname = oneStudent[1] tempByear = oneStudent[2] print("Record {:d}: (sid={:s}, sname={:s}, byear={:d})".format( recordNo, tempSid, tempSname, tempByear) ) Output Record 1: (sid=12345678, sname=Raymond, byear=1998) Record 2: (sid=56785678, sname=David Lee, byear=1998) Record 3: (sid=88888888, sname=Test Test, byear=1998)

17. MySQL Operation • Creating Table • Inserting Records • Updating Records • Querying Records • Removing Records • Dropping Table

18. 5. Removing Records The variable “stringSQL” stores what we should type in SQL. Python stringSQL = "delete from student where byear = 1998" cursor.execute(stringSQL)

19. MySQL Operation • Creating Table • Inserting Records • Updating Records • Querying Records • Removing Records • Dropping Table

20. 6. Dropping Table The variable “stringSQL” stores what we should type in SQL. Python stringSQL = "drop table student" cursor.execute(stringSQL)