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

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

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

دوره مرتبط: دوره آموزش زبان SQL

 #  آشنایی با schema دیتابیس

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

1. users
2. posts
3. comments
4. likes

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

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

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

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

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

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

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

 +  اتصال به SQLite با پایتون

SQLite احتمالاً ساده ترین پایگاه داده برای اتصال با برنامه پایتون است زیرا برای انجام این کار نیازی به نصب ماژول های خارجی SQL پایتون ندارید. به طور پیش فرض، نصب پایتون شما حاوی یک کتابخانه Python 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 در دایرکتوری ریشه ایجاد شده است. توجه داشته باشید که می توانید مکان را مطابق با تنظیمات خود تغییر دهید.

 +  اتصال به MySQL با پایتون

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

$ pip install mysql-connector-python

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

1. ایجاد یک اتصال به سرور دیتابیس mysql
2. اجرای یک دستور برای ساخت دیتابیس

تابعی را تعریف کنید که به سرور پایگاه داده 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 تعریف می کنید که سه پارامتر را می پذیرد:

1. host_name
2. user_name
3. user_password

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

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

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

این تابع به شکل زیر است:

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 استفاده می کنید. کوئری که باید اجرا شود با فرمت رشته ای به ()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 # <---- new
        )
        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 متصل می شود.

مقاله پیشنهادی: OrderedDict و dict در پایتون

 +  اتصال به PostgreSQL با پایتون

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

$ pip install psycopg2

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

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 به شماره پورت سرور پایگاه داده اشاره دارد.

مقاله پیشنهادی: راهنمایی کامل پایتون و rest api

 #  استفاده از کتابخانه‌های sql پایتون برای ساخت table دیتابیس

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

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

1. users
2. likes
3. comments
4. posts

 +  ساخت جدول در SQLite با پایتون

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

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

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

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")

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

بعد، درخواست خود را بنویسید:

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

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

1. id
2. name
3. age
4. gender
5. nationality

در نهایت، execute_query() را برای ایجاد جدول فراخوانی می کنید. شیء اتصالی را که در بخش قبلی ایجاد کرده‌اید، به همراه رشته create_users_table که شامل عبارت create 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 کلید خارجی را در جدول پست‌ها ببینید که به ستون 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 خام است. تنها کاری که باید انجام دهید این است که query را در یک متغیر رشته ای ذخیره کنید و سپس آن متغیر را به cursor.execute() بفرستید.

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

 +  ساخت جدول در MySQL با پایتون

شما از ماژول mysql-connector-python  برای ایجاد جداول در 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، query را به 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 است که به ستون 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() ارسال کنید.

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

 +  ساخت جدول در PostgreSQL با پایتون

مانند پایگاه‌های داده SQLite و MySQL، شی اتصالی که توسط psycopg2.connect() برگردانده می‌شود حاوی یک شی مکان‌نما است. می توانید از cursor.execute() برای اجرای کوئری های Python 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)

می بینید که query برای ایجاد جدول کاربران در 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 را تغییر دهید تا به جای جدول نظرات، جدول لایک ایجاد شود.

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

 #  استفاده از کتابخانه‌های sql پایتون برای وارد کردن اطلاعات در دیتابیس

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

 +  وارد کردن اطلاعات در SQLite با پایتون

برای درج رکوردها در پایگاه داده 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 جدول پست ها یک کلید خارجی است که به ستون 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 با پایتون

دو راه برای درج رکوردها در پایگاه داده 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() استفاده می کند که دو پارامتر را می پذیرد:

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

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

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() ساده تر باشد. با هر یک از این دو رویکرد، می‌توانید با موفقیت رکوردها را در جداول پست‌ها، نظرات و لایک‌ها وارد کنید.

مقاله پیشنهادی: importهای مطلق و نسبی در پایتون

 +  وارد کردن اطلاعات در PostgreSQL با پایتون

در بخش قبل، دو روش برای درج رکوردها در جداول پایگاه داده 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 با پنج عنصر مکان‌نما (%s) ایجاد می‌کنید که با پنج رکورد کاربر مطابقت دارد. رشته placeholder با پرس و جوی که رکوردها را در جدول کاربران قرار می دهد، الحاق می شود. در نهایت، رشته query و رکوردهای کاربر به .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)

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

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

 #  استفاده از کتابخانه‌های sql پایتون برای دریافت اطلاعات در دیتابیس

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

 +  خواندن اطلاعات از SQLite با پایتون

برای انتخاب رکوردها با استفاده از 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 را می پذیرد و رکورد انتخاب شده را برمی گرداند.

مقاله پیشنهادی: متد append پایتون

 -  دستور SELECT

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

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

for user in users:
    print(user)

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

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

(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

همچنین می توانید پرس و جوهای پیچیده شامل عملیات 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 استفاده کنید. به عنوان مثال، لیست زیر تمام نام ستون ها را برای پرس و جو فوق برمی گرداند:

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']

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

مقاله پیشنهادی: استفاده از عملگر and در پایتون

 -  دستور WHERE

اکنون یک کوئری 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 با پایتون

فرآیند انتخاب رکوردها در MySQL کاملاً مشابه انتخاب رکوردها در 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_users = "SELECT * FROM users"
users = execute_read_query(connection, select_users)

for user in users:
    print(user)

خروجی مشابه آنچه در SQLite دیدید خواهد بود.

ویدیو پیشنهادی: ویدیو آموزش آبجکت های container در پایتون

 +  خواندن اطلاعات از PostgreSQL با پایتون

فرآیند انتخاب رکوردها از جدول PostgreSQL با ماژول psycopg2 Python SQL مشابه کاری است که با 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)

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

 #  استفاده از کتابخانه‌های sql پایتون برای بروزرسانی اطلاعات در دیتابیس

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

 +  آپدیت اطلاعات از SQLite با پایتون

به روز رسانی رکوردها در 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 با mysql-connector-python نیز یک کپی از ماژول sqlite3 Python SQL است. شما باید query رشته را به 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 با پایتون

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

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

 #  استفاده از کتابخانه‌های sql پایتون برای حذف اطلاعات در دیتابیس

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

 +  حذف اطلاعات از SQLite با پایتون

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

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

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

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

 +  حذف اطلاعات از MySQL با پایتون

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

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

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

 +  حذف اطلاعات از PostgreSQL با پایتون

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

 #  نتیجه گیری

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