Python و دسترسی به دیتابیس MySQL

پایتون جهت دسترسی به دیتابیس از توابع کتابخانه ای DB-API استفاده کرده و interface هایی که برای اتصال به پایگاه داده و مدیریت داده های اپلیکیشن بایستی پیاده سازی شود، بر اساس همین استاندارد می باشد. در واقع بیشتر رابط های (interface) اتصال به دیتابیس از این استاندارد پیروی می کنند.

توسعه دهنده می تواند بر اساس نیاز اپلیکیشن خود، دیتابیس مناسب را انتخاب کند. توابع کتابخانه ای اتصال و استفاده از دیتابیس زبان پایتون (API) از database server های زیر پشتیبانی می کند:

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

برای مشاهده لیست interface های اتصال به دیتابیس می توانید به این لینک مراجعه کنید: http://wiki.python.org/moin/DatabaseInterfaces. لازم به ذکر است که برای اتصال به هر دیتابیس مجزا و جهت دسترسی یا مدیریت داده های اپلیکیشن می بایست یک ماژول DB API جداگانه دانلود و نصب نمایید. به طور مثال، چنانچه توسعه دهنده می بایست علاوه بر MySQL به دیتابیس Oracle دسترسی پیدا کند، بدیهی است که باید ماژول های مجزا هریک را جداگانه از اینترنت بارگیری کرده و نصب نماید (ماژول های دیتابیس MySQL و Oracle).

DB API یک حداقل استاندارد برای مدیریت دیتابیس با استفاده از ساختار و دستور نحوی زبان برنامه نویسی پایتون در اختیار توسعه دهنده قرار می دهد. استفاده از این مجموعه توابع کتابخانه ای یا API مراحل زیر را شامل می شود:

• وارد کردن ماژول این مجموعه توابع کتابخانه ای با استفاده از دستور import
• برقراری اتصال به دیتابیس
• صدور و فراخوانی دستورات و توابع (Store procedure) مورد نیاز SQL
• بستن و قطع اتصال به دیتابیس

در آموزش حاضر تمامی این مباحث را با دیتابیس رابطه ای MySQL مدیریت می کنیم. به همین جهت ماژول MySQLdb را بارگیری نموده و نصب می کنیم.

MySQLdb

MySQLdb یک رابط یا interface برای اتصال به سرویس دهنده دیتابیس MySQL (MySQL Database server) با زبان برنامه نویسی پایتون است که توسعه دهنده می بایست برای دسترسی و مدیریت داده های اپلیکیشن آن را پیاده سازی کند. این اینترفیس ویرایش 2.0 Database API پایتون را پیاده سازی کرده و بر پایه ی MySQL C API ساخته شده است.

نصب MySQLdb

جهت استفاده از توابع MySQLdb لازم است ماژول آن را بر روی دستگاه خود نصب نمایید. کافی است دستورات زیر را در اسکریپت پایتون لحاظ کرده و آن ها را اجرا نمایید:

#!/usr/bin/python
import MySQLdb

کد فوق یک پیغام خطا مبنی بر اینکه ماژول MySQLdb نصب نشده است تولید می کند:

Traceback (most recent call last):
  File "test.py", line 3, in 
    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

پیاده سازی اتصال به دیتابیس (Database connection)

پیش از اتصال به دیتابیس MySQL، لازم است اقدامات زیر را کامل انجام داده باشید:

• یک دیتابیس به نام TESTDB ایجاد نموده اید.
• یک جدول به نام EMPLOYEE در دیتابیس مزبور تعریف کرده اید.
• جدول مورد نظر فیلدهایی به نام FIRST_NAME، LAST_NAME، AGE، SEX و INCOME را دربرمی گیرد.
• جهت دسترسی به دیتابیس User ID (شناسه ی کاربری) را بر روی "testuser" و گذرواژه را بر روی "test123" تنظیم کرده اید.
• ماژول MySQLdb به طور کامل بر روی دستگاه مورد نظر نصب شده است.
• با مفاهیم پایه و ابتدایی دیتابیس MySQL آشنایی کافی داشته باشید.

ماژول

ذیل مثالی را مشاهده می کنید که در آن توسعه دهنده با زبان پایتون به دیتابیس رابطه ای 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 در خروجی بازگردانی شده و متعاقبا داخل آبجکت db جهت استفاده در آینده ذخیره می گردد. در غیر این صورت مقدار db برابر None قرار داده خواهد شد. آبجکت db سپس جهت اعلان و آماده سازی آبجکت cursor استفاده می شود. حال به منظور اجرای دستورهای درخواست داده و پرس و جو از دیتابیس، متد execute() بر روی آبجکت cursor فراخوانی می شود. در پایان، پیش از خروج از دیتابیس، اتصال به دیتابیس قطع شده و منابع مورد استفاده آزاد می شوند.

ایجاد جدول دیتابیس

پس از اینکه اتصال به دیتابیس برقرار شد، توسعه دهنده می تواند اقدام به ساخت جدول و درج سطر در جداول دیتابیس نماید. برای این منظور لازم است متد execute را بر روی آبجکت cursor صدا بزند.

مثال

در زیر یک جدول به نام 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()
# 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 زبان SQL را برای ایجاد رکورد جدید در جدول 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()

مثال فوق را می توان جهت تولید Query های SQL به صورت dynamic (در زمان اجرا) به صورت زیر نوشت:

#!/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()

مثال

تکه کد زیر روش دیگری از درج داده در سطر است که در آن شما می توانید پارامترها را به صورت مستقیم به متد execute ارسال کنید:

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

عملیات خواندن داده ها (READ)

عملیات READ منحصرا اطلاعات مفیدی را از دیتابیس واکشی می کند.

پس از برقرار اتصال به دیتابیس، می توان از آن جهت درخواست داده های مورد نظر Query گرفت. دو متد fetchone() و fetchall() نیز برای همین منظور تعبیه شده اند.

• fetchone(): این متد همان طور که از اسم آن پیدا است، تنها یک رکورد یا سطر را در خروجی برمی گرداند. در واقع متد حاضر سطر بعدی از میان مجموعه سطرهای داده (result set خروجی کوئری) را بازگردانی می نماید. زمانی که توسعه دهنده با استفاده از cursor از دیتابیس کوئری می گیرد، خروجی یک آبجکت result set (مجموعه سطرهای داده) می باشد.
• fetchall(): متد جاری قادر است همزمان چندین مقدار را ازدیتابیس واکشی کند. این متد تمامی سطرهای موجود در مجموعه سطرهای داده یا آبجکت result set را بازیابی می کند. اگر برخی از سطرها قبلا از دیتابیس استخراج شده باشد، در آن صورت باقی سطرها از آبجکت result set واکشی می شود.
• rowcount: این المان یک attribute فقط خواندنی (read-only) است و تعدد سطرهایی که تحت تاثیر متد execute() قرار گرفتند را بازمی گرداند.

مثال

procedure زیر تمامی سطرهای موجود در دیتابیس را از جدول EMPLOYEE که مقدار فیلد income آن بیشتر از 1000 می باشد را به عنوان خروجی کوئری بازمی گرداند:

#!/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 = "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 و بروز رسانی داده ها

زمانی که عملیات UPDATE بر روی دیتابیس اجرا می شود، یک یا چندین سطر موجود در این دیتابیس با داده های جدید بروز رسانی می شوند.

procedure و قطعه کدی که در زیر مشاهده می کنید، تمامی رکوردهایی که مقدار فیلد SEX آن ها 'M' می باشد را بروز رسانی می کند.در مثال جاری، مقدار فیلد AGE تمامی مردها را به میزان یک سال افزایش می دهیم.

مثال

#!/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 و حذف رکورد از دیتابیس

عملیات 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()

اجرای تراکنش بر روی دیتابیس (Transactions)

تراکنش یک مکانیزم است که دیتابیس را از یک وضعیت پایدار به وضعیت سالم و پایدار دیگر انتقال می دهد. تراکنش مجموعه ای از دستورها است که یا همه ی آن ها با موفقیت اجرا می شوند یا هیچکدام انجام نمی شوند.

تراکنش دارای چهار ویژگی معروف می باشد:

• Atomicity (اصل یا همه یا هیچ): تراکنش یا کاملا و به صورت یک پکیج اجرا می شود یا هیچ اتفاقی نمی افتد.
• Consistency (اصل یکپارچگی و پایداری): یک تراکنش باید پایگاه داده را از وضعیت پایدار و مشخص به وضعیت سالم، مشخص و پایدار دیگری انتقال دهد.
• Isolation(اصل انزوا): اطمینان حاصل می کند که تراکنش هایی که به طور همزمان اجرا می شوند، بر روی یکدیگر و سلامت دیتابیس اثری نمی گذارد، گویا هر یک در انزوا و به طور جداگانه اجرا می شوند.
• Durability (اصل پایایی و ماندگاری): زمانی که یک تراکنش به صورت نهایی ثبت و به اجرا رسید (commit)، اثرشان ماندگار و پایا خواهد بود، حتی اگر سیستم دچار خرابی ناگهانی شود.

DB API 2.0 و توابع دسترسی و مدیریت دیتابیس دو متد commit یا rollback را به ترتیب جهت ثبت نهایی تراکنش و بازگردانی آن به وضعیت قبل از تراکنش ارائه می دهد.

مثال

از قبل حتما با نحوه ی پیاده سازی تراکنش آشنایی دارید. در زیر مثالی مشابه را می بینید:

# 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 صدا می زند.

 db.commit()

عملیات ROLLBACK و بازگشت به وضعیت قبلی

اگر از تغییرات ثبت شده رضایت کامل ندارید، می توانید دیتابیس را به وضعیت قبل از انجام تراکنش بازگردانید. برای این منظور کافی است متد rollback() را بر روی آبجکت db فراخوانی نمایید.

db.rollback()

قطع اتصال به دیتابیس (متد close())

جهت بستن اتصال به دیتابیس کافی است متد close() را به صورت زیر فراخوانی نمایید:

db.close()

اگر اتصال به دیتابیس با فراخوانی متد close() بسته شود، در آن صورت تمامی تراکنش های انجام نشده به صورت نهایی، توسط DB به وضعیت قبلی بازگردانی می شوند.

مدیریت خطاها

خطاها بر اثر عوامل مختلفی رخ می دهند. برخی از خطاها بر اثر خطا گرامری و اشکال در سینتکس دستور SQL اجرا شده، رخ می دهند. برخی دیگر بر اثر عدم موفقیت در اتصال (connection failure) و برخی هم به دلیل فراخوانی متد fetch بر روی دستوری که قبلا یا کاملا انجام شده و یا لغو گردیده، اتفاق می افتند.

DB API تعدادی خطا اعلان می کند که باید در هر ماژولی تعریف شده باشد. این خطاها (exceptions) در جدول زیر لیست شده اند:

اسکریپت های پایتون اپلیکیشن شما می بایست تمامی این خطاها را مدیریت کند. اما لازم است قبل از بکار بردن هر کدام از این exception ها اطمینان حاصل نمایید که MySQLdb امکان پشتیبانی از آن ها را دارد.