SQLAlchemy – دیتابیس در دستان شماست

تو پروژه هایی که نیاز کمی به ارتباط با دیتابیس (MySQL) داشتن از کتابخونه PyMySQL استفاده می‌کردم برای پروژه های کوچیک خیلی کار راه بنداز بود تا اینکه در آخرین پروژم بکل داستان عوض شد.

تو این پروژه بحث ارتباط با دیتابیس و ارسال و دریافت اطلاعات و موجودیت های سیستم بسیار پر رنگ بود دیگه نمیتونستم از PyMySQL استفاده کنم چون PyMySQL یک wrapper ساده بود که دستورات خام sql رو اجرا میکرد ولی من یه ORM لازم داشتم با امکانات زیاد تا کار رو برام آسون تر کنه

تو ذهنم بود که از یه ORM استفاده کنم که:

  1. کارایی و سرعت سیستم رو به صورت مشخصی کم نکنه
  2. داکیومنت خوبی داشته
  3. کامیونیتی فعالی داشته باشه تا اگه مشکلی پیش اومد بتونم سریع حلش کنم
  4. سریع بشه باهاش کار کرد و مدت زمان زیادی رو برای یادگرفتن از من نگیره

اگه از حق نگذریم  SQLAlchemy همه اینا رو داره

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

اول با دستور زیر SQLAlchemy رو نصب کنید

اولین قدم ارتباط با دیتابیس هست. برای ارتباط با هر دیتابیسی نیاز به یک url با فرمت مخصوص همون دیتابیس هست الان که دیتابیس ما MySQL از کد زیر استفاده میکنیم

اشتباه نکنید با این کد SQLAlchemy به دیتابیس وصل نمیشه در واقع SQLAlchemy تنها زمانی که لازم باشه به دیتابیس وصل میشه یعنی زمانی که شما یه چیزی رو از دیتابیس درخواست کنید و یا تغییری رو اعمال کنید

این تابع چند تا پارامتر دیگه هم قبول میکنه poolClass, pool_size, max_overflow, pool_timeout,echo

echo

اگه مقدار این پارامتر رو True قرار بدید هر کوئری که به دیتابیس بفرستید تو خروجی براتون به نمایش در میاد و اگه debug بزارید علاوه بر query خود نتابج رو هم نشون میده

autocommit

تمام عملیات ها در SQLAlchemy در قالب transaction انجام میشه پس باید برای اعمال تغییراتی که  هر کوئری در دیتابیس ایجاد کرده دستور commit رو اجرا کنید اگه نخواید commit رو انجام بدید و به این صورت باشه که بعد از هر کوئری، تغییرات به صورت اتوماتیک commit بشن باید مقدار این آرگومان رو برابر True قرار بدید با این کار SQLAlchemy دستوراتی که باعث تغییر در دیتابیس میشن رو شناسایی و بعد از اون commit رو اجرا کنه (پیشنهاد میکنم مقدار این آرگومان رو همون False بزارید)

قبل از اینکه بقیه پارامتر ها رو توضیح بدم میخوام درباره Connection Pooling صحبت کنم.

روال عادی برنامه ها اینطوری هست که برنامه بعد از شروع شدن، یک اتصال (conenction) به دیتابیس ایجاد میکنه، درخواست ها رو به دیتابیس میفرسته و بعد اتصال رو میبنده حالا شما یک برنامه مثل یک وب سایت رو در نظر بگیرید و با هر برنامه دیگه ای که درخواست های زیادی بهش ارسال میشه، تو این برنامه در هر درخواست یک اتصال باید ایجاد و بعد از اتمام درخواست اتصال باید بسته بشه متوجه مشکل میشید؟

ایجاد اتصال به دیتابیس یک حرکت زمان بر هست در هر بار باید نام کاربری و رمز عبور چک بشه و یک سوکت ایحاد بشه و برای بسته شدن هم این سوکت حذف بشه و برای درخواست بعدی دوباره همین مراحل تکرار میشه؟ راه حل بهتری نیست؟

 Connection Pooling

Connection Pooling یک تکنیک در سطح برنامه و همچنین دیتابیس هست که شما اول pool_size یعنی اندازه اتصال هایی که باید در pool قرار بگیرند رو تعریف میکنید بعد برنامه شما به ازای هر درخواست یک اتصال ایجاد میکنه و تا زمانی که تعداد اتصال ها به اندازه pool_size نرسیده این کار رو انجام میده و هیچ اتصالی رو نمیبنده اما به محض اینکه تعداد اتصال ها به pool_size رسید برای درخواست های جدید از اتصال های بیکار داخل pool استفاده میکنه و اتصال جدید به دیتابیس ایجاد نمیشه.

SQLAlchemy یک پارامتر دیگه هم داره به نام max_overflow یعنی حداکثر تعداد اتصال هایی که بیشتر از اندازه pool_size میتونن داخل pool قرار بگیرن حالا اگه تعداد کل اتصال های داخل pool مشغول باشن SQLAlchemy به اندازه max_overflow هم درخواست جدید قبول میکنه و اتصال جدید میسازه.

ببینم فرق max_overflow و pool_size رو متوجه شدید؟ اگه برای مثال pool_size=5 باشه تا زمانی که تعداد اتصال ها در pool به ۵ نرسیده برنامه میاد و اتصال جدید ایجاد میکنه اما به محض اینکه به ۵ برسه از اتصال های داخل pool استفاده میکنه و اتصال جدیدی ایجاد نمیکنه اما اگه همه اتصال های داخل pool مشغول بودن چی؟

حالا برنامه میاد برای درخواست های بیشتر از pool_size تا زمانی که بیشتر از max_overflow نشدن اتصال جدید ایجاد میکنه اما این اتصال ها دیگه به pool بر نمیگردن و به محض تموم شدن کار برنامه با اونها بسته میشن.

یعنی حداکثر اتصال هایی که برنامه میتونه بسازه به اندازه pool_size + max_overflow هست و اگه این اندازه اتصال ایجاد شده باشه و درخواست جدید بیاد، اونموقع این درخواست جدید باید به اندازه pool_timeout صبر کنه تا یکی از اتصال ها کارش تموم بشه و اگه بیشتر از این مقدار صبر کرد و اتصالی گیرش نیومد درخواست دراپ و لغو میشه.

شاید کار شما جوری باشه که نخواهید درخواست های جدید صبر کنن تا یک اتصال آزاد بشه یعنی کارتون خیلی حیاتی باشه و هر ثانیه برای برنامتون اهمیت داشته باشه اما تا اونجا که برای من پیش اومده برای ۹۹ درصد برنامه هایی که تعداد درخواست های زیادی دارن استفاده از این تکنیک کارایی برنامه رو به شدت افزایش میده و همچنین دیگه با خطای Too Many Connections هم مواجه نمیشید.

برای غیر فعال کردن Connection Pooling باید poolClass=Nullpool قرار بدید.

یک مفهوم خیلی مهم در این کتابخونه Session هست

خوب Session چی هست و چیکار میکنه؟

Session یک حافظه موقتی هست که وقتی به دیتابیس ارسال میشه تغییرات شما رو اعمال میکنه در واقع میتونیم بگیم هم transaction هست هم نیست. بزارید نحوه ایجادش رو بگم بعد کامل نحوه عملکردش رو توضیح میدم

هر زمان که یک session ایجاد کنید یک اتصال جداگانه به سرور دیتابیس ایجاد و به اون session اختصاص داده میشه

اگه تو Session شما یک query اجرا کنید چه select چه چیز دیگه ای یک transaction رو شروع کردید و اگه در Session هیج query اجرا نکنید و فقط یک رکورد ایحاد کنید و با تابع add اون رو به Session اضافه کنید اون موقع هم یک transaction درست کردید این ۲ تا قانون کلی درباره Session هست.

حالا چرا مهم هست که بدونید کی یک transaction ایجاد میشه؟

وقتی یک transaction ایجاد میشه اگه isolation_level برابر Repeatble Read باشه (که پیش فرضش همین هست) تا زمانی که commit نشه هر تغییری در دیتابیس ایجاد بشه اون transaction نمیتونه اون تغییر رو ببینه.

این دو نمونه از نحوه ویرایش و اضافه کردن رکورد به دیتابیس هست.

و نوع دوم

وقتی شما commit میکنید چند تا اتفاق میفته:

۱. تمام تغییراتی که توسط شما ایجاد شده در دیتابیس نوشته میشه

۲. تمام متغیر های قبلی expire میشن

برای مثال در کد اول وقتی commit کردید اول اطلاعات رکورد با ای دی ۱ به روز میشه بعد متغیر item منقضی میشه و وقتی دوباره item.name رو اجرا کنید SQLAlchemy یک دستور select به دیتابیس میفرسته تا (dbs.query(Item).get(1 اجرا بشه بعد مقدار name رو به شما نشون میده

خوب حالا اون Item چی هست؟ اون درواقع کلاس جدول متناظر در دیتابیس هست که به این صورت تعریف میشه

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

برای اجرای انواع عملیات در دیتابیس من یه سری کد نمونه میزارم که فکر میکنم نیازی به توضیح نداره

برای query زدن میتونید از دو تابع filter و filter_by استفاده کنید که من استفاده از filter به خاطر ، داشتن api بهتر توصیه میکنم.

وقتی هم از این دو تابع استفاده میکنید هیچ چیزی اجرا نمیشه یعنی شما اگه کد زیر رو پرینت بگیرید میبیند که تنها یک دستور sql هست نه چیز دیگه ای

دستور sql زمانی اجرا میشه که یکی از متدهای all, first یا one رو اجرا کنید و یا متغیر result رو تو حلقه for بزارید

و توضیح این متدها

all

همه رکوردهای کوئری رو بر میگردونه و اگه چیزی پیدا نکنه یکی لیست خالی

first

به انتهای دستور  limit 1 اضافه میکنه و اگه باز نتایج خالی باشه None بر میگردونه

one

با این کار به SQLALchemy میگید که نتیجه فقط و فقط باید یک کورد باشه و همون رکورد رو برگردونه اگه این دستور بیشتر از یک نتیجه داشته باشه خطا نشون داده میشه

چند مثال پائین دقیقا مثل هم هستند

چطوری رکورد های زیادی رو دریافت کنیم؟

اول پیشنهاد میکنم نوشته قبلیم ۷۰۰ میلیون رکورد MySQL را به راحتی آب خوردن با PHP پردازش کنید  که در همین رابطه هست رو بخونید.

وقتی نتایج رو با all دریافت میکنید و یا به طور مستقیم مقدار  (dbs.query(Item رو تو حلقه استفاده میکنید (کد اول در مثال بالا) کل نتایج یکجا از دیتابیس دریافت میشه و در حافظه قرار میگیره و نتایج buffer میشن، حالا مواقعی هست که  نتایج خیلی زیاد هست برای مثال بیشتر از ۱۰ هزار رکورد،  وقتی همچین نتایجی رو میخواید پردازش کنید ممکن هست با مشکل حافظه مواجه بشید برای حل این مشکل باید از unbuffered queries استفاده کنید تا نتایج رو قسمت قسمت از دیتابیس دریافت کنید کد زیر همین کار رو میکنه و نتایج رو به صورت stream از MySQL میگیره.

در کد بالا در هر پیمایش شما یک رکورد رو میگیرید ولی کل نتایج در قسمت های ۱۰۰۰ تایی از دیتابیس دریافت میشه تا حافظه سرور پایتون یکباره پر نشه.

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

نکات مهم

۱. در SQLAlchemy اگه مقدار فیلدی رو برابر None بزارید در دیتابیس برابر Null قرار میگیره

۲. باید دقت کنید False برای هر نوع داده ای مقدار برابری برنمیگردونه برای مثال اگه فیلدی نوعش int باشه و شما مقدارش رو False قرار بدید SQLAlchemy در دیتابیس مقدارش رو ۰ میکنه و اگه  نوعش string باشه  Null میشه.

۳. برای بستن اتصال هم این نکته مهم رو داشته باشید

برای اینکه session رو ببندید کافیه متد close رو صدا بزنید با اینکار اگه  Connection Pooling فعال باشه این اتصال به pool برمیگرده در غیر این صورت اتصال بسته میشه و منابع سخت افزاری آزاد میشه

پس برای بستن اتصال کد زیر رو اجرا می‌کنیم

برای اینکه کل اتصال های دیتابیس رو ببینید و منابع سخت افزاری رو آزاد کنید کد زیر رو اجرا کنید

زمانی این کد رو اجرا کنید که کل برنامتون کارش تموم شده باشه و بهتره تو هندلر atexit بزارید

تمام عملیات ها در SQLAlchemy در قالب transaction انجام میشه حتی وقتی autocommit=True باشه SQLAlchemy قبل و بعد از هر دستور transaction رو شروع و commit میکنه

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

در نوشته های بعدی در رابطه scoped session، نحوه اجرای دستورات خام sql و همچنین رابطه ها در SQLAlchemy خواهم نوشت.

جستجو در کل مطالب سایت

نوشته های مشابه

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *