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

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

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

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

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

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

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

เราจะเรียนรู้แนวคิดทั้งหมดโดยใช้ MySQL ดังนั้นให้เราพูดถึงโมดูล MySQLdb

MySQLdb คืออะไร?

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

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

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

#!/usr/bin/python

import MySQLdb

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

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

ในการติดตั้งโมดูล MySQLdb ให้ใช้คำสั่งต่อไปนี้ -

For Ubuntu, use the following command -
$ sudo apt-get install python-pip python-dev libmysqlclient-dev
For Fedora, use the following command -
$ sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc
For Python command prompt, use the following command -
pip install MySQL-python

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

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

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

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

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

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

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

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

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

ตัวอย่าง

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

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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()

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

Database version : 5.0.45

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

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

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

ตัวอย่าง

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

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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

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

ตัวอย่าง

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

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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/python

import MySQLdb

# Open database connection
db = MySQLdb.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/python

import MySQLdb

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

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

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 fecth data"

# disconnect from server
db.close()

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

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

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

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

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

ตัวอย่าง

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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/python

import MySQLdb

# Open database connection
db = MySQLdb.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