עבודה עם פייתון במסד נתונים mySQL

במדריך זה הסבר על עבודה עם מסד נתונים מסוג mySQL, מפני שהוא נפוץ ביותר בשימוש באפליקציות מבוססות פייתון. ידע בסיסי של mySQL יכול להועיל לכם. אותו תוכלו לרכוש מקריאת מדריך mySQL באתר רשתטק.

התקנת mySQL על המחשב

כדי לעבוד עם מסד נתונים אתם צריכים להתקין אותו על המחשב שלכם. אתם יכולים להוריד גרסה חינמית של מסד נתונים מסוג mySQL מהקישור: www.mysql.com/downloads.

אפשר לעבוד מול מסד הנתונים ישירות משורת הפקודות אבל למתחילים עדיף ממשק ידידותי יותר דוגמת workbench. את הממשק ניתן להוריד ולהתקין מהקישור: dev.mysql.com/downloads/workbench

כשאתם מתקינים את מסד הנתונים רשמו בצד את פרטי הגישה. את שלי הגדרתי עם הפרטים שיהיה לי הכי קשה לשכוח:

user="root"
password="1234"

אני מרשה לעצמי להגדיר פרטי גישה כל כך קלים לפריצה רק כשאני עובד על המחשב הפרטי בבית. כשעובדים עם שרת מרוחק חובה להשתמש בפרטי גישה חזקים ומסובכים.

התקנת דרייבר של mySQL

כדי שפייתון יוכל לעבוד עם מסד הנתונים מסוג mySQL הוא זקוק לדרייבר. במדריך זה אני משתמש ב-mysql-connector-python.

כדי להתקין את הדרייבר באמצעות מנהל החבילות PIP צריך להריץ את הפקודה הבאה בשורת הפקודות:

$ pip3 install mysql-connector-python

אם נתקלתם בבעיה בהתקנה אז נסו להתקין את אחת החלופות:

mysql-connector
mysql-connector-python-rf

הסקריפט

ניצור סקריפט ששמו main.py, לתוכו נכתוב את הפקודות ואותו נריץ כדי לצפות בתוצאות.

כיצד לבדוק את ההתקשרות עם מסד הנתונים?

בראש הסקריפט main.py נייבא את החבילה:

import mysql.connector

בהמשך אותו הקובץ נכתוב את הקוד שיתקשר עם מסד הנתונים:

# Connect to the database
db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="1234"
)

print(db)

אם אחרי הרצת הקוד אתם רואים תוצאה דומה לזו:

mysql.connector.connection_cext.CMySQLConnection object at 0x7efc000ca240

אז הצלחתם ליצור קשר עם מסד הנתונים.

יצירת מסד נתונים באמצעות workbench

את מסד הנתונים ניצור באמצעות ממשק workbench והפקודה הבאה ליצירת מסד נתונים ששמו cars:

CREATE DATABASE cars;

לחיצה על סימן הברק ב-workbench תריץ את השאילתה.

יצירת טבלה במסד הנתונים

בתוך mySQL תמיד יהיו מספר מסדי נתונים. לכן, בתחילת העבודה חשוב להגדיר את מסד הנתונים שאיתו אנו עובדים. עדיין בתוך ה-workbench הפקודה הבאה תעביר אותנו לתוך מסד הנתונים cars:

USE cars;

בתוך מסד הנתונים, הפקודה CREATE TABLE תיצור טבלה ששמה models הכוללת שלוש עמודות: מזהה, שם ומחיר.

CREATE TABLE models (
   id int UNSIGNED NOT NULL,
   name VARCHAR(255) DEFAULT '',
   price INT UNSIGNED DEFAULT 0
)  DEFAULT CHARSET=UTF8MB4;

טבלאות מסד הנתונים מכילות רשומות, כאשר כל רשומה היא שורה של מידע.

• לכל רשומה צריך להיות מזהה (id) ייחודי כדי שנוכל בהמשך לשלוף את הרשומה, לערוך או למחוק על בסיס המזהה.
• עמודות id ו-price יכולות להכיל מספרים שלמים (INT) בתנאי שערכם גבוה מ-0 (UNSIGNED) ואינו NULL.
• עמודת name יכולה להכיל עד 255 תווים וערך ברירת המחדל שלה הוא מחרוזת ריקה.

בתוך ממשק workbench נריץ 2 פקודות שמטרתם להפוך את ה-id לאינדקס הטבלה:

ALTER TABLE models
ADD PRIMARY KEY (`id`);

ALTER TABLE models
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

• PRIMARY_KEY - מזהה ייחודי כדי שלא יהיו שתי רשומות שיש להם את אותו המזהה.
• AUTO_INCREMENT - בכל פעם שנוסיף רשומה לטבלה יתווסף אוטומטית 1 למונה. הרשומה הראשונה תקבל אוטומטית את המזהה 1, והרשומה השנייה תקבל את המזהה 2, וכיו"ב.

התחברות למסד הנתונים

נחזור לסקריפט main.py ונשנה את קוד ה-connector כדי להתחבר עם מסד הנתונים cars:

import mysql.connector

# Connect to the database
db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="1234",
  database="cars"
)

ניצור cursor באמצעותו נשתמש בקשר עם מסד הנתונים:

cur = db.cursor()

במידה ומסד הנתונים לא קיים נקבל שגיאה.

המשתנה db מכיל את הקשר עם מסד הנתונים cars. וה-cursor מאפשר לנו לתקשר עם מסד הנתונים ועל כן מעכשיו ועד לסוף המדריך נקפיד שהשורות הבאות יופיעו בתחילת הסקריפט מעל לכל קוד אחר:

import mysql.connector

# connect to the database
db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="1234",
  database="cars"
)

# get the cursor
cur = db.cursor()

קריאה ממסד הנתונים

כדי לוודא שהטבלה קיימת במסד הנתונים, נוסיף את הקוד הבא לסקריפט main.py, בהמשך לקוד שיוצר את הקשר עם מסד הנתונים.

נכתוב את השאילתה שתציג את הטבלאות במסד הנתונים:

sql = "SHOW TABLES"

נבצע את השאילתה:

cur.execute(sql)

נדפיס את שמות הטבלאות שהשאילתה מחזירה:

for t in cur:
  print(t)

התוצאה:

('models',)

הקוד המלא:

import mysql.connector

# connect to the database
db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="1234",
  database="cars"
)

# get a cursor
cur = db.cursor()

# the query
sql = "SHOW TABLES"

# execute the query
cur.execute(sql)

#print
for t in cur:
  print(t)

מילוי טבלת הנתונים באמצעות פייתון

כדי למלא את הטבלה במסד הנתונים נשתמש בשאילתת INSERT:

sql = "INSERT INTO models(name, price) VALUES (%s, %s)"

%s הוא מחזיק מקום שאותו ניתן למלא בכל מחרוזת שנרצה.

נשתמש במחזיק מקום כדי להתגבר על הבעיה של הזרקת SQL הנגרמת כאשר האקרים מצליחים להשחיל קוד זדוני לתוך שאילתה.

ניצור טאפל שבו נשים את המידע שאנו מעוניינים להזין למסד הנתונים:

model1 = ("BMW", 260000)

נבצע את השאילתה:

cur.execute(sql, model1)

הפקודה execute מקבלת את קוד ה-SQL ואת המידע מהטאפל בתור פרמטרים, מחליפה את מחזיקי המקום בערכים שמקורם בטאפל באופן בטוח, ומריצה את השאילתה.

לא לשכוח לעשות commit:

db.commit()

כשאנחנו רוצים לשנות את מסד הנתונים - להוסיף, לערוך או למחוק - אנחנו חייבים לעשות commit.

כך נראה הקוד המלא בתוך הסקריפט main.py:

import mysql.connector

# connect to the database
db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="1234",
  database="cars"
)

# get a cursor
cur = db.cursor()

# provide the query
sql = "INSERT INTO models(name, price) VALUES (%s, %s)"

# fill in the placeholders
model1 = ("BMW", 260000)

# execute the query
cur.execute(sql, model1)

# a must when executing queries that change the database
db.commit()

בתוך ה-workbench אני יכולים לראות עכשיו את השינוי אותו ערכנו:

1. נכתוב שאילתת select
2. נריץ אותה על ידי לחיצה על סמל הברק
3. נצפה בתוצאות

כדי למלא את הטבלה ביותר מרשומה אחת, ניצור מערך של טאפלים, ונשתמש בפקודה של פייתון executemany כדי להריץ את הפקודה של sql:

sql = "INSERT INTO models (name, price) VALUES (%s, %s)"
models = [
 ("Toyota", 160000),
 ("Mazda", 180000),
 ("Tesla", 240000),
 ("Sussita", 45000)
]
cur.executemany(sql, models)

db.commit()

כך נראה הקוד המלא:

import mysql.connector

# connect to the database
db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="1234",
  database="cars"
)

# get a cursor
cur = db.cursor()

# the sql
sql = "INSERT INTO models (name, price) VALUES (%s, %s)"

# the data
models = [
  ("Toyota", 160000),
  ("Mazda", 180000),
  ("Tesla", 240000),
  ("Sussita", 45000)
]

# execute for multiple records
cur.executemany(sql, models)
db.commit()

וזו התוצאה של הרצת השאילתה להצגת כל המידע בטבלה בתוך ה- work bench:

select * from models;

שליפת המידע ממסד הנתונים

השאילתה SELECT של mySQL שולפת נתונים מטבלה במסד הנתונים. כך נראית הפקודה לשליפת כל הרשומות מהטבלה models:

sql = "SELECT * FROM models"

המשמעות של הכוכבית (*) היא כל העמודות.

נבצע את השאילתה:

cur.execute(sql)

נשתמש בפקודה fetchall כדי לשלוף את הרשומות:

result = cur.fetchall(sql)

נשתמש בלולאה כדי להדפיס את התוצאה:

for row in result:
    print(row)

התוצאה:

(1, 'BMW', 260000)
(2, 'Toyota', 160000)
(3, 'Mazda', 180000)
(4, 'Tesla', 240000)
(5, 'Sussita', 45000)

ועכשיו הכל ביחד:

import mysql.connector
# connect to the database
db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="1234",
  database="cars"
)

# get a cursor
cur = db.cursor()

# the query
sql = "SELECT * FROM models"

# execute the query
cur.execute(sql)

# get the data
result = cur.fetchall()

# print each row inside a loop
for row in result:
    print(row)

השתמשנו בכוכבית (*) בשאילתת ה-SQL כדי לשלוף את כל העמודות, כדי לשלוף רק את את אחת העמודות נגדיר את שמה:

sql = "SELECT name FROM models"

ניתן לקרוא את המידע מיותר מעמודה אחת:

sql = "SELECT name, price FROM models"

כדי להגביל את מספר הרשומות ששולפת השאילתה נשתמש באופרטור LIMIT, ואחריו מספר הרשומות שנרצה לשלוף:

sql = "SELECT name FROM models LIMIT 3"

ניתן לשלוף לפי מאפיינים שונים באמצעות WHERE. לדוגמה, דגמים שהשם שלהם הוא BMW:

sql = "SELECT price FROM models WHERE name = %s"

נבצע את השאילתה:

data = ('BMW', )

cur.execute(sql, data)

• את הערך של ממלא המקום נמלא באמצעות טאפל.
• אם בטאפל יש רק פריט אחד אז חשוב לשים אחריו פסיק אחרת נקבל שגיאה.

יכולה להיות שאילתה עם יותר ממלא מקום אחד:

sql = "SELECT name, price FROM models WHERE price > %s OR name = %s"

data = (120000, 'BMW')

cur.execute(sql, data)

נגביל את מספר התוצאות שהשאילתה מחזירה באמצעות האופרטור LIMIT. לדוגמה, ל-3 תוצאות:

sql = "SELECT name, price FROM models WHERE price > %s OR name = %s LIMIT 3"

data = (120000, 'BMW')

cur.execute(sql, data)

נסדר את התוצאות באמצעות האופרטור ORDER BY.

מהמחיר הגבוה לנמוך:

sql = "SELECT name, price FROM models ORDER BY price DESC"

מהמחיר הנמוך לגבוה:

sql = "SELECT name, price FROM models ORDER BY price ASC"

כדי להגביל את המידע המוחזר לרשומה אחת נשלב את LIMIT בשאילתה עם הפקודה fetchone:

sql = "SELECT price FROM models WHERE name = %s LIMIT 1"

data = ('BMW', )

cur.execute(sql, data)

result = cur.fetchone()

print(result)

כך נראה הקוד המלא:

import mysql.connector

# connect to the database
db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="1234",
  database="cars"
)

# get a cursor
cur = db.cursor()

# the sql
sql = "SELECT price FROM models WHERE name = %s LIMIT 1"

cur.execute(sql, ('BMW', ))

# get the data from the database
result = cur.fetchone()

# print the data
print(result)

עריכת נתונים

מחיר ה-BMW ירד במסגרת הנחת קורונה, ולכן אנחנו צריכים לעדכן את הרשומה הראשונה במסד הנתונים. לשם כך נשתמש בשאילתת UPDATE:

sql = "UPDATE models SET price = %s WHERE id = %s"

טאפל המידע שיחליף את מחזיקי המקום בשאילתה:

data = (238000, 1 )

הפקודה execute מחליפה את מחזיקי המקום במידע מהטאפל:

cur.execute(sql, data)

כשאנחנו רוצים לשנות את מסד הנתונים: להוסיף, לערוך או למחוק אנחנו חייבים לעשות commit:

db.commit()

כך נראה הקוד המלא:

import mysql.connector

# connect to the database
db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="1234",
  database="cars"
)

# get a cursor
cur = db.cursor()

# the query
sql = "UPDATE models SET price = %s WHERE id = %s"

# the data
data = (238000, 1)

# execute the query
cur.execute(sql, data)

# commit
db.commit()

נריץ שאילתת select כדי לוודא את השינויים למסד הנתונים:

select * from models;

מחיקת רשומות

מודל סוסיתא כבר לא קיים במלאי ולכן צריך למחוק אותו ממסד הנתונים. נשתמש בשאילתת DELETE למחיקת הרשומה מהטבלה:

השאילתה:

sql = "DELETE FROM models WHERE name = %s"

המידע שימלא את מחליף המקום בשאילתה:

data = ('Sussita',)

ביצוע השאילתה:

cur.execute(sql)

וכמו תמיד, כשאנחנו רוצים לשנות את מסד הנתונים, להכניס נתונים, לערוך או למחוק צריך להשתמש ב-commit:

db.commit()

הקוד המלא:

import mysql.connector
# connect to the database
db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="1234",
  database="cars"
)

# get a cursor
cur = db.cursor()

# the query
sql = "DELETE FROM models WHERE name = %s"

# the data
data = ('Sussita',)

# execute the query
cur.execute(sql, data)

# commit
db.commit()

נריץ שאילתת select כדי לוודא את הצלחת המחיקה:

select * from models;

לכל המדריכים בסדרה ללימוד פייתון

אהבתם? לא אהבתם? דרגו!

0 הצבעות, ממוצע 0 מתוך 5 כוכבים