دستورات mySQL

موضوع این صفحه استفاده از پایگاه داده MySQL در زبان برنامه نویسی پایتون است و مبانی برنامه نویسی MySQL در پایتون را پوشش می دهد. در این مقاله از ماژول MySQLdb استفاده خواهیم کرد. مثالها در لینوکس توزیع اوبونتو ایجاد و تست شده اند.

درباره پایگاه داده MySQL

MySQL از پیشروان سیستمهای مدیریت پایگاه داده اپن سورس می باشد. MySQL یک سیستم مدیریت پایگاه داده چند کاربره و چند رشته ای می باشد و بصورت خاص در وب محبوب می باشد. این پایگاه داده یکی از اجزای هر پلتفرم LAMP متشکل از لینوکس، آپاچی، مای سیکوئل و پی اچ پی می باشد. در حال حاضر کمپانی Oracle صاحب این محصول می باشد. این پایگاه داده روی اکثر سیستمهای عامل در دسترس می باشد و روی یونیکس BSD، لینوکس، ویندوز و مک اجرا می شود. ویکی پدیا و یوتوب از MySQL استفاده میکنند. این سایتها میلیون ها کوئری را در هر روز مدیریت می کنند. مای سی کوئل در دو نسخه عرضه می شود: MySQL server و MySQL embeded.

قبل از شروع

برای اجرای مثالهای این مقاله نیاز به نصب چندین پکیج داریم.

اگر MySQL روی سیستمتان نصب نیست، باید ابتدا آن را نصب کنیم:

 $ sudo apt-get install mysql-server

این دستور MySQL به همراه چند پکیج دیگر را نصب می کند. در حین نصب از ما خواسته می شود تا پسوردی را برای حساب کاربری root وارد کنیم.

برای اینکه نام پکیج شامل MySQLdb را پیدا کنیم از دستور apt-cache استفاده می کنیم:

$ apt-cache search MySQLdb
python-mysqldb - A Python interface to MySQL
python-mysqldb-dbg - A Python interface to MySQL (debug extension)
bibus - bibliographic database
eikazo - graphical frontend for SANE designed for mass-scanning

بعد از پیدا کردن نام پکیج آن را نصب می کنیم.

$ sudo apt-get install python-mysqldb

سپس یک کاربر جدید برای دیتابیس و همچنین یک دیتابیس جدید ایجاد میکنیم. برای این کار از کلاینت musql استفاده می کنیم.

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.0.67-0ubuntu6 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
+--------------------+
۲ rows in set (0.00 sec)

با استفاده از کاربر root به دیتابیس متصل می شویم. می توانیم تمام دیتابیس های موجود را با دستور SHOW DATABASES نمایش دهیم.

mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.02 sec)

یک دیتابیس جدید با نام testdb ایجاد می کنیم. از این دیتابیس در طول این آموزش استفاده خواهیم کرد.

mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'test623';
Query OK, 0 rows affected (0.00 sec)

mysql> USE testdb;
Database changed

mysql> GRANT ALL ON testdb.* TO 'testuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> quit;
Bye

یک کاربر دیتابیس جدید ایجاد میکنیم و تمام مجوزهای لازم روی تمام جداول دیتابیس testdb را به آن می دهیم.

ماژول _mysql

ماژول _mysql، در واقع بصورت مستقیم MySQL C API را پیاده سازی میکند. این ماژول با Python DB API سازگار نیست.  عموما برنامه نویسان پایتون ماژول شی گرای MySQLdb را ترجیح می دهند. در مورد ماژول دوم بعدا صحبت خواهیم کرد. فعلا مثال کوچکی در مورد _mysql میزنیم.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import _mysql
import sys

try:
    con = _mysql.connect('localhost', 'testuser', 'test623', 'testdb')
        
    con.query("SELECT VERSION()")
    result = con.use_result()
    
    print "MySQL version: %s" % \
        result.fetch_row()[0]
    
except _mysql.Error, e:
  
    print "Error %d: %s" % (e.args[0], e.args[1])
    sys.exit(1)

finally:
    
    if con:
        con.close()

مثال بالا ورژن پایگاه داده MySQL را می گیرد و نمایش می دهد. برای این کار از دستور SELECT VERSION() در SQL استفاده کرده ایم.

ماژول MySQLdb

MySQLdb یک لایه پوشاننده حول _mysql است. این ماژول با Python DB API سازگار است و بنابراین کد آن را قابل حمل تر می کند. استفاده از این ماژول برای کار با پایگاه داده MySQL ترجیح داده می شود.

اولین مثال

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys

try:
    con = mdb.connect('localhost', 'testuser', 'test623', 'testdb');

    cur = con.cursor()
    cur.execute("SELECT VERSION()")

    ver = cur.fetchone()
    
    print "Database version : %s " % ver
    
except mdb.Error, e:
  
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
    
finally:    
        
    if con:    
        con.close()

در این مثال به دیتابیس testdb متصل شدیم و دستور SELECT VERSION() را اجرا کردیم. این دستور ورژن پایگاه داده را بر میگرداند سپس آن را در کنسول پرینت می کنیم.

import MySQLdb as mdb

ابتدا ماژول MySQLdb را وارد می کنیم.

con = mdb.connect('localhost', 'testuser', 
    'test623', 'testdb');

به دیتابیس متصل می شویم. متد connect() چهار پارامتر دارد. پارامتر اول host، جایی است  که پایگاه داده در آن واقع  شده است. در مثال ما این هاست localhost می باشد. پارامتر دوم نام کاربر پایگاه داده است. پارامتر بعدی پسورد این کاربری می باشد. و پارامتر آخر نام دیتابیس می باشد.

cur = con.cursor()
cur.execute("SELECT VERSION()")

کانکشن به ما یک شی cursor بر می گرداند. از cursor برای برگرداندن رکوردها از مجموعه نتایج استفاده میشود. متد execute() از شی cursor را برای اجرا کردن دستورات SQL فراخوانی میکنیم.

ver = cur.fetchone()

از آنجا که فقط به یک رکورد نیاز داریم از متد fetchone() استفاده می کنیم.

print "Database version : %s " % ver

داده ای را که به دست آوردیم در کنسول چاپ می کنیم.

except mdb.Error, e:
  
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)

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

finally:    
        
    if con:    
        con.close()

و در نهایت منابع را آزاد می کنیم.

خروجی ما چیزی شبیه زیر خواهد بود.

$ ./version.py
Database version : 5.5.9 

ایجاد و پرکردن یک جدول

یک جدول ایجاد میکنیم و آن را با مقداری داده پر میکنیم.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb');

with con:
    
    cur = con.cursor()
    cur.execute("DROP TABLE IF EXISTS Writers")
    cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \
                 Name VARCHAR(25))")
    cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')")

یک جدول با نام writers ایجاد میکنیم و پنج نویسنده به آن اضافه میکنیم.

with con:

با استفاده از کلمه کلیدی with، مفسر پایتون بصورت خودکار منابع را آزاد میکند و همچنین مدیریت خطا را انجام میدهد.

cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \
                Name VARCHAR(25))")

عبارت SQL یک جدول جدید با نام writers ایجاد میکند. این جدول دو ستون دارد Id و Name.

cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
...

از دستور INSERT برای وارد کردن نویسندگان به جدول استفاده می کنیم. که در بالا دوتا اضافه کردیم.

mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name              |
+----+-------------------+
|  ۱ | Jack London       |
|  ۲ | Honore de Balzac  |
|  ۳ | Lion Feuchtwanger |
|  ۴ | Emile Zola        |
|  ۵ | Truman Capote     |
+----+-------------------+
۵ rows in set (0.00 sec)

بعد از اجرای اسکریپت از کلاینت mysql برای انتخاب داده ها از جدول writers استفاده کردیم.

بر گرداندن داده ها

حالا که مقداری داده وارد دیتابیس کردیم، میخواهیم آنها را واخواهی کنیم.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb');

with con: 

    cur = con.cursor()
    cur.execute("SELECT * FROM Writers")

    rows = cur.fetchall()

    for row in rows:
        print row

در این مثال تمام داده ها را از جدول writers میخوانیم.

cur.execute("SELECT * FROM Writers")

این عبارت SQL تمام داده ها را از جدول writers انتخاب می کند.

rows = cur.fetchall()

متد fetchall() تمام رکوردها می گیرد. این متد یک مجموعه از نتایج را بر میگرداند. از نظر تکنیکی مجموعه نتایج یک چندتایی (tuple) از چندتایی هاست. هر tuple داخلی از مجموعه tuple ها یک رکورد از جدول را ارائه میدهد.

for row in rows:
    print row

سطر به سطر داده ها را در کنسول چاپ میکنیم.

$ ./retrieve.py
(۱L, 'Jack London')
(۲L, 'Honore de Balzac')
(۳L, 'Lion Feuchtwanger')
(۴L, 'Emile Zola')
(۵L, 'Truman Capote')

و از مثال خروجی می گیریم.

برگرداندن تمام داده ها در یک بار ممکن است بصرفه نباشد. می توانیم داده ها را سطر به سطر برگردانیم.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb');

with con:

    cur = con.cursor()
    cur.execute("SELECT * FROM Writers")

    for i in range(cur.rowcount):
        
        row = cur.fetchone()
        print row[0], row[1]

دوباره داده های جدول writers را در کنسول چاپ می کنیم اما این بار سطر به سطر.

for i in range(cur.rowcount):
    
    row = cur.fetchone()
    print row[0], row[1]

برگرداندن سطر به سطر داده ها با متد fetchone() انجام میشود. صفت rowcount تعداد رکوردهای برگردانده شده توسط عبارت SQL را به ما میدهد.

$ ./retrieve2.py
۱ Jack London
۲ Honore de Balzac
۳ Lion Feuchtwanger
۴ Emile Zola
۵ Truman Capote

و در نهایت خروجی مثال.

dictonary cursor

در ماژول MySQLdb چندین نوع cursor وجود دارد. کورسر پیشفرض نوع داده tuple متشکل از چندین tuple را بر میگرداند. وقتی از cursor نوع دیکشنری استفاده میکنیم، داده ها بصورت دیکشنری های پایتون برگردانده میشوند. با این روش میتوانیم به داده ها با استفاده از نام ستونها رجوع کنیم.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')

with con:

    cur = con.cursor(mdb.cursors.DictCursor)
    cur.execute("SELECT * FROM Writers LIMIT 4")

    rows = cur.fetchall()

    for row in rows:
        print row["Id"], row["Name"]

در این مثال با استفاده از کورسر نوع دیکشنری چهار سطر اول جدول writers را بر میگردانیم.

cur = con.cursor(mdb.cursors.DictCursor)

ازکورسر نوع دیکشنری DictCursor استفاده می کنیم.

cur.execute("SELECT * FROM Writers LIMIT 4")

چهار سطر از جدول writers را برمیگردانیم.

for row in rows:
    print row["Id"], row["Name"]

به داده ها با نام ستونهای جدول writers ارجاع می کنیم.

$ ./dictcur.py
۱ Jack London
۲ Honore de Balzac
۳ Lion Feuchtwanger
۴ Emile Zola

و در نهایت خروجی.

عناوین ستونها

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

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')

with con:

    cur = con.cursor()
    cur.execute("SELECT * FROM Writers LIMIT 5")

    rows = cur.fetchall()

    desc = cur.description

    print "%s %3s" % (desc[0][0], desc[1][0])

    for row in rows:    
        print "%2s %3s" % row

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

desc = cur.description

صفت description از کورسر اطلاعاتی درباره هر یک از ستونهای نتیجه یک کوئری را به ما میدهد.

print "%s %3s" % (desc[0][0], desc[1][0])

می توانیم نامهای ستونهای جدول را فرمت و پرینت کنیم.

for row in rows:    
    print "%2s %3s" % row

و در اینجا داده ها برگردانده و پرینت می کنیم.

$ ./columnheaders.py
Id Name
 ۱ Jack London
 ۲ Honore de Balzac
 ۳ Lion Feuchtwanger
 ۴ Emile Zola
 ۵ Truman Capote

و در نهایت خروجی برنامه.

عبارتهای آماده

حال بر استفاده از عبارتهای آماده متمرکز می شویم. وقتی از عبارتهای آماده استفاده می کنیم از مکان نگهدارها بجای نوشتن مستقیم مقادیر در عبارتها استفاده میکنیم. عبارتهای آماده امنیت و بازدهی را افزایش می دهند. مشخصات Python DB API، پنج روش برای ساختن عبارتهای آماده استفاده میکند. ماژول MySQLdb از یکی از آنها، کدهای فرمت ANSI printf استفاده می کند.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')
    
with con:    

    cur = con.cursor()
        
    cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
        ("Guy de Maupasant", "4"))        
    
    print "Number of rows updated:",  cur.rowcount

نام یک نویسنده را در خط چهارم تغییر میدهیم.

cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
    ("Guy de Maupasant", "4"))  

ما از دو مکان نگهدار که با علامت % مشخص شده اند استفاده کردیم. قبل از اینکه عبارت SQL اجرا شود مقادیر به مکان نگهدارها اختصاص داده میشوند.

$ ./prepared.py
Number of rows updated: 1

یک ردیف را آپدیت کرده ایم.

mysql> SELECT Name FROM Writers WHERE Id=4;
+------------------+
| Name             |
+------------------+
| Guy de Maupasant |
+------------------+
۱ row in set (0.00 sec)

نویسنده در خط چهارم با موفقیت عوض شده است.

وارد کردن تصاویر

افراد معمولا علاقمند به وارد کردن تصاویر در دیتابیس هستند. نحوه انجام این کار را به شما نشان خواهیم داد. تصاویر داده های باینری هستند و MySQL یک نوع داده خاص برای داده های باینری بنام BLOB دارد. TINYBLOB، BLOB، MEDIUMBLOB و LONGBLOB نسخه های دیگر این نوع داده هستند.

mysql> CREATE TABLE Images(Id INT PRIMARY KEY, Data MEDIUMBLOB);
Query OK, 0 rows affected (0.08 sec)

برای این مثال یک جدول جدید با نام images ایجاد میکنیم.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb


def read_image():
    
    fin = open("woman.jpg")    
    img = fin.read()
    
    return img
    

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')
 
with con:
    
    cur = con.cursor()
    data = read_image()
    cur.execute("INSERT INTO Images VALUES(1, %s)", (data, ))

در اسکریپت بالا یک تصویر JPG را از دیسک خوانده و در جدول images وارد میکنیم.

def read_image():
    
    fin = open("woman.jpg")    
    img = fin.read()
    
    return img

متد read_image() اطلاعات باینری را فایل JPG که در دایرکتوری جاری قرار دارد میخواند.

cur.execute("INSERT INTO Images VALUES(1, %s)", (data, ))

و در نهایت داده های تصویر را در جدول images وارد میکنیم.

خواندن تصاویر

در مثال قبلی، یک تصویر را در جدول دیتابیس وارد کردیم. حال میخواهیم که تصویر را از جدول دوباره خوانی کنیم.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb 

def writeImage(data):
    
    fout = open('woman2.jpg', 'wb')
    
    with fout:
        
        fout.write(data)

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')

with con:

    cur = con.cursor()

    cur.execute("SELECT Data FROM Images WHERE Id=1")
    data = cur.fetchone()[0]
    writeImage(data)   

یک تصویر را از جدول images میخوانیم.

cur.execute("SELECT Data FROM Images WHERE Id=1")

یک رکورد را از جدول انتخاب میکنیم.

fout = open('woman2.jpg', 'wb')

داده ها را روی دیسک می نویسیم.

حال باید یک تصویر با نام woman2.jpg در دایرکتوری جاری داشته باشیم. میتوانید تصویر را چک کنید تا ببینید همان تصویری است که در دیتابیس وارد کرده اید.

پشتیبانی از ترانس اکشن (Transaction)

یک transaction یک واحد اتمیک (تکی و غیرقابل تجزیه) از اعمال دیتابیس روی داده ها در یک یا چند دیتابیس است. تاثیر تمام عبارتهای SQL در یک ترانس اکشن یا باید همگی روی دیتابیس اعمال شوند و یا همگی برگشت بخورند.

برای دیتابیس هایی که از ترانس اکشن ها پشتیبانی میکنند، پایتون بصورت پنهانی وقتی که شی cursor ایجاد شد یک ترانس اکشن را شروع میکند. متد commit() آپدیتهایی را که cursor انجام میدهد اعمال میکند و متد rollback() آنها را از بین می برد. هر متد یک ترانس اکشن جدید ایجاد میکند.

پایگاه داده MySQL از موتورهای ذخیره سازی متفاوتی بهره میبرد. رایجترین آنها MyISAM و InnoDB هستند. از ورژن ۵٫۵ به بعد، InnoDB موتور پیشفرض ذخیره سازی است. همیشه یک هزینه-فایده در انتخاب بین سرعت و امنیت یک دیتابیس وجود دارد. جداول MyISAM برای پردازش سریعتر هستند ولی از transaction ها پشتیبانی نمی کنند و بنابراین متدهای commit() و rollback() روی آن کار نمی کنند. در سمت دیگر، جداول InnoDB در مقابل از دست رفتن داده ها مطمئن تر هستند و از transaction ها پشتیبانی میکنند ولی در پردازش کندتر هستند.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys


try:
    con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')

    cur = con.cursor()
    cur.execute("DROP TABLE IF EXISTS Writers")
    cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \
                 Name VARCHAR(25)) ENGINE=INNODB")
    cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Terry Pratchett')")
    
    con.commit()

    
except mdb.Error, e:
  
    if con:
        con.rollback()
        
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
    
finally:    
            
    if con:    
        con.close()

دوباره جدول writers را ایجاد میکنیم و بصورت صریح از transaction ها استفاده میکنیم.

cur = con.cursor()

در DB API پایتون از دستور BEGIN برای شروع یک transaction استفاده نمی کنیم. یک ترانس اکشن در هنگام ایجاد کورسور شروع میشود.

cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \
                Name VARCHAR(25)) ENGINE=INNODB")

از نوع جداول InnoDB استفاده خواهیم  کرد. برای نسخه های قدیمی MySQL (کوچکتر از ۵٫۵)، باید نوع موتور ذخیره سازی را با عبارت ENGINGE=INNODB مشخص کنیم.

con.commit()

باید یک transaction را با یک commit() و یا یک rollback() خاتمه دهیم. اگر این خط را comment کنیم جدول ایجاد میشود اما هیچ داده ای در جدول نوشته نمی شود.

در این مثال، ما با transaction بدون مشخص کردن صریح آنها کار کردیم. در واقع ما از مدیریت کننده context استفاده کردیم. مدیریت کننده context ورود و خروج به یک context از کد در حال اجرا را مدیریت میکند. مدیریت کنندگان context غالبا با استفاده از دستور with فراخوانی میشوند.

اشیا connection در MySQLdb می توانند به عنوان مدیریت کننده context استفاده شوند. آنها بصورت اتوماتیک ترانس اکشن ها را کامیت یا رول بک میکنند.  مدیریت کننده های context در connection کد را با فاکتور گرفتن از عبارات try، except و finally تمیزتر میکند.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')

with con:
    
    cur = con.cursor()
    cur.execute("DROP TABLE IF EXISTS Writers")
    cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \
                 Name VARCHAR(25))")
    cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Terry Pratchett')")

در این مثال مدیریت کننده context تمام وظیفه مدیریت خطا را به عهده میگیرد و بصورت اتوماتیک عمل commit و یا rollback یک transaction را انجام میدهد.

منبع : MySQL Python tutorial