آموزش کار با mysql در پایتون

امیرحسین بیگدلو 12 ماه قبل

MySQL یکی از محبوب ترین سیستم های مدیریت پایگاه داده (DBMS) در بازار امروز است. در رتبه بندی موتورهای DB امسال تنها پس از Oracle DBMS در رتبه دوم قرار گرفت. از آنجایی که اکثر برنامه های نرم افزاری نیاز به تعامل با داده ها به شکلی دارند، زبان های برنامه نویسی مانند پایتون ابزارهایی را برای ذخیره و دسترسی به این منابع داده ارائه می دهند.

با استفاده از تکنیک های مورد بحث در این آموزش، می توانید به طور موثر یک پایگاه داده MySQL را با برنامه پایتون ادغام کنید. شما یک پایگاه داده کوچک MySQL برای یک سیستم رتبه بندی فیلم ایجاد می کنید و یاد می گیرید که چگونه آن را مستقیماً از کد پایتون خود پرس و جو کنید.

برای استفاده حداکثری از این آموزش، باید از مفاهیم پایتون مانند حلقه‌ها، توابع، مدیریت استثنا و نصب بسته‌های پایتون با استفاده از پیپ، دانش کافی داشته باشید. همچنین باید درک اولیه ای از سیستم های مدیریت پایگاه داده رابطه ای و پرس و جوهای SQL مانند SELECT، DROP، CREATE و JOIN داشته باشید.

دوره پیشنهادی: دوره آموزش پایتون (python)

# مقایسه mysql با بقیه دیتابیس‌های رابطه‌ای

SQL مخفف Structured Query Language و یک زبان برنامه نویسی پرکاربرد برای مدیریت پایگاه داده های رابطه ای است. ممکن است در مورد طعم های مختلف DBMS های مبتنی بر SQL شنیده باشید. محبوب ترین آنها عبارتند از MySQL، PostgreSQL، SQLite و SQL Server. همه این پایگاه های داده با استانداردهای SQL اما با درجات مختلفی از انطباق مطابقت دارند.

MySQL از زمان آغاز به کار خود در سال 1995 منبع باز بودن و به سرعت به یک رهبر بازار در میان راه حل های SQL تبدیل شد. MySQL نیز بخشی از اکوسیستم اوراکل است. در حالی که عملکرد اصلی آن کاملا رایگان است، برخی از افزونه های پولی نیز وجود دارد. در حال حاضر، MySQL توسط تمام شرکت های فناوری بزرگ، از جمله گوگل، لینکدین، اوبر، نتفلیکس، توییتر و دیگران استفاده می شود.

به غیر از یک جامعه بزرگ منبع باز برای پشتیبانی، دلایل زیادی برای موفقیت MySQL وجود دارد:

1. راحتی در نصب: MySQL طوری طراحی شده است که کاربر پسند باشد. راه‌اندازی یک پایگاه داده MySQL بسیار ساده است و چندین ابزار شخص ثالث به طور گسترده در دسترس، مانند phpMyAdmin، فرآیند راه‌اندازی را ساده‌تر می‌کنند. MySQL برای تمام سیستم عامل های اصلی از جمله ویندوز، macOS، لینوکس و سولاریس در دسترس است.

2. سرعت: MySQL به عنوان یک راه حل پایگاه داده بسیار سریع شهرت دارد. ردپای نسبتاً کوچکتری دارد و در دراز مدت بسیار مقیاس پذیر است.

3. امتیازات و امنیت کاربر: MySQL با یک اسکریپت ارائه می شود که به شما امکان می دهد سطح امنیتی رمز عبور را تنظیم کنید، پسوردهای مدیریت را تعیین کنید، و امتیازات حساب کاربری را اضافه و حذف کنید. این اسکریپت فرآیند مدیریت یک پورتال مدیریت کاربر میزبان وب را بدون پیچیدگی می کند. سایر DBMS ها مانند PostgreSQL از فایل های پیکربندی استفاده می کنند که استفاده از آنها پیچیده تر است.

در حالی که MySQL به دلیل سرعت و سهولت استفاده مشهور است، می توانید ویژگی های پیشرفته تری را با PostgreSQL دریافت کنید. همچنین، MySQL به طور کامل با SQL سازگار نیست و دارای محدودیت های عملکردی خاصی است، مانند عدم پشتیبانی از بندهای FULL JOIN.

همچنین ممکن است در هنگام خواندن و نوشتن همزمان در MySQL با مشکلاتی مواجه شوید. اگر نرم افزار شما کاربران زیادی دارد که به طور همزمان داده ها را روی آن می نویسند، PostgreSQL ممکن است انتخاب مناسب تری باشد.

SQL Server همچنین یک DBMS بسیار محبوب است و به دلیل قابلیت اطمینان، کارایی و امنیت شناخته شده است. این توسط شرکت ها ترجیح داده می شود، به خصوص در حوزه بانکی، که به طور منظم با حجم کاری ترافیک زیادی سروکار دارند. این یک راه حل تجاری است و یکی از سیستم هایی است که بیشتر با سرویس های ویندوز سازگار است.

در سال 2010، زمانی که اوراکل Sun Microsystems و MySQL را خریداری کرد، بسیاری نگران آینده MySQL بودند. در آن زمان، اوراکل بزرگترین رقیب MySQL بود. توسعه دهندگان می ترسیدند که این یک تصرف خصمانه از Oracle با هدف از بین بردن MySQL باشد.

چندین توسعه دهنده به رهبری مایکل ویدنیوس، نویسنده اصلی MySQL، یک فورک از پایگاه کد MySQL ایجاد کردند و پایه و اساس MariaDB را گذاشتند. هدف این بود که دسترسی امن به MySQL و رایگان نگه داشتن آن برای همیشه باشد.

تا به امروز، MariaDB به طور کامل دارای مجوز GPL است و آن را کاملاً در دامنه عمومی نگه می دارد. از سوی دیگر، برخی از ویژگی های MySQL فقط با مجوزهای پولی در دسترس هستند. همچنین، MariaDB چندین ویژگی بسیار مفید را ارائه می دهد که توسط سرور MySQL پشتیبانی نمی شوند، مانند SQL توزیع شده و ذخیره سازی ستونی. می‌توانید تفاوت‌های بیشتری بین MySQL و MariaDB که در وب‌سایت MariaDB فهرست شده است بیابید.

MySQL از نحو بسیار مشابهی با استاندارد SQL استفاده می کند. با این حال، تفاوت های قابل توجهی در اسناد رسمی ذکر شده است.

ویدیو پیشنهادی: ویدیو آموزش اتصال به sqlite در پایتون

# نصب سرور mysql و رابط پایتون

اکنون، برای شروع کار از طریق این آموزش، باید دو چیز را راه اندازی کنید: یک سرور MySQL و یک رابط MySQL. سرور MySQL تمام خدمات مورد نیاز برای مدیریت پایگاه داده شما را ارائه می دهد. هنگامی که سرور راه اندازی و اجرا می شود، می توانید برنامه پایتون خود را با استفاده از MySQL Connector/Python به آن متصل کنید.

+ نصب سرور mysql

اسناد رسمی روش توصیه شده برای دانلود و نصب سرور MySQL را شرح می دهد. دستورالعمل‌هایی را برای همه سیستم‌عامل‌های محبوب، از جمله Windows، macOS، Solaris، Linux و بسیاری دیگر پیدا خواهید کرد.

برای ویندوز، بهترین راه این است که MySQL Installer را دانلود کنید و اجازه دهید تمام مراحل را به عهده بگیرد. مدیر نصب همچنین به شما کمک می کند تا تنظیمات امنیتی سرور MySQL را پیکربندی کنید. در صفحه حساب‌ها و نقش‌ها، باید یک رمز عبور برای حساب ریشه (admin) وارد کنید و همچنین به صورت اختیاری کاربران دیگری با امتیازات مختلف را اضافه کنید:

نصب mysql در ویندوز

در حالی که باید اعتبار حساب ریشه را در حین راه اندازی مشخص کنید، می توانید بعداً این تنظیمات را تغییر دهید.

اگرچه برای این آموزش فقط به سرور MySQL نیاز دارید، اما می توانید ابزارهای مفید دیگری مانند MySQL Workbench را نیز با استفاده از این نصب کننده ها راه اندازی کنید. اگر نمی خواهید MySQL را مستقیماً در سیستم عامل خود نصب کنید، پس استقرار MySQL در لینوکس با Docker یک جایگزین مناسب است.

+ نصب سرور mysql Connector/Python

درایور پایگاه داده(database driver) نرم افزاری است که به یک برنامه کاربردی اجازه می دهد تا با یک سیستم پایگاه داده ارتباط برقرار کند. زبان های برنامه نویسی مانند پایتون قبل از اینکه بتوانند با یک پایگاه داده از یک فروشنده خاص صحبت کنند به یک درایور خاص نیاز دارند.

این درایورها معمولاً به عنوان ماژول های شخص ثالث به دست می آیند. API پایگاه داده پایتون (DB-API) رابط استانداردی را تعریف می کند که همه درایورهای پایگاه داده پایتون باید با آن مطابقت داشته باشند. این جزئیات در PEP 249 مستند شده است. همه درایورهای پایگاه داده Python، مانند sqlite3 برای SQLite و psycopg برای PostgreSQL، و MySQL Connector/Python برای MySQL، از این قوانین پیاده سازی پیروی می کنند.

بسیاری از زبان های برنامه نویسی محبوب API پایگاه داده خود را دارند. به عنوان مثال، جاوا دارای API اتصال به پایگاه داده جاوا (JDBC) است. اگر نیاز به اتصال یک برنامه جاوا به پایگاه داده MySQL دارید، باید از کانکتور MySQL JDBC استفاده کنید که از JDBC API پیروی می کند.

به طور مشابه، در پایتون برای تعامل با پایگاه داده MySQL باید یک رابط Python MySQL نصب کنید. بسیاری از بسته ها از استانداردهای DB-API پیروی می کنند، اما محبوب ترین آنها MySQL Connector/Python است. شما می توانید آن را با pip دریافت کنید:

$ pip install mysql-connector-python

pip کانکتور را به عنوان یک ماژول شخص ثالث در محیط مجازی فعال فعلی نصب می کند. توصیه می شود یک محیط مجازی مجزا برای پروژه به همراه تمام وابستگی ها راه اندازی کنید.

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

>>> import mysql.connector

اگر کد بالا بدون خطا اجرا شود، mysql.connector نصب شده و آماده استفاده است. اگر با خطا مواجه شدید، مطمئن شوید که در محیط مجازی صحیح هستید و از مفسر پایتون مناسب استفاده می کنید.

مطمئن شوید که بسته صحیح mysql-connector-python را نصب کرده اید، که یک پیاده سازی خالص پایتون است. مراقب کانکتورهایی با نام مشابه اما اکنون مستهلک شده مانند mysql-connector باشید.

مقاله مرتبط: معرفی کتابخانه‌های sql پایتون

# برقراری ارتباط با mysql در پایتون

MySQL یک سیستم مدیریت پایگاه داده مبتنی بر سرور است. یک سرور ممکن است شامل چندین پایگاه داده باشد. برای تعامل با پایگاه داده، ابتدا باید با سرور ارتباط برقرار کنید. گردش کار کلی یک برنامه پایتون که با پایگاه داده مبتنی بر MySQL در تعامل است به شرح زیر است:

به سرور MySQL متصل شوید.
یک پایگاه داده جدید ایجاد کنید.
به پایگاه داده جدید یا یک پایگاه داده موجود متصل شوید.
پرس و جوی SQL را اجرا کنید و نتایج را واکشی کنید.
در صورت ایجاد هرگونه تغییر در جدول به پایگاه داده اطلاع دهید.
اتصال به سرور MySQL را ببندید.

این یک گردش کار عمومی است که ممکن است بسته به برنامه فردی متفاوت باشد. اما برنامه کاربردی هر چه باشد، اولین قدم این است که پایگاه داده خود را با برنامه خود متصل کنید.

+ ایجاد اتصال

اولین قدم در تعامل با سرور MySQL، ایجاد یک اتصال است. برای انجام این کار، به connect() از ماژول mysql.connector نیاز دارید. این تابع پارامترهایی مانند میزبان، کاربر و رمز عبور را می گیرد و یک شی MySQLConnection را برمی گرداند. می‌توانید این اعتبارنامه‌ها را به‌عنوان ورودی از کاربر دریافت کنید و آن‌ها را برای connect() استفاده کنید:

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
    ) as connection:
        print(connection)
except Error as e:
    print(e)

کد بالا از اعتبارنامه های login وارد شده برای ایجاد ارتباط با سرور MySQL شما استفاده می کند. در عوض، یک شی MySQLConnection دریافت می کنید که در متغیر connect ذخیره می شود. از این پس، از این متغیر برای دسترسی به سرور MySQL خود استفاده خواهید کرد.

چند نکته مهم در کد بالا وجود دارد که باید به آنها توجه کنید:

همیشه باید با استثناهایی که ممکن است در هنگام برقراری ارتباط با سرور MySQL مطرح شود، مقابله کنید. به همین دلیل است که برای گرفتن و چاپ هر استثنایی که ممکن است با آن مواجه شوید، از یک بلوک try...except استفاده می کنید.

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

شما هرگز نباید اعتبار ورود به سیستم خود، یعنی نام کاربری و رمز عبور خود را مستقیماً در یک اسکریپت پایتون کدگذاری کنید. این یک عمل بد برای استقرار است و یک تهدید امنیتی جدی است. کد بالا از کاربر درخواست می کند تا اعتبار ورود به سیستم را دریافت کند. از ماژول getpass داخلی برای مخفی کردن رمز عبور استفاده می کند. در حالی که این بهتر از کدگذاری سخت است، روش‌های امن‌تر دیگری برای ذخیره اطلاعات حساس مانند استفاده از متغیرهای محیطی وجود دارد.

شما اکنون یک ارتباط بین برنامه خود و سرور MySQL خود برقرار کرده اید، اما همچنان باید یک پایگاه داده جدید ایجاد کنید یا به یک پایگاه داده موجود در داخل سرور متصل شوید.

مقاله پیشنهادی: مدیریت خطای KeyError در پایتون

+ ساخت یک دیتابیس جدید

در بخش قبل، شما با سرور MySQL خود ارتباط برقرار کردید. برای ایجاد یک پایگاه داده جدید، باید یک دستور SQL را اجرا کنید:

CREATE DATABASE books_db;

عبارت فوق یک پایگاه داده جدید با نام books_db ایجاد می کند.

برای اجرای یک کوئری SQL در پایتون، باید از cursor استفاده کنید که دسترسی به رکوردهای پایگاه داده را انتزاعی می کند. MySQL Connector/Python کلاس MySQLCursor را در اختیار شما قرار می دهد که اشیایی را که می توانند پرس و جوهای MySQL را در پایتون اجرا کنند نمونه می کند. به یک نمونه از کلاس MySQLCursor، مکان نما نیز گفته می شود.

اشیاء مکان نما از یک شی MySQLConnection برای تعامل با سرور MySQL شما استفاده می کنند. برای ایجاد مکان نما، از متد .cursor() متغیر connect خود استفاده کنید:

cursor = connection.cursor()

کد بالا نمونه ای از کلاس MySQLCursor را به شما می دهد.

یک کوئری که باید اجرا شود با فرمت رشته ای به ()cursor.execute ارسال می شود. در این موقعیت خاص، کوئری CREATE DATABASE را به cursor.execute():

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
    ) as connection:
        create_db_query = "CREATE DATABASE online_movie_rating"
        with connection.cursor() as cursor:
            cursor.execute(create_db_query)
except Error as e:
    print(e)

پس از اجرای کد بالا، یک پایگاه داده جدید به نام online_movie_rating در سرور MySQL خود خواهید داشت.

پرس و جو CREATE DATABASE به عنوان یک رشته در متغیر create_db_query ذخیره می شود و سپس برای اجرا به cursor.execute() ارسال می شود. کد از یک context manager با شی cursor برای مدیریت فرآیند پاکسازی استفاده می کند.

اگر پایگاه داده ای با همین نام از قبل در سرور شما وجود داشته باشد، ممکن است در اینجا خطایی دریافت کنید. برای تایید این موضوع می توانید نام تمامی پایگاه های داده را در سرور خود نمایش دهید. با استفاده از همان شی MySQLConnection قبلی، دستور SHOW DATABASES را اجرا کنید:

>>> show_db_query = "SHOW DATABASES"
>>> with connection.cursor() as cursor:
...     cursor.execute(show_db_query)
...     for db in cursor:
...         print(db)
...
('information_schema',)
('mysql',)
('online_movie_rating',)
('performance_schema',)
('sys',)

کد بالا نام تمام پایگاه های داده موجود در سرور MySQL شما را چاپ می کند. دستور SHOW DATABASES همچنین برخی از پایگاه های داده را که شما در سرور خود ایجاد نکرده اید مانند information_schema، performance_schema و غیره خروجی می دهد. این پایگاه های داده به طور خودکار توسط سرور MySQL تولید می شوند و دسترسی به انواع متادیتای پایگاه داده و تنظیمات سرور MySQL را فراهم می کنند.

شما با اجرای دستور CREATE DATABASE یک پایگاه داده جدید در این بخش ایجاد کردید. در بخش بعدی، نحوه اتصال به پایگاه داده ای که از قبل وجود دارد را خواهید دید.

مقاله پیشنهادی: مستند سازی کد پایتون

+ اتصال به یک دیتابیس موجود

در قسمت آخر یک پایگاه داده جدید به نام online_movie_rating ایجاد کردید. با این حال، شما هنوز به آن متصل نشده اید. در بسیاری از موقعیت ها، شما قبلاً یک پایگاه داده MySQL دارید که می خواهید با برنامه پایتون خود متصل شوید.

می توانید این کار را با استفاده از همان تابع connect() که قبلاً استفاده کرده بودید با ارسال یک پارامتر اضافی به نام پایگاه داده انجام دهید:

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating",
    ) as connection:
        print(connection)
except Error as e:
    print(e)

کد بالا بسیار شبیه به اسکریپت اتصالی است که قبلا استفاده کردید. تنها تغییر در اینجا یک پارامتر پایگاه داده اضافی است که نام پایگاه داده شما به connect() ارسال می شود. پس از اجرای این اسکریپت، به پایگاه داده online_movie_rating متصل خواهید شد.

# ساخت، تغییر و حذف جداول دیتابیس

در این بخش، نحوه اجرای برخی پرس‌و‌جوهای اولیه DDL مانند CREATE، DROP و ALTER را با پایتون خواهید آموخت. نگاهی سریع به پایگاه داده MySQL خواهید داشت که در ادامه این آموزش از آن استفاده خواهید کرد. شما همچنین تمام جداول مورد نیاز برای پایگاه داده را ایجاد خواهید کرد و نحوه انجام تغییرات در این جداول را در آینده یاد خواهید گرفت.

+ ساخت اسکیما دیتابیس

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

1. movies: حاوی اطلاعات کلی درباره فیلم ها و دارای ویژگی های زیر است:

id
title
release_year
genre
collection_in_mil

2. reviewer: حاوی اطلاعاتی درباره افرادی است که نظرات یا رتبه‌بندی‌ها را ارسال کرده‌اند و دارای ویژگی‌های زیر است:

id
first_name
last_name

3. ratings: حاوی اطلاعاتی در مورد رتبه بندی هایی است که ارسال شده است و دارای ویژگی های زیر است:

movie_id
reviewer_id
rating

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

ساخت طرحواره دیتابیس

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

ویدیو پیشنهادی: ویدیو آموزش context manager در پایتون

+ ساخت جدول با دستور CREATE TABLE

حال برای ایجاد جدول جدید در MySQL باید از عبارت CREATE TABLE استفاده کنید. پرس و جو MySQL زیر جدول فیلم ها را برای پایگاه داده online_movie_rating شما ایجاد می کند:

CREATE TABLE movies(
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100),
    release_year YEAR(4),
    genre VARCHAR(100),
    collection_in_mil INT
);

اگر قبلاً به عبارات SQL نگاه کرده اید، ممکن است اکثر پرس و جوهای بالا منطقی باشند. اما تفاوت هایی در نحو MySQL وجود دارد که باید از آنها آگاه باشید.

به عنوان مثال، MySQL انواع مختلفی از انواع داده را برای مطالعه شما دارد، از جمله YEAR، INT، BIGINT و غیره. همچنین، MySQL زمانی از کلمه کلیدی AUTO_INCREMENT استفاده می کند که یک مقدار ستون باید به طور خودکار در درج رکوردهای جدید افزایش یابد.

برای ایجاد یک جدول جدید، باید این پرس و جو را به cursor.execute() ارسال کنید، که یک کوئری MySQL را می پذیرد و پرس و جو را در پایگاه داده MySQL متصل اجرا می کند:

create_movies_table_query = """
CREATE TABLE movies(
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100),
    release_year YEAR(4),
    genre VARCHAR(100),
    collection_in_mil INT
)
"""
with connection.cursor() as cursor:
    cursor.execute(create_movies_table_query)
    connection.commit()

اکنون جدول فیلم ها را در پایگاه داده خود دارید. شما create_movies_table_query را به cursor.execute()، که اجرای مورد نیاز را انجام می دهد، ارسال می کنید.

همچنین به عبارت connection.commit() در انتهای کد توجه کنید. به طور پیش فرض، رابط MySQL شما تراکنش ها را به صورت خودکار انجام نمی دهد. در MySQL، تغییرات ذکر شده در یک تراکنش تنها زمانی رخ می دهد که در پایان از یک دستور COMMIT استفاده کنید. همیشه این متد را بعد از هر تراکنش فراخوانی کنید تا تغییرات در جدول واقعی انجام شود.

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

create_reviewers_table_query = """
CREATE TABLE reviewers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100)
)
"""
with connection.cursor() as cursor:
    cursor.execute(create_reviewers_table_query)
    connection.commit()

در صورت نیاز، می‌توانید اطلاعات بیشتری در مورد یک reviewer، مانند شناسه ایمیل یا اطلاعات جمعیت‌شناختی او اضافه کنید. اما first_name و last_name در حال حاضر هدف شما را برآورده خواهند کرد.

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

create_ratings_table_query = """
CREATE TABLE ratings (
    movie_id INT,
    reviewer_id INT,
    rating DECIMAL(2,1),
    FOREIGN KEY(movie_id) REFERENCES movies(id),
    FOREIGN KEY(reviewer_id) REFERENCES reviewers(id),
    PRIMARY KEY(movie_id, reviewer_id)
)
"""
with connection.cursor() as cursor:
    cursor.execute(create_ratings_table_query)
    connection.commit()

اجرای روابط کلید خارجی در MySQL در مقایسه با SQL استاندارد کمی متفاوت و محدود است. در MySQL، هم والد و هم فرزند در محدودیت کلید خارجی باید از یک موتور ذخیره سازی استفاده کنند.

storage engine جزء نرم افزاری زیربنایی است که سیستم مدیریت پایگاه داده برای انجام عملیات SQL از آن استفاده می کند. در MySQL، موتورهای ذخیره سازی در دو نوع مختلف ارائه می شوند:

1. Transactional storage engines: تراکنش امن هستند و به شما امکان می دهند با استفاده از دستورات ساده ای مانند roll back، تراکنش ها را به عقب برگردانید. بسیاری از موتورهای محبوب MySQL از جمله InnoDB و NDB به این دسته تعلق دارند.

2. Nontransactional storage engines: برای خنثی سازی عبارات تعهد شده در پایگاه داده، به کد دستی دقیق وابسته است. MyISAM، MEMORY و بسیاری دیگر از موتورهای MySQL غیرمعامله ای هستند

InnoDB پیش فرض و محبوب ترین موتور ذخیره سازی است. با پشتیبانی از محدودیت های کلید خارجی به حفظ یکپارچگی داده ها کمک می کند. این بدان معنی است که هر عملیات CRUD روی یک کلید خارجی بررسی می شود تا اطمینان حاصل شود که منجر به ناسازگاری در جداول مختلف نمی شود.

همچنین، توجه داشته باشید که جدول ratings از ستون‌های movie_id و reviewer_id، هر دو کلید خارجی، به‌عنوان کلید اصلی استفاده می‌کند. این مرحله تضمین می‌کند که reviewer نمی‌تواند به یک فیلم دوبار امتیاز دهد.

می توانید انتخاب کنید که از یک cursor برای چندین اجرا، استفاده مجدد کنید. در آن صورت، همه اجراها به جای چند تراکنش جداگانه، به یک تراکنش اتمی تبدیل می شوند. به عنوان مثال، می توانید تمام دستورات CREATE TABLE را با یک مکان نما اجرا کنید و سپس تراکنش خود را تنها یک بار انجام دهید:

with connection.cursor() as cursor:
    cursor.execute(create_movies_table_query)
    cursor.execute(create_reviewers_table_query)
    cursor.execute(create_ratings_table_query)
    connection.commit()

کد بالا ابتدا هر سه دستور CREATE را اجرا می کند. سپس یک دستور COMMIT را به سرور MySQL ارسال می کند که تراکنش شما را انجام می دهد. همچنین می توانید از .rollback() برای ارسال یک دستور ROLLBACK به سرور MySQL و حذف تمام تغییرات داده ها از تراکنش استفاده کنید.

دوره پیشنهادی: دوره آموزش زبان SQL

+ نمایش اسکیما با دستور DESCRIB

اکنون که هر سه جدول را ایجاد کرده اید، می توانید با استفاده از عبارت SQL زیر به طرح آنها نگاه کنید:

DESCRIBE <table_name>;

برای بازگرداندن برخی نتایج از شی cursor، باید از cursor.fetchall() استفاده کنید. این متد تمام سطرها را از آخرین دستور اجرا شده واکشی می کند. با فرض اینکه قبلاً شی MySQLConnection را در متغیر اتصال دارید، می توانید تمام نتایج واکشی شده توسط cursor.fetchall() را ببینید:

>>> show_table_query = "DESCRIBE movies"
>>> with connection.cursor() as cursor:
...     cursor.execute(show_table_query)
...     # Fetch rows from last executed query
...     result = cursor.fetchall()
...     for row in result:
...         print(row)
...
('id', 'int(11)', 'NO', 'PRI', None, 'auto_increment')
('title', 'varchar(100)', 'YES', '', None, '')
('release_year', 'year(4)', 'YES', '', None, '')
('genre', 'varchar(100)', 'YES', '', None, '')
('collection_in_mil', 'int(11)', 'YES', '', None, '')

پس از اجرای کد بالا، باید یک جدول حاوی اطلاعات مربوط به تمام ستون های جدول movies دریافت کنید. برای هر ستون، جزئیاتی مانند نوع داده ستون، کلید اصلی بودن ستون و غیره را دریافت خواهید کرد.

+ تغییر اسکیما با دستور ALTER

در جدول فیلم ها، ستونی به نام collection_in_mil دارید که شامل مجموعه باکس آفیس یک فیلم به میلیون دلار است. می‌توانید عبارت MySQL زیر را برای تغییر نوع داده صفت collection_in_mil از INT به DECIMAL بنویسید:

ALTER TABLE movies MODIFY COLUMN collection_in_mil DECIMAL(4,1);

DECIMAL(4,1) به معنای عدد اعشاری است که حداکثر می تواند 4 رقم داشته باشد که 1 رقم آن اعشاری است مانند 120.1، 3.4، 38.0 و غیره. پس از اجرای دستور ALTER TABLE، می توانید طرح جدول به روز شده را با استفاده از DESCRIBE نشان دهید:

>>> alter_table_query = """
... ALTER TABLE movies
... MODIFY COLUMN collection_in_mil DECIMAL(4,1)
... """
>>> show_table_query = "DESCRIBE movies"
>>> with connection.cursor() as cursor:
...     cursor.execute(alter_table_query)
...     cursor.execute(show_table_query)
...     # Fetch rows from last executed query
...     result = cursor.fetchall()
...     print("Movie Table Schema after alteration:")
...     for row in result:
...         print(row)
...
Movie Table Schema after alteration
('id', 'int(11)', 'NO', 'PRI', None, 'auto_increment')
('title', 'varchar(100)', 'YES', '', None, '')
('release_year', 'year(4)', 'YES', '', None, '')
('genre', 'varchar(100)', 'YES', '', None, '')
('collection_in_mil', 'decimal(4,1)', 'YES', '', None, '')

همانطور که در خروجی نشان داده شده است، صفت collection_in_mil اکنون از نوع DECIMAL(4,1) است. همچنین توجه داشته باشید که در کد بالا، ()cursor.execute را دو بار فراخوانی می کنید. اما cursor.fetchall() سطرها را فقط از آخرین کوئری اجرا شده که show_table_query است واکشی می کند.

دوره پیشنهادی: دوره اول آموزش پروژه محور پایتون

+ حذف جدول با دستور DROP

برای حذف جدول، باید دستور DROP TABLE را در MySQL اجرا کنید. حذف جدول یک فرآیند غیرقابل برگشت است. اگر کد زیر را اجرا می‌کنید، برای استفاده از جدول ratings در بخش‌های آینده، باید دوباره کوئری CREATE TABLE را فراخوانی کنید.

برای حذف جدول رتبه‌بندی، drop_table_query را به cursor.execute():

drop_table_query = "DROP TABLE ratings"
with connection.cursor() as cursor:
    cursor.execute(drop_table_query)

اگر کد بالا را اجرا کنید، جدول ratings را با موفقیت حذف کرده اید.

# وارد کردن اطلاعات در جداول mysql با پایتون

در بخش قبل، شما سه جدول در پایگاه داده خود ایجاد کردید: movies، reveiwers و ratings. اکنون باید این جداول را با داده ها پر کنید. این بخش دو روش مختلف برای درج رکوردها در کانکتور MySQL برای پایتون را پوشش می دهد.

روش اول، .execute()، زمانی که تعداد رکوردها کم باشد و رکوردها به صورت سخت کدگذاری شوند، به خوبی کار می کند. روش دوم، executemany()، محبوب تر است و برای سناریوهای دنیای واقعی مناسب تر است.

مقاله پیشنهادی: 10 مشکل امنیتی برای پایتونی‌ها

+ استفاده از execute

رویکرد اول از همان متد cursor.execute () استفاده می‌کند که شما تا کنون استفاده کرده‌اید. شما کوئری INSERT INTO را در یک رشته می نویسید و آن را به cursor.execute(). می توانید از این روش برای درج داده ها در جدول فیلم ها استفاده کنید.

شما نیازی به اضافه کردن داده برای id ندارید زیرا AUTO_INCREMENT به طور خودکار id را برای شما محاسبه می کند. اسکریپت زیر رکوردها را در جدول فیلم ها وارد می کند:

insert_movies_query = """
INSERT INTO movies (title, release_year, genre, collection_in_mil)
VALUES
    ("Forrest Gump", 1994, "Drama", 330.2),
    ("3 Idiots", 2009, "Drama", 2.4),
    ("Eternal Sunshine of the Spotless Mind", 2004, "Drama", 34.5),
    ("Good Will Hunting", 1997, "Drama", 138.1),
    ("Skyfall", 2012, "Action", 304.6),
    ("Gladiator", 2000, "Action", 188.7),
    ("Black", 2005, "Drama", 3.0),
    ("Titanic", 1997, "Romance", 659.2),
    ("The Shawshank Redemption", 1994, "Drama",28.4),
    ("Udaan", 2010, "Drama", 1.5),
    ("Home Alone", 1990, "Comedy", 286.9),
    ("Casablanca", 1942, "Romance", 1.0),
    ("Avengers: Endgame", 2019, "Action", 858.8),
    ("Night of the Living Dead", 1968, "Horror", 2.5),
    ("The Godfather", 1972, "Crime", 135.6),
    ("Haider", 2014, "Action", 4.2),
    ("Inception", 2010, "Adventure", 293.7),
    ("Evil", 2003, "Horror", 1.3),
    ("Toy Story 4", 2019, "Animation", 434.9),
    ("Air Force One", 1997, "Drama", 138.1),
    ("The Dark Knight", 2008, "Action",535.4),
    ("Bhaag Milkha Bhaag", 2013, "Sport", 4.1),
    ("The Lion King", 1994, "Animation", 423.6),
    ("Pulp Fiction", 1994, "Crime", 108.8),
    ("Kai Po Che", 2013, "Sport", 6.0),
    ("Beasts of No Nation", 2015, "War", 1.4),
    ("Andadhun", 2018, "Thriller", 2.9),
    ("The Silence of the Lambs", 1991, "Crime", 68.2),
    ("Deadpool", 2016, "Action", 363.6),
    ("Drishyam", 2015, "Mystery", 3.0)
"""
with connection.cursor() as cursor:
    cursor.execute(insert_movies_query)
    connection.commit()

جدول فیلم ها اکنون با سی رکورد بارگذاری شده است. کد در پایان ()connection.commit را فرا می خواند. بسیار مهم است که پس از انجام هر گونه تغییر در جدول، .commit() را فراخوانی کنید.

مقاله پیشنهادی: معرفی کلمات کلیدی در پایتون

+ استفاده از executemany

روش قبلی زمانی مناسب تر است که تعداد رکوردها نسبتاً کم باشد و می توانید این رکوردها را مستقیماً در کد بنویسید. اما این به ندرت درست است. شما اغلب این داده ها را در فایل دیگری ذخیره می کنید، یا داده ها توسط یک اسکریپت دیگر تولید می شوند و باید به پایگاه داده MySQL اضافه شوند.

اینجاست که .executemany() مفید است. دو پارامتر را می پذیرد:

query: حاوی متغیرهایی برای رکوردهایی است که باید درج شوند.
list: شامل تمام رکوردهایی است که می خواهید درج کنید.

مثال زیر رکوردهایی را برای جدول reviewers درج می کند:

insert_reviewers_query = """
INSERT INTO reviewers
(first_name, last_name)
VALUES ( %s, %s )
"""
reviewers_records = [
    ("Chaitanya", "Baweja"),
    ("Mary", "Cooper"),
    ("John", "Wayne"),
    ("Thomas", "Stoneman"),
    ("Penny", "Hofstadter"),
    ("Mitchell", "Marsh"),
    ("Wyatt", "Skaggs"),
    ("Andre", "Veiga"),
    ("Sheldon", "Cooper"),
    ("Kimbra", "Masters"),
    ("Kat", "Dennings"),
    ("Bruce", "Wayne"),
    ("Domingo", "Cortes"),
    ("Rajesh", "Koothrappali"),
    ("Ben", "Glocker"),
    ("Mahinder", "Dhoni"),
    ("Akbar", "Khan"),
    ("Howard", "Wolowitz"),
    ("Pinkie", "Petit"),
    ("Gurkaran", "Singh"),
    ("Amy", "Farah Fowler"),
    ("Marlon", "Crafford"),
]
with connection.cursor() as cursor:
    cursor.executemany(insert_reviewers_query, reviewers_records)
    connection.commit()

در اسکریپت بالا، هم query و هم لیست رکوردها را به عنوان آرگومان به .executemany() ارسال میکنیم. این رکوردها می توانستند از یک فایل یا از کاربر واکشی شده باشند و در لیست reviewers_records ذخیره شوند.

به طور مشابه می توانید از executemany() برای درج رکوردها در جدول ratings استفاده کنید:

insert_ratings_query = """
INSERT INTO ratings
(rating, movie_id, reviewer_id)
VALUES ( %s, %s, %s)
"""
ratings_records = [
    (6.4, 17, 5), (5.6, 19, 1), (6.3, 22, 14), (5.1, 21, 17),
    (5.0, 5, 5), (6.5, 21, 5), (8.5, 30, 13), (9.7, 6, 4),
    (8.5, 24, 12), (9.9, 14, 9), (8.7, 26, 14), (9.9, 6, 10),
    (5.1, 30, 6), (5.4, 18, 16), (6.2, 6, 20), (7.3, 21, 19),
    (8.1, 17, 18), (5.0, 7, 2), (9.8, 23, 3), (8.0, 22, 9),
    (8.5, 11, 13), (5.0, 5, 11), (5.7, 8, 2), (7.6, 25, 19),
    (5.2, 18, 15), (9.7, 13, 3), (5.8, 18, 8), (5.8, 30, 15),
    (8.4, 21, 18), (6.2, 23, 16), (7.0, 10, 18), (9.5, 30, 20),
    (8.9, 3, 19), (6.4, 12, 2), (7.8, 12, 22), (9.9, 15, 13),
    (7.5, 20, 17), (9.0, 25, 6), (8.5, 23, 2), (5.3, 30, 17),
    (6.4, 5, 10), (8.1, 5, 21), (5.7, 22, 1), (6.3, 28, 4),
    (9.8, 13, 1)
]
with connection.cursor() as cursor:
    cursor.executemany(insert_ratings_query, ratings_records)
    connection.commit()

هر سه جدول اکنون با داده ها پر شده اند. اکنون یک پایگاه داده رتبه بندی فیلم آنلاین کاملاً کاربردی دارید. مرحله بعدی درک نحوه تعامل با این پایگاه داده است.

مقاله پیشنهادی: چطور اسکریپت های پایتون را اجرا کنیم؟

# خواندن اطلاعات از mysql در پایتون

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

+ خواندن اطلاعات با SELECT

برای بازیابی رکوردها، باید یک کوئری SELECT به ()cursor.execute ارسال کنید. سپس از cursor.fetchall() برای استخراج جدول بازیابی شده در قالب لیستی از ردیف ها یا رکوردها استفاده می کنید.

سعی کنید یک پرس و جو MySQL بنویسید تا همه رکوردها را از جدول فیلم ها انتخاب کنید و آن را به .execute():

>>> select_movies_query = "SELECT * FROM movies LIMIT 5"
>>> with connection.cursor() as cursor:
...     cursor.execute(select_movies_query)
...     result = cursor.fetchall()
...     for row in result:
...         print(row)
...
(1, 'Forrest Gump', 1994, 'Drama', Decimal('330.2'))
(2, '3 Idiots', 2009, 'Drama', Decimal('2.4'))
(3, 'Eternal Sunshine of the Spotless Mind', 2004, 'Drama', Decimal('34.5'))
(4, 'Good Will Hunting', 1997, 'Drama', Decimal('138.1'))
(5, 'Skyfall', 2012, 'Action', Decimal('304.6'))

متغیر result رکوردهای بازگردانده شده از .fetchall() را نگه می دارد. این فهرستی از تاپل ها است که رکوردهای فردی را از جدول نشان می دهد.

در پرس و جوی بالا، از عبارت LIMIT برای محدود کردن تعداد ردیف هایی که از دستور SELECT دریافت می شود استفاده می کنید. توسعه دهندگان اغلب از LIMIT برای انجام صفحه بندی در هنگام مدیریت حجم زیادی از داده ها استفاده می کنند.

در MySQL، عبارت LIMIT یک یا دو آرگومان عددی غیرمنفی می گیرد. هنگام استفاده از یک آرگومان، حداکثر تعداد ردیف هایی را که باید برگردانده شوند را مشخص می کنید. از آنجایی که درخواست شما شامل LIMIT 5 است، تنها 5 رکورد اول واکشی می شوند. هنگام استفاده از هر دو آرگومان، می توانید افست ردیف اول را نیز برای بازگشت مشخص کنید:

SELECT * FROM movies LIMIT 2,5;

آرگومان اول یک آفست 2 را مشخص می کند و آرگومان دوم تعداد ردیف های برگشتی را به 5 محدود می کند. کوئری بالا ردیف های 3 تا 7 را برمی گرداند.

همچنین می توانید برای ستون های انتخابی پرس و جو کنید:

>>> select_movies_query = "SELECT title, release_year FROM movies LIMIT 5"
>>> with connection.cursor() as cursor:
...     cursor.execute(select_movies_query)
...     for row in cursor.fetchall():
...         print(row)
...
('Forrest Gump', 1994)
('3 Idiots', 2009)
('Eternal Sunshine of the Spotless Mind', 2004)
('Good Will Hunting', 1997)
('Skyfall', 2012)

اکنون، کد فقط مقادیری را از دو ستون مشخص شده خروجی می‌دهد: title و release_year.

مقاله پیشنهادی: مدیریت حافظه در پایتون

+ فیلتر کردن اطلاعات با WHERE

با استفاده از عبارت WHERE می توانید رکوردهای جدول را با معیارهای خاص فیلتر کنید. به عنوان مثال، برای بازیابی همه فیلم‌هایی که مجموعه باکس آفیس آنها بیش از 300 میلیون دلار است، می‌توانید عبارت زیر را اجرا کنید:

SELECT title, collection_in_mil
FROM movies
WHERE collection_in_mil > 300;

همچنین می توانید از عبارت ORDER BY در آخرین پرس و جو برای مرتب سازی نتایج از بیشترین به کمترین درآمد استفاده کنید:

>>> select_movies_query = """
... SELECT title, collection_in_mil
... FROM movies
... WHERE collection_in_mil > 300
... ORDER BY collection_in_mil DESC
... """
>>> with connection.cursor() as cursor:
...     cursor.execute(select_movies_query)
...     for movie in cursor.fetchall():
...         print(movie)
...
('Avengers: Endgame', Decimal('858.8'))
('Titanic', Decimal('659.2'))
('The Dark Knight', Decimal('535.4'))
('Toy Story 4', Decimal('434.9'))
('The Lion King', Decimal('423.6'))
('Deadpool', Decimal('363.6'))
('Forrest Gump', Decimal('330.2'))
('Skyfall', Decimal('304.6'))

MySQL مجموعه ای از عملیات قالب بندی رشته ای مانند CONCAT را برای الحاق رشته ها ارائه می دهد. اغلب، وب سایت ها برای جلوگیری از سردرگمی، عنوان فیلم را همراه با سال انتشار آن نشان می دهند. برای بازیابی عناوین پنج فیلم پرفروش که با سال‌های اکران آن‌ها همراه شده‌اند، می‌توانید عبارت زیر را بنویسید:

>>> select_movies_query = """
... SELECT CONCAT(title, " (", release_year, ")"),
...       collection_in_mil
... FROM movies
... ORDER BY collection_in_mil DESC
... LIMIT 5
... """
>>> with connection.cursor() as cursor:
...     cursor.execute(select_movies_query)
...     for movie in cursor.fetchall():
...         print(movie)
...
('Avengers: Endgame (2019)', Decimal('858.8'))
('Titanic (1997)', Decimal('659.2'))
('The Dark Knight (2008)', Decimal('535.4'))
('Toy Story 4 (2019)', Decimal('434.9'))
('The Lion King (1994)', Decimal('423.6'))

اگر نمی خواهید از بند LIMIT استفاده کنید و نیازی به واکشی همه رکوردها ندارید، شی مکان نما دارای متدهای .fetchone() و .fetchmany() نیز هست:

.fetchone() یا سطر بعدی نتیجه را به صورت تاپلی بازیابی می کند یا اگر ردیف دیگری در دسترس نباشد، None را بازیابی می کند.
.fetchmany() مجموعه ردیف های بعدی را از نتیجه به عنوان لیستی از تاپل ها بازیابی می کند. این آرگومان اندازه دارد که به طور پیش فرض 1 است که می توانید از آن برای تعیین تعداد ردیف هایی که باید واکشی کنید استفاده کنید. اگر هیچ ردیف دیگری در دسترس نباشد، متد یک لیست خالی برمی گرداند.

دوباره عناوین پنج فیلم پرفروش را که با سال‌های اکران آن‌ها پیوند خورده‌اند، بازیابی کنید، اما این بار از .fetchmany():

>>> select_movies_query = """
... SELECT CONCAT(title, " (", release_year, ")"),
...       collection_in_mil
... FROM movies
... ORDER BY collection_in_mil DESC
... """
>>> with connection.cursor() as cursor:
...     cursor.execute(select_movies_query)
...     for movie in cursor.fetchmany(size=5):
...         print(movie)
...     cursor.fetchall()
...
('Avengers: Endgame (2019)', Decimal('858.8'))
('Titanic (1997)', Decimal('659.2'))
('The Dark Knight (2008)', Decimal('535.4'))
('Toy Story 4 (2019)', Decimal('434.9'))
('The Lion King (1994)', Decimal('423.6'))

خروجی با .fetchmany() مشابه چیزی است که هنگام استفاده از عبارت LIMIT دریافت کردید. ممکن است متوجه فراخوانی اضافی cursor.fetchall () در پایان شده باشید. شما این کار را برای پاک کردن تمام نتایج باقیمانده که توسط .fetchmany() خوانده نشده اند انجام می دهید.

قبل از اجرای هر دستور دیگری در همان اتصال، لازم است همه نتایج خوانده نشده را پاک کنید. در غیر این صورت، یک InternalError: Unread result ایجاد خواهد شد.

مقاله پیشنهادی: آموزش برنامه نویسی فانکشنال در پایتون

# مدیریت چند جدول با دستور JOIN

اگر پرس و جوهای بخش قبل را کاملاً ساده یافتید، نگران نباشید. می‌توانید با استفاده از همان روش‌های بخش قبل، کوئری‌های SELECT خود را به همان اندازه که می‌خواهید پیچیده کنید.

بیایید به برخی از جستارهای JOIN کمی پیچیده تر نگاه کنیم. اگر می خواهید نام پنج فیلم برتر در پایگاه داده خود را بیابید، می توانید پرس و جو زیر را اجرا کنید:

>>> select_movies_query = """
... SELECT title, AVG(rating) as average_rating
... FROM ratings
... INNER JOIN movies
...     ON movies.id = ratings.movie_id
... GROUP BY movie_id
... ORDER BY average_rating DESC
... LIMIT 5
... """
>>> with connection.cursor() as cursor:
...     cursor.execute(select_movies_query)
...     for movie in cursor.fetchall():
...         print(movie)
...
('Night of the Living Dead', Decimal('9.90000'))
('The Godfather', Decimal('9.90000'))
('Avengers: Endgame', Decimal('9.75000'))
('Eternal Sunshine of the Spotless Mind', Decimal('8.90000'))
('Beasts of No Nation', Decimal('8.70000'))

همانطور که در بالا نشان داده شد، Night of the Living Dead و The Godfather به عنوان فیلم های دارای بالاترین امتیاز در پایگاه داده online_movie_rating شما قرار دارند.

برای یافتن نام داوری که بیشترین امتیاز را داده است، عبارت زیر را بنویسید:

>>> select_movies_query = """
... SELECT CONCAT(first_name, " ", last_name), COUNT(*) as num
... FROM reviewers
... INNER JOIN ratings
...     ON reviewers.id = ratings.reviewer_id
... GROUP BY reviewer_id
... ORDER BY num DESC
... LIMIT 1
... """
>>> with connection.cursor() as cursor:
...     cursor.execute(select_movies_query)
...     for movie in cursor.fetchall():
...         print(movie)
...
('Mary Cooper', 4)

Mary Cooper بیشترین امتیاز در این پایگاه داده است. همانطور که در بالا مشاهده شد، مهم نیست که پرس و جو چقدر پیچیده باشد زیرا در نهایت توسط سرور MySQL مدیریت می شود. فرآیند شما برای اجرای یک کوئری همیشه یکسان خواهد ماند: پرس و جو را به cursor.execute() ارسال کنید و نتایج را با استفاده از .fetchall() واکشی کنید.

مقاله پیشنهادی: آموزش اتصال به mongodb با پایتون

# بروزرسانی و حذف اطلاعات در دیتابیس mysql در پایتون

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

+ دستور UPDATE

یکی از reviewers پایگاه داده شما، Amy Farah Fowler، اکنون با Sheldon Cooper ازدواج کرده است. نام خانوادگی او اکنون به Cooper تغییر کرده است، بنابراین باید پایگاه داده خود را بر این اساس به روز کنید. برای به‌روزرسانی رکوردها، MySQL از عبارت UPDATE استفاده می‌کند:

update_query = """
UPDATE
    reviewers
SET
    last_name = "Cooper"
WHERE
    first_name = "Amy"
"""
with connection.cursor() as cursor:
    cursor.execute(update_query)
    connection.commit()

کد، کوئری به روز رسانی را به cursor.execute() می فرستد و commit() تغییرات مورد نیاز را در جدول reviewers می آورد.

فرض کنید باید گزینه ای ارائه دهید که به reviewers اجازه می دهد رتبه بندی را تغییر دهند. یک reviewer سه مقدار، movie_id، reviewer_id و rating جدید را ارائه می دهد. کد پس از انجام اصلاحات مشخص شده رکورد را نمایش می دهد.

با فرض اینکه movie_id = 18، reviewer_id = 15، و rating = 5.0، می توانید از پرس و جوهای MySQL زیر برای انجام اصلاحات مورد نیاز استفاده کنید:

UPDATE
    ratings
SET
    rating = 5.0
WHERE
    movie_id = 18 AND reviewer_id = 15;

SELECT *
FROM ratings
WHERE
    movie_id = 18 AND reviewer_id = 15;

کوئری های بالا ابتدا rating را به روز می کنند و سپس نمایش می دهند. شما می توانید یک اسکریپت کامل پایتون ایجاد کنید که ارتباطی با پایگاه داده برقرار می کند و به reviewer اجازه می دهد تا یک rating را تغییر دهد:

from getpass import getpass
from mysql.connector import connect, Error

movie_id = input("Enter movie id: ")
reviewer_id = input("Enter reviewer id: ")
new_rating = input("Enter new rating: ")
update_query = """
UPDATE
    ratings
SET
    rating = "%s"
WHERE
    movie_id = "%s" AND reviewer_id = "%s";

SELECT *
FROM ratings
WHERE
    movie_id = "%s" AND reviewer_id = "%s"
""" % (
    new_rating,
    movie_id,
    reviewer_id,
    movie_id,
    reviewer_id,
)

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating",
    ) as connection:
        with connection.cursor() as cursor:
            for result in cursor.execute(update_query, multi=True):
                if result.with_rows:
                    print(result.fetchall())
            connection.commit()
except Error as e:
    print(e)

این کد را در فایلی با نام modify_ratings.py ذخیره کنید. کد بالا از %s متغیرهایی برای درج ورودی دریافتی در رشته update_query استفاده می کند. برای اولین بار در این آموزش، شما چندین پرس و جو در داخل یک رشته دارید. برای ارسال چند کوئری به یک cursor.execute()، باید آرگومان چند متد را روی True تنظیم کنید.

اگر multi برابر True باشد، cursor.execute() یک تکرار کننده برمی گرداند. هر آیتم در تکرار کننده مربوط به یک شی مکان نما است که دستور ارسال شده در پرس و جو را اجرا می کند. کد بالا یک حلقه for را روی این تکرار کننده اجرا می کند و سپس .fetchall() را روی هر شی مکان نما فراخوانی می کند.

اگر هیچ مجموعه ای از نتیجه در یک عملیات واکشی نشود، .fetchall() یک استثنا ایجاد می کند. برای جلوگیری از این خطا، در کد بالا از ویژگی cursor.with_rows استفاده می‌کنید که نشان می‌دهد آیا آخرین عملیات اجرا شده ردیف‌هایی را تولید کرده است یا خیر.

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

به عنوان مثال، اگر کاربر movie_id=18، reviewer_id=15، و رتبه جدید=5.0 را به عنوان ورودی ارسال کند، خروجی به شکل زیر است:

$ python modify_ratings.py
Enter movie id: 18
Enter reviewer id: 15
Enter new rating: 5.0
Enter username: <user_name>
Enter password:
[(18, 15, Decimal('5.0'))]

rating با movie_id=18 و reviewer_id=15 به 5.0 تغییر یافته است. اما اگر هکر بودید، ممکن است یک دستور مخفی را در ورودی خود ارسال کنید:

$ python modify_ratings.py
Enter movie id: 18
Enter reviewer id: 15"; UPDATE reviewers SET last_name = "A
Enter new rating: 5.0
Enter username: <user_name>
Enter password:
[(18, 15, Decimal('5.0'))]

باز هم خروجی نشان می دهد که رتبه مشخص شده به 5.0 تغییر کرده است. چه چیزی تغییر کرده است؟

هکر هنگام وارد شدن به reviewer_id در یک درخواست به‌روزرسانی مخفیانه نفوذ کرد. درخواست به‌روزرسانی، update reviewers set last_name = "A، نام خانوادگی همه رکوردهای جدول reviewers را به "A" تغییر می‌دهد. اگر جدول reviewers را چاپ کنید، می‌توانید این تغییر را مشاهده کنید:

>>> select_query = """
... SELECT first_name, last_name
... FROM reviewers
... """
>>> with connection.cursor() as cursor:
...     cursor.execute(select_query)
...     for reviewer in cursor.fetchall():
...         print(reviewer)
...
('Chaitanya', 'A')
('Mary', 'A')
('John', 'A')
('Thomas', 'A')
('Penny', 'A')
('Mitchell', 'A')
('Wyatt', 'A')
('Andre', 'A')
('Sheldon', 'A')
('Kimbra', 'A')
('Kat', 'A')
('Bruce', 'A')
('Domingo', 'A')
('Rajesh', 'A')
('Ben', 'A')
('Mahinder', 'A')
('Akbar', 'A')
('Howard', 'A')
('Pinkie', 'A')
('Gurkaran', 'A')
('Amy', 'A')
('Marlon', 'A')

کد بالا first_name و last_name را برای همه رکوردها در جدول reviewers نمایش می دهد. حمله تزریق SQL با تغییر نام last_name همه رکوردها به "A" این جدول را خراب کرد.

یک راه حل سریع برای جلوگیری از چنین حملاتی وجود دارد. مقادیر درخواست ارائه شده توسط کاربر را مستقیماً به رشته درخواست خود اضافه نکنید. در عوض، اسکریپت modify_ratings.py را به روز کنید تا این مقادیر پرس و جو را به عنوان آرگومان به .execute() ارسال کند:

from getpass import getpass
from mysql.connector import connect, Error

movie_id = input("Enter movie id: ")
reviewer_id = input("Enter reviewer id: ")
new_rating = input("Enter new rating: ")
update_query = """
UPDATE
    ratings
SET
    rating = %s
WHERE
    movie_id = %s AND reviewer_id = %s;

SELECT *
FROM ratings
WHERE
    movie_id = %s AND reviewer_id = %s
"""
val_tuple = (
    new_rating,
    movie_id,
    reviewer_id,
    movie_id,
    reviewer_id,
)

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating",
    ) as connection:
        with connection.cursor() as cursor:
            for result in cursor.execute(update_query, val_tuple, multi=True):
                if result.with_rows:
                    print(result.fetchall())
            connection.commit()
except Error as e:
    print(e)

توجه داشته باشید که متغیرهای %s دیگر در گیومه رشته نیستند. رشته‌هایی که به placeholders ارسال می‌شوند ممکن است حاوی نویسه‌های خاصی باشند. در صورت لزوم، اینها می توانند به درستی توسط کتابخانه زیرین غیرفعال شوند.

cursor.execute() مطمئن می شود که مقادیر تاپل دریافت شده به عنوان آرگومان از نوع داده مورد نیاز است. اگر کاربر سعی کند برخی از کاراکترهای مشکل ساز را پنهان کند، کد یک استثنا ایجاد می کند:

$ python modify_ratings.py
Enter movie id: 18
Enter reviewer id: 15"; UPDATE reviewers SET last_name = "A
Enter new rating: 5.0
Enter username: <user_name>
Enter password:
1292 (22007): Truncated incorrect DOUBLE value: '15";
UPDATE reviewers SET last_name = "A'

() cursor.execute در صورت یافتن کاراکترهای ناخواسته در ورودی کاربر، یک استثنا ایجاد می کند. هر زمان که ورودی کاربر را در یک پرس و جو وارد می کنید، باید از این رویکرد استفاده کنید.

ویدیو پیشنهادی: ویدیو آموزش ماژول hashlib در پایتون

+ دستور DELETE

حذف رکوردها بسیار شبیه به روز رسانی رکوردها عمل می کند. شما از عبارت DELETE برای حذف رکوردهای انتخاب شده استفاده می کنید.

توصیه می شود ابتدا یک کوئری SELECT را با همان فیلتر اجرا کنید تا مطمئن شوید که سوابق مناسب را حذف می کنید. به عنوان مثال، برای حذف تمام رتبه‌بندی‌های داده شده توسط reviewer_id = 2، ابتدا باید کوئری SELECT مربوطه را اجرا کنید:

>>> select_movies_query = """
... SELECT reviewer_id, movie_id FROM ratings
... WHERE reviewer_id = 2
... """
>>> with connection.cursor() as cursor:
...     cursor.execute(select_movies_query)
...     for movie in cursor.fetchall():
...         print(movie)
...
(2, 7)
(2, 8)
(2, 12)
(2, 23)

قطعه کد بالا، reviewer_id و movie_id را برای رکوردهای موجود در جدول رتبه‌بندی که در آن reviewer_id = 2 است، خروجی می‌دهد. هنگامی که تأیید کردید که اینها رکوردهایی هستند که باید حذف کنید، می‌توانید یک عبارت DELETE را با همان فیلتر اجرا کنید:

delete_query = "DELETE FROM ratings WHERE reviewer_id = 2"
with connection.cursor() as cursor:
    cursor.execute(delete_query)
    connection.commit()

با این پرس و جو، تمام رتبه بندی های داده شده توسط بازبین با reviewer_id = 2 را از جدول رتبه بندی حذف می کنید.

# نتیجه گیری

در این آموزش، نحوه استفاده از MySQL Connector/Python را برای ادغام پایگاه داده MySQL با برنامه پایتون خود مشاهده کردید. همچنین برخی از ویژگی های منحصر به فرد پایگاه داده MySQL را دیدید که آن را از سایر پایگاه های داده SQL متمایز می کند.

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