آشنایی با کتابخانه های SQL پایتون

همه برنامه های نرم افزاری با داده ها تعامل دارند، معمولا از طریق یک سیستم مدیریت پایگاه داده (DBMS). برخی از زبان های برنامه نویسی دارای ماژول هایی هستند که می توانید از آنها برای تعامل با DBMS استفاده کنید، در حالی که برخی دیگر نیاز به استفاده از بسته های شخص ثالث دارند. در این آموزش، کتابخانه های مختلف SQL پایتون را که می توانید استفاده کنید، بررسی خواهید کرد. شما یک اپلیکیشن ساده برای تعامل با پایگاه داده های SQLite ،MySQL و PostgreSQL توسعه خواهید داد.

در این آموزش یاد خواهید گرفت که چگونه:

اتصال به سیستم های مختلف مدیریت پایگاه داده با کتابخانه های SQL پایتون
تعامل با پایگاه داده های SQLite ،MySQL و PostgreSQL
اجرای کوئری های رایج پایگاه داده با استفاده از اپلیکیشن پایتون
توسعه اپلیکیشن ها در پایگاه داده های مختلف با استفاده از اسکریپت پایتون

برای استفاده حداکثری از این آموزش، باید دانش پایتون، SQL و کار با سیستم های مدیریت پایگاه داده را داشته باشید. همچنین باید بتوانید بسته ها را در پایتون دانلود و وارد کنید و بدانید که چگونه سرورهای پایگاه داده مختلف را به صورت محلی یا از راه دور نصب و اجرا کنید.

آشنایی با اسکیمای پایگاه داده

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

users

posts

comments

likes

نمودار سطح بالا از طرحواره پایگاه داده در زیر نشان داده شده است:

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

استفاده از کتابخانه های SQL پایتون برای اتصال به پایگاه داده

قبل از اینکه از طریق کتابخانه SQL پایتون با هر پایگاه داده ای تعامل داشته باشید، باید به آن پایگاه داده متصل شوید. در این بخش، نحوه اتصال به پایگاه داده های SQLite ،MySQL و PostgreSQL را از داخل یک اپلیکیشن پایتون خواهید دید.

توجه: قبل از اجرای اسکریپت ها در بخش های پایگاه داده MySQL و PostgreSQL به سرورهای MySQL و PostgreSQL نیاز دارید. برای معرفی سریع در مورد نحوه راه اندازی سرور MySQL، بخش MySQL شروع یک پروژه جنگو را بررسی کنید. برای یادگیری نحوه ایجاد پایگاه داده در PostgreSQL، بخش راه اندازی پایگاه داده جلوگیری از حملات تزریق SQL با پایتون را بررسی کنید.

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

اسکیوالایت

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

علاوه بر این، پایگاه داده های SQLite بدون سرور و مستقل هستند، زیرا داده ها را در یک فایل می خوانند و می نویسند. این بدان معناست که برخلاف MySQL و PostgreSQL، شما حتی نیازی به نصب و اجرای سرور SQLite برای انجام عملیات پایگاه داده ندارید!

در اینجا نحوه استفاده از sqlite3 برای اتصال به پایگاه داده SQLite در پایتون آورده شده است:

import sqlite3
from sqlite3 import Error

def create_connection(path):
    connection = None
    try:
        connection = sqlite3.connect(path)
        print("Connection to SQLite DB successful")
    except Error as e:
        print(f"The error '{e}' occurred")

    return connection

در اینجا نحوه عملکرد این کد آمده است:

خطوط 1 و 2 sqlite3 و کلاس Error ماژول را وارد می کنند.
خط 4 یک تابع .create_connection() را تعریف می کند که مسیر پایگاه داده SQLite را می پذیرد.
خط 7 از .connect() از ماژول sqlite3 استفاده می کند و مسیر پایگاه داده SQLite را به عنوان یک پارامتر در نظر می گیرد. اگر پایگاه داده در مکان مشخص شده وجود داشته باشد، اتصال به پایگاه داده برقرار می شود. در غیر این صورت، یک پایگاه داده جدید در مکان مشخص شده ایجاد می شود و یک اتصال برقرار می شود.
خط 8 وضعیت اتصال پایگاه داده موفق را چاپ می کند.
خط 9 هر استثنایی را که ممکن است در صورت عدم ایجاد اتصال .connect() پرتاب شود، می گیرد.
خط 10 پیام خطا را در کنسول نمایش می دهد.

sqlite3.connect(path) یک شی اتصال را برمی گرداند که به نوبه خود توسط create_connection() برگردانده می شود. از این شی اتصال می توان برای اجرای پرس و جوها در پایگاه داده SQLite استفاده کرد. اسکریپت زیر یک اتصال به پایگاه داده SQLite ایجاد می کند:

connection = create_connection("E:\\sm_app.sqlite")

پس از اجرای اسکریپت بالا، خواهید دید که یک فایل پایگاه داده sm_app.sqlite در دایرکتوری ریشه ایجاد شده است. توجه داشته باشید که می توانید مکان را مطابق با تنظیمات خود تغییر دهید.

مایاسکیول

برخلاف SQLite، هیچ ماژول پیش فرضی Python SQL وجود ندارد که بتوانید از آن برای اتصال به پایگاه داده MySQL استفاده کنید. در عوض، باید یک درایور SQL پایتون برای MySQL نصب کنید تا با یک پایگاه داده MySQL از داخل یک برنامه پایتون تعامل داشته باشید. یکی از این درایورها mysql-connector-python است. می توانید این ماژول SQL پایتون را با pip دانلود کنید:

pip install mysql-connector-python

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

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

تابعی را تعریف کنید که به سرور پایگاه داده MySQL متصل شود و شی اتصال را برگرداند:

import mysql.connector
from mysql.connector import Error

def create_connection(host_name, user_name, user_password):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password
        )
        print("Connection to MySQL DB successful")
    except Error as e:
        print(f"The error '{e}' occurred")

    return connection

connection = create_connection("localhost", "root", "")

در اسکریپت بالا، یک تابع create_connection() تعریف می کنید که سه پارامتر را می پذیرد:

نام میزبان
نام کاربری
رمز عبور کاربر

ماژول mysql.connector Python SQL شامل یک متد .connect() است که در خط 7 برای اتصال به سرور پایگاه داده MySQL استفاده می کنید. پس از برقراری اتصال، شی اتصال به تابع فراخوانی بازگردانده می شود. در نهایت در خط 18 با نام میزبان، نام کاربری و رمز عبور با create_connection() تماس می گیرید.

تا کنون، شما فقط ارتباط برقرار کرده اید. پایگاه داده هنوز ایجاد نشده است. برای انجام این کار، تابع دیگری create_database() تعریف می کنید که دو پارامتر را می پذیرد:

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

در اینجا این عملکرد به نظر می رسد:

def create_database(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        print("Database created successfully")
    except Error as e:
        print(f"The error '{e}' occurred")

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

یک پایگاه داده به نام sm_app برای برنامه رسانه اجتماعی خود در سرور پایگاه داده MySQL ایجاد کنید:

create_database_query = "CREATE DATABASE sm_app"
create_database(connection, create_database_query)

اکنون یک sm_app پایگاه داده در سرور پایگاه داده ایجاد کرده اید. با این حال، شی اتصال بازگردانده شده توسط create_connection() به سرور پایگاه داده MySQL متصل است. شما باید به پایگاه داده sm_app متصل شوید. برای انجام این کار، می توانید create_connection() را به صورت زیر تغییر دهید:

def create_connection(host_name, user_name, user_password, db_name):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password,
            database=db_name
        )
        print("Connection to MySQL DB successful")
    except Error as e:
        print(f"The error '{e}' occurred")

    return connection

در خط 8 می توانید ببینید که create_connection() اکنون یک پارامتر اضافی به نام db_name را می پذیرد. این پارامتر نام پایگاه داده ای را که می خواهید به آن متصل شوید مشخص می کند. هنگام فراخوانی این تابع می توانید نام پایگاه داده ای را که می خواهید به آن متصل شوید ارسال کنید:

connection = create_connection("localhost", "root", "", "sm_app")

اسکریپت فوق با موفقیت create_connection() را فراخوانی می کند و به پایگاه داده sm_app متصل می شود.

پستگرسکیوال

مانند MySQL، هیچ کتابخانه پیش فرضی Python SQL وجود ندارد که بتوانید از آن برای تعامل با پایگاه داده PostgreSQL استفاده کنید. در عوض، برای تعامل با PostgreSQL باید یک درایور SQL شخص ثالث پایتون نصب کنید. یکی از این درایورهای SQL پایتون برای PostgreSQL psycopg2 است. دستور زیر را روی ترمینال خود اجرا کنید تا ماژول Python SQL را نصب کنید:

pip install psycopg2

مانند پایگاه داده های SQLite و MySQL، شما create_connection() را برای ایجاد ارتباط با پایگاه داده PostgreSQL خود تعریف می کنید:

import psycopg2
from psycopg2 import OperationalError

def create_connection(db_name, db_user, db_password, db_host, db_port):
    connection = None
    try:
        connection = psycopg2.connect(
            database=db_name,
            user=db_user,
            password=db_password,
            host=db_host,
            port=db_port,
        )
        print("Connection to PostgreSQL DB successful")
    except OperationalError as e:
        print(f"The error '{e}' occurred")
    return connection

شما از psycopg2.connect() برای اتصال به سرور PostgreSQL از داخل برنامه پایتون خود استفاده می کنید.

سپس می توانید از create_connection() برای ایجاد اتصال به پایگاه داده PostgreSQL استفاده کنید. ابتدا، با استفاده از رشته زیر با postgres پیش فرض پایگاه داده ارتباط برقرار خواهید کرد:

connection = create_connection(
    "postgres", "postgres", "abc123", "127.0.0.1", "5432"
)

در مرحله بعد، باید sm_app پایگاه داده را در پایگاه داده پیش فرض postgres ایجاد کنید. می توانید تابعی را برای اجرای هر کوئری SQL در PostgreSQL تعریف کنید. در زیر، create_database() را برای ایجاد یک پایگاه داده جدید در سرور پایگاه داده PostgreSQL تعریف می کنید:

def create_database(connection, query):
    connection.autocommit = True
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        print("Query executed successfully")
    except OperationalError as e:
        print(f"The error '{e}' occurred")

create_database_query = "CREATE DATABASE sm_app"
create_database(connection, create_database_query)

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

قبل از اجرای پرس و جوها در پایگاه داده sm_app، باید به آن متصل شوید:

connection = create_connection(
    "sm_app", "postgres", "abc123", "127.0.0.1", "5432"
)

پس از اجرای اسکریپت بالا، با پایگاه داده sm_app واقع در سرور پایگاه داده postgres ارتباط برقرار می شود. در اینجا، 127.0.0.1 به آدرس IP میزبان سرور پایگاه داده و 5432 به شماره پورت سرور پایگاه داده اشاره دارد.

ایجاد جداول

در بخش قبل، نحوه اتصال به سرورهای دیتابیس SQLite، MySQL و PostgreSQL با استفاده از کتابخانه های مختلف Python SQL را مشاهده کردید. شما پایگاه داده sm_app را در هر سه سرور پایگاه داده ایجاد کرده اید. در این بخش نحوه ایجاد جداول در این سه پایگاه داده را مشاهده خواهید کرد.

همانطور که قبلا بحث شد، چهار جدول ایجاد خواهید کرد:

users

posts

comments

likes

شما با SQL Ite شروع خواهید کرد.

اسکیوالایت

برای اجرای کوئری ها در SQLite، از cursor.execute() استفاده کنید. در این بخش، یک تابع execute_query() تعریف می کنید که از این متد استفاده می کند. تابع شما شی اتصال و یک رشته پرس و جو را می پذیرد که آن را به cursor.execute() ارسال می کنید.

.execute() می تواند هر پرس و جویی را که به آن ارسال می شود به صورت رشته اجرا کند. شما از این روش برای ایجاد جداول در این بخش استفاده خواهید کرد. در بخش های آینده، از همین روش برای اجرا، بروزرسانی و حذف کوئری ها نیز استفاده خواهید کرد.

توجه: این اسکریپت باید در همان فایلی که اتصال پایگاه داده SQLite خود را ایجاد کرده اید اجرا شود.

در اینجا تعریف تابع شما آمده است:

def execute_query(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        connection.commit()
        print("Query executed successfully")
    except Error as e:
        print(f"The error '{e}' occurred")

این کد سعی می کند پرس و جو داده شده را اجرا کند و در صورت لزوم یک پیام خطا چاپ می کند.

سپس، پرسش خود را بنویسید:

create_users_table = """
CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  age INTEGER,
  gender TEXT,
  nationality TEXT
);
"""

این می گوید برای ایجاد یک جدول کاربران با پنج ستون زیر:

id

name

age

gender

nationality

در نهایت، برای ایجاد جدول execute_query() را فراخوانی می کنید. شما شی اتصالی را که در بخش قبلی ایجاد کرده اید، به همراه رشته create_users_table که حاوی پرس و جو جدول ایجاد است، ارسال خواهید کرد:

execute_query(connection, create_users_table)

پرس و جو زیر برای ایجاد جدول ها استفاده می شود:

create_posts_table = """
CREATE TABLE IF NOT EXISTS posts(
  id INTEGER PRIMARY KEY AUTOINCREMENT, 
  title TEXT NOT NULL, 
  description TEXT NOT NULL, 
  user_id INTEGER NOT NULL, 
  FOREIGN KEY (user_id) REFERENCES users (id)
);
"""

از آنجایی که یک رابطه یک به چند بین کاربران و ها وجود دارد، می توانید یک user_id کلید خارجی را در جدول ها مشاهده کنید که به ستون شناسه در جدول کاربران ارجاع می دهد. اسکریپت زیر را برای ایجاد جدول ها اجرا کنید:

execute_query(connection, create_posts_table)

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

create_comments_table = """
CREATE TABLE IF NOT EXISTS comments (
  id INTEGER PRIMARY KEY AUTOINCREMENT, 
  text TEXT NOT NULL, 
  user_id INTEGER NOT NULL, 
  post_id INTEGER NOT NULL, 
  FOREIGN KEY (user_id) REFERENCES users (id) FOREIGN KEY (post_id) REFERENCES posts (id)
);
"""

create_likes_table = """
CREATE TABLE IF NOT EXISTS likes (
  id INTEGER PRIMARY KEY AUTOINCREMENT, 
  user_id INTEGER NOT NULL, 
  post_id integer NOT NULL, 
  FOREIGN KEY (user_id) REFERENCES users (id) FOREIGN KEY (post_id) REFERENCES posts (id)
);
"""

execute_query(connection, create_comments_table)  
execute_query(connection, create_likes_table)

می بینید که ایجاد جداول در SQLite بسیار شبیه به استفاده از SQL خام است. تنها کاری که باید انجام دهید این است که پرس و جو را در یک متغیر رشته ذخیره کنید و سپس آن متغیر را به cursor.execute() منتقل کنید.

مایاسکیوال

شما از ماژول mysql-connector-python Python SQL برای ایجاد جداول در MySQL استفاده خواهید کرد. درست مانند SQLite، باید پرس و جو خود را به cursor.execute() ارسال کنید، که با فراخوانی .cursor() روی شی اتصال برگردانده می شود. می توانید تابع دیگری execute_query() ایجاد کنید که اتصال و رشته پرس و جو را می پذیرد:

def execute_query(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        connection.commit()
        print("Query executed successfully")
    except Error as e:
        print(f"The error '{e}' occurred")

در خط 4، پرس و جو را به cursor.execute() ارسال می کنید.

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

create_users_table = """
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT, 
  name TEXT NOT NULL, 
  age INT, 
  gender TEXT, 
  nationality TEXT, 
  PRIMARY KEY (id)
) ENGINE = InnoDB
"""

execute_query(connection, create_users_table)

پرس و جو برای پیاده سازی رابطه کلید خارجی در MySQL در مقایسه با SQLite کمی متفاوت است. علاوه بر این، MySQL از کلمه کلیدی AUTO_INCREMENT (در مقایسه با کلمه کلیدی SQLite AUTOINCREMENT) برای ایجاد ستون هایی استفاده می کند که در آن مقادیر به طور خودکار با درج رکوردهای جدید افزایش می یابند.

اسکریپت زیر جدول ها را ایجاد می کند که شامل یک کلید خارجی user_id است که به ستون شناسه جدول کاربران ارجاع می دهد:

create_posts_table = """
CREATE TABLE IF NOT EXISTS posts (
  id INT AUTO_INCREMENT, 
  title TEXT NOT NULL, 
  description TEXT NOT NULL, 
  user_id INTEGER NOT NULL, 
  FOREIGN KEY fk_user_id (user_id) REFERENCES users(id), 
  PRIMARY KEY (id)
) ENGINE = InnoDB
"""

execute_query(connection, create_posts_table)

به طور مشابه، برای ایجاد جداول نظرات و لایک، می توانید پرس و جوهای CREATE مربوطه را به execute_query() ارسال کنید.

پستگرسکیوئل

مانند پایگاه داده های SQLite و MySQL، شی اتصالی که توسط psycopg2.connect() برگردانده می شود حاوی یک شی مکان نما است. می توانید از cursor.execute() برای اجرای کوئری های SQL پایتون در پایگاه داده PostgreSQL خود استفاده کنید.

یک تابع execute_query()را تعریف کنید:

def execute_query(connection, query):
    connection.autocommit = True
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        print("Query executed successfully")
    except OperationalError as e:
        print(f"The error '{e}' occurred")

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

اکنون جدول کاربران را در پایگاه داده sm_app ایجاد کنید:

create_users_table = """
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL, 
  age INTEGER,
  gender TEXT,
  nationality TEXT
)
"""

execute_query(connection, create_users_table)

می بینید که پرس و جو برای ایجاد جدول کاربران در PostgreSQL کمی متفاوت از SQLite و MySQL است. در اینجا، کلمه کلیدی SERIAL برای ایجاد ستون هایی استفاده می شود که به طور خودکار افزایش می یابند. به یاد بیاورید که MySQL از کلمه کلیدی AUTO_INCREMENT استفاده می کند.

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

create_posts_table = """
CREATE TABLE IF NOT EXISTS posts (
  id SERIAL PRIMARY KEY, 
  title TEXT NOT NULL, 
  description TEXT NOT NULL, 
  user_id INTEGER REFERENCES users(id)
)
"""

execute_query(connection, create_posts_table)

برای ایجاد جدول نظرات، باید یک پرس و جو CREATE برای جدول نظرات بنویسید و آن را به execute_query() ارسال کنید. روند ایجاد جدول لایک ها یکسان است. شما فقط باید پرس و جو CREATE را تغییر دهید تا جدول لایک ها را به جای جدول نظرات ایجاد کنید.

درج رکوردها

در بخش قبل، دیدید که چگونه با استفاده از ماژول های مختلف SQL پایتون، جداول را در پایگاه داده های SQLite ،MySQL و PostgreSQL ایجاد کنید. در این بخش، نحوه درج رکوردها در جداول خود را خواهید دید.

اسکیوالایت

برای درج رکوردها در پایگاه داده SQLite خود، می توانید از همان تابع execute_query() استفاده کنید که برای ایجاد جداول استفاده کرده اید. ابتدا باید پرس و جو INSERT INTO خود را در یک رشته ذخیره کنید. سپس، می توانید شی اتصال و رشته پرس و جو را به execute_query() ارسال کنید. بیایید پنج رکورد را در جدول کاربران وارد کنیم:

create_users = """
INSERT INTO
  users (name, age, gender, nationality)
VALUES
  ('James', 25, 'male', 'USA'),
  ('Leila', 32, 'female', 'France'),
  ('Brigitte', 35, 'female', 'England'),
  ('Mike', 40, 'male', 'Denmark'),
  ('Elizabeth', 21, 'female', 'Canada');
"""

execute_query(connection, create_users)

از آنجایی که ستون id را روی افزایش خودکار تنظیم می کنید، نیازی به تعیین مقدار ستون id برای این کاربران ندارید. جدول کاربران به طور خودکار این پنج رکورد را با مقادیر شناسه از 1 تا 5 پر می کند.

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

create_posts = """
INSERT INTO
  posts (title, description, user_id)
VALUES
  ("Happy", "I am feeling very happy today", 1),
  ("Hot Weather", "The weather is very hot today", 2),
  ("Help", "I need some help with my work", 2),
  ("Great News", "I am getting married", 1),
  ("Interesting Game", "It was a fantastic game of tennis", 5),
  ("Party", "Anyone up for a late-night party today?", 3);
"""

execute_query(connection, create_posts)

ذکر این نکته ضروری است که ستون user_id جدول ها یک کلید خارجی است که به ستون شناسه جدول کاربران ارجاع می دهد. این بدان معنی است که ستون user_id باید حاوی مقداری باشد که از قبل در ستون id جدول کاربران وجود دارد. اگر وجود نداشته باشد، خطایی مشاهده خواهید کرد.

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

create_comments = """
INSERT INTO
  comments (text, user_id, post_id)
VALUES
  ('Count me in', 1, 6),
  ('What sort of help?', 5, 3),
  ('Congrats buddy', 2, 4),
  ('I was rooting for Nadal though', 4, 5),
  ('Help with your thesis?', 2, 3),
  ('Many congratulations', 5, 4);
"""

create_likes = """
INSERT INTO
  likes (user_id, post_id)
VALUES
  (1, 6),
  (2, 3),
  (1, 5),
  (5, 4),
  (2, 4),
  (4, 2),
  (3, 6);
"""

execute_query(connection, create_comments)
execute_query(connection, create_likes)

در هر دو مورد، کوئری INSERT INTO خود را به عنوان یک رشته ذخیره می کنید و آن را با execute_query() اجرا می کنید.

مایاسکیوال

دو راه برای درج رکوردها در پایگاه داده های MySQL از یک برنامه پایتون وجود دارد. رویکرد اول مشابه SQLite است. می توانید پرس و جو INSERT INTO را در یک رشته ذخیره کنید و سپس از cursor.execute() برای درج رکوردها استفاده کنید.

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

create_users = """
INSERT INTO
  `users` (`name`, `age`, `gender`, `nationality`)
VALUES
  ('James', 25, 'male', 'USA'),
  ('Leila', 32, 'female', 'France'),
  ('Brigitte', 35, 'female', 'England'),
  ('Mike', 40, 'male', 'Denmark'),
  ('Elizabeth', 21, 'female', 'Canada');
"""

execute_query(connection, create_users)

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

رشته پرس و جو حاوی متغیرهای رکوردهایی که باید درج شوند
لیست رکوردهایی که می خواهید درج کنید

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

sql = "INSERT INTO likes ( user_id, post_id ) VALUES ( %s, %s )"
val = [(4, 5), (3, 4)]

cursor = connection.cursor()
cursor.executemany(sql, val)
connection.commit()

این به شما بستگی دارد که کدام روش را برای درج رکوردها در جدول MySQL خود انتخاب کنید. اگر در SQL متخصص هستید، می توانید از .execute() استفاده کنید. اگر با SQL آشنایی زیادی ندارید، ممکن است استفاده از ().executemany() برای شما ساده تر باشد. با هر یک از این دو رویکرد، می توانید با موفقیت رکوردها را در جداول ها، نظرات و لایک ها وارد کنید.

پستگریسکیوال

در بخش قبلی، دو روش برای درج رکوردها در جداول پایگاه داده SQLite مشاهده کردید. اولی از پرس و جو رشته SQL و دومی از .executemany() استفاده می کند. PsycopG2 از این رویکرد دوم پیروی می کند، اگرچه .execute() برای اجرای یک پرس و جو مبتنی بر مکان نگهدارنده استفاده می شود.

شما کوئری SQL را با متغیرها و لیست رکوردها را به .execute() ارسال می کنید. هر رکورد در لیست یک تاپل خواهد بود که در آن مقادیر تاپل با مقادیر ستون در جدول پایگاه داده مطابقت دارد. در اینجا نحوه درج رکوردهای کاربر در جدول کاربران در پایگاه داده PostgreSQL آورده شده است:

users = [
    ("James", 25, "male", "USA"),
    ("Leila", 32, "female", "France"),
    ("Brigitte", 35, "female", "England"),
    ("Mike", 40, "male", "Denmark"),
    ("Elizabeth", 21, "female", "Canada"),
]

user_records = ", ".join(["%s"] * len(users))

insert_query = (
    f"INSERT INTO users (name, age, gender, nationality) VALUES {user_records}"
)

connection.autocommit = True
cursor = connection.cursor()
cursor.execute(insert_query, users)

اسکریپت بالا لیستی از کاربران ایجاد می کند که شامل پنج رکورد کاربر به شکل تاپل است. در مرحله بعد، یک رشته placeholder با پنج عنصر placeholder (٪s) ایجاد می کنید که با پنج رکورد کاربر مطابقت دارد. رشته نگهدارنده مکان با پرس و جوی که رکوردها را در جدول کاربران درج می کند، متصل می شود. در نهایت، رشته پرس و جو و رکوردهای کاربر به .execute() ارسال می شوند. اسکریپت بالا با موفقیت پنج رکورد را در جدول کاربران درج می کند.

به مثال دیگری از درج رکوردها در جدول PostgreSQL نگاهی بیندازید. اسکریپت زیر رکوردها را در جدول ها درج می کند:

posts = [
    ("Happy", "I am feeling very happy today", 1),
    ("Hot Weather", "The weather is very hot today", 2),
    ("Help", "I need some help with my work", 2),
    ("Great News", "I am getting married", 1),
    ("Interesting Game", "It was a fantastic game of tennis", 5),
    ("Party", "Anyone up for a late-night party today?", 3),
]

post_records = ", ".join(["%s"] * len(posts))

insert_query = (
    f"INSERT INTO posts (title, description, user_id) VALUES {post_records}"
)

connection.autocommit = True
cursor = connection.cursor()
cursor.execute(insert_query, posts)

با همین روش می توانید سوابق را در جداول نظرات و لایک ها وارد کنید.

انتخاب رکوردها

در این بخش، نحوه انتخاب رکوردها از جداول پایگاه داده با استفاده از ماژول های مختلف Python SQL را خواهید دید. به طور خاص، خواهید دید که چگونه کوئری های SELECT را در پایگاه داده های SQLite ،MySQL و PostgreSQL خود انجام دهید.

اسکیوالایت

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

برای ساده سازی فرآیند، می توانید یک تابع ایجاد کنید execute_read_query():

def execute_read_query(connection, query):
    cursor = connection.cursor()
    result = None
    try:
        cursor.execute(query)
        result = cursor.fetchall()
        return result
    except Error as e:
        print(f"The error '{e}' occurred")

این تابع شی اتصال و پرس و جو SELECT را می پذیرد و رکورد انتخاب شده را برمی گرداند.

`انتخاب`

بیایید اکنون تمام رکوردها را از جدول کاربران انتخاب کنیم:

select_users = "SELECT * from users"
users = execute_read_query(connection, select_users)

for user in users:
    print(user)

در اسکریپت بالا، پرس و جو SELECT همه کاربران را از جدول کاربران انتخاب می کند. این به execute_read_query() منتقل می شود که تمام رکوردها را از جدول کاربران برمی گرداند. سپس سوابق پیمایش شده و در کنسول چاپ می شوند.

** توجه:** استفاده از SELECT * در جداول بزرگ توصیه نمی شود زیرا می تواند منجر به تعداد زیادی عملیات I/O شود که ترافیک شبکه را افزایش می دهد.

خروجی پرس و جو بالا به این شکل است:

(1, 'James', 25, 'male', 'USA')
(2, 'Leila', 32, 'female', 'France')
(3, 'Brigitte', 35, 'female', 'England')
(4, 'Mike', 40, 'male', 'Denmark')
(5, 'Elizabeth', 21, 'female', 'Canada')

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

select_posts = "SELECT * FROM posts"
posts = execute_read_query(connection, select_posts)

for post in posts:
    print(post)

خروجی به این شکل است:

(1, 'Happy', 'I am feeling very happy today', 1)
(2, 'Hot Weather', 'The weather is very hot today', 2)
(3, 'Help', 'I need some help with my work', 2)
(4, 'Great News', 'I am getting married', 1)
(5, 'Interesting Game', 'It was a fantastic game of tennis', 5)
(6, 'Party', 'Anyone up for a late-night party today?', 3)

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

`پیوستن`

همچنین می توانید کوئری های پیچیده شامل عملیات JOIN را برای بازیابی داده ها از دو جدول مرتبط اجرا کنید. به عنوان مثال، اسکریپت زیر شناسه ها و نام های کاربری را به همراه توضیحات هایی که این کاربران ارسال کرده اند برمی گرداند:

select_users_posts = """
SELECT
  users.id,
  users.name,
  posts.description
FROM
  posts
  INNER JOIN users ON users.id = posts.user_id
"""

users_posts = execute_read_query(connection, select_users_posts)

for users_post in users_posts:
    print(users_post)

در اینجا خروجی آمده است:

(1, 'James', 'I am feeling very happy today')
(2, 'Leila', 'The weather is very hot today')
(2, 'Leila', 'I need some help with my work')
(1, 'James', 'I am getting married')
(5, 'Elizabeth', 'It was a fantastic game of tennis')
(3, 'Brigitte', 'Anyone up for a late night party today?')

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

select_posts_comments_users = """
SELECT
  posts.description as post,
  text as comment,
  name
FROM
  posts
  INNER JOIN comments ON posts.id = comments.post_id
  INNER JOIN users ON users.id = comments.user_id
"""

posts_comments_users = execute_read_query(
    connection, select_posts_comments_users
)

for posts_comments_user in posts_comments_users:
    print(posts_comments_user)

خروجی به این شکل است:

('Anyone up for a late night party today?', 'Count me in', 'James')
('I need some help with my work', 'What sort of help?', 'Elizabeth')
('I am getting married', 'Congrats buddy', 'Leila')
('It was a fantastic game of tennis', 'I was rooting for Nadal though', 'Mike')
('I need some help with my work', 'Help with your thesis?', 'Leila')
('I am getting married', 'Many congratulations', 'Elizabeth')

از خروجی می توانید ببینید که نام ستون ها توسط .fetchall() برگردانده نمی شوند. برای بازگرداندن نام ستون ها، می توانید از ویژگی .description شی مکان نما استفاده کنید. به عنوان مثال، لیست زیر تمام نام ستون های پرس و جو بالا را برمی گرداند:

cursor = connection.cursor()
cursor.execute(select_posts_comments_users)
cursor.fetchall()

column_names = [description[0] for description in cursor.description]
print(column_names)

خروجی به این شکل است:

['post', 'comment', 'name']

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

`آن`

اکنون یک پرس و جو SELECT اجرا می کنید که را به همراه تعداد کل لایک هایی که دریافت کرده است برمی گرداند:

select_post_likes = """
SELECT
  description as Post,
  COUNT(likes.id) as Likes
FROM
  likes,
  posts
WHERE
  posts.id = likes.post_id
GROUP BY
  likes.post_id
"""

post_likes = execute_read_query(connection, select_post_likes)

for post_like in post_likes:
    print(post_like)

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

('The weather is very hot today', 1)
('I need some help with my work', 1)
('I am getting married', 2)
('It was a fantastic game of tennis', 1)
('Anyone up for a late night party today?', 2)

با استفاده از بند WHERE، می توانید نتایج خاص تری را برگردانید.

مایاسکیول

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

def execute_read_query(connection, query):
    cursor = connection.cursor()
    result = None
    try:
        cursor.execute(query)
        result = cursor.fetchall()
        return result
    except Error as e:
        print(f"The error '{e}' occurred")

اکنون تمام رکوردها را از جدول کاربران انتخاب کنید:

select_users = "SELECT * FROM users"
users = execute_read_query(connection, select_users)

for user in users:
    print(user)

خروجی مشابه چیزی خواهد بود که با SQLite دیدید.

پستگرسکیو ال

فرآیند انتخاب رکوردها از جدول PostgreSQL با ماژول Python SQL psycopg2 مشابه کاری است که شما با SQLite و MySQL انجام دادید. باز هم، از cursor.execute() و سپس .fetchall() برای انتخاب رکوردها از جدول PostgreSQL خود استفاده خواهید کرد. اسکریپت زیر تمام رکوردها را از جدول کاربران انتخاب کرده و در کنسول چاپ می کند:

def execute_read_query(connection, query):
    cursor = connection.cursor()
    result = None
    try:
        cursor.execute(query)
        result = cursor.fetchall()
        return result
    except OperationalError as e:
        print(f"The error '{e}' occurred")

select_users = "SELECT * FROM users"
users = execute_read_query(connection, select_users)

for user in users:
    print(user)

باز هم، خروجی مشابه آنچه قبلا دیده اید خواهد بود.

بروزرسانی رکوردهای جدول

در بخش آخر نحوه انتخاب رکوردها از پایگاه داده های SQLite ،MySQL و PostgreSQL را مشاهده کردید. در این بخش، فرآیند به روز رسانی رکوردها با استفاده از کتابخانه های Python SQL برای SQLite ،PostgresSQL و MySQL را پوشش خواهید داد.

اسکیوالایت

به روز رسانی رکوردها در SQLite بسیار ساده است. می توانید دوباره از execute_query() استفاده کنید. به عنوان مثال، می توانید توضیحات را با شناسه 2 به روز کنید. ابتدا توضیحات این را انتخاب کنید:

select_post_description = "SELECT description FROM posts WHERE id = 2"

post_description = execute_read_query(connection, select_post_description)

for description in post_description:
    print(description)

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

('The weather is very hot today',)

اسکریپت زیر توضیحات را به روز می کند:

update_post_description = """
UPDATE
  posts
SET
  description = "The weather has become pleasant now"
WHERE
  id = 2
"""

execute_query(connection, update_post_description)

اکنون، اگر پرس و جو SELECT را دوباره اجرا کنید، باید نتیجه زیر را مشاهده کنید:

('The weather has become pleasant now',)

خروجی به روز شده است.

مایاسکیوال

فرآیند به روز رسانی رکوردها در MySQL با mysql-connector-python نیز یک کپی کربنی از ماژول sqlite3 Python SQL است. شما باید پرس و جو رشته را به cursor.execute() ارسال کنید. به عنوان مثال، اسکریپت زیر توضیحات را با شناسه 2 به روز می کند:

update_post_description = """
UPDATE
  posts
SET
  description = "The weather has become pleasant now"
WHERE
  id = 2
"""

execute_query(connection,  update_post_description)

باز هم، شما از تابع wrapper خود execute_query() برای به روز رسانی توضیحات استفاده کرده اید.

پستگریسکیوال

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

حذف رکوردهای جدول

در این بخش، نحوه حذف رکوردهای جدول با استفاده از ماژول های Python SQL برای پایگاه داده های SQLite، MySQL و PostgreSQL را خواهید دید. فرآیند حذف رکوردها برای هر سه پایگاه داده یکنواخت است زیرا پرس و جو DELETE برای سه پایگاه داده یکسان است.

اسکیوالایت

می توانید دوباره از execute_query() برای حذف رکوردها از پایگاه داده SQLite خود استفاده کنید. تنها کاری که باید انجام دهید این است که شی اتصال و پرس و جو رشته ای را برای رکوردی که می خواهید حذف کنید به execute_query() ارسال کنید. سپس، execute_query() با استفاده از اتصال یک شی مکان نما ایجاد می کند و پرس و جو رشته را به cursor.execute() ارسال می کند که رکوردها را حذف می کند.

به عنوان مثال، سعی کنید نظر را با شناسه 5 حذف کنید:

delete_comment = "DELETE FROM comments WHERE id = 5"
execute_query(connection, delete_comment)

حال اگر تمام رکوردها را از جدول نظرات انتخاب کنید، میبینید که کامنت پنجم حذف شده است.

مایاسکیوال

فرآیند حذف در MySQL نیز مشابه SQLite است، همانطور که در مثال زیر نشان داده شده است:

delete_comment = "DELETE FROM comments WHERE id = 2"
execute_query(connection, delete_comment)

در اینجا، نظر دوم را از جدول نظرات پایگاه داده sm_app در سرور پایگاه داده MySQL خود حذف می کنید.

پستگریسکیو ال

پرس و جو حذف برای PostgreSQL نیز مشابه SQLite و MySQL است. می توانید با استفاده از کلمه کلیدی DELETE و سپس ارسال پرس و جو و شی اتصال به execute_query() یک رشته پرس و جو حذف بنویسید. با این کار رکوردهای مشخص شده از پایگاه داده PostgreSQL شما حذف می شود.

نتیجه

در این آموزش، نحوه استفاده از سه کتابخانه رایج SQL پایتون را یاد گرفتید. sqlite3، mysql-connector-python و psycopg2 به شما این امکان را می دهند که یک برنامه پایتون را به ترتیب به پایگاه داده های SQLite، MySQL و PostgreSQL متصل کنید.

اکنون می توانید:

تعامل با پایگاه داده های SQLite ،MySQL یا PostgreSQL
استفاده از سه ماژول مختلف SQL پایتون
اجرای کوئری های SQL در پایگاه داده های مختلف از داخل یک اپلیکیشن پایتون

با این حال، این فقط نوک کوه یخ است! همچنین کتابخانه های SQL پایتون برای نگاشت شی-رابطه ای مانند SQLAlchemy و Django ORM وجود دارد که کار تعامل پایگاه داده را در پایتون خودکار می کند. در مورد این کتابخانه ها در آموزش های دیگر در بخش پایگاه داده های پایتون ما بیشتر خواهید آموخت.