آموزش پایتون - ارتباط با دیتابیس

استاندارد Python برای رابط‌های پایگاه داده، Python DB-API است. اکثر رابط‌های پایگاه داده Python به این استاندارد پایبند هستند.

شما می‌توانید پایگاه داده مناسب را برای برنامه خود انتخاب کنید. Python Database API از طیف گسترده‌ای از سرورهای پایگاه داده پشتیبانی می‌کند، از جمله:

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

در اینجا لیستی از رابط‌های پایگاه داده Python موجود است: Python Database Interfaces and APIs. شما باید برای هر پایگاه داده ای که نیاز دارید به آن دسترسی پیدا کنید، یک ماژول DB API جداگانه دانلود کنید. به عنوان مثال، اگر نیاز دارید به یک پایگاه داده Oracle و همچنین یک پایگاه داده MySQL دسترسی پیدا کنید، باید هر دو ماژول Oracle و MySQL را دانلود کنید.

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

• وارد کردن ماژول API
• دریافت یک اتصال با پایگاه داده
• ارسال دستورات SQL و stored procedures
• بستن اتصال

ما همه مفاهیم را با استفاده از 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

توجه: مطمئن شوید که برای نصب ماژول فوق، حق دسترسی root را دارید.

اتصال به پایگاه داده

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

• یک پایگاه داده TESTDB ایجاد کرده اید.
• یک جدول EMPLOYEE در TESTDB ایجاد کرده اید.
• این جدول دارای فیلدهای FIRST_NAME، LAST_NAME، AGE، SEX و INCOME است.
• شناسه کاربر "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()

در حالی که این اسکریپت را اجرا می کنم، نتیجه زیر را در دستگاه لینوکس من تولید می کند:

Database version : 5.0.45

اگر اتصال با منبع داده برقرار شد، یک شیء Connection بازگردانده می شود و در db برای استفاده بعدی ذخیره می شود، در غیر این صورت db به None تنظیم می شود. سپس، شیء db برای ایجاد یک شیء cursor استفاده می شود، که به نوبه خود برای اجرای پرس‌وجوهای SQL استفاده می شود. سرانجام، قبل از خروج، اطمینان حاصل می‌کند که اتصال پایگاه داده بسته شده و منابع آزاد شده‌اند.

ایجاد جدول پایگاه داده

هنگامی که یک اتصال پایگاه داده برقرار شد، ما آماده هستیم تا جداول یا رکوردها را در جداول پایگاه داده با استفاده از روش 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

وقتی می‌خواهید رکوردهای خود را در یک جدول پایگاه داده ایجاد کنید، لازم است.

مثال

مثال زیر، دستور 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))
..................................

عمل خواندن

عمل خواندن در هر پایگاه داده به معنای بازیابی برخی اطلاعات مفید از پایگاه داده است.

پس از برقراری اتصال پایگاه داده ما، شما آماده انجام یک پرس و جو در این پایگاه داده هستید. شما می توانید از روش fetchone() برای بازیابی یک رکورد واحد یا روش fetchall() برای بازیابی چندین مقدار از یک جدول پایگاه داده استفاده کنید.

• fetchone() − این روش، ردیف بعدی یک مجموعه نتیجه پرس و جو را بازیابی می کند. یک مجموعه نتیجه، یک شیء است که زمانی که یک شیء cursor برای پرس و جو یک جدول استفاده می شود، بازگردانده می شود.

• fetchall() − این روش، همه ردیف ها را در یک مجموعه نتیجه بازیابی می کند. اگر برخی از ردیف ها قبلاً از مجموعه نتیجه استخراج شده باشند، سپس ردیف های باقی مانده از مجموعه نتیجه بازیابی می شوند.

• rowcount − این یک عنصر read-only است و تعداد ردیف هایی را که توسط یک روش execute() affected شده اند، برمی گرداند.

مثال

پروتکل زیر همه رکوردهای جدول EMPLOYEE را که حقوق آنها بیش از 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()

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 در هر پایگاه داده به معنای به روزرسانی یک یا چند رکورد است که قبلاً در پایگاه داده موجود است.

پروتکل زیر همه رکوردهایی را که 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()

انجام تراکنش ها

تراکنش ها یک مکانیسم هستند که اطمینان از یکپارچگی داده ها را تضمین می کنند. تراکنش ها دارای چهار ویژگی زیر هستند:

• اتمی بودن − یک تراکنش یا کامل می شود یا اصلاً اتفاقی نمی افتد.

• همبستگی − یک تراکنش باید در یک حالت یکنواخت شروع شود و سیستم را در یک حالت یکنواخت ترک کند.

• جدایی − نتایج میانی یک تراکنش برای خارج از تراکنش فعلی قابل مشاهده نیست.

• دوام − پس از انجام یک تراکنش، اثرات آن پایدار هستند، حتی پس از خرابی سیستم.

API پایگاه داده Python 2.0 دو روش برای انجام یا ردیابی یک تراکنش فراهم می کند.

مثال

شما already می دانید که چگونه تراکنش ها را پیاده سازی کنید. در اینجا یک مثال مشابه است:

# 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.rollbac

db.close()

k()

قطع اتصال پایگاه داده

برای قطع اتصال پایگاه داده، از روش close() استفاده کنید.

اگر اتصال به پایگاه داده توسط کاربر با روش close() بسته شود، هرگونه تراکنش outstanding توسط DB رول‌بک می‌شود. با این حال، به جای وابستگی به جزئیات پیاده‌سازی سطح پایین DB، بهتر است که به‌طور صریح commit یا rollback را فراخوانی کنید.

مدیریت خطاها

منابع خطا بسیار زیادی وجود دارد. چند نمونه عبارتند از: یک خطای نحوی در یک عبارت SQL اجرا شده، یک خرابی اتصال، یا calling the fetch method for an already canceled or finished statement handle.

DB API تعدادی از خطاهایی را که باید در هر ماژول پایگاه داده وجود داشته باشد، تعریف می‌کند. جدول زیر این استثنائات را فهرست می‌کند.

اسکریپت‌های Python شما باید این خطاها را مدیریت کنند، اما قبل از استفاده از هر یک از استثنائات فوق، مطمئن شوید که MySQLdb شما از آن استثنا پشتیبانی می‌کند. می‌توانید با خواندن مشخصات DB API 2.0 اطلاعات بیشتری در مورد آنها کسب کنید.