پایتون و MySQL: دستور WHERE و ORDER BY

دستور WHERE

در قسمت قبل با دو دستور INSERT INTO و SELECT آشنا شدیم که یکی برای ثبت و وارد کردن اطلاعات به جدول و دیگری برای دریافت اطلاعات از آن بود. دستور WHERE در زبان SQL به ما کمک می کند تا دستورات SELECT خود را کامل تر کنیم (مانند یک فیلتر برای محدود کردن دستور). به طور مثال اگر بخواهیم فقط کاربرانی را انتخاب کنیم که در آدرس Park Lane 38 زندگی می کنند باید بگوییم:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "SELECT * FROM customers WHERE address = 'Park Lane 38'"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

خروجی:

(11, 'Ben', 'Park Lane 38')

بنابراین ما فقط یک مشتری داریم که در 'Park Lane 38' زندگی می کند.

نکته: استفاده از متد fetchone در اینجا تغییری ایجاد نمی کند چرا که فقط یک مشتری با آدرس مورد نظر وجود داشت اما اگر چندین مشتری با این آدرس داشته باشیم و از fetchone استفاده کنیم، فقط یکی از آن ها را دریافت خواهیم کرد. بنابراین استفاده از متد درست در زبان پایتون به اندازه ی خود کوئری های SQL مهم است و باید همیشه تعادلی بین این دو ایجاد کرد.

استفاده از کاراکترهای wildcard

شما می توانید ردیف هایی را انتخاب کنید که با حرف یا عبارت خاصی شروع شده یا تمام شوند و یا فقط شامل آن باشند. برای این کار می توانیم از کاراکتر  % استفاده کنیم که یک کاراکتر wildcard است. بگذارید با یک مثال توضیح بدهم؛ فرض کنید می خواهیم مشتریانی را انتخاب کنیم که در آدرس آن ها کلمه ی way وجود داشته باشد (حالا هر جای آدرس که میخواهد باشد؛ چه اول آن، چه وسط و چه در آخر آن). برای دریافت این اطلاعات می توان گفت:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "SELECT * FROM customers WHERE address Like '%way%'"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

خروجی:

(1, 'John', 'Highway 21')
(9, 'Susan', 'One way 98')
(14, 'Viola', 'Sideway 1633')

جلوگیری از SQL Injection

ما قبلا در سایت روکسو بارها در مورد حملات SQL Injection صحبت کرده ایم. SQL Injection یا تزریق SQL زمانی رخ می دهد که مقادیر وارد شده در پایگاه داده از سمت کاربر به سمت ما ارسال شود؛ در این حالت کاربر می تواند به جای اطلاعات صحیح، کدهای مخربی را به سمت ما ارسال کند و اگر ما کدهای برنامه را به درستی ننوشته باشیم همان کدهای مخرب را مستقیما وارد پایگاه داده می کنیم که خود باعث انواع مشکلات و ریسک های امنیتی خواهد شد (مطالعه ی بیشتر در این لینک).

خوشبختانه ماژول mysql.connector در پایتون متدی برای پاک سازی (escape دادن) تمام داده های کاربران دارد. ما می توانیم مقادیر کاربران را با استفاده از s% پاکسازی کنیم:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "SELECT * FROM customers WHERE address = %s"
adr = ("Yellow Garden 2", )

mycursor.execute(sql, adr)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

خروجی:

(10, 'Vicky', 'Yellow Garden 2')

در مثال بالا ممکن بود کاربر به جای آدرس خود، کدهای مخربی وارد کند اما اگر از s% استفاده کنیم این کدهای مخرب خنثی خواهند شد.

دستور ORDER BY

همانطور که از نام این دستور مشخص است، کار ORDER BY مرتب کردن نتایج به صورت صعودی یا نزولی است. حالت پیش فرض ORDER BY مرتب سازی صعودی است و اگر میخواهید داده ها به صورت نزولی مرتب شوند حتما باید کلیدواژه ی DESC را ذکر کنید. به طور مثال در کد زیر می خواهیم نام مشتریان خود را بر اساس حروف الفبا مرتب کنیم:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "SELECT * FROM customers ORDER BY name"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

خروجی:

(3, 'Amy', 'Apple st 652')
(11, 'Ben', 'Park Lane 38')
(7, 'Betty', 'Green Grass 1')
(13, 'Chuck', 'Main Road 989')
(4, 'Hannah', 'Mountain 21')
(1, 'John', 'Highway 21')
(5, 'Michael', 'Valley 345')
(15, 'Michelle', 'Blue Village') (2, 'Peter', 'Lowstreet 27')
(8, 'Richard', 'Sky st 331')
(6, 'Sandy', 'Ocean blvd 2')
(9, 'Susan', 'One way 98')
(10, 'Vicky', 'Yellow Garden 2')
(14, 'Viola', 'Sideway 1633')
(12, 'William', 'Central st 954')

همانطور که می بینید حالت پیش فرض صعودی است یعنی از اولین حرف انگلیسی a به آخرین z مرتب کرده است (نتایج از صفر به 100 صعود کرده است).

حالا اگر بخواهیم نتایج را به صورت نزولی مرتب کنیم باید کلیدواژه ی DESC را به آن اضافه کنیم:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "SELECT * FROM customers ORDER BY name DESC"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

خروجی:

(12, 'William', 'Central st 954') (14, 'Viola', 'Sideway 1633')
(10, 'Vicky', 'Yellow Garden 2')
(9, 'Susan', 'One way 98')
(6, 'Sandy', 'Ocean blvd 2')
(8, 'Richard', 'Sky st 331')
(2, 'Peter', 'Lowstreet 27')
(15, 'Michelle', 'Blue Village') (5, 'Michael', 'Valley 345')
(1, 'John', 'Highway 21')
(4, 'Hannah', 'Mountain 21')
(13, 'Chuck', 'Main Road 989')
(7, 'Betty', 'Green Grass 1')
(11, 'Ben', 'Park Lane 38')
(3, 'Amy', 'Apple st 652')

حالا نام کاربران به صورت برعکس مرتب شده است. امیدوارم با کاربرد این دو دستور به طور کامل آشنا شده باشید. برای مطالعه ی بیشتر می توانید به دوره ی زبان SQL ما سری بزنید.