آموزش

آموزش SQLAlchemy همراه با مثال

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

تاریخ انتشار:

15 شهریور 1405

پایتون، SQL

13 دقیقه

کاربرهای فینکا در چه شرکت‌هایی مشغول به کار هستند؟

این مقاله تو رو با SQLAlchemy آشنا می‌کنه؛ ابزاری برای کار با SQL در پایتون که کارهایی مثل کوئری زدن، ساختن و مدیریت دیتابیس‌ها رو خیلی ساده می‌کنه.

بعد از خوندن این آموزش، پیشنهاد می‌کنم برای تمرین بیشتر در دوره مقدمه‌ای در دیتابیس‌ها در پایتون فینکا ثبت‌نام کنی. این دوره پروژه‌های عملی خوبی داره که بهت کمک می‌کنه فیلتر کردن و گروه‌بندی داده‌ها، کوئری‌های پیشرفته SQLAlchemy و نحوه کوئری زدن، ساختن و نوشتن در دیتابیس‌های مهمی مثل SQLite، MySQL و PostgreSQL رو یاد بگیری.

SQLAlchemy چیه؟

SQLAlchemy یه ابزار SQL برای پایتونه که به برنامه‌نویس‌ها اجازه می‌ده با زبان پایتون به دیتابیس‌های SQL وصل بشن و اون‌ها رو مدیریت کنن. با این ابزار می‌تونی کوئری‌ها رو به‌صورت رشته (String) بنویسی یا از اشیا پایتون برای ساخت کوئری استفاده کنی. کار با اشیا انعطاف‌پذیری زیادی بهت می‌ده تا بتونی برنامه‌های پرقدرتی بر پایه SQL بسازی.

به زبان ساده، این ابزار کمک می‌کنه تا با پایتون به دیتابیس وصل بشی، کوئری‌های SQL رو با روش شی‌گرا اجرا کنی و کلاً کارت خیلی راحت‌تر بشه.

نصب SQLAlchemy

نصب این پکیج و شروع کدنویسی باهاش خیلی راحته.

می‌تونی SQLAlchemy رو با ابزار مدیریت پکیج پایتون (pip) نصب کنی:

اگه از توزیع Anaconda در پایتون استفاده می‌کنی، این دستور رو در ترمینال conda وارد کن:

حالا بیا چک کنیم ببینیم پکیج درست نصب شده یا نه:

'1.4.41'

عالیه، ما با موفقیت نسخه 1.4.41 SQLAlchemy رو نصب کردیم.

شروع کار

در این بخش یاد می‌گیریم چطور به دیتابیس‌های SQLite وصل بشیم، جدول‌ها رو بسازیم و ازشون برای اجرای کوئری‌های SQL استفاده کنیم.

اتصال به دیتابیس

ما از یک دیتابیس SQLite به اسم European Football از سایت Kaggle استفاده می‌کنیم که دو تا جدول داره: divisions و matchs.

اول با تابع create_engine، موتور SQLite رو می‌سازیم و آدرس فایل دیتابیس رو بهش می‌دیم. بعد با این موتور یه اتصال (Connection) برقرار می‌کنیم. از شی conn برای اجرای انواع کوئری‌های SQL استفاده خواهیم کرد.

اگه می‌خوای به دیتابیس‌های دیگه‌ای مثل PostgreSQL، MySQL، Oracle یا Microsoft SQL Server وصل بشی، برای تنظیمات اتصال می‌تونی به engine configuration سر بزنی.

در این آموزش فرض بر اینه که تو مبانی پایتون و SQL رو بلدی. اگه نه، اصلاً جای نگرانی نیست. می‌تونی برای قوی کردن پایه‌ات، مسیرهای مهارتی مبانی SQL و مبانی پایتون فینکا رو بگذرونی.

دسترسی به جدول

برای ساخت یه شی از جدول، باید نام جدول و فراداده‌ها (Metadata) رو مشخص کنیم. می‌تونی فراداده‌ها رو با تابع MetaData() در SQLAlchemy بسازی.

بیا فراداده‌های جدول divisions رو چاپ کنیم.

Table('divisions', MetaData(), Column('division', TEXT(), table=<divisions>), 
Column('name', TEXT(), table=<divisions>), Column('country', TEXT(), 
table=<divisions>), schema=None)

این فراداده‌ها شامل اسم جدول، اسم ستون‌ها به همراه نوع داده‌شون و ساختار (Schema) دیتابیس می‌شن.

حالا بیا از شی جدول division استفاده کنیم تا اسم ستون‌ها رو ببینیم.

['division', 'name', 'country']

همون‌طور که می‌بینی، این جدول ستون‌های division، name و country رو داره.

کوئری ساده SQL

حالا رسیدیم به بخش جذاب ماجرا! از شی جدول استفاده می‌کنیم تا کوئری بزنیم و نتیجه‌ها رو بگیریم.

در کد زیر، ما همه ستون‌های جدول division رو انتخاب (Select) می‌کنیم.

SELECT divisions.division, divisions.name, divisions.country
FROM divisions

نکته: می‌تونی دستور select رو به‌صورت db.select([division]) هم بنویسی.

اگه شی کوئری رو چاپ کنی، دقیقاً دستور SQL معادلش رو بهت نشون می‌ده.

دریافت نتیجه کوئری

حالا کوئری رو با شیء اتصالمون اجرا می‌کنیم و ۵ سطر اول رو می‌گیریم.

fetchone(): هر بار فقط یه سطر رو برمی‌گردونه.
fetchmany(n): هر بار n سطر رو برمی‌گردونه.
fetchall(): همه سطرها رو با هم برمی‌گردونه.

[('B1', 'Division 1A', 'Belgium'), ('D1', 'Bundesliga', 'Deutschland'), ('D2', '2. Bundesliga', 'Deutschland'), ('E0', 'Premier League', 'England'), ('E1', 'EFL Championship', 'England')]

اینم از نتیجه که ۵ سطر اول جدول رو بهمون نشون می‌ده.

مثال‌های بیشتر از SQLAlchemy

در این بخش، مثال‌های مختلفی از SQLAlchemy برای کارهایی مثل ساخت جدول، وارد کردن داده، اجرای کوئری‌های SQL، تحلیل داده‌ها و مدیریت جدول‌ها رو با هم بررسی می‌کنیم.

ساخت جدول

اول یه دیتابیس جدید به اسم finca.sqlite می‌سازیم. تابع create_engine اگه دیتابیسی با این اسم وجود نداشته باشه، خودش یکی می‌سازه؛ برای همین ساختن و وصل شدن به دیتابیس خیلی شبیه همن.

بعد از وصل شدن، یه شی فراداده (Metadata) می‌سازیم.

حالا با استفاده از تابع Table تو SQLAlchemy، جدولی به اسم Student تعریف می‌کنیم.

این جدول قراره این ستون‌ها رو داشته باشه:

Id: عدد صحیح (Integer) و کلید اصلی (Primary key)
Name: رشته (String) و غیرقابل خالی بودن (Non-nullable)
Major: رشته با مقدار پیش‌فرض Math
Pass: بولین (Boolean) با مقدار پیش‌فرض True

خب ساختار جدول رو تعریف کردیم. حالا با دستور metadata.create_all(engine) اون رو تو دیتابیس ثبت می‌کنیم.

درج یک سطر

برای اضافه کردن یه رکورد جدید، اول از متد insert استفاده می‌کنیم و شی جدول رو بهش پاس می‌دیم. بعد با values، مقادیر مدنظرمون رو برای هر ستون مشخص می‌کنیم. این کار شبیه پاس دادن آرگومان به توابع پایتونه.

در نهایت، کوئری رو با شیء اتصالمون (conn) اجرا می‌کنیم تا داده تو دیتابیس ذخیره بشه.

حالا بیا با یه کوئری select ساده چک کنیم ببینیم این سطر به جدول Student اضافه شده یا نه.

[(1, 'Matthew', 'English', True)]

داده‌ها با موفقیت اضافه شدن!

درج چند سطر هم‌زمان

اضافه کردن داده‌ها یکی‌یکی روش خوبی برای پر کردن دیتابیس نیست. بیا چند تا رکورد رو به‌صورت گروهی و با استفاده از لیست‌ها اضافه کنیم.

یه کوئری insert برای جدول Student بساز.
یه لیست از چند تا دیکشنری بساز که هر کدوم شامل اسم ستون‌ها و مقادیر مربوطه باشه.
کوئری رو اجرا کن و اون لیست رو به‌عنوان آرگومان دوم بهش پاس بده.

برای بررسی نتیجه، دوباره همون کوئری select ساده رو اجرا می‌کنیم.

[(1, 'Matthew', 'English', True), (2, 'Nisha', 'Science', False), (3, 'Natasha', 'Math', True), (4, 'Ben', 'English', False)]

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

اجرای کوئری خام SQL با SQLAlchemy

به جای استفاده از متدهای پایتونی، می‌تونیم کوئری‌های SQL رو مستقیماً به‌صورت رشته (String) هم اجرا کنیم.

کافیه کوئری خام رو به تابع execute() پاس بدی و نتیجه رو با fetchall() بگیری.

[(1, 'Matthew', 'English', 1), (2, 'Nisha', 'Science', 0), (3, 'Natasha', 'Math', 1), (4, 'Ben', 'English', 0)]

حتی می‌تونی کوئری‌های پیچیده‌تری رو هم به این روش اجرا کنی. مثلاً در کد زیر، اسم و رشته تحصیلی دانشجویانی رو می‌گیریم که تو آزمون قبول شدن.

[('Matthew', 'English'), ('Natasha', 'Math')]

استفاده از API ابزار SQLAlchemy

تا اینجا از APIها یا اشیا ساده SQLAlchemy استفاده کردیم. حالا بیا سراغ کوئری‌های پیچیده‌تر و چندمرحله‌ای بریم.

در مثال زیر، دانشجویانی رو انتخاب می‌کنیم که رشته تحصیلی‌شون انگلیسی (English) باشه.

[(1, 'Matthew', 'English', True), (4, 'Ben', 'English', False)]

حالا بیا شرط AND رو هم به WHERE اضافه کنیم.

تو این مثال دنبال دانشجویانی می‌گردیم که رشته‌شون انگلیسیه ولی تو آزمون مردود شدن.

نکته: علامت != یعنی نامساوی.

[(4, 'Ben', 'English', False)]

همون‌طور که می‌بینی، فقط Ben با رشته انگلیسی تو آزمون رد شده.

با همین روش می‌تونیم انواع دستورات رو اجرا کنیم که تو جدول زیر می‌تونی چند نمونه‌اش رو ببینی.

می‌تونی این کدها رو کپی کنی و خودت تستشون کنی.

دستور (Command)	رابط API
in	Student.select().where(Student.columns.Major.in_(['English','Math']))
and, or, not	Student.select().where(db.or_(Student.columns.Major == 'English', Student.columns.Pass = True))
order by	Student.select().order_by(db.desc(Student.columns.Name))
limit	Student.select().limit(3)
sum, avg, count, min, max	db.select([db.func.sum(Student.columns.Id)])
group by	db.select([db.func.sum(Student.columns.Id),Student.columns.Major]).group_by(Student.columns.Pass)
distinct	db.select([Student.columns.Major.distinct()])

برای آشنایی با توابع بیشتر، پیشنهاد می‌کنیم داکیومنت رسمی SQL Statements and Expressions API رو بخونی.

تبدیل خروجی به دیتافریم Pandas

متخصصین داده کار با دیتافریم‌های Pandas رو خیلی دوست دارن. تو این بخش یاد می‌گیریم چطوری نتایج کوئری‌های SQLAlchemy رو مستقیم بریزیم تو یه دیتافریم pandas.

اول کوئری رو اجرا کن و نتایجش رو تو یه متغیر ذخیره کن.

حالا از تابع DataFrame() تو پایتون استفاده کن و نتایج رو بهش بده. در نهایت با استفاده از results[0].keys()، اسم ستون‌ها رو هم به دیتافریم اضافه کن.

نکته: برای گرفتن اسم ستون‌ها با keys()، می‌تونی از هر کدوم از سطرهای نتیجه استفاده کنی.

   Id     Name    Major   Pass
0   1  Matthew  English   True
1   3  Natasha     Math   True
2   4      Ben  English  False

تحلیل داده‌ها با SQLAlchemy

در این بخش دوباره به دیتابیس European football وصل می‌شیم، چند تا کوئری پیچیده‌تر می‌زنیم و در نهایت داده‌ها رو مصورسازی می‌کنیم.

پیوند (Join) دو جدول

مثل همیشه، اول با توابع create_engine() و connect() به دیتابیس وصل می‌شیم.

چون قراره دو تا جدول رو با هم ترکیب (Join) کنیم، باید شی هر دو جدول division و match رو بسازیم.

اجرای یه کوئری پیچیده

اول ستون‌های هر دو جدول division و match رو انتخاب می‌کنیم.
اون‌ها رو با استفاده از ستون مشترکشون پیوند می‌دیم: division.division و match.Div.
داده‌ها رو فیلتر می‌کنیم تا فقط رکوردهایی رو بیاره که دیویژنشون E1 و فصلشون 2009 باشه.
در نهایت نتیجه رو بر اساس HomeTeam مرتب (Order) می‌کنیم.

با ترکیب متدهای مختلف، می‌تونی کوئری‌های خیلی پیچیده‌تری هم بسازی.

نکته: برای پیوند خودکار (Auto-join) دو جدول می‌تونی به این شکل هم بنویسی: db.select([division.columns.division,match.columns.Div]).

  Div                      Date HomeTeam          AwayTeam  FTHG  FTAG FTR  season
0  E1  2008-08-16T00:00:00.000Z Barnsley          Coventry     1     2   A    2009
1  E1  2008-08-30T00:00:00.000Z Barnsley             Derby     2     0   H    2009
2  E1  2008-09-16T00:00:00.000Z Barnsley           Cardiff     0     1   A    2009
3  E1  2008-09-27T00:00:00.000Z Barnsley           Norwich     0     0   D    2009
4  E1  2008-10-04T00:00:00.000Z Barnsley         Doncaster     4     1   H    2009
5  E1  2008-10-21T00:00:00.000Z Barnsley    Sheffield Weds     2     1   H    2009
6  E1  2008-10-25T00:00:00.000Z Barnsley      Bristol City     0     0   D    2009
7  E1  2008-11-08T00:00:00.000Z Barnsley  Sheffield United     1     2   A    2009
8  E1  2008-11-15T00:00:00.000Z Barnsley           Watford     2     1   H    2009
9  E1  2008-11-24T00:00:00.000Z Barnsley           Burnley     3     2   H    2009

بعد از اجرای کوئری، نتیجه رو تبدیل کردیم به یه دیتافریم pandas.

هر دو جدول با هم ترکیب شدن و خروجی فقط شامل رکوردهای دیویژن E1 در فصل 2009 هست که بر اساس اسم تیم میزبان (HomeTeam) مرتب شده.

مصورسازی داده‌ها

حالا که داده‌هامون تو دیتافریمه، می‌تونیم با استفاده از کتابخونه Seaborn اون‌ها رو به شکل یه نمودار میله‌ای قشنگ رسم کنیم.

ما قراره:

تم رو روی «whitegrid» تنظیم کنیم.
اندازهٔ نمودار رو به 15×6 تغییر بدیم.
برچسب‌های محور x رو ۹۰ درجه بچرخونیم.
پالت رنگ رو روی «pastels» تنظیم کنیم.
یک نمودار میله‌ای از «HomeTeam» در برابر «FTHG» با رنگ آبی رسم کنیم.
یک نمودار میله‌ای از «HomeTeam» در برابر «FTAG» با رنگ قرمز رسم کنیم.
راهنمای نمودار رو در گوشهٔ بالا سمت چپ نمایش بدیم.
برچسب‌های محورهای x و y رو حذف کنیم.
خطوط حاشیهٔ سمت چپ و پایین نمودار رو حذف کنیم.

هدف اصلی این بخش اینه که نشون بدیم چطور می‌تونیم از خروجی کوئری SQL استفاده کنیم و نمودارهای جذابی برای نمایش داده‌ها بسازیم.

ذخیره نتایج در فایل CSV

وقتی نتیجه کوئری رو تبدیل کردی به دیتافریم pandas، دیگه خیلی راحت می‌تونی با متد .to_csv() اون رو تو یه فایل متنی ذخیره کنی.

با آرگومان index=False به پانداس می‌گیم که شماره ردیف‌ها رو به عنوان یه ستون مجزا تو فایل ذخیره نکنه.

تبدیل فایل CSV به جدول SQL

تو این قسمت می‌خوایم برعکس کار قبل رو بکنیم؛ یعنی یه فایل CSV رو بخونیم و اون رو تبدیل کنیم به یه جدول تو دیتابیس.

اول به دیتابیس fincasqlite وصل می‌شیم.

بعد فایل CSV رو با تابع read_csv() وارد پانداس می‌کنیم. در نهایت، با استفاده از متد to_sql()، دیتافریممون رو مستقیماً به عنوان یه جدول تو SQL ذخیره می‌کنیم.

متد to_sql() به اسم جدول و شیء اتصال (یا همون موتور) نیاز داره. پارامتر if_exists برای اینه که اگه جدولی با همین اسم از قبل بود، جایگزینش کنه و index هم باعث می‌شه ستون شماره ردیف‌ها تو دیتابیس ذخیره نشه.

برای اینکه مطمئن بشیم درست کار کرده، دوباره به دیتابیس وصل می‌شیم و یه شی برای این جدول می‌سازیم.

حالا یه کوئری می‌زنیم تا ببینیم داده‌ها هستن یا نه.

('HSI', '1986-12-31', 2568.300049, 2568.300049, 2568.300049, 2568.300049, 2568.300049, 0, 333.87900637)
('HSI', '1987-01-02', 2540.100098, 2540.100098, 2540.100098, 2540.100098, 2540.100098, 0, 330.21301274)
('HSI', '1987-01-05', 2552.399902, 2552.399902, 2552.399902, 2552.399902, 2552.399902, 0, 331.81198726)
('HSI', '1987-01-06', 2583.899902, 2583.899902, 2583.899902, 2583.899902, 2583.899902, 0, 335.90698726)
('HSI', '1987-01-07', 2607.100098, 2607.100098, 2607.100098, 2607.100098, 2607.100098, 0, 338.92301274)

همون‌طور که می‌بینی، همه مقادیر با موفقیت از فایل CSV به جدول SQL منتقل شدن.

مدیریت جدول در SQL

به‌روزرسانی داده‌ها

تغییر دادن و آپدیت کردن مقادیر خیلی راحته. ما از ترکیب توابع update، values و where برای این کار استفاده می‌کنیم.

به عنوان مثال، بیا وضعیت قبولی (Pass) دانشجویی به اسم Nisha رو از False به True تغییر بدیم.

برای اینکه ببینیم تغییرات اعمال شده یا نه، یه کوئری ساده می‌زنیم و نتایج رو تو یه دیتافریم نشون می‌دیم.

   Id     Name    Major   Pass
0   1  Matthew  English   True
1   2    Nisha  Science   True
2   3  Natasha     Math   True
3   4      Ben  English  False

حذف سطرها

حذف کردن سطرها یا رکوردها هم شبیه آپدیت کردنه و با کمک توابع delete() و where() انجام می‌شه.

حالا بیا به عنوان نمونه، رکورد مربوط به دانشجویی با نام Ben رو حذف کنیم.

برای بررسی، یه کوئری می‌گیریم و نتایج رو تو دیتافریم می‌بینیم. همون‌طور که می‌بینی، سطر مربوط به Ben کلاً پاک شده.

   Id     Name    Major   Pass
0   1  Matthew  English   True
1   2    Nisha  Science   True
2   3  Natasha     Math   True

حذف کامل جدول (Drop Table)

اگه از SQLite استفاده می‌کنی، ممکنه موقع پاک کردن جدول با ارور database is locked مواجه بشی. دلیلش چیه؟ چون SQLite یه دیتابیس خیلی سبکه و در لحظه فقط می‌تونه یه کار رو انجام بده. از اونجایی که اتصالمون هنوز بازه، قبل از پاک کردن جدول باید اون رو ببندیم.

بعد از بستن اتصالات، از تابع drop_all() روی شی فراداده (metadata) استفاده می‌کنیم و جدول موردنظرمون رو بهش می‌دیم تا پاکش کنه. البته می‌تونی از دستور Student.drop(engine) هم مستقیماً برای حذف یه جدول خاص استفاده کنی.

حواست باشه، اگه تو تابع drop_all() اسم جدولی رو مشخص نکنی، این دستور تمام جداول موجود تو دیتابیس رو پاک می‌کنه!

نتیجه‌گیری

این آموزش SQLAlchemy توابع مختلفی رو از اتصال به دیتابیس گرفته تا تغییر روی داده‌های جداول پوشش داد. اگه دوست داری چیزهای بیشتری یاد بگیری، پیشنهاد می‌کنم دوره تعاملی مقدمه ای بر دیتابیس‌ها در پایتون رو تو فینکا بگذرونی. اونجا مبانی دیتابیس‌های رابطه‌ای، فیلتر کردن، مرتب‌سازی و گروه‌بندی رو کامل یاد می‌گیری. علاوه‌بر این، با توابع پیشرفته SQLAlchemy برای کار با داده‌ها هم آشنا می‌شی.