Skip to content

16. با استفاده از SGBD MySQL

Image

16.1. نصب SGBD و MySQL

برای استفاده از SGBD و MySQL، نرم‌افزار Laragon را نصب خواهیم کرد.

16.1.1. نصب Laragon

Laragon یک بسته شامل چندین نرم‌افزار است:

  • یک سرور وب آپاچی. ما از این برای نوشتن اسکریپت‌های وب با پایتون استفاده خواهیم کرد؛
  • SGBD و MySQL؛
  • زبان اسکریپتی PHP، که از آن استفاده نخواهیم کرد؛
  • یک سرور Redis که حافظه پنهان (caching) را برای برنامه‌های وب فراهم می‌کند. ما از آن استفاده نخواهیم کرد؛

Laragon را می‌توان (فوریه ۲۰۲۰) از آدرس زیر دانلود کرد:

https://laragon.org/download/

Image

Image

  • نصب [1-5] ساختار دایرکتوری زیر را ایجاد می‌کند:

Image

  • در [6]، پوشه نصب برای PHP (در این سند استفاده نشده است)؛

راه‌اندازی [Laragon] پنجره زیر را نمایش می‌دهد:

Image

  • [1]: منوی اصلی Laragon؛
  • [2]: دکمه [Start All] سرور وب آپاچی و SGBD MySQL را راه‌اندازی می‌کند؛
  • [3]: دکمه [WEB] صفحه وب [http://localhost] را نمایش می‌دهد؛
  • [4]: دکمه [Database] به شما امکان می‌دهد تا SGBD و MySQL را با استفاده از ابزار [phpMyAdmin] مدیریت کنید. شما باید ابتدا این ابزار را نصب کنید؛
  • [5]: دکمه [Terminal] یک پنجره فرمان را باز می‌کند؛
  • [6]: دکمه [Root]، کاوشگر فایل ویندوز را باز می‌کند و پوشه [<laragon>/www] را که ریشه وب‌سایت [http://localhost] است، انتخاب می‌کند. اینجا جایی است که باید برنامه‌های وب ایستا را که توسط سرور آپاچی Laragon مدیریت می‌شوند، قرار دهید؛

16.1.2. ایجاد پایگاه داده

اکنون به شما نشان خواهیم داد که چگونه با استفاده از ابزار Laragon یک پایگاه داده و یک کاربر به نام MySQL ایجاد کنید.

Image

  • پس از راه‌اندازی، Laragon [1] را می‌توان از طریق منوی [2] مدیریت کرد؛
  • در [3-5]، ابزار مدیریت [phpMyAdmin] را برای MySQL نصب کنید، اگر قبلاً نصب نشده باشد؛

Image

  • در [6]، سرور وب آپاچی به همراه SGBD و MySQL راه‌اندازی می‌شود؛
  • در [7]، سرور آپاچی راه‌اندازی می‌شود؛
  • در [8]، SGBD و MySQL راه‌اندازی می‌شوند؛

Image

  • در [8-10]، یک پایگاه داده ایجاد می‌شود و [dbpersonnes] [11] نام‌گذاری می‌شود. ما قصد داریم یک پایگاه داده از افراد بسازیم؛

Image

  • در [11]، ما پایگاه‌داده‌ای را که همین حالا ایجاد کرده‌ایم مدیریت خواهیم کرد؛

Image

  • فرآیند [Bases de données] یک درخواست وب را به URL، [http://localhost/phpmyadmin] و [12] ارسال می‌کند. سرور وب آپاچی Laragon پاسخ می‌دهد. URL و [http://localhost/phpmyadmin] بخشی از ابزار URL هستند که قبلاً نصب کردیم ([phpMyAdmin] و [5]). این ابزار به شما امکان مدیریت پایگاه‌های داده MySQL را می‌دهد؛
  • به‌طور پیش‌فرض، نام کاربری و رمز عبور مدیر پایگاه داده عبارتند از: root [13] بدون رمز عبور [14];

Image

  • در [16]، پایگاه‌داده‌ای که قبلاً ایجاد کردیم؛

Image

  • در حال حاضر ما یک پایگاه داده به نام [dbpersonnes]، [17] که خالی است، و [18] داریم؛

ما کاربری به نام [admpersonnes] با رمز عبور [nobody] ایجاد می‌کنیم که دسترسی کامل به پایگاه داده [dbpersonnes] خواهد داشت:

Image

  • در [19]، ما روی پایگاه داده [dbpersonnes] قرار داریم؛
  • در [20]، زبانه [Privileges] را انتخاب کنید؛
  • در [21-22]، می‌توانیم ببینیم که کاربر [root] دارای حقوق کامل بر پایگاه داده [dbpersonnes] است؛
  • در [23]، یک کاربر جدید ایجاد کنید؛

Image

  • در [25-26]، کاربر دارای شناسه [admdbpersonnes] خواهد بود؛
  • در [27-29]، رمز عبور آنها [nobody] خواهد بود؛
  • در [30]، phpMyAdmin نشان می‌دهد که رمز عبور بسیار ضعیف (به راحتی قابل هک شدن) است. در یک محیط تولید، ترجیح داده می‌شود که با استفاده از [31] یک رمز عبور قوی ایجاد شود؛
  • در [32] مشخص شده است که کاربر [admdbpersonnes] باید دارای حقوق کامل بر پایگاه داده [dbpersonnes] باشد؛
  • در [33]، اطلاعات ارائه‌شده اعتبارسنجی می‌شود؛

Image

  • در [35]، phpMyAdmin نشان می‌دهد که کاربر ایجاد شده است؛
  • در [36]، فرمان SQL که به پایگاه داده صادر شده است؛
  • در [37]، کاربر [admpersonnes] دارای حقوق کامل بر پایگاه داده [dbpersonnes] است؛

اکنون داریم:

  • یک پایگاه داده به نام MySQL و [dbpersonnes]؛
  • کاربری به نام [admpersonnes/nobody] که حقوق کامل بر این پایگاه داده دارد؛

16.2. نصب بسته [mysql-connector-python]

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

Image

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

Image

از آنجا که همه کانکتورها برای SGBD به یک رابط واحد پایبند هستند، معمولاً نیازی به تغییر اسکریپت پایتون نیست. در عمل، اکثر SGBD دارای یک SQL اختصاصی هستند:

  • آنها با استاندارد SQL (زبان پرس‌وجوی ساختاریافته) مطابقت دارند؛
  • اما آن را با افزونه‌های زبانی اختصاصی که به خودی خود کافی نیست، گسترش می‌دهند؛

در نتیجه، معمول است که تغییرات در SGBD مستلزم اصلاحات در SQL در اسکریپت‌ها باشد.

به طور پیش‌فرض، پایتون قابلیت مدیریت پایگاه داده MySQL را ارائه نمی‌دهد. برای این کار، باید یک پکیج دانلود کنید. چندین پکیج در دسترس وجود دارد. در اینجا، ما از پکیج [mysql-connector-python] استفاده خواهیم کرد که کانکتور رسمی شرکت Oracle، مالک MySQL، است.

این بسته در یک پنجره [Terminal] در PyCharm نصب خواهد شد:

Image

  • پوشه‌ای به نام [2] برای مراحل بعدی بی‌ربط است؛

در ترمینال، دستور [pip search MySQL] را تایپ کنید:

  • [pip] (نصب‌کنندهٔ بسته‌های پایتون) ابزاری برای نصب بسته‌های پایتون است. ابزار [pip] به مخزن حاوی بسته‌های پایتون متصل می‌شود؛
  • [search MySQL]: فهرست بسته‌هایی را که عبارت [MySQL] (بدون توجه به حروف بزرگ/کوچک) در نامشان وجود دارد، بازیابی می‌کند؛

نتایج این فرمان به شرح زیر است:


mysql (0.0.2)                                                   - Virtual package for MySQL-python
jx-mysql (3.49.20042)                                           - jx-mysql - JSON Expressions for MySQL
weibo-mysql (0.1)                                               - insert mysql
bits-mysql (1.0.3)                                              - BITS MySQL
MySQL-python (1.2.5)                                            - Python interface to MySQL
deployfish-mysql (0.2.13)                                       - Deployfish MySQL plugin
mtstat-mysql (0.7.3.3)                                          - MySQL Plugins for mtstat
bottle-mysql (0.3.1)                                            - MySQL integration for Bottle.
WintxDriver-MySQL (2.0.0-1)                                     - MySQL support for Wintx
py-mysql (1.0)                                                  - Operating Mysql for Python.
mysql-utilities (1.4.3)                                         - MySQL Utilities 1.4.3 (part of MySQL Workbench Distribution 6.0.0)
….                                        - Tool to move slices of data from one MySQL store to another
mysql-tracer (2.0.2)                                            - A MySQL client to run queries, write execution reports and export results
mysql-utils (0.0.2)                                             - A simple MySQL library including a set of utility APIs for Python database programming
mysql-connector-repackaged (0.3.1)                              - MySQL driver written in Python
dffml-source-mysql (0.0.5)                                      - DFFML Source for MySQL Protocol
mysql-connector-python (8.0.19)                                 - MySQL driver written in Python
  INSTALLED: 8.0.19 (latest)
prometheus-mysql-exporter (0.2.0)                               - MySQL query Prometheus exporter
backwork-backup-mysql (0.3.0)                                   - Backwork plug-in for MySQL backups.
django-mysql-manager (0.1.4)                                    - django-mysql-manager is a Django based management interface for MySQL users and databases.
….                                              - mysql operate

C:\Data\st-2020\dev\python\cours-2020\v-01>

تمام ماژول‌هایی که نام یا توضیحاتشان حاوی کلمه کلیدی MySQL است، فهرست شده‌اند. آن مدولی که ما استفاده خواهیم کرد (فوریه ۲۰۲۰) [mysql-connector-python]، خط ۱۷ است. برای نصب آن، دستور [pip install -U mysql-connector-python] را در ترمینال تایپ کنید:


C:\Data\st-2020\dev\python\cours-2020\v-01>pip install -U mysql-connector-python
Collecting mysql-connector-python
  Using cached mysql_connector_python-8.0.19-py2.py3-none-any.whl (355 kB)
Requirement already satisfied, skipping upgrade: protobuf==3.6.1 in c:\myprograms\python38\lib\site-packages (from mysql-connector-python) (3.6.1)
Requirement already satisfied, skipping upgrade: dnspython==1.16.0 in c:\myprograms\python38\lib\site-packages (from mysql-connector-python) (1.16.0)
Requirement already satisfied, skipping upgrade: six>=1.9 in c:\users\serge\appdata\roaming\python\python38\site-packages (from protobuf==3.6.1->mysql-connector-python) (1.14.0)
Requirement already satisfied, skipping upgrade: setuptools in c:\myprograms\python38\lib\site-packages (from protobuf==3.6.1->mysql-connector-python) (41.2.0)
Installing collected packages: mysql-connector-python
Successfully installed mysql-connector-python-8.0.19
  • خط ۱: گزینه [install -U] (U=ارتقا) آخرین نسخه بسته‌های مختلف مرتبط با بسته [mysql-connector-python] را درخواست می‌کند؛

برای اطلاع از اینکه کدام بسته‌ها در محیط پایتون ماشین ما نصب شده‌اند، دستور [pip list] را وارد کنید:


C:\Data\st-2020\dev\python\cours-2020\v-01>pip list
Package                Version
---------------------- ----------
asgiref                3.2.3
astroid                2.3.3
atomicwrites           1.3.0
attrs                  19.3.0
certifi                2019.11.28
…
MarkupSafe             1.1.1
mccabe                 0.6.1
more-itertools         8.1.0
mysql-connector-python 8.0.19
mysqlclient            1.4.6
packaging              20.0
pip                    20.0.1
pipenv                 2018.11.26
…
  • خط ۱۳: بسته [mysql-connector-python] در واقع موجود است؛

برای اطلاع از نحوهٔ استفاده از بستهٔ [mysql-connector-python] برای مدیریت پایگاه‌دادهٔ MySQL، به وب‌سایت بسته مراجعه کنید |https://dev.mysql.com/doc/connector-python/en/|. بخش بعدی مجموعه‌ای از مثال‌ها را ارائه می‌دهد.

16.3. اسکریپت [mysql_01]: اتصال به پایگاه داده MySQL – ۱

اسکریپت [mysql_01] اولین گام در استفاده از یک پایگاه داده را نشان می‌دهد. این اسکریپت به ما امکان می‌دهد تا بررسی کنیم که آیا می‌توانیم به پایگاه داده [dbpersonnes] که قبلاً ایجاد شده است متصل شویم.


# وارد کردن ماژول mysql.connector
from mysql.connector import connect, DatabaseError, InterfaceError

#اتصال به یک پایگاه داده MySql [dbpersonnes]
# هویت کاربر (admpersonnes, nobody) است
USER = "admpersonnes"
PWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"

# برویم
connexion = None
try:
    print("Connexion au SGBD MySQL en cours...")
    # ورود
    connexion = connect(host=HOST, user=USER, password=PWD, database=DATABASE)
    # ردیابی
    print(
        f"Connexion MySQL réussie à la base database={DATABASE}, host={HOST} sous l'identité user={USER}, passwd={PWD}")
except (InterfaceError, DatabaseError) as erreur:
    # نمایش خطا
    print(f"L'erreur suivante s'est produite : {erreur}")
finally:
    # در صورتی که اتصال باز شده باشد، آن را ببندید
    if connexion:
        connexion.close()

یادداشت‌ها

  • خط ۲: ما توابع و کلاس‌های خاصی را از ماژول [mysql.connector] وارد می‌کنیم؛
  • خطوط ۶–۷: اطلاعات کاربری (credentials) کاربری که متصل خواهد شد؛
  • خط ۸: ماشینی که میزبان پایگاه داده است. کانکتور MySQL به شما امکان می‌دهد با یک پایگاه دادهٔ راه دور کار کنید؛
  • خط ۹: نام پایگاه داده‌ای که می‌خواهیم به آن متصل شویم؛
  • خطوط ۱۱–۲۶: اسکریپت کاربر [admpersonnes / nobody] را به پایگاه داده [dbpersonnes] متصل می‌کند (خط ۱۶)؛
  • خطوط ۲۰–۲۶: اتصال ممکن است ناموفق باشد. بنابراین، در یک بلوک try/except/finally مدیریت می‌شود؛
  • خط 16: متد connect از ماژول [mysq.connector] پارامترهای نام‌گذاری‌شدهٔ مختلفی را می‌پذیرد:
    • user: کاربری که مالک اتصال است [admpersonnes];
    • password: رمز عبور کاربر [nobody];
    • host: ماشین برای SGBD، MySQL و [localhost]؛
    • database: پایگاه‌دادهٔ مورد اتصال. اختیاری.
  • خط ۲۰: اگر یک استثنا پرتاب شود، از نوع [DatabaseError] یا [InterfaceError] است؛
  • خطوط ۲۳–۲۶: در عبارت [finally]، اتصال بسته می‌شود؛

نتایج

1
2
3
4
5
C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/mysql/mysql_01.py
Connexion au SGBD MySQL en cours...
Connexion MySQL réussie à la base database=dbpersonnes, host=localhost sous l'identité user=admpersonnes, passwd=nobody

Process finished with exit code 0

16.4. اسکریپت [mysql_02]: اتصال به پایگاه داده MySQL - 2

در این اسکریپت جدید، اتصال به پایگاه داده در یک تابع محصور شده است:


# ماژول mysql.connector را وارد کنید
from mysql.connector import DatabaseError, InterfaceError, connect


# ---------------------------------------------------------------------------------
def connexion(host: str, database: str, login: str, pwd: str):
    # وارد می‌شود و سپس خارج می‌شود (نام کاربری، رمز عبور) از پایگاه داده [database] روی سرور [host]
    # در صورت بروز مشکل، استثنای DatabaseError را پرتاب می‌کند
    connexion = None
    try:
        #اتصال
        connexion = connect(host=host, user=login, password=pwd, database=database)
        print(
            f"Connexion réussie à la base database={database}, host={host} sous l'identité user={login}, passwd={pwd}")
    finally:
        # ارتباط در صورتی که باز شده باشد، بسته می‌شود
        if connexion:
            connexion.close()
            print("Déconnexion réussie\n")


# ---------------------------------------------- اصلی
# اطلاعات ورود
USER = "admpersonnes"
PASSWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"

# ورود برای کاربر موجود
try:
    connexion(host=HOST, login=USER, pwd=PASSWD, database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # یک خطا نمایش داده می‌شود
    print(erreur)

# ورود با کاربر ناموجود
try:
    connexion(host=HOST, login="xx", pwd="xx", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    #خطای نمایش داده شده
    print(erreur)

یادداشت‌ها:

  • خطوط ۶–۱۹: تابع [connexion] که تلاش می‌کند کاربر را به پایگاه داده [dbpersonnes] متصل و سپس قطع اتصال کند. نتیجه را نمایش می‌دهد؛
  • خطوط ۲۹–۴۱: برنامه اصلی – متد connexion را دو بار فراخوانی می‌کند و هرگونه استثنا را نمایش می‌دهد؛

نتایج

1
2
3
4
5
6
7
C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/mysql/mysql_02.py
Connexion MySQL réussie à la base database=dbpersonnes, host=localhost sous l'identité user=admpersonnes, passwd=nobody
Déconnexion MySQL réussie

1045 (28000): Access denied for user 'xx'@'localhost' (using password: YES)

Process finished with exit code 0

16.5. اسکریپت [mysql_03]: ایجاد یک جدول MySQL

اکنون که می‌دانیم چگونه یک اتصال با SGBD MySQL برقرار کنیم، می‌توانیم شروع به صدور دستورات SQL از طریق این اتصال کنیم. برای این کار، به پایگاه‌دادهٔ ایجادشده ([dbpersonnes]) متصل می‌شویم و از این اتصال برای ایجاد یک جدول در پایگاه‌داده استفاده می‌کنیم.


# وارد می‌کند
import sys

from mysql.connector import DatabaseError, InterfaceError, connect
from mysql.connector.connection import MySQLConnection


# ---------------------------------------------------------------------------------
def execute_sql(connexion: MySQLConnection, update: str):
    # یک پرس‌وجوی به‌روزرسانی را روی اتصال اجرا می‌کند
    curseur = None
    try:
        # یک نشانگر درخواست شده است
        curseur = connexion.cursor()
        # پرس‌وجوی به‌روزرسانی را روی اتصال اجرا می‌کند
        curseur.execute(update)
    finally:
        # اگر کرسر گرفته شده باشد، آن را می‌بندد
        if curseur:
            curseur.close()


# ---------------------------------------------- اصلی
# اطلاعات احراز هویت اتصال
#شناسه کاربر
ID = "admpersonnes"
PWD = "nobody"
# ماشین میزبان DBMS
HOST = "localhost"
# نام پایگاه داده
DATABASE = "dbpersonnes"

# بیایید قدم به قدم پیش برویم
try:
    # اتصال
    connexion = connect(host=HOST, user=ID, password=PWD, database=DATABASE)
    # حالت AUTOCOMMIT
    connexion.autocommit = True
except (InterfaceError, DatabaseError) as erreur:
    # نمایش خطا
    print(f"L'erreur suivante s'est produite : {erreur}")
    #خروج
    sys.exit()

# جدول «people» را اگر وجود داشته باشد حذف کنید
# اگر وجود نداشته باشد، خطایی رخ خواهد داد – این خطا نادیده گرفته می‌شود
requête = "drop table personnes"
try:
    execute_sql(connexion, requête)
except (InterfaceError, DatabaseError):
    pass

#جدول «people» را ایجاد کنید
requête = "create table personnes (id int PRIMARY KEY, prenom varchar(30) NOT NULL, nom varchar(30) NOT NULL, age integer NOT NULL, " \
          "unique(nom,prenom)) "
try:
    # اجرای پرس‌وجو
    execute_sql(connexion, requête)
    # نمایش
    print(f"{requête} : requête réussie")
except (InterfaceError, DatabaseError) as erreur:
    # نمایش خطا
    print(f"L'erreur suivante s'est produite : {erreur}")
finally:
    # خروج
    connexion.close()

یادداشت‌ها:

  • خط ۹: تابع execute_sql یک پرس‌وجو SQL را روی یک اتصال باز اجرا می‌کند؛
  • خط ۱۴: عملیات SQL روی اتصال از طریق یک شیء خاص به نام کرسر انجام می‌شود؛
  • خط ۱۴: یک نشانگر به دست می‌آید؛
  • خط 16: اجرای پرس‌وجوی SQL;
  • خطوط 17–20: صرف‌نظر از اینکه خطا رخ دهد یا خیر، کرسر بسته می‌شود. این کار منابع مرتبط با آن را آزاد می‌کند. اگر استثنا (exception) رخ دهد، در اینجا رسیدگی نمی‌شود. این استثنا به کد فراخواننده منتقل خواهد شد؛
  • خطوط ۳۳–۴۳: یک اتصال به پایگاه داده برقرار می‌شود؛
  • خط ۳۸: حالت AUTOCOMMIT=True برای یک اتصال به این معناست که هر پرس‌وجو در یک تراکنش خودکار اجرا می‌شود. حالت پیش‌فرض AUTOCOMMIT=False است، که در آن توسعه‌دهنده مسئول مدیریت تراکنش‌ها است. یک تراکنش سازوکاری است که اجرای چندین پرس‌وجو، از ۱ تا n را در بر می‌گیرد. یا همگی آن‌ها موفق می‌شوند یا هیچ‌کدام. بنابراین، اگر پرس‌وجوهای ۱ تا i موفق شوند اما پرس‌وجوی i+1 ناموفق باشد، آنگاه پرس‌وجوهای ۱ تا i «به‌ عقب بازگردانده» (rolled back) می‌شوند تا پایگاه داده به حالتی بازگردد که قبل از اجرای پرس‌وجوی ۱ در آن بود؛
  • در اینجا دو پرس‌وجو وجود دارد: SQL (خطوط 49، 58). هر یک در داخل یک تراکنش اجرا خواهد شد. اینکه دومی شکست بخورد، تأثیری بر اولی ندارد؛
  • خطوط ۴۵–۵۱: دستور SQL [drop table personnes] اجرا می‌شود. این دستور جدول با نام [personnes] را حذف می‌کند. اگر این جدول وجود نداشته باشد، ممکن است خطایی گزارش شود. این خطا نادیده گرفته می‌شود (خط ۵۱)؛
  • خطوط ۵۳–۵۵: دستور ایجاد جدول [personnes]. یک جدول را می‌توان به‌عنوان مجموعه‌ای از سطرها و ستون‌ها در نظر گرفت. دستور ایجاد، نام ستون‌ها را مشخص می‌کند:
    • [id]: یک شناسه‌ی عددی. این شناسه برای هر فرد یکتا خواهد بود. این کلید اصلی خواهد بود (PRIMARY KEY). این بدان معناست که در این جدول، این ستون حاوی هیچ مقدار تکراری نیست و می‌توان از آن برای شناسایی یک شخص استفاده کرد؛
    • [nom]: یک رشته تا ۳۰ کاراکتر؛
    • [prenom]: یک رشته تا ۳۰ کاراکتر؛
    • [age]: یک عدد صحیح؛
    • ویژگی [NOT NULL] برای هر یک از این ستون‌ها به این معناست که در هیچ سطری از جدول، هیچ‌یک از این سه ستون نمی‌تواند خالی باشد؛
    • پارامتر [unique(nom,prenom)] یک محدودیت نامیده می‌شود. در اینجا، محدودیت بر روی سطرها این است که زوج (نام خانوادگی، نام) در سطر باید در کل جدول یکتا باشد. این بدان معناست که فردی که نام خانوادگی و نام او مشخص است، می‌تواند به طور یکتا در جدول شناسایی شود؛
  • خطوط ۵۶–۶۰: اجرای دستور SQL;
  • خطوط ۶۱–۶۳: رسیدگی به هرگونه خطا؛
  • خطوط ۶۴–۶۶: قطع اتصال از پایگاه داده؛

نتایج

1
2
3
4
C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/mysql/mysql_03.py
create table personnes (id int PRIMARY KEY, prenom varchar(30) NOT NULL, nom varchar(30) NOT NULL, age integer NOT NULL, unique(nom,prenom))  : requête réussie

Process finished with exit code 0

تأیید با استفاده از [phpMyAdmin]:

Image

  • پایگاه داده [dbpersonnes] [1] شامل یک جدول [personnes] [2] است که ساختار [3-4] را دارد، کلید اصلی [5] و قید یکتایی [6]؛

16.6. اسکریپت [mysql_04]: اجرای یک فایل فرمان SQL

پس از ایجاد جدول [personnes]، اکنون آن را پر کرده و سپس با استفاده از دستورات SQL پردازش خواهیم کرد.

ما می‌خواهیم دستورات SQL را از یک فایل متنی اجرا کنیم:

Image

محتویات فایل [commandes.sql] به شرح زیر است:


# حذف جدول [personnes]
drop table personnes
# ایجاد جدول «people»
create table personnes (prenom varchar(30) not null, nom varchar(30) not null, age integer not null, primary key (nom,prenom))
# افزودن دو نفر
insert into personnes(prenom, nom, age) values('Paul','Langevin',48)
insert into personnes(prenom, nom, age) values ('Sylvie','Lefur',70)
# نمایش جدول
select prenom, nom, age from personnes
# خطای عمدی
xx
# افزودن سه نفر
insert into personnes(prenom, nom, age) values ('Pierre','Nicazou',35)
insert into personnes(prenom, nom, age) values ('Geraldine','Colou',26)
insert into personnes(prenom, nom, age) values ('Paulette','Girond',56)
#نمایش جدول
select prenom, nom, age from personnes
# فهرست افراد به ترتیب الفبایی نام خانوادگی؛ در صورتی که نام خانوادگی یکسان باشد، به ترتیب الفبایی نام کوچک
select nom,prenom from personnes order by nom asc, prenom desc
#فهرست افرادی که سنشان در بازه [20,40] قرار دارد، به ترتیب نزولی سن
#سپس، برای هم‌سن‌وسال‌ها، به ترتیب الفبایی نام خانوادگی و در صورت یکسان بودن نام خانوادگی، به ترتیب الفبایی نام کوچک
select nom,prenom,age from personnes where age between 20 and 40 order by age desc, nom asc, prenom asc
# افزودن خانم برونو
insert into personnes(prenom, nom, age) values('Josette','Bruneau',46)
# به‌روزرسانی سن او
update personnes set age=47 where nom='Bruneau'
# فهرست افرادی با نام خانوادگی برونو
select nom,prenom,age from personnes where nom='Bruneau'
# حذف خانم برونو
delete from personnes where nom='Bruneau'
# فهرست افرادی با نام خانوادگی برونو
select nom,prenom,age from personnes where nom='Bruneau'

ابتدا، ما چند تابع را تعریف می‌کنیم که آن‌ها را در یک ماژول قرار می‌دهیم تا بتوانیم دوباره از آن‌ها استفاده کنیم:

Image

اسکریپت [mysql_module] به شرح زیر است:


# واردات‌ها
from mysql.connector import DatabaseError, InterfaceError
from mysql.connector.connection import MySQLConnection
from mysql.connector.cursor import MySQLCursor


# ---------------------------------------------------------------------------------
def afficher_infos(curseur: MySQLCursor):
     # نتیجه یک دستور SQL را نمایش می‌دهد
    …


# ---------------------------------------------------------------------------------
def execute_list_of_commands(connexion: MySQLConnection, sql_commands: list,
                             suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
    #از اتصال باز [connexion] استفاده می‌کند
    #دستورات SQL موجود در لیست [sql_commands] را روی این اتصال اجرا می‌کند
    #این فایل یک فایل دسته‌ای است که حاوی دستورات SQL است، هر دستور در یک خط
    #اگر tracking=True باشد، هر بار که یک دستور SQL اجرا می‌شود، پیامی نمایش داده می‌شود که نشان می‌دهد آیا موفقیت‌آمیز بوده یا ناموفق
    # اگر stop=True باشد، تابع در اولین خطای برخورد کرده متوقف می‌شود؛ در غیر این صورت، تمام دستورات SQL اجرا می‌شوند
    #اگر with_transaction=True باشد، آنگاه هر خطایی تمام دستورات SQL اجرا شده قبلی را لغو می‌کند
    #اگر with_transaction=False باشد، آنگاه خطا هیچ تأثیری بر دستورات SQL اجرا شده قبلاً ندارد
    #تابع یک لیست [erreur1, erreur2, ...] را بازمی‌گرداند

    ….


# ---------------------------------------------------------------------------------
def execute_file_of_commands(connexion: MySQLConnection, sql_filename: str,
                             suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
    #از اتصال باز [connexion] استفاده می‌کند
    # دستورهای SQL موجود در فایل متنی sql_filename را از طریق این اتصال اجرا می‌کند
    #این فایل یک فایل فرمان است که حاوی دستورات SQL می‌باشد، هر دستور در یک خط
    #اگر tracking=True باشد، هر بار که یک دستور SQL اجرا می‌شود، پیامی نمایش داده می‌شود که نشان می‌دهد آیا موفقیت‌آمیز بوده یا ناموفق
    # اگر stop=True باشد، تابع در اولین خطای برخورد کرده متوقف می‌شود؛ در غیر این صورت، تمام دستورات SQL اجرا می‌شوند
    #اگر with_transaction=True باشد، آنگاه هر خطایی تمام دستورات SQL اجرا شده قبلی را لغو می‌کند
    #اگر with_transaction=False باشد، آنگاه خطا هیچ تأثیری بر دستورات SQL اجرا شده قبلاً ندارد
    #تابع یک لیست را بازمی‌گرداند [erreur1, erreur2, ...]

    #پردازش فایل SQL
    try:
        #باز کردن فایل برای خواندن
        file = open(sql_filename, "r")
        #پردازش
        return execute_list_of_commands(connexion, file.readlines(), suivi, arrêt, with_transaction)
    except BaseException as erreur:
        # یک آرایه خطا را بازمی‌گرداند
        return [f"Le fichier {sql_filename} n'a pu être être exploité : {erreur}"]

یادداشت‌ها:

  • خط ۲۹: تابع [execute_file_of_commands] دستورات SQL را که در فایل متنی به نام [sql_filename] قرار دارند، اجرا می‌کند:
  • لطفاً برای اطلاع از معنای پارامترها به توضیحات در خطوط ۳۱–۳۸ مراجعه کنید؛
  • خطوط ۴۰–۴۸: فایل متنی [sql_filename] پردازش می‌شود؛
  • خط ۴۳: فایل باز می‌شود؛
  • خط ۳۴: تابع [execute_list_of_commands] اجرا می‌شود که به نوبه خود دستورات SQL را که در یک لیست به آن ارسال شده است، اجرا می‌کند. این لیست در اینجا شامل تمام خطوط موجود در فایل متنی [file.readlines()] (خط ۴۵) است؛

تابع [execute_list_of_commands] به شرح زیر است:


# ---------------------------------------------------------------------------------
def execute_list_of_commands(connexion: MySQLConnection, sql_commands: list,
                             suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
    #از اتصال باز [connexion] استفاده می‌کند
    # دستورهای SQL موجود در لیست [sql_commands] را از طریق این اتصال اجرا می‌کند
    #این فایل یک فایل دسته‌ای است که حاوی دستورات SQL است، هر دستور در یک خط
    #اگر tracking=True باشد، هر بار که یک دستور SQL اجرا می‌شود، پیامی نمایش داده می‌شود که نشان می‌دهد آیا موفقیت‌آمیز بوده یا ناموفق
    # اگر stop=True باشد، تابع در اولین خطای برخورد کرده متوقف می‌شود؛ در غیر این صورت، تمام دستورات SQL اجرا می‌شوند
    #اگر with_transaction=True باشد، هر خطایی تمام دستورات SQL اجرا شده قبلی را لغو می‌کند
    #اگر with_transaction=False باشد، آنگاه خطا هیچ تأثیری بر دستورات SQL اجرا شده قبلاً ندارد
    #تابع یک لیست را بازمی‌گرداند [erreur1, erreur2, ...]

    # ابتدایی‌سازی‌ها
    curseur = None
    connexion.autocommit = not with_transaction
    erreurs = []
    try:
        #یک اسلایدر لازم است
        curseur = connexion.cursor()
        #اجرای sql_commands و SQL موجود در sql_commands
        # آنها را یکی یکی اجرا کنید
        for command in sql_commands:
            # ما فاصله‌های خالی ابتدایی و انتهایی را از دستور فعلی حذف می‌کنیم
            command = command.strip()
            #آیا دستور خالی است یا نظر (comment)؟ اگر چنین است، به دستور بعدی برو
            if command == '' or command[0] == "#":
                continue
            # دستور فعلی را اجرا کنید
            error = None
            try:
                curseur.execute(command)
            except (InterfaceError, DatabaseError) as erreur:
                error = erreur
            #آیا خطایی رخ داده است؟
            if error:
                # خطای دیگر
                msg = f"{command} : Erreur ({error})"
                erreurs.append(msg)
                # روی صفحه نمایش داده شود یا نه؟
                if suivi:
                    print(msg)
                # آیا متوقف شویم؟
                if with_transaction or arrêt:
                    # فهرست خطاها را بازگردانید
                    return erreurs
            else:
                # بدون خطا
                if suivi:
                    print(f"[{command}] : Exécution réussie")
                # نمایش نتیجه فرمان
                afficher_infos(curseur)
        # بازگرداندن جدول خطاها
        return erreurs
    finally:
        # پایان دادن به نشانگر
        if curseur:
            curseur.close()
        # تأیید یا لغو تراکنش در صورت وجود
        if with_transaction:
            if erreurs:
                #لغو
                connexion.rollback()
            else:
                #تأیید
                connexion.commit()

یادداشت‌ها

  • خط ۲: تابع [execute_list_of_commands] دستورات SQL را که در لیست [sql_commands] قرار دارند، اجرا می‌کند:
  • برای معنای پارامترها به توضیحات خطوط ۴–۱۱ مراجعه کنید؛
  • خط ۲: اتصال دریافتی یک اتصال باز به یک پایگاه داده است؛
  • خط ۱۵: اگر می‌خواهید تمام دستورات موجود در لیست [sql_commands] در داخل یک تراکنش اجرا شوند، باید در حالت AUTOCOMMIT=False کار کنید. در غیر این صورت، شما در حالت AUTOCOMMIT=True کار خواهید کرد، که در این صورت هر دستور در لیست [sqlCommands] در یک تراکنش خودکار اجرا می‌شود و هیچ تراکنش کلی وجود نخواهد داشت؛
  • خط ۱۹: یک کرسر برای اجرای دستورات مختلف SQL درخواست می‌شود؛
  • خطوط ۲۲–۵۱: دستورات یکی یکی اجرا می‌شوند؛
  • خطوط ۲۶–۲۷: خطوط خالی و نظرات در لیست دستورات SQL پذیرفته می‌شوند. در این صورت، دستور به سادگی نادیده گرفته می‌شود؛
  • خطوط ۳۰–۳۳: پرس‌وجوی جاری اجرا می‌شود؛
  • خطوط ۳۵–۴۵: رسیدگی به هرگونه خطایی که ممکن است هنگام اجرای پرس‌وجوی جاری رخ دهد؛
  • خطوط ۳۷–۳۸: خطا به آرایه خطاها اضافه می‌شود؛
  • خطوط ۴۰–۴۱: اگر ثبت وقایع درخواست شده باشد، پیام خطا نمایش داده می‌شود؛
  • خطوط ۴۳–۴۵: اگر کد فراخوانی‌کننده پس از اولین خطا درخواست خاتمه یا درخواست استفاده از یک تراکنش کرده باشد، برنامه باید متوقف شود. آرایه خطا بازگردانده می‌شود؛
  • خطوط ۴۶–۵۱: حالتی که در اجرای پرس‌وجوی جاری هیچ خطایی رخ نداده است؛
  • خطوط ۴۸–۴۹: اگر ردیابی درخواست شده باشد، پرس‌وجوی اجرا شده با برچسب «موفق» نمایش داده می‌شود؛
  • خطوط ۵۰–۵۱: نتیجه پرس‌وجوی اجرا شده نمایش داده می‌شود. کمی بعد به تابع [afficher_infos] باز خواهیم گشت؛
  • خطوط ۵۴–۶۵: عبارت [finally] در همه موارد، صرف‌نظر از اینکه خطایی رخ داده باشد یا خیر، اجرا می‌شود؛
  • خطوط ۵۶–۵۷: کرسر بسته می‌شود. این کار منابع تخصیص‌یافته به آن را آزاد می‌کند؛
  • خطوط 59–65: موردی که کد فراخوانی درخواست کرده است دستورات SQL در داخل یک تراکنش اجرا شوند، رسیدگی می‌شود؛
  • خط ۶۰: بررسی می‌کنیم که آیا لیست [erreurs] خالی است یا خیر، که به معنای عدم وقوع هیچ استثنایی است. در این صورت، تراکنش commit می‌شود (خط ۶۵)؛ در غیر این صورت، roll back می‌شود (خط ۶۲)؛

توابع [afficher_infos] نتیجه یک پرس‌وجو را نمایش می‌دهد:


# ---------------------------------------------------------------------------------
def afficher_infos(curseur: MySQLCursor):
    print(type(curseur))
    #نتیجه یک دستور SQL را نمایش می‌دهد
    #آیا این یک عبارت SELECT بود؟
    if curseur.description:
        # کرسر دارای توضیح است – بنابراین یک SELECT اجرا شد
        # توضیحات [i] توضیحات ستون شماره i در عبارت SELECT است
        #descriptionQZXW2HTMLBW2ldZQXQZXW2HTMLBWzBdZQX نام ستون شماره i در عبارت SELECT است
        #نام فیلدها نمایش داده می‌شوند
        titre = ""
        for i in range(len(curseur.description)):
            titre += curseur.description[i][0] + ", "
        # فهرست فیلدها بدون ویرگول انتهایی نمایش داده می‌شود
        print(titre[0:len(titre) - 1])
        #خط جداکننده
        print("*" * (len(titre) - 1))
        # ردیف جاری در SELECT
        ligne = curseur.fetchone()
        while ligne:
            # آن را نمایش می‌دهد
            print(ligne)
            # ردیف بعدی در لیست کشویی
            ligne = curseur.fetchone()
        # خط جداکننده
        print("*" * (len(titre) - 1))
    else:
        # نشانگر هیچ فیلدی ندارد [description] – بنابراین یک فرمان اجرا شده است SQL
        # دستور به‌روزرسانی (درج، حذف، به‌روزرسانی)
        print(f"nombre de lignes modifiées : {curseur.rowcount}")

یادداشت‌ها

  • خط ۱: پارامتر تابع همان کرسر است که به تازگی یک دستور SQL را اجرا کرده است. بسته به اینکه این دستور یک SELECT باشد یا یک دستور به‌روزرسانی مانند INSERT، UPDATE یا DELETE، محتویات کرسر متفاوت خواهد بود؛
  • خط ۶: اگر کرسر فیلد [description] را داشته باشد، آنگاه یک SELECT اجرا شده است، و [description] فیلدهای درخواست‌شده در SELECT را توصیف می‌کند:
    • description[i] فیلد شماره i را که توسط SELECT درخواست شده است، توصیف می‌کند. این یک لیست است؛
    • description[i][0] نام فیلد شماره i است؛
  • خطوط ۱۱–۱۷: نام فیلدهای درخواست‌شده توسط SELECT نمایش داده می‌شوند؛
  • خطوط ۱۸–۲۴: نتیجه SELECT پردازش می‌شود؛
  • خطوط ۲۰ و ۲۴: نتیجه یک SELECT به‌صورت متوالی پردازش می‌شود. این نتیجه مجموعه‌ای از سطرها است. سطر فعلی توسط [curseur.fetchone()] (خط ۱۹) به‌دست می‌آید. سپس یک تِیوپ به‌دست می‌آید؛
  • خطوط 27–30: اگر کرسر فیلد [description] را نداشته باشد، آنگاه یک دستور به‌روزرسانی INSERT، UPDATE، DELETE اجرا شده است. سپس می‌توانیم مشخص کنیم که چند سطر در جدول با اجرای این دستور تغییر یافته‌اند؛
  • خط ۳۰: [curseur.rowcount] همان عدد است؛

اسکریپت اصلی [mysql-04] از ماژول [mysql_module] که همین حالا توضیح دادیم استفاده می‌کند:

Image

فایل [config_04] زمینهٔ اجرای اسکریپت [mysql_04] را پیکربندی می‌کند:


def configure():
    import os

    # مسیر مطلق به پوشه فایل پیکربندی
    script_dir = os.path.dirname(os.path.abspath(__file__))
    #پوشه پیکربندی syspath
    absolute_dependencies = [
        #پوشه‌های محلی
        f"{script_dir}/shared",
    ]

    # تنظیم syspath
    from myutils import set_syspath
    set_syspath(absolute_dependencies)

    # اعمال پیکربندی
    return {
        # فایل فرمان SQL
        "commands_filename": f"{script_dir}/data/commandes.sql",
        # اطلاعات اعتبارنامه‌ی اتصال به پایگاه داده
        "host": "localhost",
        "database": "dbpersonnes",
        "user": "admpersonnes",
        "password": "nobody"
    }

اسکریپت [mysql_04] به شرح زیر است:


# بازیابی پیکربندی برنامه
import config_04

config = config_04.configure()

#مسیر سیستم پیکربندی شده است – اکنون می‌توانیم واردات را انجام دهیم
import sys
from mysql_module import execute_file_of_commands
from mysql.connector import connect, DatabaseError, InterfaceError

# ---------------------------------------------- اصلی
# بررسی نحوی فراخوانی
# argv[0] true / false
args = sys.argv
erreur = len(args) != 2
if not erreur:
    with_transaction = args[1].lower()
    erreur = with_transaction != "true" and with_transaction != "false"
# خطا؟
if erreur:
    print(f"syntaxe : {args[0]} true / false")
    sys.exit()

# محاسبه متن
with_transaction = with_transaction == "true"
if with_transaction:
    texte = "avec transaction"
else:
    texte = "sans transaction"

# گزارش‌های صفحه
print("--------------------------------------------------------------------")
print(f"Exécution du fichier SQL {config['commands_filename']} {texte}")
print("--------------------------------------------------------------------")

#اجرای دستورات SQL از فایل
connexion = None
try:
    #اتصال به پایگاه داده
    connexion = connect(host=config['host'], user=config['user'], password=config['password'],
                        database=config['database'])
    #اجرای فایل فرمان SQL
    erreurs = execute_file_of_commands(connexion, config["commands_filename"], suivi=True, arrêt=False,
                                       with_transaction=with_transaction)
except (InterfaceError, DatabaseError) as erreur:
     #خطای نمایش داده شده
    print(f"L'erreur fatale suivante s'est produite : {erreur}")
    #خروج
    sys.exit()
finally:
    # بستن اتصال در صورت باز بودن
    if connexion:
        connexion.close()

#نمایش تعداد خطاها
print("--------------------------------------------------------------------")
print(f"Exécution terminée")
print("--------------------------------------------------------------------")
print(f"Il y a eu {len(erreurs)} erreur(s)")
# نمایش خطاها
for erreur in erreurs:
    print(erreur)

یادداشت‌ها

  • خطوط ۱–۴: پیکربندی اسکریپت؛
  • خط ۸: وارد کردن ماژول [mysql_module] که در بالا توضیح داده شده است:
  • خطوط ۱۲–۲۲: اسکریپت [mysql-04] منتظر پارامتری است که باید یکی از مقادیر [true / false] را داشته باشد. این پارامتر مشخص می‌کند که آیا فایل فرمان SQL باید در داخل یک تراکنش اجرا شود (true) یا خیر (false)؛
  • خط 14: پارامترهای ارسال‌شده توسط کاربر به اسکریپت در لیست [sys.argv] یافت می‌شوند؛
  • خط ۱۵: دو پارامتر مورد نیاز است، برای مثال [mysql-04 true]. نام اسکریپت به‌عنوان یک پارامتر محسوب می‌شود؛
  • خطوط ۱۷–۱۸: اگر واقعاً دو پارامتر وجود داشته باشد، پارامتر دوم باید یک رشته با مقدار 'true' یا 'false' باشد؛
  • خطوط ۲۴–۲۹: محاسبه متنی که باید در خط ۳۳ نمایش داده شود؛
  • خطوط ۳۹–۴۴: دستورات موجود در فایل [./data/commandes.sql] اجرا می‌شوند؛
  • خطوط ۴۵–۴۹: اگر در حین اتصال (خط ۴۰) خطایی رخ دهد یا خطایی پیش بیاید که توسط اسکریپت [execute_file_of_commands] مدیریت نشده باشد، خطا نمایش داده می‌شود و فرآیند متوقف می‌گردد؛
  • خطوط ۵۵–۶۲: اگر اجرای موفقیت‌آمیز باشد، تعداد خطاهای رخ‌داده حین اجرای دستورات در فایل SQL نمایش داده می‌شود؛

اجرای شمارهٔ ۱

ابتدا، یک اجرای بدون تراکنش را انجام می‌دهیم. برای این کار، یک پیکربندی اجرا را همانطور که در بخش |پیکربندی زمینه اجرا| توضیح داده شده است، ایجاد خواهیم کرد:

Image

  • در [1-4]، ما یک پیکربندی اجرای پایتون ایجاد می‌کنیم؛

Image

  • [5]: نام پیکربندی اجرای؛
  • [6]: مسیر اسکریپت برای اجرا؛
  • [7]: پارامترهای اسکریپت؛
  • [8]: دایرکتوری اجرای برنامه؛

بنابراین این پیکربندی معادل اجرای فایل SQL با یک تراکنش است. برای تأیید پیکربندی از دکمه [Apply] استفاده کنید.

ما پیکربندی اجرای [mysql mysql-04 without_transaction] را به همین روش ایجاد می‌کنیم:

Image

بنابراین این پیکربندی، اجرای فایل SQL بدون تراکنش را نشان می‌دهد. برای تأیید پیکربندی از دکمه [Apply] استفاده کنید.

ابتدا نسخه بدون تراکنش را اجرا می‌کنیم:

Image

نتایج به شرح زیر است:


C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/mysql/mysql_04.py false
--------------------------------------------------------------------
Exécution du fichier SQL C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\databases\mysql/data/commandes.sql sans transaction
--------------------------------------------------------------------
[drop table personnes] : Exécution réussie
nombre de lignes modifiées : 0
[create table personnes (id int primary key, prenom varchar(30) not null, nom varchar(30) not null, age integer not null, unique (nom,prenom))] : Exécution réussie
nombre de lignes modifiées : 0
[insert into personnes(id, prenom, nom, age) values(1, 'Paul','Langevin',48)] : Exécution réussie
nombre de lignes modifiées : 1
[insert into personnes(id, prenom, nom, age) values (2, 'Sylvie','Lefur',70)] : Exécution réussie
nombre de lignes modifiées : 1
[select prenom, nom, age from personnes] : Exécution réussie
prenom, nom, age,
*****************
('Paul', 'Langevin', 48)
('Sylvie', 'Lefur', 70)
*****************
xx : Erreur (1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx' at line 1)
[insert into personnes(id, prenom, nom, age) values (3, 'Pierre','Nicazou',35)] : Exécution réussie
nombre de lignes modifiées : 1
[insert into personnes(id, prenom, nom, age) values (4, 'Geraldine','Colou',26)] : Exécution réussie
nombre de lignes modifiées : 1
[insert into personnes(id, prenom, nom, age) values (5, 'Paulette','Girond',56)] : Exécution réussie
nombre de lignes modifiées : 1
[select prenom, nom, age from personnes] : Exécution réussie
prenom, nom, age,
*****************
('Paul', 'Langevin', 48)
('Sylvie', 'Lefur', 70)
('Pierre', 'Nicazou', 35)
('Geraldine', 'Colou', 26)
('Paulette', 'Girond', 56)
*****************
[select nom,prenom from personnes order by nom asc, prenom desc] : Exécution réussie
nom, prenom,
************
('Colou', 'Geraldine')
('Girond', 'Paulette')
('Langevin', 'Paul')
('Lefur', 'Sylvie')
('Nicazou', 'Pierre')
************
[select nom,prenom,age from personnes where age between 20 and 40 order by age desc, nom asc, prenom asc] : Exécution réussie
nom, prenom, age,
*****************
('Nicazou', 'Pierre', 35)
('Colou', 'Geraldine', 26)
*****************
[insert into personnes(id, prenom, nom, age) values(6, 'Josette','Bruneau',46)] : Exécution réussie
nombre de lignes modifiées : 1
[update personnes set age=47 where nom='Bruneau'] : Exécution réussie
nombre de lignes modifiées : 1
[select nom,prenom,age from personnes where nom='Bruneau'] : Exécution réussie
nom, prenom, age,
*****************
('Bruneau', 'Josette', 47)
*****************
[delete from personnes where nom='Bruneau'] : Exécution réussie
nombre de lignes modifiées : 1
[select nom,prenom,age from personnes where nom='Bruneau'] : Exécution réussie
nom, prenom, age,
*****************
*****************
--------------------------------------------------------------------
Exécution terminée
--------------------------------------------------------------------
Il y a eu 1 erreur(s)
xx : Erreur (1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx' at line 1)

Process finished with exit code 0

یادداشت‌ها:

  • خط ۱۹: می‌توانیم ببینیم که پس از خطا، اجرای دستورات SQL ادامه یافت؛ این به این دلیل است که اجرا بدون تراکنش و با پارامتر [arrêt=False] انجام شد. بنابراین تمام دستورات SQL اجرا شدند. در نتیجه باید یک جدول [personnes] داشته باشیم که این اجرا را منعکس کند؛

تأیید با استفاده از phpMyAdmin:

Image

اجرای شمارهٔ ۲

اکنون پیکربندی [mysql mysql-04 with_transaction] را اجرا می‌کنیم. نتایج به شرح زیر است:


C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/mysql/mysql_04.py true
--------------------------------------------------------------------
Exécution du fichier SQL C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\databases\mysql/data/commandes.sql avec transaction
--------------------------------------------------------------------
[drop table personnes] : Exécution réussie
nombre de lignes modifiées : 0
[create table personnes (id int primary key, prenom varchar(30) not null, nom varchar(30) not null, age integer not null, unique (nom,prenom))] : Exécution réussie
nombre de lignes modifiées : 0
[insert into personnes(id, prenom, nom, age) values(1, 'Paul','Langevin',48)] : Exécution réussie
nombre de lignes modifiées : 1
[insert into personnes(id, prenom, nom, age) values (2, 'Sylvie','Lefur',70)] : Exécution réussie
nombre de lignes modifiées : 1
[select prenom, nom, age from personnes] : Exécution réussie
prenom, nom, age,
*****************
('Paul', 'Langevin', 48)
('Sylvie', 'Lefur', 70)
*****************
xx : Erreur (1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx' at line 1)
--------------------------------------------------------------------
Exécution terminée
--------------------------------------------------------------------
Il y a eu 1 erreur(s)
xx : Erreur (1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx' at line 1)

Process finished with exit code 0

یادداشت‌ها:

  • خط ۱۹: می‌توانیم ببینیم که پس از خطا، هیچ دستور SQL دیگری اجرا نمی‌شود؛ این به آن دلیل است که اجرا در داخل یک تراکنش انجام شد و با مواجهه با اولین خطا، ما تراکنش را لغو کرده و اجرای دستورات SQL را متوقف کردیم. این بدان معناست که نتایج دستورات در خطوط ۹، ۱۱ و ۱۳ لغو شده‌اند. بنابراین باید جدول [personnes] خالی باشد؛

بررسی‌ها با استفاده از phpMyAdmin:

Image

  • در [5]، می‌توانیم ببینیم که جداول [personnes] و [2] خالی هستند؛

16.7. اسکریپت [mysql_05]: استفاده از پرس‌وجوهای پارامتریک

اسکریپت [mysql_05] مفهوم پرس‌وجوهای پارامتریک را معرفی می‌کند:


# واردات‌ها
from mysql.connector import connect, DatabaseError, InterfaceError

# هویت کاربر
ID = "admpersonnes"
PWD = "nobody"
# ماشین میزبان DBMS
HOST = "localhost"
#شناسهٔ پایگاه داده
BASE = "dbpersonnes"

# فهرست افراد (نام خانوادگی، نام، سن)
personnes = []
for i in range(5):
    personnes.append((i, f"n0{i}", f"p0{i}", i + 10))
personnes.append((40, "d'Aboot", "Y'éna", 18))
# فهرست دیگری از افراد
autresPersonnes = []
for i in range(5):
    autresPersonnes.append((i + 100, f"n1{i}", f"p1{i}", i + 20))
autresPersonnes.append((200, "d'Aboot", "F'ilhem", 34))

#دسترسی به SGBD
connexion = None
try:
    #ورود
    connexion = connect(host=HOST, user=ID, password=PWD, database=BASE)
    # نماینده
    curseur = connexion.cursor()
    # حذف رکوردهای موجود
    curseur.execute("delete from personnes")
    # وارد کردن رکوردها به‌صورت تک‌تک با استفاده از پرس‌وجوی آماده
    for personne in personnes:
        curseur.execute("insert into personnes(id,nom,prenom,age) values(%s,%s,%s,%s)", personne)
    # واردسازی دسته‌ای یک فهرست افراد
    curseur.executemany("insert into personnes(id,nom,prenom,age) values(%s, %s,%s,%s)", autresPersonnes)
    #اعتبارسنجی تراکنش
    connexion.commit()
except (DatabaseError, InterfaceError) as erreur:
    # نمایش خطا
    print(f"L'erreur suivante s'est produite : {erreur}")
    #لغو تراکنش
    if connexion:
        connexion.rollback()
finally:
    # خروج
    if connexion:
        connexion.close()

یادداشت‌ها

  • خطوط ۱۲–۲۱: دو لیست از افرادی که باید در پایگاه داده [dbpersonnes] گنجانده شوند ایجاد می‌شوند؛
  • خط ۲۷: اتصال به پایگاه داده؛
  • خط ۳۱: محتویات جدول [personnes] حذف می‌شوند؛
  • خطوط ۳۳–۳۴: وارد کردن افراد با استفاده از یک پرس‌وجوی پارامتریک. خط ۳۴: پارامتر اول، دستور SQL است که باید اجرا شود. این دستور ناقص است. این دستور حاوی پارامترهایی از [%s] است که به صورت تک‌تک و به ترتیب با مقادیر موجود در لیستِ پارامتر دوم جایگزین خواهند شد؛
  • خط ۳۶: درج افراد، این بار با یک دستور واحد، [curseur.executemany]. بنابراین پارامتر دوم [executemany] یک لیست از لیست‌ها است؛

مزایای پرس‌وجوهای پارامتریک در دو نکته نهفته است:

  • آنها سریع‌تر از پرس‌وجوهای «سخت‌کدشده» اجرا می‌شوند که باید در هر اجرا پارس شوند. پرس‌وجوی پارامتریک [executemany] تنها یک بار پارس می‌شود. سپس n بار بدون پارس شدن مجدد اجرا می‌شود؛
  • پارامترهای ارسال‌شده به پرس‌وجوی پارامتریک بررسی می‌شوند. اگر این پارامترها حاوی کاراکترهای رزرو شده، مانند آپاستروف، باشند، این کاراکترها 'escaped' (فرار داده می‌شوند) تا با اجرای دستور SQL تداخل نکنند. ما این نکته را برای تأیید، در لیست (خطوط ۱۶ و ۲۱) شامل نام‌ها و نام‌های خانوادگی حاوی آپاستروف قرار دادیم؛

نتایج حاصل در phpMyAdmin به شرح زیر است:

Image

  • توجه کنید که رشته‌های حاوی آپاستروف – یک کاراکتر رزرو شده در SQL – به درستی وارد شده‌اند. پرس‌وجوی پارامتریک از آنها محافظت کرده است. بدون پرس‌وجوی پارامتریک، ما مجبور بودیم این کار را خودمان انجام دهیم؛
https://stahe.github.io
این آموزش دوره, نوشته شده توسط سرگئه تاه, تحت شرایط مجوز Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported در دسترس عموم قرار گرفته است.