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

در این جلسه در رابطه با 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 در جنگو صحبت کردیم . پ ن : در اینجا دوره ما تموم میشه ! اگه قسمتمون بود , باز برمیگردیم و با جلسات بیشتر در خدمتتون هستیم . مارو از دعای خیر خودتون بی نصیب نزارید . مخلص شما :)