آموزش استفاده از ماژول sqlite3 در پایتون 3

کیانا3 هفته پیش

0 11 خواندن این مطلب 7 دقیقه زمان میبرد

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

در این آموزش، ماژول sqlite3 را در پایتون 3 بررسی خواهیم کرد. ما یک اتصال به پایگاه داده SQLite ایجاد می‌کنیم، یک جدول به آن اضافه می‌کنیم، داده‌هایی را در آن جدول درج کرده و سپس داده‌ها را می‌خوانیم و تغییر می‌دهیم.

برای این آموزش، ما عمدتاً با یک پایگاه داده مربوط به موجودی ماهی‌ها کار می‌کنیم که باید با اضافه یا کم شدن ماهی‌ها در یک آکواریوم فرضی، آن را به‌روزرسانی کنیم.

پیش‌نیازها برای درک بهتر این آموزش، داشتن آشنایی اولیه با برنامه‌نویسی پایتون و دانش پایه‌ای در مورد SQL توصیه می‌شود.

مرحله ۱ — ایجاد اتصال به پایگاه داده SQLite

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

می‌توانیم با استفاده از ماژول sqlite3 پایتون به یک پایگاه داده SQLite متصل شویم:

python

import sqlite3

connection = sqlite3.connect(“aquarium.db”)

دستور import sqlite3 به برنامه پایتون ما اجازه می‌دهد به ماژول sqlite3 دسترسی داشته باشد. تابع sqlite3.connect() یک شیء Connection (اتصال) را برمی‌گرداند که ما از آن برای تعامل با پایگاه داده SQLite ذخیره شده در فایل aquarium.db استفاده خواهیم کرد. اگر فایل aquarium.db از قبل روی سیستم شما وجود نداشته باشد، توسط تابع sqlite3.connect() به طور خودکار ایجاد می‌شود.

می‌توانیم با اجرای کد زیر، موفقیت‌آمیز بودن ایجاد شیء اتصال خود را بررسی کنیم:

python

print(connection.total_changes)

اگر این کد پایتون را اجرا کنیم، خروجی زیر را خواهیم دید:

text

Output

عبارت connection.total_changes تعداد کل ردیف‌های پایگاه داده است که توسط این اتصال تغییر کرده‌اند. از آنجایی که هنوز هیچ دستور SQLی را اجرا نکرده‌ایم، مقدار 0 کاملاً درست است.

اگر در هر زمانی خواستید این آموزش را از نو شروع کنید، می‌توانید فایل aquarium.db را از سیستم خود حذف کنید.

نکته: همچنین می‌توان با ارسال رشته خاص ":memory:" به تابع sqlite3.connect()، به پایگاه داده SQLite که کاملاً در حافظه موقت (RAM) قرار دارد (و نه در یک فایل) متصل شد. برای مثال: sqlite3.connect(":memory:"). یک پایگاه داده ":memory:" به محض خروج برنامه پایتون شما ناپدید می‌شود. این موضوع در صورتی که یک محیط موقت (Sandbox) برای آزمایش چیزی در SQLite بخواهید و نیازی به ذخیره دائمی داده‌ها نداشته باشید، بسیار مفید است.

مرحله ۲ — افزودن داده‌ها به پایگاه داده SQLite

اکنون که به پایگاه داده SQLite با نام aquarium.db متصل شده‌ایم، می‌توانیم داده‌ها را در آن درج و بخوانیم.

در یک پایگاه داده SQL، داده‌ها در جداول (Tables) ذخیره می‌شوند. جداول مجموعه‌ای از ستون‌ها را تعریف می‌کنند و شامل ۰ یا چند ردیف (Row) با داده‌هایی برای هر یک از ستون‌های تعریف شده هستند.

ما جدولی به نام fish (ماهی) ایجاد می‌کنیم که داده‌های زیر را ردیابی می‌کند:

name	species	tank_number
Sammy	shark	1
Jamie	cuttlefish	7

جدول fish مقدار name (نام)، species (گونه) و tank_number (شماره تانک) را برای هر ماهی در آکواریوم ثبت می‌کند. دو ردیف نمونه برای ماهی‌ها لیست شده است: یک ردیف برای کوسه‌ای به نام سامی (Sammy) و یک ردیف برای ماهی مرکب به نام جیمی (Jamie).

می‌توانیم این جدول fish را در SQLite با استفاده از اتصالی که در مرحله 1 ایجاد کردیم، بسازیم:

python

cursor = connection.cursor()

cursor.execute(“CREATE TABLE fish (name TEXT, species TEXT, tank_number INTEGER)”)

دستور connection.cursor() یک شیء Cursor (مکان‌نما) را برمی‌گرداند. اشیاء Cursor به ما اجازه می‌دهند با استفاده از cursor.execute() دستورات SQL را به پایگاه داده SQLite ارسال کنیم. رشته "CREATE TABLE fish ..." یک دستور SQL است که جدولی به نام fish با سه ستون توضیح داده شده ایجاد می‌کند: name از نوع TEXT، species از نوع TEXT و tank_number از نوع INTEGER.

اکنون که جدول را ایجاد کرده‌ایم، می‌توانیم ردیف‌های داده را در آن درج کنیم:

python

cursor.execute(“INSERT INTO fish VALUES (‘Sammy’, ‘shark’, 1)”)

cursor.execute(“INSERT INTO fish VALUES (‘Jamie’, ‘cuttlefish’, 7)”)

ما cursor.execute() را دو بار فراخوانی می‌کنیم: یک بار برای درج ردیف مربوط به کوسه سامی در تانک 1 و یک بار برای درج ردیف مربوط به ماهی مرکب جیمی در تانک 7. عبارت "INSERT INTO fish VALUES ..." یک دستور SQL است که به ما اجازه می‌دهد ردیف‌هایی را به جدول اضافه کنیم.

در بخش بعد، از دستور SELECT در SQL برای بررسی ردیف‌هایی که به تازگی در جدول fish درج کرده‌ایم استفاده خواهیم کرد.

مرحله ۳ — خواندن داده‌ها از پایگاه داده SQLite

در مرحله 2، دو ردیف به جدولی به نام fish در SQLite اضافه کردیم. می‌توانیم این ردیف‌ها را با استفاده از دستور SQL زیر بازیابی کنیم:

python

rows = cursor.execute(“SELECT name, species, tank_number FROM fish”).fetchall()

print(rows)

اگر این کد را اجرا کنیم، خروجی زیر را مشاهده خواهیم کرد:

text

Output

[(‘Sammy’, ‘shark’, 1), (‘Jamie’, ‘cuttlefish’, 7)]

تابع cursor.execute() دستور SELECT را اجرا می‌کند تا مقادیر ستون‌های name، species و tank_number را از جدول fish بازیابی کند. متد fetchall() تمام نتایج دستور SELECT را برمی‌گرداند. هنگامی که print(rows) را اجرا می‌کنیم، لیستی از دو تاپل (Tuple) را می‌بینیم. هر تاپل دارای سه ورودی است؛ یک ورودی برای هر ستونی که از جدول fish انتخاب کرده‌ایم. این دو تاپل داده‌هایی را دارند که در مرحله 2 درج کردیم: یک تاپل برای کوسه سامی و یک تاپل برای ماهی مرکب جیمی.

اگر بخواهیم ردیف‌هایی را در جدول fish بازیابی کنیم که با معیارهای خاصی مطابقت دارند، می‌توانیم از بند WHERE استفاده کنیم:

python

target_fish_name = “Jamie”

rows = cursor.execute(

“SELECT name, species, tank_number FROM fish WHERE name = ?”,

(target_fish_name,),

).fetchall()

print(rows)

اگر این کد را اجرا کنیم، خروجی زیر را خواهیم دید:

text

Output

[(‘Jamie’, ‘cuttlefish’, 7)]

مانند مثال قبلی، cursor.execute(<SQL statement>).fetchall() به ما اجازه می‌دهد تمام نتایج یک دستور SELECT را واکشی کنیم. بند WHERE در دستور SELECT، ردیف‌هایی را فیلتر می‌کند که مقدار name در آن‌ها برابر با متغیر target_fish_name باشد. دقت کنید که ما از علامت ? برای جایگذاری ایمن متغیر target_fish_name در دستور SELECT استفاده کرده‌ایم. ما انتظار داریم فقط یک ردیف مطابقت داشته باشد و همانطور که می‌بینید، فقط ردیف مربوط به جیمی برگردانده شده است.

هشدار: هرگز از عملیات رشته‌ای (String Operations) پایتون برای ایجاد پویا یک دستور SQL استفاده نکنید. استفاده از این روش شما را در برابر حملات تزریق SQL (SQL Injection) آسیب‌پذیر می‌کند. حملات تزریق SQL می‌توانند برای سرقت، تغییر یا اصلاح داده‌های ذخیره شده در پایگاه داده شما استفاده شوند. همیشه از جای‌دار ? در دستورات SQL خود برای جایگذاری پویای مقادیر از برنامه پایتون استفاده کنید. یک تاپل از مقادیر را به عنوان آرگومان دوم به Cursor.execute() پاس دهید تا مقادیر شما به صورت ایمن به دستور SQL متصل شوند.

مرحله ۴ — تغییر داده‌ها در پایگاه داده SQLite

ردیف‌های یک پایگاه داده SQLite را می‌توان با استفاده از دستورات UPDATE و DELETE در SQL تغییر داد.

به عنوان مثال، فرض کنید کوسه سامی به تانک شماره 2 منتقل شده است. می‌توانیم ردیف سامی را در جدول fish تغییر دهیم تا این تغییر را منعکس کند:

python

new_tank_number = 2

moved_fish_name = “Sammy”

cursor.execute(

“UPDATE fish SET tank_number = ? WHERE name = ?”,

(new_tank_number, moved_fish_name)

)

ما یک دستور UPDATE صادر می‌کنیم تا tank_number سامی را به مقدار جدید یعنی 2 تغییر دهیم. بند WHERE در دستور UPDATE تضمین می‌کند که فقط در صورتی مقدار tank_number را تغییر دهیم که ردیف دارای name = "Sammy" باشد.

اگر دستور SELECT زیر را اجرا کنیم، می‌توانیم صحت به‌روزرسانی خود را تایید کنیم:

python

rows = cursor.execute(“SELECT name, species, tank_number FROM fish”).fetchall()

print(rows)

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

text

Output

[(‘Sammy’, ‘shark’, 2), (‘Jamie’, ‘cuttlefish’, 7)]

توجه کنید که ردیف مربوط به سامی اکنون دارای مقدار 2 برای ستون tank_number است.

حالا فرض کنید کوسه سامی به طبیعت رها شده است و دیگر در آکواریوم نگهداری نمی‌شود. از آنجایی که سامی دیگر در آکواریوم زندگی نمی‌کند، منطقی است که ردیف مربوط به او را از جدول fish حذف کنیم.

برای حذف یک ردیف، دستور DELETE را صادر کنید:

python

released_fish_name = “Sammy”

cursor.execute(

“DELETE FROM fish WHERE name = ?”,

(released_fish_name,)

)

ما یک دستور DELETE صادر می‌کنیم تا ردیف مربوط به کوسه سامی را حذف کنیم. بند WHERE در دستور DELETE تضمین می‌کند که فقط ردیفی حذف شود که دارای name = "Sammy" باشد.

اگر دستور SELECT زیر را اجرا کنیم، می‌توانیم صحت حذف خود را تایید کنیم:

python

rows = cursor.execute(“SELECT name, species, tank_number FROM fish”).fetchall()

print(rows)

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

text

Output

[(‘Jamie’, ‘cuttlefish’, 7)]

همانطور که می‌بینید، ردیف مربوط به کوسه سامی حذف شده است و فقط ماهی مرکب جیمی باقی مانده است.

مرحله ۵ — استفاده از دستورات `with` برای پاکسازی خودکار

در این آموزش، ما از دو شیء اصلی برای تعامل با پایگاه داده SQLite "aquarium.db" استفاده کرده‌ایم: یک شیء Connection به نام connection و یک شیء Cursor به نام cursor.

همانطور که فایل‌های پایتون باید پس از اتمام کار با آن‌ها بسته شوند، اشیاء Connection و Cursor نیز باید زمانی که دیگر به آن‌ها نیاز نیست، بسته شوند.

می‌توانیم از دستور with برای کمک به بسته شدن خودکار اشیاء Connection و Cursor استفاده کنیم:

python

from contextlib import closing

with closing(sqlite3.connect(“aquarium.db”)) as connection:

with closing(connection.cursor()) as cursor:

rows = cursor.execute(“SELECT 1”).fetchall()

print(rows)

تابع closing یک تابع کاربردی است که توسط ماژول contextlib ارائه می‌شود. هنگامی که یک بلوک with به پایان می‌رسد، closing تضمین می‌کند که متد close() روی هر شیئی که به آن پاس داده شده است فراخوانی شود. در این مثال، از تابع closing دو بار استفاده شده است. یک بار برای اطمینان از بسته شدن خودکار شیء Connection و بار دوم برای اطمینان از بسته شدن خودکار شیء Cursor.

اگر این کد را اجرا کنیم، خروجی زیر را مشاهده خواهیم کرد:

text

Output

[(1,)]

از آنجایی که "SELECT 1" یک دستور SQL است که همیشه یک ردیف با یک ستون و مقدار 1 را برمی‌گرداند، دیدن یک تاپل تک‌عضوی با مقدار 1 در خروجی کاملاً منطقی است.

نتیجه‌گیری ماژول sqlite3 بخش قدرتمندی از کتابخانه استاندارد پایتون است؛ این ماژول به ما اجازه می‌دهد بدون نصب هیچ نرم‌افزار اضافی، با یک پایگاه داده SQL کامل روی دیسک کار کنیم.

در این آموزش، یاد گرفتیم که چگونه از ماژول sqlite3 برای اتصال به یک پایگاه داده SQLite، افزودن داده‌ها به آن، و همچنین خواندن و تغییر داده‌ها در آن پایگاه داده استفاده کنیم. در این مسیر، ما همچنین با خطرات حملات تزریق SQL و نحوه استفاده از contextlib.closing برای فراخوانی خودکار متد close() روی اشیاء پایتون در بلوک‌های with آشنا شدیم.