Skip to content

17. با استفاده از SGBD و PostgreSQL

SGBD PostgreSQL به‌صورت آزاد در دسترس است. این جایگزینی برای نسخه «جامعه‌ای» MySQL است.

ما در اینجا از آن استفاده می‌کنیم تا نشان دهیم مهاجرت اسکریپت‌های Python / MySQL به اسکریپت‌های Python / PostgreSQL نسبتاً ساده است.

با SGBD و MySQL، معماری اسکریپت‌های ما به شرح زیر بود:

با SGBD و PostgreSQL، به این صورت خواهد بود:

Image

17.1. نصب SGBD و PostgreSQL

نسخه‌های SGBD و PostgreSQL در نسخه‌های URL و [https://www.postgresql.org/download/] (مه ۲۰۱۹) موجود هستند. ما نصب نسخه ۶۴ بیتی ویندوز را نشان خواهیم داد:

Image

Image

  • در [1-4]، نصب‌کننده را برای SGBD دانلود کنید؛

نصب‌کننده دانلود شده را اجرا کنید:

Image

  • در [6]، یک پوشه نصب مشخص کنید؛

Image

  • در [8]، گزینه [Stack Builder] برای کاری که ما اینجا انجام می‌دهیم لازم نیست؛
  • در [10]، مقدار نمایش‌داده‌شده را بدون تغییر باقی بگذارید؛

Image

  • برای [12-13]، رمز عبور [root] را اینجا وارد کرده‌ایم. این رمز عبور مدیر برای SGBD خواهد بود که [postgres] نامیده می‌شود. PostgreSQL همچنین آن را ابرکاربر می‌نامد؛
  • برای [15]، مقدار پیش‌فرض را حفظ کنید: این پورت گوش دادن برای SGBD است؛

Image

  • در [17]، مقدار پیش‌فرض را حفظ کنید؛
  • در [19]، خلاصه پیکربندی نصب؛

Image

Image

در ویندوز، SGBD و PostgreSQL به‌عنوان سرویس‌های ویندوز نصب می‌شوند که به‌طور خودکار راه‌اندازی می‌شوند. در اکثر موارد، این امر مطلوب نیست. ما قرار است این پیکربندی را تغییر دهیم. عبارت [services] را در نوار جستجو ویندوز تایپ کنید:

Image

  • به [29]. می‌بینیم که سرویس‌های SGBD و PostgreSQL روی «خودکار» تنظیم شده‌اند. می‌توانیم این را با دسترسی به ویژگی‌های سرویس [30] تغییر دهیم:

Image

  • برای [31-32]، نوع راه‌اندازی را روی دستی تنظیم کنید؛
  • برای [33]، سرویس را متوقف کنید؛

وقتی می‌خواهید SGBD را به‌صورت دستی راه‌اندازی کنید، به برنامه [services] بازگردید، روی سرویس [postgresql] (34) کلیک راست کرده و آن را راه‌اندازی کنید (35).

17.2. سرویس PostgreSQL را با استفاده از ابزار [pgAdmin] مدیریت کنید

سرویس ویندوزی SGBD (PostgreSQL) را راه‌اندازی کنید (به پاراگراف قبلی مراجعه کنید). سپس، به همان روشی که ابزار [services] را راه‌اندازی کردید، ابزار [pgadmin] را که برای مدیریت SGBD، PostgreSQL و [1-3] استفاده می‌شود، اجرا کنید:

Image

ممکن است در مقطعی از شما رمز عبور ابرکاربر خواسته شود. این [postgres] است. شما این رمز عبور را هنگام نصب SGBD تعیین می‌کنید. در این سند، ما رمز عبور [root] را در حین نصب به ابرکاربر اختصاص داده‌ایم.

  • در [4]، [pgAdmin] یک برنامه وب است؛
  • در [5]، فهرست سرورهای PostgreSQL شناسایی‌شده توسط [pgAdmin]، در این مورد ۱؛
  • در [6]، سرور PostgreSQL که ما راه‌اندازی کرده‌ایم؛
  • در [7]، پایگاه‌های داده SGBD، در این مورد ۱؛
  • در [8]، پایگاه داده [postgresql] توسط ابرکاربر [postgres] مدیریت می‌شود؛

ابتدا، بیایید کاربری [admpersonnes] را با رمز عبور [nobody] ایجاد کنیم:

Image

Image

  • در [17]، ما [nobody] را وارد کرده‌ایم؛

Image

  • در [21]، کدی که SQL به SGBD و PostgreSQL ارسال می‌کند. این روشی برای یادگیری زبان اختصاصی SQL از PostgreSQL است؛
  • در [22]، پس از اعتبارسنجی توسط جادوگر [Save]، کاربر [admpersonnes] ایجاد شد؛

اکنون در حال ایجاد پایگاه داده [dbpersonnes] هستیم:

Image

روی [23] کلیک راست کرده، سپس [24-25] را برای ایجاد یک پایگاه داده جدید انتخاب کنید. در زبانه [26]، نام پایگاه داده را روی [27] و مالک آن را روی [admpersonnes] [28] تنظیم کنید.

Image

  • در [30]، کد ایجاد پایگاه داده SQL;
  • به [31]؛ پس از اعتبارسنجی توسط جادوگر [Save]، پایگاه داده [dbpersonnes] ایجاد می‌شود؛

ما از پایگاه داده [dbpersonnes] با اسکریپت‌های پایتون استفاده خواهیم کرد.

17.3. نصب کانکتور پایتون برای SGBD و PostgreSQL

Image

نمودار بالا یک کانکتور را نشان می‌دهد که اسکریپت‌های پایتون را به SGBD و PostgreSQL متصل می‌کند. چندین مورد از این کانکتورها وجود دارد. ما در حال نصب کانکتور [psycopg2] هستیم. این کار در یک ترمینال پایتون انجام می‌شود (صرف‌نظر از پوشه‌ای که ترمینال در آن باز است). کانکتور با استفاده از دستور [pip install psycopg2] نصب می‌شود:


(venv) C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\troiscouches\v01\tests>pip install psycopg2
Collecting psycopg2
  Downloading psycopg2-2.8.5-cp38-cp38-win_amd64.whl (1.1 MB)
     || 1.1 MB 3.2 MB/s
Installing collected packages: psycopg2
Successfully installed psycopg2-2.8.5

17.4. مهاجرت اسکریپت‌های MySQL به اسکریپت‌های PostgreSQL

Image

  • پوشه [1] حاوی اسکریپت‌های MySQL کپی (Ctrl-C / Ctrl-V) می‌شود، سپس نام فایل‌ها بدون تغییر محتوا تغییر داده می‌شوند؛

17.4.1. ماژول [pgres_module]

این ماژول کپی ماژول [mysql_module] است (رجوع کنید به بخش |script [mysql-04]: اجرای یک فایل فرمان SQL|). ورودی‌ها تغییر یافته‌اند:

به جای:


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

نوشتن:


# واردات
from psycopg2 import DatabaseError, InterfaceError
from psycopg2.extensions import connection, cursor

امضای تابع [afficher_infos] به این صورت بود:


def afficher_infos(curseur: MySQLCursor):

به این صورت درمی‌آید:


def afficher_infos(curseur: cursor)

امضای تابع [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)

به این صورت درمی‌آید:


def execute_list_of_commands(connexion: connection, sql_commands: list,
                             suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):

در غیر این صورت، هیچ چیز دیگری تغییر نمی‌کند.

17.4.2. اسکریپت [pgres_01]

اسکریپت [pgres_01] کپی اسکریپت [mysql_01] است (رجوع کنید به پاراگراف |script [mysql-01]: اتصال به پایگاه داده MySQL - 1|). تغییرات زیر در آن اعمال شده است:

به‌جای:


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

نوشتن:


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

بقیه بدون تغییر باقی می‌ماند. نتایج همانند MySQL است.

17.4.3. اسکریپت [pgres_02]

اسکریپت [pgres_02] کپی اسکریپت [mysql_02] است (رجوع کنید به بخش |script [mysql-02]: اتصال به پایگاه داده MySQL - 2|). تغییرات زیر در آن اعمال شده است:

به جای:


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

بنویسید:


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

نتایج با نتایج اسکریپت [mysql_02] یکسان نیست:

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/postgresql/pgres_02.py
Connexion MySQL réussie à la base database=dbpersonnes, host=localhost sous l'identité user=admpersonnes, passwd=nobody
Déconnexion MySQL réussie

Process finished with exit code 0

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


#وارد کردن ماژول mysql.connector
from psycopg2 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="yy", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    #خطای نمایش داده شده
    print(erreur)

در حالی که خطوط ۳۶ تا ۴۱ باید یک پیام خطا نمایش می‌دادند که نشان می‌داد اتصال به SGBD ناموفق بوده است، هیچ چیزی نمایش داده نمی‌شود. در واقع، با بررسی بیشتر، مشخص می‌شود که اسکریپت در خطوط ۳۵–۳۷، متغیر [except] را به درستی به [erreur] پاس می‌دهد، اما متغیر [erreur] روی [None] تنظیم شده است. این مشکل در نسخهٔ 2.8.4 کانکتور [psycopg2] رخ می‌دهد.

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


# ورود با کاربر غیرموجود
try:
    connexion(host=HOST, login="xx", pwd="yy", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    #خطا نمایش داده می‌شود
    print(f"Erreur de connexion à la base [{DATABASE}] par l'utilisateur [xx/yy]")

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


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/postgresql/pgres_02.py
Connexion réussie à la base database=dbpersonnes, host=localhost sous l'identité user=admpersonnes, passwd=nobody
Déconnexion réussie

Erreur de connexion à la base [dbpersonnes] par l'utilisateur [xx/yy]

Process finished with exit code 0

17.4.4. اسکریپت [pgres_03]

اسکریپت [pgres_03] کپی اسکریپت [mysql_03] است (بخش |script [mysql-03]: ایجاد جدول MySQL| را ببینید). تغییرات زیر در آن اعمال شده است:

به جای:


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

نوشتن:


from psycopg2 import DatabaseError, InterfaceError, connect
from psycopg2.extensions import connection

علاوه بر این، امضای تابع [execute_sql] که به این صورت بود:


def execute_sql(connexion: MySQLConnection, update: str):

به این صورت درمی‌آید:


def execute_sql(connexion: connection, update: str):

بقیه بدون تغییر باقی می‌ماند. نتیجه به شرح زیر است:


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/postgresql/pgres_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

می‌توانید با استفاده از ابزار مدیریت [pgAdmin]، وجود جدول [personnes] را بررسی کنید:

Image

17.4.5. اسکریپت [pgres_04]

اسکریپت [pgres_04] کپی اسکریپت [mysql_04] است (به بخش |اسکریپت [mysql-04]: اجرای یک فایل فرمان SQL| مراجعه کنید). این اسکریپت از ماژول [pgres_module] استفاده می‌کند:


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

config = config_04.configure()

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

بقیه بدون تغییر باقی می‌ماند.

یک پیکربندی [pgres pgres-04 without_transaction] همان‌طور که در پاراگراف |script [mysql-04]: اجرای یک فایل فرمان SQL| توصیف شده است، ایجاد می‌شود. یک پیکربندی [pgres pgres-04 with_transaction] نیز ایجاد می‌شود.

اجرای پیکربندی [pgres pgres-04 without_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/postgresql/pgres_04.py false
--------------------------------------------------------------------
Exécution du fichier SQL C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\databases\postgresql/data/commandes.sql sans transaction
--------------------------------------------------------------------
[drop table if exists personnes] : Exécution réussie
nombre de lignes modifiées : -1
[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 : -1
[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 (ERREUR:  erreur de syntaxe sur ou près de « xx »
LINE 1: xx
        ^
)
[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 (ERREUR:  erreur de syntaxe sur ou près de « xx »
LINE 1: xx
        ^
)

Process finished with exit code 0
  • خط ۵: دستور حذف جدول [personnes] باید اصلاح می‌شد. برخلاف کانکتور MySQL، کانکتور PostgreSQL در صورتی که جدول مورد نظر برای حذف وجود نداشته باشد، یک استثنا پرتاب می‌کند. دستور [drop table] دارای یک نوع دیگر به نام [drop table if exists] است که اگر جدول وجود نداشته باشد، خطا ایجاد نمی‌کند. ما در اینجا از این نوع استفاده کرده‌ایم. این یک مثال است که در آن دو دستور SGBD در شرایط مشابه، رفتار یکسانی ندارند؛

جدول [personnes] در ابزار [pgAdmin] به شرح زیر است:

Image

اجرای پیکربندی [pgres pgres_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/postgresql/pgres_04.py true
--------------------------------------------------------------------
Exécution du fichier SQL C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\databases\postgresql/data/commandes.sql avec transaction
--------------------------------------------------------------------
[drop table if exists personnes] : Exécution réussie
nombre de lignes modifiées : -1
[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 : -1
[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 (ERREUR:  erreur de syntaxe sur ou près de « xx »
LINE 1: xx
        ^
)
--------------------------------------------------------------------
Exécution terminée
--------------------------------------------------------------------
Il y a eu 1 erreur(s)
xx : Erreur (ERREUR:  erreur de syntaxe sur ou près de « xx »
LINE 1: xx
        ^
)

Process finished with exit code 0

جدول [personnes] در ابزار [pgAdmin] به شرح زیر است:

Image

در اینجا، نتیجه با آنچه با MySQL به دست آمده متفاوت است. اگر اسکریپت‌ها تحت همان شرایط اجرا شوند – یعنی پس از اجرای اسکریپت بدون تراکنش – نتایج زیر به دست می‌آیند:

  • با MySQL، جدول [personnes] خالی است؛
  • با PostgreSQL، جدول [personnes] خالی نیست؛

تفاوت در روش‌های متفاوت بازگشت تراکنش توسط این دو دستور SGBD است:

  • MySQL سفارش‌های [drop table] و [create table] را بازگردانده نمی‌کند. این باعث خالی ماندن جدول [personnes] می‌شود؛
  • PostgreSQL سفارش‌های [drop table] و [create table] را معکوس می‌کند. جدول با یک تراکنش به حالتی که قبل از اجرای اسکریپت در آن بود بازگردانده می‌شود؛

17.4.6. اسکریپت [pgres_05]

اسکریپت [pgres_05] کپی اسکریپت [mysql_05] است (بخش |script [mysql-05]: استفاده از پرس‌وجوهای پارامتریک| را ببینید). اسکریپت به شرح زیر اصلاح شده است:

به جای:


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

نوشتن:


# واردات
from psycopg2 import connect, DatabaseError, InterfaceError

بقیه بدون تغییر باقی می‌ماند.

نتایج به‌دست‌آمده در [pgAdmin] به شرح زیر است:

Image

17.5. نتیجه‌گیری

انتقال اسکریپت‌های MySQL به اسکریپت‌های PostgreSQL نسبتاً ساده بود. این یک استثنا است. دو نسخه SGBD از قراردادهای نام‌گذاری یکسان برای اشیاء SQL (پایگاه‌های داده، جداول، ستون‌ها، محدودیت‌ها، انواع داده و غیره) پشتیبانی نمی‌کنند و افزونه‌های SQL ناسازگار دارند… برای تضمین یک انتقال ساده، باید در هر دو مورد به استاندارد SQL پایبند بود، بدون اینکه سعی در استفاده از افزونه‌های اختصاصی SGBD شود. با این حال، این امر به قیمت کاهش عملکرد تمام می‌شود.

https://stahe.github.io
این آموزش دوره, نوشته شده توسط سرگئه تاه, تحت شرایط مجوز Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported در دسترس عموم قرار گرفته است.