MySQL در پایتون از جمله مهم‌ترین موضوعاتی است که برنامه نویسان زبان پایتون باید با آن آشنایی داشته باشند. پایگاه داده یکی از مهم‌ترین علوم رایانه محسوب می‌شود، زیرا بدون آن نگه‌داری و حفظ هیچ‌گونه داده و اطلاعاتی در رایانه امکان‌پذیر نیست. در برنامه نویسی پایتون، گاهی لازم است با پایگاه داده ارتباط برقرار کنیم. پایگاه داده انواع مختلفی مانند NoSQL ،SQLite ،SQL Server MySQL و... دارد. ما در این آموزش، نحوه‌ی اتصال پایتون به MySQL را شرح خواهیم داد. اگر به کار کردن با پایگاه داده علاقه‌مند هستید، این آموزش را تا انتها دنبال کنید.

پایگاه داده MySQL چیست؟

شاید این سوال برای شما پیش بیاید که تفاوت MySQL با SQL چیست؟ MySQL یک سیستم مدیریت پایگاه داده‌ رابطه‌ای (RDBMS) است، در حالی که SQL زبانی برای مدیریت داده‌ها در این سیستم می‌باشد. با استفاده از دستورات SQL می‌توان داده‌ها را در پایگاه داده ایجاد، درج، به‌روزرسانی و حذف کرد. این دستورات به حروف کوچک و بزرگ حساس نیستند، به این معنی که دستور CREATE و create هر دو یک کار را انجام می‌دهند. اما برای خوانایی بیشتر، دستورات SQL را با حروف بزرگ می‌نویسند.

اتصال به MySQL با استفاده از MySQL-Connector Python

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

برای متصل شدن به MySQL، ابتدا باید درایور MySQL-Connector را روی پایتون نصب کنیم. بهترین روش نصب، استفاده از دستور PIP پایتون است.

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

C:\Users\Reza Molaei>python -m pip install mysql-connector

بعد از اجرای این دستور، درایور یا راه‌انداز MySQL-Connector بر روی کامپیوتر شما نصب خواهد شد.

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

import mysql.connector

متدهای ماژول MySQL Connector در پایتون

متد ()connect:

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

• user: نام کاربری مرتبط با سرور MySQL که برای تأیید اعتبار اتصال استفاده می‌شود.
• password: کلمه‌ی عبور مربوط به نام کاربری بالا که برای احراز هویت استفاده می‌شود.
• database: نام پایگاه داده‌ در MySQL که برای ایجاد جدول استفاده می‌شود.

متد ()cursor:

مکان‌نما (Cursor)، فضای کار ایجاد شده در حافظه‌ی سیستم است که در هنگام اجرای دستورات SQL به وجود می‌آید. این حافظه موقتی است.

متد ()execute:

تابع execute به عنوان آرگومان، یک پرس‌وجو یا کوئری SQL را گرفته و اجرا می‌شود. یک کوئری (Query) می‌تواند هر دستور SQL باشد که برای ایجاد (create)، درج (insert)، بازیابی (retrieve)، به‌روزرسانی (update)، حذف (delete) و... به کار می‌رود.

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

در مثال زیر با استفاده از متد ()connect ماژول mysql-connector، به پایگاه داده MySQL متصل می‌شویم.

مثال: برنامه اتصال به MySQL با استفاده متد connect

# Python program to connect
# to mysql databse
import mysql.connector
# Connecting from the server
conn = mysql.connector.connect(user = 'username',
                               host = 'localhost',
                               database = 'databse_name')
print(conn)
# Disconnecting from the server
conn.close()

همچنین به جای استفاده از متد ()connect می‌توانیم از کلاس ()connection.MySQLConnection استفاده کنیم.

مثال: برنامه اتصال به MySQL با استفاده از کلاس MySQLConnection

# Python program to connect
# to mysql databse
from mysql.connector import connection
# Connecting to the server
conn = connection.MySQLConnection(user = 'username',
                                  host = 'localhost',
                                  database = 'database_name')
print(conn)
# Disconnecting from the server
conn.close()

ایجاد پایگاه داده

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

دستور SQL برای ایجاد پایگاه داده به صورت زیر است:

CREATE DATABASE ;

برای ایجاد پایگاه داده، ابتدا از طریق روش‌های بالا به سرور MySQL متصل می‌شویم، سپس همانند مثال زیر یک شی ساخته و دستور SQL را برای ایجاد database وارد می‌کنیم. به این صورت:

# importing rquired libraries
import mysql.connector
conn = mysql.connector.connect(
                               host ="localhost",
                               user ="user",
                               passwd ="arm"
                                )
# preparing a cursor object
cursorObject = conn.cursor()
# creating database
cursorObject.execute("CREATE DATABASE 7learn")
print("7learn Data base is created")

ایجاد جدول

جدول (Table) مجموعه‌ای از داده‌ها است که به صورت ردیف و ستون سازمان یافته‌اند. جدول، جزئی از یک پایگاه داده است. به ردیف‌های جدول، تاپل و به ستون‌های آن، صفات جدول نیز می‌گویند.

دستور SQL برای ایجاد جدول به صورت زیر است:

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

انواع داده‌های SQL

از انواع داده برای تعریف نوع داده‌ی ذخیره شده در سلول جدول، استفاده می‌شود. انواع داده‌ی‌ مورد استفاده در MySQL به شرح زیر است:

1. عددی (Numeric)
2. کاراکتر/رشته (Character/String)
3. تاریخ/ساعت (Date/time)
4. یونیکد کاراکتر/رشته (Unicode Character/String)
5. باینری (Binary)

به غیر از موارد ذکر شده، انواع مختلف دیگری از نوع داده در MySQL وجود دارد که شامل XML ،JSON ،BLOB و CLOB می‌شود.

کد پایتون زیر یک جدول به نام student که شامل دو ستون "Name" و "Roll_no" است را در پایگاه داده‌ی 7learn ایجاد می‌کند.

# Python code for creating Table in the Database
# Host: It is the server name. It will be "localhost"
# if you are using localhost database
import mysql.connector as SQLC
def CreateTable():
    # Connecting To the Database in Localhost
    DataBase = SQLC.connect(
                            host ="server name",
                            user ="user name",
                            password ="password",
                            database ="7learn"
                            )
    # Cursor to the database
    Cursor = DataBase.cursor()
    # Query for Creating the table
    # The student table contains two columns Name and
    # Roll number of data types varchar i.e to store string
    # and Roll number of the integer data type.
    TableName ="""CREATE TABLE Student
                                      (
                                        Name VARCHAR(255),
                                        Roll_no int
                                        );"""
    Cursor.execute(TableName)
    print("Student Table is Created in the Database")
    return
# Calling CreateTable function
CreateTable()

درج داده‌ها

شما می‌توانید به طور هم‌زمان، یک یا چند ردیف داده را در جدول درج (insert) کنید. برای این کار ابتدا باید به پایگاه داده متصل شوید. با دستورات زیر، اتصال پایتون به پایگاه داده‌ی مورد نظر انجام می‌شود.

# Enter the server name in host
# followed by your user and
# password along with the database
# name provided by you.
import mysql.connector
mydb = mysql.connector.connect(
                               host = "localhost",
                               user = "username",
                               password = "password",
                               database = "database_name"
                                )
mycursor = mydb.cursor()

اکنون، کوئری insert into را می‌توان به صورت زیر نوشت:

sql = "INSERT INTO Student (Name, Roll_no) VALUES (%s, %s)"
val = ("Mohamad", "85")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "details inserted")
# disconnecting from server
mydb.close()

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

sql = "INSERT INTO Student (Name, Roll_no) VALUES (%s, %s)"
val = [("Ali", "98"),
       ("Sara", "23"),
       ("Jamshid", "43"),
       ("Sasan", "87"),
       ("Akbar", "45"),
       ("Narges", "55"),
       ("Maryam", "95")]
mycursor.executemany(sql, val)
mydb.commit()
print(mycursor.rowcount, "details inserted")
# disconnecting from server
mydb.close()

نکته:

• ()cursor به منظور تکرار از طریق سطرها استفاده می‌شود.
• بدون دستور ()commit تغییرات ذخیره نمی‌شوند.

کوئری

پس از اتصال به پایگاه داده‌ در MySQL، می‌توانیم از جدول‌های موجود در آن، کوئری مورد نظرمان را درخواست کنیم.

دستور SQL برای انتخاب کوئری به صورت زیر است:

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

• SELECT attr1, attr2 FROM table_name

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

• SELECT * FROM table_name

مثال: فرض کنیم جدولی به این شکل داریم:

مثال 1: کد زیر، برنامه‌ای برای انتخاب کوئری از جدول موجود در پایگاه داده است.

# importing required library
import mysql.connector
# connecting to the database
dataBase = mysql.connector.connect(
                                   host = "localhost",
                                   user = "user",
                                   passwd = "pswrd",
                                   database = "7learn" )
# preparing a cursor object
cursorObject = dataBase.cursor()
print("Displaying NAME and ROLL columns from the STUDENT table:")
# selecting query
query = "SELECT NAME, ROLL FROM STUDENT"
cursorObject.execute(query)
myresult = cursorObject.fetchall()
for x in myresult:
    print(x)
# disconnecting from server
dataBase.close()

مثال 2: در این مثال، نحوه‌ی نوشتن کوئری برای انتخاب همه‌ی ستون‌ها را مشاهده می‌کنید.

# importing required library
import mysql.connector
# connecting to the database
dataBase = mysql.connector.connect(
                                   host = "localhost",
                                   user = "user",
                                   passwd = "pswrd",
                                   database = "7learn" )
# preparing a cursor object
cursorObject = dataBase.cursor()
print("Displaying NAME and ROLL columns from the STUDENT table:")
# selecting query
query = "SELECT * FROM STUDENT"
cursorObject.execute(query)
myresult = cursorObject.fetchall()
for x in myresult:
    print(x)
# disconnecting from server
dataBase.close()

دستور Where

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

سینتکس Where در دستورات SQL:

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

مثال: فرض کنیم جداولی به صورت زیر داریم:

import mysql.connector
#Establishing connection
conn = mysql.connector.connect(user='your_username',
                               host='localhost',
                               password ='your_password',
                               database='College')
# Creating a cursor object using
# the cursor() method
mycursor = conn.cursor();
# SQL Query
sql = "select * from Student where Roll_no >= 3;"
# Executing query
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
    print(x)
# Closing the connection
conn.close()

دستور Order By

از دستور OrdeyBy برای نمایش نتیجه به ترتیب صعودی و نزولی استفاده می‌شود. به طور پیش‌فرض، همیشه ترتیب به صورت صعودی است، مگر اینکه دستور ‘DESC’ در آن ذکر شود و نتیجه را به صورت نزولی نمایش دهد.

می‌توان با استفاده از ‘ASC’ به طور صریح ترتیب را صعودی کرد، اما این کار لازم نیست زیرا به صورت پیش‌فرض انجام شده است.

سینتکس Order By در دستورات SQL:

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

مثال 1: برنامه‌ای برای نمایش نام داده‌ها به ترتیب صعودی:

# Python program to demonstrate
# order by clause
import mysql.connector
# Connecting to the Database
mydb = mysql.connector.connect(
                               host ='localhost',
                               database ='College',
                               user ='root',
                               password = ''
                              )
cs = mydb.cursor()
# Order by clause
statement ="SELECT * FROM Student ORDER BY Name"
cs.execute(statement)
result_set = cs.fetchall()
for x in result_set:
    print(x)
# Disconnecting from the database
mydb.close()

مثال 2: برنامه‌ای برای نمایش نام داده‌ها به ترتیب نزولی:

# Python program to demonstrate
# order by clause
import mysql.connector
# Connecting to the Database
mydb = mysql.connector.connect(
                               host ='localhost',
                               database ='College',
                               user ='root',
                              )
cs = mydb.cursor()
# Order by clause
statement ="SELECT * FROM Student ORDER BY Name DESC"
cs.execute(statement)
result_set = cs.fetchall()
for x in result_set:
    print(x)
# Disconnecting from the database
mydb.close()

حذف کوئری از جدول

از دستور Delete برای حذف رکوردهای موجود در یک جدول استفاده می‌شود. هنگام حذف رکوردها در یک جدول مراقب باشید! و به دستور WHERE در دستور DELETE توجه کنید، زیرا دستور WHERE مشخص می‌کند کدام رکورد(ها) باید حذف شود. اگر دستور WHERE را حذف کنید، تمام رکوردهای جدول حذف خواهند شد!

سینتکس Delete در دستورات MySQL:

DELETE FROM TABLE_NAME WHERE ATTRIBUTE_NAME = ATTRIBUTE_VALUE

مثال:

# importing required library
import mysql.connector
# connecting to the database
dataBase = mysql.connector.connect(
                                   host = "localhost",
                                   user = "user",
                                   passwd = "pswrd",
                                   database = "7learn" )
# 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)
# inserting data into the table
query = "INSERT INTO STUDENT (NAME, BRANCH, ROLL, SECTION, AGE) VALUES (%s, %s, %s, %s, %s)"
attrValues = ("Reza Akbari", "Information Technology", "1706256", "IT-3", "20")
cursorObject.execute(query, attrValues)
attrValues = ("Sohrab Ebrahimi", "Information Technology", "1706254", "IT-3", "21")
cursorObject.execute(query, attrValues)
attrValues = ("Narges Mahmodi", "Information Technology", "1706253", "IT-3", "21")
cursorObject.execute(query, attrValues)
# deleting query
query = "DELETE FROM STUDENT WHERE ROLL = 1706256"
cursorObject.execute(query)
dataBase.commit()
# disconnecting from server
dataBase.close()

دستور Drop Table

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

سینتکس Drop Table در دستورات SQL:

DROP TABLE tablename;
DROP TABLE IF EXISTS tablename;

با کمک مثال‌های زیر، این موضوع را بهتر درک می‌کنید.

جدول‌ قبل از Drop:

مثال 1: برنامه‌ی کاربرد IF EXISTS در Drop Table:

# Python program to demonstrate
# drop clause
import mysql.connector
# Connecting to the Database
mydb = mysql.connector.connect(
                               host ='localhost',
                               database ='College',
                               user ='root',
                               )
cs = mydb.cursor()
# drop clause
statement = "Drop Table if exists Employee"
# Uncommenting statement ="DROP TABLE employee"
# Will raise an error as the table employee
# does not exists
cs.execute(statement)
# Disconnecting from the database
mydb.close()

در این برنامه با اینکه جدولی به نام Employee در بین جداول ما وجود ندارد، ولی بدون خطا اجرا می‌شود. اما اگر از دستور "DROP TABLE employee" استفاده می‌کردیم، خطایی شبیه به خروجی زیر صادر می‌کرد.

مثال 2: استفاده از Drop Table برای حذف جدول موجود:

# Python program to demonstrate
# drop clause
import mysql.connector
# Connecting to the Database
mydb = mysql.connector.connect(
                               host ='localhost',
                               database ='College',
                               user ='root',
                               )
cs = mydb.cursor()
# drop clause
statement ="DROP TABLE Geeks"
cs.execute(statement)
# Disconnecting from the database
mydb.close()

دستور Update

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

سینتکس Update در دستورات SQL:

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

برنامه‌ی زیر به درک بهتر شما از Update کمک خواهد کرد.

داده‌های جدول Student:

مثال 1: برنامه‌ی به‌روزرسانی سن دانشجو:

# Python program to demonstrate
# update clause
import mysql.connector
# Connecting to the Database
mydb = mysql.connector.connect(
                               host ='localhost',
                               database ='College',
                               user ='root',
                               )
cs = mydb.cursor()
# drop clause
statement ="UPDATE STUDENT SET AGE = 23 WHERE Name ='Reza Molaei'"
cs.execute(statement)
mydb.commit()
# Disconnecting from the database
mydb.close()

دستور Limit

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

سینتکس Limit در دستورات SQL:

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

فرض می‌کنیم جدولی با داده‌های زیر داریم:

مثال 1: برنامه برای نمایش 2 رکورد:

import mysql.connector
# Conencting to the database
mydb = mysql.connector.connect(
                               host ='localhost',
                               database ='College',
                               user ='root',
                               )
cs = mydb.cursor()
# STUDENT and STudent are
# two different database
statement ="SELECT * FROM STUDENT LIMIT 2"
cs.execute(statement)
result_set = cs.fetchall()
for x in result_set:
    print(x)

مثال 2: برنامه‌ای برای شروع از رکورد دوم و نمایش دو رکورد بعد از آن:

import mysql.connector
# Conencting to the database
mydb = mysql.connector.connect(
                               host ='localhost',
                               database ='College',
                               user ='root',
                               )
cs = mydb.cursor()
# STUDENT and STudent are
# two different database
statement ="SELECT * FROM STUDENT LIMIT 2 OFFSET 1"
cs.execute(statement)
result_set = cs.fetchall()
for x in result_set:
    print(x)

دستور Join

با دستور Join می‌توانیم دو یا چند جدول را در SQL، بر اساس ستون مرتبط بین آن‌ها، ترکیب کنیم. این دستور در مباحث پیشرفته‌تر SQL بسیار کارآمد خواهد بود. Join سه نوع متفاوت دارد:

INNER JOIN:

رکوردهای تولیدشده توسط ستون‌های منطبق را خروجی می‌دهد. کارکرد JOIN و INNER JOIN یکسان است.

سینتکس:

SELECT column1, column2...
FROM tablename
JOIN tablename ON condition;

SELECT column1, column2...
FROM tablename
INNER JOIN tablename ON condition;

LEFT JOIN:

SELECT column1, column2...
FROM tablename
LEFT JOIN tablename ON condition;

RIGHT JOIN:

سینتکس:

SELECT column1, column2...
FROM tablename
RIGHT JOIN tablename ON condition;

جداولی که در مثال خواهیم آورد:

مثال 1: استفاده از inner join:

import mysql.connector
# Conencting to the database
mydb = mysql.connector.connect(
                               host ='localhost',
                               database ='College',
                               user ='root',
                               )
cs = mydb.cursor()
# STUDENT and PERSON are
# two different database
statement ="""SELECT person.Name from person JOIN 
              student on student.Roll_no = person.Roll_no"""
cs.execute(statement)
result_set = cs.fetchall()
for x in result_set:
    print(x)

مثال 2: استفاده از LEFT JOIN:

import mysql.connector
# Conencting to the database
mydb = mysql.connector.connect(
                               host ='localhost',
                               database ='College',
                               user ='root',
                               )
cs = mydb.cursor()
# STUDENT and PERSON are
# two different database
statement ="""SELECT student.Name from student 
               LEFT JOIN person ON person.Roll_no = student.Roll_no"""
cs.execute(statement)
result_set = cs.fetchall()
for x in result_set:
    print(x)

مثال 3: استفاده از RIGHT JOIN:

import mysql.connector
# Conencting to the database
mydb = mysql.connector.connect(
                               host ='localhost',
                               database ='College',
                               user ='root',
                               )
cs = mydb.cursor()
# STUDENT and PERSON are
# two different database
statement ="""SELECT student.Name from student 
              Right JOIN person ON person.Roll_no = student.Roll_no"""
cs.execute(statement)
result_set = cs.fetchall()
for x in result_set:
    print(x)

در این آموزش، با سیستم مدیریت پایگاه داده‌ رابطه‌ای MySQL و زبان مدیریت این نوع پایگاه داده، یعنی SQL آشنا شدیم. دانستیم برای اتصال پایتون، به MySQL و یک درایور اتصال نیاز داریم که ما MySQL-Connector را انتخاب کردیم. در ادامه نیز به روش اتصال و نیز ساخت پایگاه داده اشاره کردیم و سپس ایجاد، درج، به‌روزرسانی و حذف رکوردها و جداول را آموزش دادیم. با دستورات DROP ،LIMIT و JOIN و انواع آن نیز آشنا شدید. هر برنامه نویسی به کار کردن با پایگاه داده و ارتباط با آن، نیازمند خواهد شد. پس بهتر آن است تمامی برنامه نویسان پایتون، زبان SQL و کار با پایگاه داده‌ای نظیر MySQL را فرا گرفته و بیشتر از پیش به سوی حرفه‌ای شدن قدم بگذارند. نظرات و آموخته‌های خود در مورد MySQL را با ما در میان بگذارید.

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