200 likes | 211 Views
COMP4332/RMBI4310. Using SQL in Python. Prepared by Raymond Wong Presented by Raymond Wong. 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. Including Package.
E N D
COMP4332/RMBI4310 Using SQL in Python Prepared by Raymond Wong Presented by Raymond Wong
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
Including Package • At the beginning of the “Python” script, include the following package. Python import mysql.connector
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
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)
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)
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.
MySQL Operation • Creating Table • Inserting Records • Updating Records • Querying Records • Removing Records • Dropping Table
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)
MySQL Operation • Creating Table • Inserting Records • Updating Records • Querying Records • Removing Records • Dropping Table
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)
MySQL Operation • Creating Table • Inserting Records • Updating Records • Querying Records • Removing Records • Dropping Table
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)
MySQL Operation • Creating Table • Inserting Records • Updating Records • Querying Records • Removing Records • Dropping Table
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”
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)
MySQL Operation • Creating Table • Inserting Records • Updating Records • Querying Records • Removing Records • Dropping Table
5. Removing Records The variable “stringSQL” stores what we should type in SQL. Python stringSQL = "delete from student where byear = 1998" cursor.execute(stringSQL)
MySQL Operation • Creating Table • Inserting Records • Updating Records • Querying Records • Removing Records • Dropping Table
6. Dropping Table The variable “stringSQL” stores what we should type in SQL. Python stringSQL = "drop table student" cursor.execute(stringSQL)