کار با MySQL در پایتون — جامع و کاربردی

کانکتور MySQL در پایتون یک درایور است که به یکپارچه‌سازی MySQL در پایتون کمک می‌کند. این کتابخانه Python MySQL امکان تبدیل بین نوع داده‌های پایتون و MySQL را فراهم می‌کند. رابط برنامه‌نویسی کانکتور MySQL به طور کامل با استفاده از پایتون خالص پیاده‌سازی شده و به هیچ کتابخانه شخص ثالثی نیاز ندارد.

مقدمه

MySQL در پایتون یکی از روش‌های رایج برای مدیریت و پردازش داده‌ها «Data processing» در برنامه‌های کاربردی است. MySQL در پایتون یک سیستم مدیریت پایگاه داده رابطه‌ای «RDBMS» است که برای ذخیره و بازیابی داده‌ها «Saved and retrieved data» به صورت ساختاریافته «Structured» طراحی شده است. پایتون «Python» به دلیل سادگی و قدرت خود، ابزاری عالی برای اتصال به پایگاه‌های داده MySQL و انجام عملیات مختلف مانند ایجاد، خواندن، به‌روزرسانی و حذف داده‌ها «CRUD» فراهم می‌آورد.

با استفاده از کتابخانه‌هایی مانند mysql.connector، می‌توان به راحتی با سرور MySQL ارتباط برقرار کرد و از امکانات آن برای ذخیره و پردازش داده‌ها بهره برد. این تعاملات به‌ویژه برای ساخت برنامه‌های کاربردی پیچیده‌ای که نیاز به پایگاه داده دارند، ضروری است. همچنین، پایتون ابزارهایی را برای مدیریت خطاها «Error management» بهینه‌سازی عملکرد «Performance optimization» و تضمین امنیت «Security assurance» در ارتباط با پایگاه داده «Database» فراهم می‌کند.

در این زمینه، یادگیری نحوه کار با MySQL در پایتون به شما کمک می‌کند تا به راحتی داده‌ها را مدیریت کرده و برنامه‌های مقیاس‌پذیر و کارآمد بسازید.

نصب MySQL در پایتون

برای نصب ماژول Python-MySQL-Connector، لازم است که پایتون و PIP از قبل روی سیستم نصب شده باشند. اگر پایتون و PIP از قبل نصب شده‌اند، دستور زیر را در ترمینال وارد کنید.

pip3 installiert MySQL-Connector-Python

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

اتصال به سرور MySQL در پایتون

می‌توانیم با استفاده از متد ()connect به سرور MySQL متصل شویم.

# importing required libraries
import mysql.connector

dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password"
)

print(dataBase)

# Disconnecting from the server
dataBase.close()

خروجی:

<mysql.connector.connection_cext.CMySQLConnection object at 0x7f73f0191d00>

توجه: برای اطلاعات بیشتر به اتصال به پایگاه داده MySQL با استفاده از MySQL-Connector Python مراجعه کنید.

ایجاد پایگاه داده در پایتون

پس از اتصال به سرور MySQL، بیایید ببینیم چگونه می‌توان یک پایگاه داده MySQL را با استفاده از پایتون ایجاد کرد. برای این کار ابتدا یک شی ()cursor ایجاد می‌کنیم و سپس دستور SQL را به‌صورت یک رشته به متد ()execute ارسال می‌کنیم. دستور SQL برای ایجاد یک پایگاه داده به این صورت است:

CREATE DATABASE DATABASE_NAME

مثال: ایجاد پایگاه داده MySQL با استفاده از پایتون

# importing required libraries
import mysql.connector

dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password"
)

# preparing a cursor object
cursorObject = dataBase.cursor()

# creating database
cursorObject.execute("CREATE DATABASE PS")

خروجی:

برای ایجاد جداول، همانند روش قبلی دستورهای SQL را به‌صورت رشته نوشته و سپس آن‌ها را به متد ()execute شی cursor ارسال می‌کنیم. دستور SQL برای ایجاد یک جدول به این صورت است:

CREATE TABLE
(
    column_name_1 column_Data_type,
    column_name_2 column_Data_type,
    :
    :
    column_name_n column_Data_type
);

مثال: ایجاد جدول MySQL با استفاده از پایتون

# importing required libraries
import mysql.connector

dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password",
database = "PS"
)

# preparing a cursor object
cursorObject = dataBase.cursor()

# creating table 
studentRecord = """CREATE TABLE STUDENT (
    NAME VARCHAR(20) NOT NULL,
    BRANCH VARCHAR(50),
    ROLL INT NOT NULL,
    SECTION VARCHAR(5),
    AGE INT
    )"""

# table created
cursorObject.execute(studentRecord) 

# disconnecting from server
dataBase.close()

خروجی:

وارد کردن داده‌ها به جداول

برای وارد کردن داده‌ها به جدول MySQL از کوئری Insert into استفاده می‌شود.

نحو یا سینتکس:

INSERT INTO table_name (column_names) VALUES (data)

مثال ۱: وارد کردن یک ردیف داده

# importing required libraries
import mysql.connector

dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password",
database = "PS"
)

# preparing a cursor object
cursorObject = dataBase.cursor()

sql = "INSERT INTO STUDENT (NAME, BRANCH, ROLL, SECTION, AGE)\
VALUES (%s, %s, %s, %s, %s)"
val = ("Ram", "CSE", "85", "B", "19")

cursorObject.execute(sql, val)
dataBase.commit()

# disconnecting from server
dataBase.close()

خروجی:

مثال ۲: وارد کردن چندین ردیف داده

برای وارد کردن چندین مقدار به‌طور هم‌زمان از متد ()executemany استفاده می‌شود. این متد از روی دنباله پارامترها تکرار می‌کند و پارامتر فعلی را به متد execute ارسال می‌کند.

# importing required libraries
import mysql.connector

dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password",
database = "PS"
)

# preparing a cursor object
cursorObject = dataBase.cursor()

sql = "INSERT INTO STUDENT (NAME, BRANCH, ROLL, SECTION, AGE)\
VALUES (%s, %s, %s, %s, %s)"
val = [("Nikhil", "CSE", "98", "A", "18"),
 ("Nisha", "CSE", "99", "A", "18"),
 ("Rohan", "MAE", "43", "B", "20"),
 ("Amit", "ECE", "24", "A", "21"),
 ("Anil", "MAE", "45", "B", "20"), 
 ("Megha", "ECE", "55", "A", "22"), 
 ("Sita", "CSE", "95", "A", "19")]

cursorObject.executemany(sql, val)
dataBase.commit()

# disconnecting from server
dataBase.close()

خروجی:

استخراج داده‌ ها در پایتون

برای انتخاب داده‌ها از جداول MySQL می‌توانیم از کوئری select به روش‌های زیر استفاده کنیم:

برای انتخاب ستون‌های خاص از جدول، نام ویژگی‌ها «ستون‌ها» را می‌نویسیم.

SELECT attr1, attr2 FROM table_name

برای انتخاب تمام ستون‌های ویژگی از یک جدول، از نماد ستاره * استفاده می‌کنیم.

SELECT * FROM table_name

مثال: انتخاب داده‌ها از جدول MySQL با استفاده از پایتون

# importing required libraries
import mysql.connector

dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password",
database = "PS"
)

# preparing a cursor object
cursorObject = dataBase.cursor()

query = "SELECT NAME, ROLL FROM STUDENT"
cursorObject.execute(query)

myresult = cursorObject.fetchall()

for x in myresult:
 print(x)

# disconnecting from server
dataBase.close()

خروجی:

عبارت Where

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

نحو یا سینتکس:

SELECT column1, column2, …. columnN FROM [TABLE NAME] WHERE [CONDITION];

مثال: استفاده از عبارت Where در MySQL با استفاده از پایتون

# importing required libraries
import mysql.connector

dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password",
database = "PS"
)

# preparing a cursor object
cursorObject = dataBase.cursor()

query = "SELECT * FROM STUDENT where AGE >=20"
cursorObject.execute(query)

myresult = cursorObject.fetchall()

for x in myresult:
 print(x)

# disconnecting from server
dataBase.close()

خروجی:

('Rohan', 'MAE', 43, 'B', 20)
('Amit', 'ECE', 24, 'A', 21)
('Anil', 'MAE', 45, 'B', 20)
('Megha', 'ECE', 55, 'A', 22)

عبارت Order By

عبارت Order By برای ترتیب‌دهی نتایج به ترتیب صعودی یا نزولی استفاده می‌شود. به‌طور پیش‌فرض، نتایج همیشه به ترتیب صعودی مرتب می‌شوند مگر اینکه «DESC» ذکر شود که نتایج را به ترتیب نزولی مرتب می‌کند. همچنین، «ASC» می‌تواند به‌طور صریح برای ترتیب‌دهی صعودی استفاده شود، اما معمولا این کار انجام نمی‌شود چون پیش‌فرض همین ترتیب است.

نحو یا سینتکس:

SELECT column1, column2
FROM table_name
ORDER BY column_name ASC|DESC;

مثال: استفاده از عبارت Order By در MySQL با استفاده از پایتون

# importing required libraries
import mysql.connector

dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password",
database = "PS"
)

# preparing a cursor object
cursorObject = dataBase.cursor()

query = "SELECT * FROM STUDENT ORDER BY NAME DESC"
cursorObject.execute(query)

myresult = cursorObject.fetchall()

for x in myresult:
 print(x)

# disconnecting from server
dataBase.close()

خروجی:

عبارت Limit

عبارت Limit در SQL برای کنترل یا محدود کردن تعداد رکوردهای بازگشتی از کوئری استفاده می‌شود. به‌طور پیش‌فرض، SQL تعداد موردنظر رکوردها را از بالا شروع به نمایش می‌دهد، اما استفاده از کلمه کلیدی OFFSET نیز مجاز است. OFFSET به شما این امکان را می‌دهد که از یک ردیف دلخواه شروع کنید و تعداد رکوردهای موردنظر را دریافت کنید.

نحو یا سینتکس:

SELECT * FROM tablename LIMIT limit;
SELECT * FROM tablename LIMIT limit OFFSET offset;

مثال: استفاده از عبارت Limit در MySQL با استفاده از پایتون

# importing required libraries
import mysql.connector

dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password",
database = "PS"
)

# preparing a cursor object
cursorObject = dataBase.cursor()

query = "SELECT * FROM STUDENT LIMIT 2 OFFSET 1"
cursorObject.execute(query)

myresult = cursorObject.fetchall()

for x in myresult:
 print(x)

# disconnecting from server
dataBase.close()

خروجی:

('Nikhil', 'CSE', 98, 'A', 18)
('Nisha', 'CSE', 99, 'A', 18)

بروزرسانی داده‌ ها در پایتون

کوئری update برای تغییر مقادیر موجود در یک پایگاه داده استفاده می‌شود. با استفاده از این دستور می‌توان یک مقدار خاص را اصلاح یا به‌روزرسانی کرد. این دستور فقط داده‌ها را تحت تأثیر قرار می‌دهد و ساختار جدول را تغییر نمی‌دهد. مزیت اصلی این دستور این است که دقت جدول را حفظ می‌کند.

نحو یا سینتکس:

UPDATE tablename
SET ="new value"
WHERE ="old value";

مثال: به‌روزرسانی جدول MySQL با استفاده از پایتون

# importing required libraries
import mysql.connector

dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password",
database = "PS"
)

# preparing a cursor object
cursorObject = dataBase.cursor()

query = "UPDATE STUDENT SET AGE = 23 WHERE Name ='Ram'"
cursorObject.execute(query)
dataBase.commit()

# disconnecting from server
dataBase.close()

خروجی:

حذف داده‌ ها از جدول در پایتون

برای حذف داده‌ها از جدول در MySQL می‌توانیم از کوئری Delete استفاده کنیم.

نحو یا سینتکس:

DELETE FROM TABLE_NAME WHERE ATTRIBUTE_NAME = ATTRIBUTE_VALUE

مثال: حذف داده‌ها از جدول MySQL با استفاده از پایتون

# importing required libraries
import mysql.connector

dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password",
database = "PS"
)

# preparing a cursor object
cursorObject = dataBase.cursor()

query = "DELETE FROM STUDENT WHERE NAME = 'Ram'"
cursorObject.execute(query)
dataBase.commit()

# disconnecting from server
dataBase.close()

خروجی:

حذف جداول در پایتون

دستور Drop بر ساختار جدول تاثیر می‌گذارد و نه داده‌ها. این دستور برای حذف یک جدول موجود استفاده می‌شود. برای مواردی که از وجود یا عدم وجود جدول برای حذف مطمئن نیستید، از دستور DROP TABLE IF EXISTS استفاده می‌شود. در مثال‌های زیر هر دو حالت بررسی خواهند شد.

نحو یا سینتکس:

DROP TABLE tablename;
DROP TABLE IF EXISTS tablename;

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

می‌توانیم مشاهده کنیم که دو جدول برای دانش‌آموزان وجود دارد، بنابراین بیایید جدول دوم را حذف کنیم.

مثال ۱: حذف جدول در MySQL با استفاده از پایتون

# importing required libraries
import mysql.connector

dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password",
database = "PS"
)

# preparing a cursor object
cursorObject = dataBase.cursor()

query ="DROP TABLE Student;"

cursorObject.execute(query)
dataBase.commit()

# disconnecting from server
dataBase.close()

خروجی:

مثال ۲: حذف جدول در صورت وجود

# importing required libraries
import mysql.connector

dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password",
database = "gfg"
)

# preparing a cursor object
cursorObject = dataBase.cursor()

query ="Drop Table if exists Employee;"

cursorObject.execute(query)
dataBase.commit()

# disconnecting from server
dataBase.close()

مثال بالا هیچ خطا یا خروجی ایجاد نمی‌کند چون از کوئری DROP TABLE IF EXISTS استفاده کرده‌ایم. اگر فقط از دستور DROP TABLE Employee استفاده کنیم، خطای ProgrammingError: 1051 (42S02): Unknown table PS.Employee ایجاد خواهد شد.

نتیجه‌گیری

کار با MySQL در پایتون به کمک کتابخانه mysql.connector یک روش مؤثر برای تعامل با پایگاه‌های داده محسوب می‌شود. این فرآیند به شما امکان می‌دهد عملیات مختلفی مانند اتصال به پایگاه داده، اجرای دستورات SQL، ایجاد و ویرایش جداول و مدیریت داده‌ها را به‌راحتی انجام دهید. با استفاده از این ابزار، می‌توان برنامه‌هایی با قابلیت ذخیره‌سازی و پردازش داده‌های گسترده طراحی کرد.

برای اتصال به MySQL، استفاده از mysql.connector.connect ضروری است. این متد ارتباط بین برنامه پایتون و سرور MySQL را برقرار می‌کند. هنگام تنظیم مشخصات اتصال، باید از صحت اطلاعات ورودی مانند نام کاربری، رمز عبور و نام پایگاه داده اطمینان حاصل کنید. علاوه بر این، اجرای دستورات SQL با استفاده از شی cursor امکان‌پذیر است که اجازه ارسال درخواست‌های پایگاه داده را فراهم می‌کند.

یکی از نکات مهم در کار با MySQL، مدیریت خطاها است. استفاده از بلوک‌های try-except باعث جلوگیری از توقف ناگهانی برنامه در صورت بروز مشکلاتی مانند خطاهای اتصال یا اجرای نادرست کوئری‌ها می‌شود. همچنین، امنیت در کار با پایگاه داده اهمیت زیادی دارد. همیشه باید از اطلاعات ورود امن و روش‌های رمزنگاری مناسب برای محافظت از داده‌ها استفاده شود.  در مجموع، کار با MySQL در پایتون این امکان را به برنامه‌نویسان می‌دهد که به‌صورت بهینه داده‌های خود را مدیریت کرده و برنامه‌های کاربردی قدرتمند و مقیاس‌پذیر ایجاد کنند. با رعایت اصول امنیتی و استفاده صحیح از ابزارهای مدیریت داده، می‌توان عملکرد برنامه را بهبود بخشید و از بروز مشکلات جلوگیری کرد.