Python 3 - การเข้าถึงฐานข้อมูล MySQL

มาตรฐาน Python สำหรับอินเทอร์เฟซฐานข้อมูลคือ Python DB-API อินเทอร์เฟซฐานข้อมูล Python ส่วนใหญ่เป็นไปตามมาตรฐานนี้

คุณสามารถเลือกฐานข้อมูลที่เหมาะสมสำหรับแอปพลิเคชันของคุณ Python Database API รองรับเซิร์ฟเวอร์ฐานข้อมูลที่หลากหลายเช่น -

• GadFly
• mSQL
• MySQL
• PostgreSQL
• Microsoft SQL Server 2000
• Informix
• Interbase
• Oracle
• Sybase
• SQLite

นี่คือรายการสินค้าจากอินเตอร์เฟซฐานข้อมูลหลาม - งูหลามการเชื่อมต่อฐานข้อมูลและ API คุณต้องดาวน์โหลดโมดูล DB API แยกต่างหากสำหรับแต่ละฐานข้อมูลที่คุณต้องการเข้าถึง ตัวอย่างเช่นหากคุณต้องการเข้าถึงฐานข้อมูล Oracle และฐานข้อมูล MySQL คุณต้องดาวน์โหลดทั้งโมดูลฐานข้อมูล Oracle และ MySQL

DB API มีมาตรฐานขั้นต่ำสำหรับการทำงานกับฐานข้อมูลโดยใช้โครงสร้าง Python และไวยากรณ์ทุกที่ที่ทำได้ API นี้มีดังต่อไปนี้ -

• การนำเข้าโมดูล API
• รับการเชื่อมต่อกับฐานข้อมูล
• การออกคำสั่ง SQL และกระบวนงานที่เก็บไว้
• กำลังปิดการเชื่อมต่อ

Python มีการรองรับ SQLite ในตัว ในส่วนนี้เราจะเรียนรู้แนวคิดทั้งหมดโดยใช้ MySQL โมดูล MySQLdb ซึ่งเป็นอินเทอร์เฟซยอดนิยมกับ MySQL ไม่สามารถทำงานร่วมกับ Python 3 ได้เราจะใช้โมดูลPyMySQLแทน

PyMySQL คืออะไร?

PyMySQL เป็นอินเทอร์เฟซสำหรับเชื่อมต่อกับเซิร์ฟเวอร์ฐานข้อมูล MySQL จาก Python ใช้ Python Database API v2.0 และมีไลบรารีไคลเอนต์ Pure-Python MySQL เป้าหมายของ PyMySQL คือการแทนที่ MySQLdb แบบดรอปอิน

ฉันจะติดตั้ง PyMySQL ได้อย่างไร

ก่อนดำเนินการต่อคุณต้องแน่ใจว่าคุณได้ติดตั้ง PyMySQL บนเครื่องของคุณแล้ว เพียงพิมพ์สิ่งต่อไปนี้ในสคริปต์ Python ของคุณและดำเนินการ -

#!/usr/bin/python3

import pymysql

หากสร้างผลลัพธ์ต่อไปนี้แสดงว่าไม่ได้ติดตั้งโมดูล MySQLdb -

Traceback (most recent call last):
   File "test.py", line 3, in <module>
      Import pymysql
ImportError: No module named pymysql

รุ่นล่าสุดที่เสถียรมีอยู่ใน PyPI และสามารถติดตั้งด้วย pip -

pip install pymysql

อีกทางเลือกหนึ่ง (เช่นหากไม่มี pip) สามารถดาวน์โหลด tarball ได้จากGitHubและติดตั้งด้วย Setuptools ดังนี้ -

$ # X.X is the desired pymysql version (e.g. 0.5 or 0.6).
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python setup.py install
$ # The folder PyMySQL* can be safely removed now.

Note - ตรวจสอบให้แน่ใจว่าคุณมีสิทธิ์ root ในการติดตั้งโมดูลด้านบน

การเชื่อมต่อฐานข้อมูล

ก่อนเชื่อมต่อกับฐานข้อมูล MySQL โปรดตรวจสอบประเด็นต่อไปนี้ -

• คุณได้สร้างฐานข้อมูล TESTDB

• คุณได้สร้างตาราง EMPLOYEE ใน TESTDB

• ตารางนี้มีฟิลด์ FIRST_NAME, LAST_NAME, AGE, SEX และ INCOME

• ID ผู้ใช้ "testuser" และรหัสผ่าน "test123" ถูกตั้งค่าให้เข้าถึง TESTDB

• โมดูล Python PyMySQL ได้รับการติดตั้งอย่างถูกต้องบนเครื่องของคุณ

• คุณได้อ่านบทช่วยสอน MySQL เพื่อทำความเข้าใจเกี่ยวกับ MySQL Basics

ตัวอย่าง

ต่อไปนี้เป็นตัวอย่างการเชื่อมต่อกับฐานข้อมูล MySQL "TESTDB" -

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")

# Fetch a single row using fetchone() method.
data = cursor.fetchone()
print ("Database version : %s " % data)

# disconnect from server
db.close()

ในขณะที่รันสคริปต์นี้จะสร้างผลลัพธ์ต่อไปนี้

Database version : 5.5.20-log

หากสร้างการเชื่อมต่อกับแหล่งข้อมูลแล้ว Connection Object จะถูกส่งคืนและบันทึกลงใน db สำหรับการใช้งานต่อไปมิฉะนั้น dbถูกตั้งค่าเป็นไม่มี ต่อไป,db วัตถุถูกใช้เพื่อสร้างไฟล์ cursorซึ่งจะใช้ในการดำเนินการแบบสอบถาม SQL สุดท้ายก่อนที่จะออกมาตรวจสอบให้แน่ใจว่าการเชื่อมต่อฐานข้อมูลถูกปิดและปล่อยทรัพยากร

การสร้างตารางฐานข้อมูล

เมื่อสร้างการเชื่อมต่อฐานข้อมูลแล้วเราก็พร้อมที่จะสร้างตารางหรือบันทึกลงในตารางฐานข้อมูลโดยใช้ execute วิธีการของเคอร์เซอร์ที่สร้างขึ้น

ตัวอย่าง

ให้เราสร้างตารางฐานข้อมูลพนักงาน -

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Drop table if it already exist using execute() method.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# Create table as per requirement
sql = """CREATE TABLE EMPLOYEE (
   FIRST_NAME  CHAR(20) NOT NULL,
   LAST_NAME  CHAR(20),
   AGE INT,  
   SEX CHAR(1),
   INCOME FLOAT )"""

cursor.execute(sql)

# disconnect from server
db.close()

การทำงานของ INSERT

จำเป็นต้องใช้ INSERT Operation เมื่อคุณต้องการสร้างระเบียนของคุณลงในตารางฐานข้อมูล

ตัวอย่าง

ตัวอย่างต่อไปนี้รันคำสั่งSQL INSERTเพื่อสร้างเรกคอร์ดในตาราง EMPLOYEE -

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
   LAST_NAME, AGE, SEX, INCOME)
   VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

ตัวอย่างข้างต้นสามารถเขียนได้ดังนี้เพื่อสร้างแบบสอบถาม SQL แบบไดนามิก -

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
   LAST_NAME, AGE, SEX, INCOME) \
   VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
   ('Mac', 'Mohan', 20, 'M', 2000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

ตัวอย่าง

ส่วนรหัสต่อไปนี้เป็นรูปแบบการดำเนินการอื่นที่คุณสามารถส่งผ่านพารามิเตอร์ได้โดยตรง -

..................................
user_id = "test123"
password = "password"

con.execute('insert into Login values("%s", "%s")' % \
             (user_id, password))
..................................

อ่านการทำงาน

READ Operation บนฐานข้อมูลใด ๆ หมายถึงการดึงข้อมูลที่เป็นประโยชน์จากฐานข้อมูล

เมื่อสร้างการเชื่อมต่อฐานข้อมูลแล้วคุณก็พร้อมที่จะทำการสืบค้นในฐานข้อมูลนี้ คุณสามารถใช้อย่างใดอย่างหนึ่งfetchone() วิธีการดึงข้อมูลระเบียนเดียวหรือ fetchall() วิธีการดึงหลายค่าจากตารางฐานข้อมูล

• fetchone()- ดึงข้อมูลแถวถัดไปของชุดผลการค้นหา ชุดผลลัพธ์คือวัตถุที่ส่งคืนเมื่อวัตถุเคอร์เซอร์ถูกใช้เพื่อสอบถามตาราง

• fetchall()- ดึงข้อมูลแถวทั้งหมดในชุดผลลัพธ์ หากมีการแยกแถวออกจากชุดผลลัพธ์แล้วระบบจะดึงข้อมูลแถวที่เหลือจากชุดผลลัพธ์

• rowcount - นี่เป็นแอตทริบิวต์แบบอ่านอย่างเดียวและส่งกลับจำนวนแถวที่ได้รับผลกระทบจากเมธอด execute ()

ตัวอย่าง

ขั้นตอนต่อไปนี้สอบถามบันทึกทั้งหมดจากตารางพนักงานที่มีเงินเดือนมากกว่า 1,000 -

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = "SELECT * FROM EMPLOYEE \
      WHERE INCOME > '%d'" % (1000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      # Now print fetched result
      print ("fname = %s,lname = %s,age = %d,sex = %s,income = %d" % \
         (fname, lname, age, sex, income ))
except:
   print ("Error: unable to fetch data")

# disconnect from server
db.close()

เอาต์พุต

สิ่งนี้จะให้ผลลัพธ์ดังต่อไปนี้ -

fname = Mac, lname = Mohan, age = 20, sex = M, income = 2000

อัปเดตการทำงาน

UPDATE การทำงานบนฐานข้อมูลหมายถึงการอัปเดตระเบียนตั้งแต่หนึ่งรายการขึ้นไปซึ่งมีอยู่แล้วในฐานข้อมูล

ขั้นตอนต่อไปนี้จะอัปเดตระเบียนทั้งหมดที่มี SEX เป็น 'M'. ที่นี่เราเพิ่มอายุของผู้ชายทุกคนขึ้นหนึ่งปี

ตัวอย่าง

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to UPDATE required records
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
                          WHERE SEX = '%c'" % ('M')
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

ลบการดำเนินการ

จำเป็นต้องดำเนินการ DELETE เมื่อคุณต้องการลบบางระเบียนออกจากฐานข้อมูลของคุณ ต่อไปนี้เป็นขั้นตอนในการลบบันทึกทั้งหมดจาก EMPLOYEE ที่ AGE มากกว่า 20 -

ตัวอย่าง

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

การทำธุรกรรม

ธุรกรรมเป็นกลไกที่ทำให้ข้อมูลมีความสอดคล้องกัน ธุรกรรมมีคุณสมบัติสี่ประการดังต่อไปนี้ -

• Atomicity - ไม่ว่าธุรกรรมจะเสร็จสมบูรณ์หรือไม่มีอะไรเกิดขึ้นเลย

• Consistency - ธุรกรรมต้องเริ่มต้นในสถานะที่สอดคล้องกันและปล่อยให้ระบบอยู่ในสถานะที่สอดคล้องกัน

• Isolation - ผลลัพธ์ระดับกลางของธุรกรรมจะไม่ปรากฏนอกธุรกรรมปัจจุบัน

• Durability - เมื่อมีการทำธุรกรรมแล้วผลกระทบจะคงอยู่แม้ระบบจะล้มเหลวก็ตาม

Python DB API 2.0 มีสองวิธีในการคอมมิตหรือย้อนกลับธุรกรรม

ตัวอย่าง

คุณรู้วิธีดำเนินธุรกรรมแล้ว นี่คือตัวอย่างที่คล้ายกัน -

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

COMMIT การดำเนินการ

Commit คือการดำเนินการซึ่งให้สัญญาณสีเขียวไปยังฐานข้อมูลเพื่อทำการเปลี่ยนแปลงให้เสร็จสิ้นและหลังจากการดำเนินการนี้จะไม่มีการเปลี่ยนกลับ

นี่คือตัวอย่างง่ายๆในการเรียกไฟล์ commit วิธี.

db.commit()

การทำงานของ ROLLBACK

หากคุณไม่พอใจกับการเปลี่ยนแปลงอย่างน้อยหนึ่งรายการและต้องการเปลี่ยนกลับการเปลี่ยนแปลงเหล่านั้นอย่างสมบูรณ์ให้ใช้ไฟล์ rollback() วิธี.

นี่คือตัวอย่างง่ายๆในการเรียกไฟล์ rollback() วิธี.

db.rollback()

กำลังยกเลิกการเชื่อมต่อฐานข้อมูล

ในการยกเลิกการเชื่อมต่อฐานข้อมูลให้ใช้เมธอด close ()

db.close()

หากการเชื่อมต่อกับฐานข้อมูลถูกปิดโดยผู้ใช้ด้วยเมธอด close () ธุรกรรมที่ค้างอยู่จะถูกย้อนกลับโดย DB อย่างไรก็ตามแทนที่จะขึ้นอยู่กับรายละเอียดการใช้งาน DB ในระดับที่ต่ำกว่าแอปพลิเคชันของคุณจะดีกว่าหากเรียกคอมมิตหรือย้อนกลับอย่างชัดเจน

การจัดการข้อผิดพลาด

มีหลายแหล่งที่มาของข้อผิดพลาด ตัวอย่างบางส่วน ได้แก่ ข้อผิดพลาดทางไวยากรณ์ในคำสั่ง SQL ที่เรียกใช้งานความล้มเหลวในการเชื่อมต่อหรือการเรียกวิธีการดึงข้อมูลสำหรับหมายเลขอ้างอิงคำสั่งที่ยกเลิกไปแล้ว

DB API กำหนดข้อผิดพลาดจำนวนหนึ่งที่ต้องมีอยู่ในแต่ละโมดูลฐานข้อมูล ตารางต่อไปนี้แสดงรายการข้อยกเว้นเหล่านี้

ซีเนียร์	ข้อยกเว้นและคำอธิบาย
1	Warning ใช้สำหรับปัญหาที่ไม่ร้ายแรง ต้องมีคลาสย่อย StandardError
2	Error คลาสพื้นฐานสำหรับข้อผิดพลาด ต้องมีคลาสย่อย StandardError
3	InterfaceError ใช้สำหรับข้อผิดพลาดในโมดูลฐานข้อมูลไม่ใช่ตัวฐานข้อมูล ต้องมีข้อผิดพลาดคลาสย่อย
4	DatabaseError ใช้สำหรับข้อผิดพลาดในฐานข้อมูล ต้องมีข้อผิดพลาดคลาสย่อย
5	DataError คลาสย่อยของ DatabaseError ที่อ้างถึงข้อผิดพลาดในข้อมูล
6	OperationalError คลาสย่อยของ DatabaseError ที่อ้างถึงข้อผิดพลาดเช่นการสูญเสียการเชื่อมต่อกับฐานข้อมูล โดยทั่วไปข้อผิดพลาดเหล่านี้อยู่นอกการควบคุมของ Python scripter
7	IntegrityError คลาสย่อยของ DatabaseError สำหรับสถานการณ์ที่จะทำลายความสมบูรณ์เชิงสัมพันธ์เช่นข้อ จำกัด เฉพาะหรือคีย์ต่างประเทศ
8	InternalError คลาสย่อยของ DatabaseError ที่อ้างถึงข้อผิดพลาดภายในโมดูลฐานข้อมูลเช่นเคอร์เซอร์ไม่ทำงานอีกต่อไป
9	ProgrammingError คลาสย่อยของ DatabaseError ที่อ้างถึงข้อผิดพลาดเช่นชื่อตารางที่ไม่ถูกต้องและสิ่งอื่น ๆ ที่สามารถตำหนิคุณได้อย่างปลอดภัย
10	NotSupportedError คลาสย่อยของ DatabaseError ที่อ้างถึงการพยายามเรียกใช้ฟังก์ชันที่ไม่รองรับ

สคริปต์ Python ของคุณควรจัดการกับข้อผิดพลาดเหล่านี้ แต่ก่อนที่จะใช้ข้อยกเว้นใด ๆ ข้างต้นตรวจสอบให้แน่ใจว่า MySQLdb ของคุณรองรับข้อยกเว้นนั้น คุณสามารถรับข้อมูลเพิ่มเติมเกี่ยวกับสิ่งเหล่านี้ได้โดยอ่านข้อกำหนด DB API 2.0