پایتون و MySQL: دستور LIMIT و انواع JOIN

دستور LIMIT

برخی اوقات لازم است که نتایج برگشت داده شده از سمت پایگاه داده را محدود کنیم؛ به طور مثال فقط 10 ردیف آخر از نتایج را برگردانیم؛ در چنین حالتی از دستور LIMIT در MySQL استفاده می کنیم. برای مثال در کد زیر گفته ایم که فقط 5 ردیف اول از نتایج را به ما بده و از آنجایی که کل ردیف های جدول را انتخاب کرده بودیم (علامت *) پنج ردیف اول جدول customers به ما برگردانده خواهد شد:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  passwd="mypassword",
  database="mydatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers LIMIT 5")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

خروجی:

(1, 'John', 'Highway 21')
(2, 'Peter', 'Lowstreet 27')
(3, 'Amy', 'Apple st 652')
(4, 'Hannah', 'Mountain 21')
(5, 'Michael', 'Valley 345')

شاید با خودتان فکر کنید که این دستور کاربرد خاصی ندارد اما ما می توانیم با استفاده از LIMIT در MySQL کارهای جالبی انجام دهیم؛ به طور مثال pagination (صفحه سازی) معمولا با استفاده از دستور LIMIT انجام می شود، یعنی زمانی که تعداد مطالب در یک سایت زیاد است و سایت آن ها را صفحه بندی می کند (مانند صفحه ی نتایج گوگل).

البته محدود کردن نتایج به هر عددی که باشد، می تواند شروع جداگانه ای نیز داشته باشد که به آن OFFSET می گوییم. فرض کنید بخواهیم به برنامه بگوییم که 5 ردیف اول را برگردان اما شمارش را از ردیف سوم شروع کن. در این حالت:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  passwd="mypassword",
  database="mydatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers LIMIT 5 OFFSET 2")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

در اینجا برنامه از ردیف سوم شروع به شمردن می کند و 5 ردیف را برمی گرداند:

(3, 'Amy', 'Apple st 652')
(4, 'Hannah', 'Mountain 21')
(5, 'Michael', 'Valley 345')
(6, 'Sandy', 'Ocean blvd 2')
(7, 'Betty', 'Green Grass 1')

در واقع OFFSET 2 یعنی ردیف اول و دوم را نادیده بگیر.

نکته: یادتان باشد که این جلسه و جلسات قبل فقط و فقط در مورد MySQL است و اینطور نیست که دستور SELECT LIMIT در تمام پایگاه های داده ی SQL پشتیبانی شود! اگر از پایگاه های داده ای مانند SQL Server یا MSAccess استفاده میکنید باید به جای SELECT LIMIT از دستور SELECT TOP استفاده کنید. در واقع تمامی کدهای زیر معادل یک دیگر هستند:

برای SQL Server و MS Access می گوییم:

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

برای Oracle می گوییم:

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

و اگر بخواهیم تمام جلسات قبل را کنار هم بگذاریم، برای MySQL می گوییم:

SELECT expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]]
LIMIT number_rows [ OFFSET offset_value ];

دستور JOIN

دستور JOIN نیز از قابلیت های جالب MySQL است. این دستور به شما اجازه می دهد که بر اساس یک ستون مشترک بین دو جدول مختلف، آن ها را با هم ادغام کنید! فرض کنید دو جدول به نام های users و products داشته باشید.

جدول users:

{ id: 1, name: 'John', fav: 154},
{ id: 2, name: 'Peter', fav: 154},
{ id: 3, name: 'Amy', fav: 155},
{ id: 4, name: 'Hannah', fav:},
{ id: 5, name: 'Michael', fav:}

جدول products:

{ id: 154, name: 'Chocolate Heaven' },
{ id: 155, name: 'Tasty Lemons' },
{ id: 156, name: 'Vanilla Dreams' }

ما می توانیم این دو جدول را با استفاده از ستون fav (مخفف favorite - به معنی «مورد علاقه») در users و ستون id در products به هم متصل کنیم. به طور مثال اگر بخواهیم نام کاربر و محصول مورد علاقه ی او را نمایش دهیم می گوییم:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  passwd="mypassword",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  INNER JOIN products ON users.fav = products.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

خروجی:

('John', 'Chocolate Heaven')
('Peter', 'Chocolate Heaven')
('Amy', 'Tasty Lemon')

در حالت عادی باید دو درخواست جداگانه به پایگاه داده ارسال میکردیم تا یک بار نام فرد و بار دیگر id محصول مورد علاقه اش را پیدا می کردیم اما در کد بالا و با استفاده از JOIN هر دو جدول را به هم متصل کردیم و با یک درخواست نتیجه را برگرداندیم. همچنین به جای INNER JOIN می توانید از JOIN خالی استفاده کنید، نتیجه یکسان خواهد بود.

احتمالا از خودتان می پرسید INNER JOIN چیست؟ همانطور که قبلا گفتم تمرکز این دوره روی زبان SQL نیست و شما باید با زبان SQL آشنا باشید تا بتوانید با هر زبان برنامه نویسی مانند پایتون با پایگاه داده ارتباط برقرار کنید اما به صورت خلاصه برایتان توضیح میدهم که JOIN چهار حالت مختلف دارد:

• INNER) JOIN): ردیف هایی را برمی گرداند که دارای مقادیر تطبیق یافته با کوئری در هر دو جدول هستند.
• LEFT (OUTER) JOIN: تمام ردیف ها را از جدول سمت چپ و مقادیر تطبیق یافته را از جدول سمت راست برمی گرداند.
• RIGHT (OUTER) JOIN: تمام ردیف ها را از جدول سمت راست و مقادیر تطبیق یافته را از جدول سمت چپ برمی گرداند.
• FULL (OUTER) JOIN: اگر در سمت چپ یا راست ردیف ها و مقادیر تطبیق یافته ای باشد، همه را بر می گرداند.

برای آشنایی بیشتر به قسمت «بررسی انواع دستورات JOIN» از دوره ی آموزش SQL روکسو مراجعه کنید. بنابراین JOIN و INNER JOIN یکی هستند. دو مورد LEFT JOIN و RIGHT JOIN را هم به طور خلاصه بررسی می کنیم.

LEFT JOIN

در مثال قبلی (کد بالا) Hannah و Michael در نتایج حضور نداشتند چرا که INNER JOIN تنها ردیف هایی را نشان می دهد که در دو جدول تطبیق و معادل داشته باشند. اگر بخواهید تمام کاربران را نمایش بدهید (حتی اگر محصول مورد علاقه ای ندارند) می توانید از LEFT JOIN استفاده کنید:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  passwd="mypassword",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  LEFT JOIN products ON users.fav = products.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

خروجی:

('John', 'Chocolate Heaven')
('Peter', 'Chocolate Heaven')
('Amy', 'Tasty Lemon')
('Hannah', None)
('Michael', None)

RIGHT JOIN

اگر می خواهید تمام محصولات را برگردانید و کاربرانی که آن محصول را به عنوان محصول مورد علاقه ی خود دارند، نمایش دهید از RIGHT JOIN استفاده می کنیم:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  passwd="mypassword",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  RIGHT JOIN products ON users.fav = products.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

خروجی:

('John', 'Chocolate Heaven')
('Peter', 'Chocolate Heaven')
('Amy', 'Tasty Lemon')
(None, 'Vanilla Dreams')

توجه داشته باشید که حتی اگر محصولی توسط هیچ کاربری به عنوان محصول مورد علاقه انتخاب نشده باشد، باز هم در این لیست نمایش داده می شود (مانند Vanilla Dreams که مقدار None را دارد، یعنی هیچ کاربری آن را مورد علاقه ی خودش انتخاب نکرده است) اما کاربرانی مانند Hannah و Michael که هیچ محصول مورد علاقه ای ندارند در هیچ قسمتی از نتایج حضور ندارند و نادیده گرفته شده اند.

بهتر است برای روان تر شدن در این بحث به قسمت «بررسی انواع دستورات JOIN» سری بزنید.