آموزش جنگو : جلسه چهل و چهار | بررسی Raw SQL در جنگو

در این جلسه در رابطه با Raw SQL در جنگو صحبت خواهیم کرد . با ما همراه باشید .

مفهوم Raw SQL

همانطور که قبلا گفتیم جنگو می تواند کد های SQL خام را نیز اجرا کند . یعنی کد های SQL را خودتان به صورت دستی بنویسید و جنگو فقط برای شما آنها را اجرا کند . اینکار به کمک یکی از متد ها با نام raw انجام می شد . پس از اجرای SQL ,اگر یک کوئری را در آن نوشته باشید نتایج آن را دریافت خواهید کرد . یک راه دیگر برای اجرای کد های خام SQL ,نوشتن آنها در ترمینال دیتابیس است که کاملا جدا از جنگو عمل خواهد کرد .

نکته : تا حد ممکن توصیه می شود که از اجرای کد های خام جلوگیری کنید . قبل از هر استفاده ,بررسی کنید که آیا خود متد های جنگو می توانند آن کار را برای شما انجام دهند یا خیر . در صورتی که این را نمی دانید ,می توانید از کانال های پشتیبانی جنگو که در خود سایت رسمی آن وجود دارند , سوال کنید .

نکته : در استفاده از raw به شدت مراقب باشید . بررسی نکردن نحوه کارکرد کد شما و پارامتر های ارسالی به کد SQLشما ,باعث ایجاد باگ SQL injection می شود .

اجرای دستورات SQL خام

متد Manager.raw را می توان از manager مربوط به مدل فراخوانی کرد . در این بخش یاد می گیریم که چطور یک Query را با استفاده از raw بنویسیم . سینتکس کلی آن به شکل زیر است :

Manager.raw(raw_query, params=, translations=None)

این متد یک Query را به شکل کد SQL دریافت می کند ,آن را اجرا می کند و نتایج آن را به صورت شی ای از کلاس django.db.models.query.RawQuerySet بازمیگرداند . شی کلاس RawQuerySet ,می تواند مانند یک QuerySet معمولی در جنگو ,پیمایش شود و اشیایی را بازگرداند .

بیایید با یک مثال آن را بررسی کنیم . فرض کنید مدل زیر را دارید :

class Person(models.Model):
    first_name = models.CharField(...)
    last_name = models.CharField(...)
    birth_date = models.DateField(...)

می توانید کد SQL خود را مانند زیر اجرا کنید :

>>> for p in Person.objects.raw(&quotSELECT * FROM myapp_person&quot):
...          print(p)
...
John Smith
Jane Jones

مثال بالا مانند این است که کد Person.objects.all را فراخوانی کنیم . آنچنان هیجان انگیز نیست ,مگرنه؟ گرچه در ادامه با پارامتر و آرگومان هایی در این متد آشنا می شویم که بسیار شگفت انگیز هستند !

نکته : شاید برای شما سوال باشد که نام جدول درون کد SQL از کجا آمده است ؟ به طور پیش فرض جنگو نام جدول مربوط به مدل را اینطور مشخص می کند :نام اپ شما که در settings.py وجود دارد +علامت _ +نام کلاس مدل شما . در این مثال ,ما فرض کردیم که مدل Person در اپ myapp قرار دارد ,پس نام جدول مربوط به آن برابر با myapp_person است .

شما همچنین ممکن است با تنظیم db_table برای مدل خود ,نام جدول مربوطه را خودتان تنظیم کنید .

نکته : جنگو هیچگاه کد SQL وارد شده در متد raw را بررسی نمی کند . جنگو انتظار دارد که کد شما یک مجموعه از ردیف ها (سطر ها) را بازگرداند . اگر کد شما این کار را انجام ندهد ,به ارور های عجیب و رمزگذاری شده (cryptic) برخواهید خورد .

نکته : اگر از دیتابیس MySQL استفاده می کنید ,به یاد داشته باشید که قابلیت MySQL’s silent type coercion در این دیتابیس ممکن است باعث ایجاد نتایج غیرمنتظره شود . برای مثال اگر در ستونی که نوع آن str است , Query اجرا کنید ولی مقدار ارسال شده برای جستجوی خود را یک Integerبگذارید ,دیتابیس MySQL قبل از فرایند مقایسه و جستجو ,تمامی مقادیر جدول را به یک Integerتبدیل خواهد کرد . در این صورت اگر جدول شما دارای مقادیری مانند ‘abc’ و ‘def’ باشد و شما WHERE mycolumn=0 را اجرا کنید ,هر دو مقدار بازگردانده می شوند . برای جلوگیری از این امر , نوع صحیح برای نوشتن در کد SQL خود را پیدا کنید .

اتصال فیلد های درون Query و مدل

متد raw به صورت خودکار فیلد های درون کد SQL شما را با فیلد های مدل متصل می کند (به این فرایند map می گویند) .

ترتیب فیلد ها در کد شما مهم نیست . به عبارت دیگر دو کد زیر , هر دو یک نتیجه را بازمیگردانند :

>>> Person.objects.raw(&quotSELECT id, first_name, last_name, birth_date FROM myapp_person&quot)

>>> Person.objects.raw(&quotSELECT last_name, birth_date, first_name, id FROM myapp_person&quot)

در واقع فرایند تطبیق فیلد ها با استفاده از نام آنها انجام می شود . این به این معنی است که می توانید از دستور AS(یکی از دستورات SQL) استفاده کنید تا خودتان فیلد های مدل و فیلد های درون کد SQL خود را متصل کنید . بنابراین اگر جدول دیگری داشتید که داده های Person را درونش داشتید ,شما می توانید به راحتی آن را در اشیای مدل Person ,به اصطلاح map کنید :

>>> Person.objects.raw(
...      &quot&quot&quot
...          SELECT first AS first_name,
...          last AS last_name,
...          bd AS birth_date,
...          pk AS id,
...          FROM some_other_table
...      &quot&quot&quot
...  )

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

از طرف دیگر می توانید از آرگومان translations نیز در متد raw استفاده کنید . این آرگومان یک دیکشنری را دریافت می کند و با استفاده از آن نام فیلد ها در کد SQL را به نام فیلد ها در مدل ,متصل می کند . به عنوان مثال ,کد بالا می تواند به صورت زیر نیز نوشته شود :

>>> name_map = {&quotfirst&quot: &quotfirst_name&quot, &quotlast&quot: &quotlast_name&quot, &quotbd&quot: &quotbirth_date&quot, &quotpk&quot: &quotid&quot}
>>> Person.objects.raw(&quotSELECT * FROM some_other_table&quot, translations=name_map)

پشتیبانی از index

متد raw از indexing نیز پشتیبانی می کند . این یعنی اگر از میان نتایج ,فقط به اولین از آنها نیاز دارید ,میتوانید بنویسید :

>>> first_person = Person.objects.raw(&quotSELECT * FROM myapp_person&quot)[0]

با این حال فرایند indexing و slicing در سطح دیتابیس انجام نمی شود و جنگو خودش آن را بر عهده می گیرد . اگر تعداد اشیایی که با آنها کار می کنید زیاد است ,توصیه می شود تا جستجوی خود را با استفاده از دستور LIMIT ,محدودتر کنید . برای مثال :

>>> first_person = Person.objects.raw(&quotSELECT * FROM myapp_person LIMIT 1&quot)[0]

تاخیر در بارگذاری فیلد ها

فیلد ها همچنین می توانند در حالت معوق باشند (متد defer را به یاد دارید ؟) :

>>> people = Person.objects.raw(&quotSELECT id, first_name FROM myapp_person&quot)

اشیای مدل Person که توسط این Query بازگردانده می شوند ,مانند این است که defer روی آنها فراخوانی شده باشد . در واقع معنی آن این است که فیلد هایی که از Query حذف شده باشند ,فقط در صورت استفاده ,از دیتابیس بارگذاری می شوند . برای مثال :

>>> for p in Person.objects.raw(&quotSELECT id, first_name FROM myapp_person&quot):
...         print(
...              p.first_name, # This will be retrieved by the original query
...              p.last_name, # This will be retrieved on demand
...         )
...
John Smith
Jane Jones

در ظاهر به نظر می رسد که Query ,هر دو فیلد first_name و last_name را از دیتابیس بارگذاری کرده است . ولی در واقع این مثال 3 Query مختلف را انجام داده است . فقط فیلد first_name با متد raw از دیتابیس بارگذاری می شود . فیلد last_name فقط در صورتی که به آن اشاره شود ,بارگذاری خواهد شد .

فقط یک فیلد وجود دارد که هیچ گاه نمی توانید آن را معوق کنید (کاری کنید که فقط وقتی آن را استفاده کردید ,بارگذاری شود) ;فیلد primary key .جنگو از این فیلد برای شناسایی اشیا استفاده می کند . بنابراین همیشه باید در کد SQL نوشته شده ,این فیلد ذکر شود . در صورتی که این کار را انجام ندهید , با یک ارور FieldDoesNotExist مواجه خواهید شد .

استفاده از annotations

همچنین می توانید از فیلد هایی در Query خود استفاده کنید که در مدل شما تعریف نشده اند . برای مثال می توانید از تابع age در PostgreSQL (جزو توابع خود دیتابیس است و ربطی به جنگو ندارد) استفاده کنید تا اشیایی بازگردانده شوند که سن آنها (فیلد age) با کمک دیتابیس محاسبه می شود :

>>> people = Person.objects.raw(&quotSELECT *, age(birth_date) AS age FROM myapp_person&quot)
>>> for p in people:
...          print(&quot%s is %s.&quot % (p.first_name, p.age))
...
John is 37.
Jane is 42.
...

البته این عملکرد زیاد مورد استفاده قرار نمی گیرد . معمولا برای این کار از توابع F و یا Func که در آینده با آن اشنا می شوید ,استفاده می شود .

ارسال پارامتر به متد raw

اگر نیاز به ارسال پارامتر به Query خود دارید ,از آرگومان params برای اینکار استفاده کنید . مانند :

>>> lname = &quotDoe&quot
>>> Person.objects.raw(&quotSELECT * FROM myapp_person WHERE last_name = %s&quot, [lname])

آرگومان params یک لیست یا دیکشنری از پارامتر ها را می پذیرد . شما باید از %s برای جایگذاری پارامتر های یک لیست و از (key)% برای جایگذاری پارامتر های یک دیکشنری (که در آن key با مقدار key در دیکشنری ارسالی جایگزین می شود) استفاده کنید . متغییر های تعریف شده در Query با محتویات آرگومان params جایگزین خواهند شد.

نکته : در دیتابیس SQLite شما نمی توانید برای آرگومان params یک دیکشنری را ارسال کنید . بجای دیکشنری از لیست استفاده کنید .

نکته : هیچگاه در Query خود از string formatting و یا quote placeholders استفاده نکنید . برای مثال شاید بخواهید Query بالا را به شکل زیر بنویسید (که نباید اینطور بنویسید) :

>>> query = &quotSELECT * FROM myapp_person WHERE last_name = %s&quot % lname
>>> Person.objects.raw(query)

شاید هم فکر کنید که می توانید مثال بالا را به شکل زیر نیز بنویسید که نباید این کار را نیز انجام بدهید (به کمک استفاده از ‘ در %s) :

>>> query = &quotSELECT * FROM myapp_person WHERE last_name = '%s'&quot

انجام این اشتباهات ممکن است باعث ایجاد باگ هایی مانند SQL injection می شود . استفاده از آرگومان params و استفاده نکردن از ‘ در کد SQL خود ,باعث می شود تا جنگو شما را در برابر این گونه حملات تحت محافظت قرار بدهد .

اجرای دستورات SQL به صورت مستقیم

گاهی اوقات فقط استفاده از متد raw کافی نخواهد بود . ممکن است گاهی لازم باشد تا یک Queryرا ایجاد کنید که به مدل شما بطور واضح اشاره نمی کند و یا دستورات UPDATE , INSERT و DELETE را مستقیما خودتان بنویسید .

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

شی django.db.connection حاوی اطلاعات نحوه اتصال به دیتابیس پیش فرض شماست . برای استفاده و اتصال به دیتابیس , connection.cursor را فراخوانی کنید تا یک شی از کلاس cursor را دریافت کنید . سپس cursor.execute(sql, [params]) را فراخوانی کنید و کد SQL خود را درون آن قرار دهید . در پایان می توانید از cursor.fetchone یا cursor.fetchall برای دریافت نتایج و ردیف های موجود در نتیجه استفاده کنید .

برای مثال :

from django.db import connection

def my_custom_sql(self):
    with connection.cursor as cursor:
        cursor.execute(&quotUPDATE bar SET foo = 1 WHERE baz = %s&quot, [self.baz])
        cursor.execute(&quotSELECT foo FROM bar WHERE baz = %s&quot, [self.baz])
        row = cursor.fetchone
    return row

برای حفظ امنیت و مقابله با sql injection ,نباید در اطراف %sو اینگونه متغییر ها در کد خود از علامت ‘استفاده کنید .

اگر می خواستید دقیقا علامت درصد برای کد شما اعمال شود ,علامت درصد را دو بار بنویسید . برای مثال :

cursor.execute(&quotSELECT foo FROM bar WHERE baz = '30%'&quot)
cursor.execute(&quotSELECT foo FROM bar WHERE baz = '30%%' AND id = %s&quot, [self.id])

اگر از بیش از یک دیتابیس استفاده می کنید ,می توانید از django.db.connections استفاده کنید تا شی کلاس cursor را برای دیتابیس دلخواه خود دریافت کنید . django.db.connections یک دیکشنری مانند است که به شما اجازه می دهد با alias name دیتابیس خود ,اتصال مربوطه را پیدا کنید و در آخر با آن دیتابیس کار کنید . برای مثال :

from django.db import connections

with connections[&quotmy_db_alias&quot].cursor as cursor:
    # Your code here

به طور پیش فرض , API مربوط به دیتابیس ها در پایتون نتایج خود را بدون نام فیلد آنها بازمیگرداند , این یعنی شما بجای یک دیکشنری ,یک لیست از مقادیر را دریافت می کنید . با کدی شبیه به کد زیر و استفاده از مقدار کمی از حافظه ,می توانید نتایج را تبدیل به دیکشنری کنید :

def dictfetchall(cursor):
    &quot&quot&quot Return all rows from a cursor as a dict. Assume the column names are unique. &quot&quot&quot
    columns = [col[0] for col in cursor.description]
    return [dict(zip(columns, row)) for row in cursor.fetchall]

یک راه دیگر استفاده از collections.namedtuple است که یکی از کتابخانه های استاندارد پایتون است . یک namedtuple در واقع شبیه تاپل است که دارای فیلد هایی است که می توانید به آنها بر اساس ویژگی هایشان دسترسی پیدا کنید . همچنین ویژگی های iterable و indexable را نیز دارد . به فیلد ها می توانید از طریق نام آنها یا ایندکس آنها دسترسی پیدا کنید . برای مثال :

from collections import namedtuple

def namedtuplefetchall(cursor):
    &quot&quot&quot Return all rows from a cursor as a namedtuple.
    Assume the column names are unique. &quot&quot&quot
    desc = cursor.description
    nt_result = namedtuple(&quotResult&quot, [col[0] for col in desc])
    return [nt_result(*row) for row in cursor.fetchall]

مثال های بالا (dictfetchall و namedtuplefetchall) فرض می گیرد که نام ستون ها در دیتابیس متفاوت و unique هستند ,زیرا شی های cursor نمی توانند ستون ها را از جداول مختلف تشخیص دهند .

در اینجا یک مثال از تفاوت بین این سه وجود دارد :

>>> cursor.execute(&quotSELECT id, parent_id FROM test LIMIT 2&quot)
>>> cursor.fetchall
((54360982, None), (54360880, None))
>>> cursor.execute(&quotSELECT id, parent_id FROM test LIMIT 2&quot)
>>> dictfetchall(cursor)
[{'parent_id': None, 'id': 54360982}, {'parent_id': None, 'id': 54360880}]

>>> cursor.execute(&quotSELECT id, parent_id FROM test LIMIT 2&quot)
>>> results = namedtuplefetchall(cursor)
>>> results
[Result(id=54360982, parent_id=None), Result(id=54360880, parent_id=None)]
>>> results[0].id
54360982
>>> results[0][0]
54360982

نکته ای درباره connectionsو cursors

تا اینجا دو مفهوم با نام های cursor و connection(اتصال شما به دیتابیس) بررسی شده است .

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

اگر با استاندارد های این API آشنا نیستید ,به یاد داشته باشید که کد SQL در cursor.execute ,از متغییر ها به شکل "%s" به جای اضافه کردن پارامتر ها به صورت مستقیم استفاده می کند .

توجه داشته باشید که جنگو انتظار دارد تا از "%s" برای تعریف متغییر استفاده کنید و نه “?” (که فقط در ساختار های SQLite استفاده می شود) .

در مثال زیر نیز از یک شی cursor به عنوان یک context manager استفاده کردیم :

with connection.cursor as c:
    c.execute(...)

کد بالا برابر با کد زیر خواهد بود :

c = connection.cursor
try:
    c.execute(...)
finally:
    c.close

در این جلسه در رابطه با Raw SQL در جنگو صحبت کردیم .

پ ن : در اینجا دوره ما تموم میشه ! اگه قسمتمون بود , باز برمیگردیم و با جلسات بیشتر در خدمتتون هستیم . مارو از دعای خیر خودتون بی نصیب نزارید . مخلص شما :)