1 / 20

COMP4332/RMBI4310

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.

jhealy
Télécharger la présentation

COMP4332/RMBI4310

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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)

More Related