אינטליגנציה של נתונים גנרטיביים

קריאה וכתיבה של קבצי SQL בפנדות

תאריך:

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

סקירה כללית של SQL ופנדות

SQL (שפת שאילתות מובנית) היא שפת תכנות המשמשת לניהול ולתפעל מסדי נתונים יחסיים. מצד שני, Pandas היא ספריית Python המשמשת למניפולציה וניתוח נתונים.

ניתוח נתונים כרוך בעבודה עם כמויות גדולות של נתונים, ולעתים קרובות משתמשים בבסיסי נתונים לאחסון נתונים אלה. SQL ו-Pandas מספקים כלים רבי עוצמה לעבודה עם מסדי נתונים, המאפשרים למנתחי נתונים לחלץ, לתפעל ולנתח נתונים ביעילות. על ידי מינוף הכלים הללו, מנתחי נתונים יכולים לקבל תובנות חשובות מנתונים שאחרת יהיה קשה להשיג.

במאמר זה, נחקור כיצד להשתמש ב-SQL וב-Pandas כדי לקרוא ולכתוב למסד נתונים.

מתחבר ל-DB

התקנת הספריות

עלינו להתקין תחילה את הספריות הדרושות לפני שנוכל להתחבר למסד הנתונים של SQL עם Pandas. שתי הספריות העיקריות הנדרשות הן Pandas ו- SQLAlchemy. Pandas היא ספריית מניפולציית נתונים פופולרית המאפשרת אחסון של מבני נתונים גדולים, כפי שהוזכר בהקדמה. לעומת זאת, SQLAlchemy מספקת API לחיבור ואינטראקציה עם מסד הנתונים של SQL.

אנו יכולים להתקין את שתי הספריות באמצעות מנהל החבילות של Python, pip, על ידי הפעלת הפקודות הבאות בשורת הפקודה.

$ pip install pandas
$ pip install sqlalchemy

יצירת הקשר

כשהספריות מותקנות, אנו יכולים כעת להשתמש ב-Pandas כדי להתחבר למסד הנתונים של SQL.

כדי להתחיל, ניצור איתו אובייקט מנוע SQLAlchemy create_engine(). ה create_engine() הפונקציה מחברת את קוד Python למסד הנתונים. זה לוקח כארגומנט מחרוזת חיבור שמציינת את סוג מסד הנתונים ופרטי החיבור. בדוגמה זו, נשתמש בסוג מסד הנתונים SQLite ובנתיב של קובץ מסד הנתונים.

צור אובייקט מנוע עבור מסד נתונים של SQLite באמצעות הדוגמה שלהלן:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db')

אם קובץ מסד הנתונים של SQLite, student.db במקרה שלנו, נמצא באותה ספרייה כמו הסקריפט של Python, נוכל להשתמש בשם הקובץ ישירות, כפי שמוצג להלן.

engine = create_engine('sqlite:///student.db')

קריאת קבצי SQL עם פנדות

בואו לקרוא נתונים עכשיו לאחר שיצרנו חיבור. בחלק זה, נסתכל על read_sql, read_sql_table, ו read_sql_query פונקציות וכיצד להשתמש בהן לעבודה עם מסד נתונים.

ביצוע שאילתות SQL באמצעות Panda's read_sql() פונקציה

אל האני read_sql() היא פונקציית ספריית Pandas המאפשרת לנו לבצע שאילתת SQL ולאחזר את התוצאות לתוך מסגרת נתונים של Pandas. ה read_sql() הפונקציה מחברת את SQL ו-Python, ומאפשרת לנו לנצל את הכוח של שתי השפות. הפונקציה עוטפת read_sql_table() ו read_sql_query(). ה read_sql() הפונקציה מנותבת פנימית בהתבסס על הקלט שסופק, מה שאומר שאם הקלט הוא לבצע שאילתת SQL, הוא ינותב אל read_sql_query(), ואם זו טבלת מסד נתונים, היא תנותב אל read_sql_table().

אל האני read_sql() התחביר הוא כדלקמן:

pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)

נדרשים פרמטרים של SQL ו-con; השאר הם אופציונליים. עם זאת, אנו יכולים לתפעל את התוצאה באמצעות פרמטרים אופציונליים אלה. בואו נסתכל מקרוב על כל פרמטר.

  • sql: שאילתת SQL או שם טבלת מסד נתונים
  • con: אובייקט חיבור או כתובת URL של חיבור
  • index_col: פרמטר זה מאפשר לנו להשתמש בעמודה אחת או יותר מתוצאת שאילתת SQL כאינדקס של מסגרת נתונים. זה יכול לקחת עמודה בודדת או רשימה של עמודות.
  • coerce_float: פרמטר זה מציין אם יש להמיר ערכים לא מספריים למספרים צפים או להשאיר אותם כמחרוזות. הוא מוגדר כ-true כברירת מחדל. במידת האפשר, הוא ממיר ערכים לא מספריים לסוגי צפים.
  • params: הפרמטרים מספקים שיטה מאובטחת להעברת ערכים דינמיים לשאילתת SQL. אנו יכולים להשתמש בפרמטר params כדי להעביר מילון, טופלה או רשימה. בהתאם למסד הנתונים, התחביר של פרמים משתנה.
  • parse_dates: זה מאפשר לנו לציין איזו עמודה במסגרת הנתונים המתקבלת תתפרש כתאריך. הוא מקבל עמודה בודדת, רשימת עמודות או מילון עם המפתח כשם העמודה והערך כפורמט העמודה.
  • columns: זה מאפשר לנו להביא רק עמודות נבחרות מהרשימה.
  • chunksize: כאשר עובדים עם מערך נתונים גדול, גודל הנתונים חשוב. הוא מאחזר את תוצאת השאילתה בנתחים קטנים יותר, ומשפר את הביצועים.

להלן דוגמא לשימוש read_sql():

קוד:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql("SELECT * FROM Student", engine, index_col='Roll Number', parse_dates='dateOfBirth')
print(df)
print("The Data type of dateOfBirth: ", df.dateOfBirth.dtype) engine.dispose()

פלט:

 firstName lastName email dateOfBirth
rollNumber
1 Mark Simson [email protected] 2000-02-23
2 Peter Griffen [email protected] 2001-04-15
3 Meg Aniston [email protected] 2001-09-20
Date type of dateOfBirth: datetime64[ns]

לאחר החיבור למסד הנתונים, אנו מבצעים שאילתה שמחזירה את כל הרשומות מה- Student טבלה ומאחסן אותם ב-DataFrame df. העמודה "מספר רול" מומרת לאינדקס באמצעות ה index_col פרמטר, וסוג הנתונים "dateOfBirth" הוא "datetime64[ns]" עקב parse_dates. אנחנו יכולים להשתמש read_sql() לא רק כדי לאחזר נתונים אלא גם כדי לבצע פעולות אחרות כגון הוספה, מחיקה ועדכון. read_sql() היא פונקציה גנרית.

טעינת טבלאות או תצוגות ספציפיות מה-DB

טעינת שולחן או תצוגה ספציפיים עם פנדות read_sql_table() היא טכניקה נוספת לקריאת נתונים ממסד הנתונים לתוך מסגרת נתונים של Pandas.

מה read_sql_table?

ספריית Pandas מספקת את read_sql_table פונקציה, שתוכננה במיוחד לקרוא טבלת SQL שלמה מבלי לבצע שאילתות כלשהן ולהחזיר את התוצאה כ-Dataframe של Pandas.

התחביר של read_sql_table() הוא כמו להלן:

pandas.read_sql_table(table_name, con, schema=None, index_col=None, coerce_float=True, parse_dates=None, columns=None, chunksize=None)

למעט table_name וסכימה, הפרמטרים מוסברים באותו אופן כמו read_sql().

  • table_name: הפרמטר table_name הוא השם של טבלת SQL במסד הנתונים.
  • schema: פרמטר אופציונלי זה הוא שם הסכימה המכילה את שם הטבלה.

לאחר יצירת חיבור למסד הנתונים, נשתמש ב- read_sql_table פונקציה לטעינת Student טבלה לתוך Pandas DataFrame.

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql_table('Student', engine)
print(df.head()) engine.dispose()

פלט:

 rollNumber firstName lastName email dateOfBirth
0 1 Mark Simson [email protected] 2000-02-23
1 2 Peter Griffen [email protected] 2001-04-15
2 3 Meg Aniston [email protected] 2001-09-20

נניח שזה שולחן גדול שיכול להיות עתיר זיכרון. בואו נחקור כיצד נוכל להשתמש ב- chunksize פרמטר כדי לטפל בבעיה זו.

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

קוד:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df_iterator = pd.read_sql_table('Student', engine, chunksize = 1) for df in df_iterator: print(df.head()) engine.dispose()

פלט:

 rollNumber firstName lastName email dateOfBirth
0 1 Mark Simson [email protected] 2000-02-23
0 2 Peter Griffen [email protected] 2001-04-15
0 3 Meg Aniston [email protected] 2001-09-20

אנא זכור כי chunksize אני משתמש כאן הוא 1 כי יש לי רק 3 רשומות בטבלה שלי.

שאילתה ישירה של ה-DB עם תחביר SQL של ​​Pandas

חילוץ תובנות ממסד הנתונים הוא חלק חשוב עבור מנתחי נתונים ומדענים. לשם כך, נמנף את read_sql_query() פונקציה.

מה זה read_sql_query()?

שימוש בפנדות read_sql_query() פונקציה, נוכל להריץ שאילתות SQL ולקבל את התוצאות ישירות לתוך DataFrame. ה read_sql_query() הפונקציה נוצרה במיוחד עבור SELECT הצהרות. לא ניתן להשתמש בו עבור כל פעולות אחרות, כגון DELETE or UPDATE.

תחביר:

pandas.read_sql_query(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, chunksize=None, dtype=None, dtype_backend=_NoDefault.no_default)

כל תיאורי הפרמטרים זהים ל- read_sql() פוּנקצִיָה. הנה דוגמה של read_sql_query():

קוד:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql_query('Select firstName, lastName From Student Where rollNumber = 1', engine)
print(df) engine.dispose()

פלט:

 firstName lastName
0 Mark Simson

כתיבת קבצי SQL עם Pandas

תוך כדי ניתוח נתונים, נניח שגילינו שצריך לשנות כמה ערכים או שנדרשת טבלה או תצוגה חדשה עם הנתונים. כדי לעדכן או להוסיף רשומה חדשה, שיטה אחת היא להשתמש read_sql() ולכתוב שאילתה. עם זאת, שיטה זו יכולה להיות ארוכה. פנדות מספקות שיטה מצוינת בשם to_sql() למצבים כאלה.

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

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

לפני שניצור טבלה חדשה, בואו נדון תחילה to_sql() בפירוט.

מה to_sql()?

אל האני to_sql() הפונקציה של ספריית Pandas מאפשרת לנו לכתוב או לעדכן את מסד הנתונים. ה to_sql() הפונקציה יכולה לשמור נתוני DataFrame במסד נתונים של SQL.

תחביר עבור to_sql():

DataFrame.to_sql(name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None, method=None)

רק name ו con פרמטרים הם חובה להפעיל to_sql(); עם זאת, פרמטרים אחרים מספקים גמישות ואפשרויות התאמה אישית נוספות. בואו נדון בפירוט על כל פרמטר:

  • name: שם טבלת SQL שיש ליצור או לשנות.
  • con: אובייקט החיבור של מסד הנתונים.
  • schema: הסכימה של הטבלה (אופציונלי).
  • if_exists: ערך ברירת המחדל של פרמטר זה הוא "כשל". פרמטר זה מאפשר לנו להחליט על הפעולה שיש לבצע אם הטבלה כבר קיימת. האפשרויות כוללות "נכשל", "החלף" ו"להוסיף".
  • index: פרמטר האינדקס מקבל ערך בוליאני. כברירת מחדל, הוא מוגדר כ-True, כלומר האינדקס של ה-DataFrame ייכתב לטבלת SQL.
  • index_label: פרמטר אופציונלי זה מאפשר לנו לציין תווית עמודה עבור עמודות האינדקס. כברירת מחדל, האינדקס נכתב לטבלה, אך ניתן לתת שם ספציפי באמצעות פרמטר זה.
  • chunksize: מספר השורות שיש לכתוב בכל פעם במסד הנתונים של SQL.
  • dtype: פרמטר זה מקבל מילון עם מפתחות כשמות עמודות וערכים כסוגי הנתונים שלהם.
  • method: פרמטר השיטה מאפשר לציין את השיטה המשמשת להכנסת נתונים ל-SQL. כברירת מחדל, הוא מוגדר ל-None, מה שאומר שהפנדות ימצאו את הדרך היעילה ביותר בהתבסס על מסד הנתונים. ישנן שתי אפשרויות עיקריות לפרמטרים של השיטה:
    • multi: הוא מאפשר הוספת שורות מרובות בשאילתת SQL אחת. עם זאת, לא כל מסדי הנתונים תומכים בהוספת שורות מרובות.
    • פונקציה ניתנת להתקשרות: כאן, נוכל לכתוב פונקציה מותאמת אישית ל-insert ולקרוא לה באמצעות פרמטרי מתודה.

הנה דוגמה לשימוש to_sql():

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') data = {'Name': ['Paul', 'Tom', 'Jerry'], 'Age': [9, 8, 7]}
df = pd.DataFrame(data) df.to_sql('Customer', con=engine, if_exists='fail') engine.dispose()

טבלה חדשה בשם Customer נוצרת במסד הנתונים, עם שני שדות בשם "שם" ו"גיל".

תמונת מצב של מסד נתונים:

פלט של to_sql()

עדכון טבלאות קיימות עם מסגרות נתונים של Pandas

עדכון נתונים במסד נתונים הוא משימה מורכבת, במיוחד כאשר עוסקים בנתונים גדולים. עם זאת, באמצעות to_sql() פונקציה ב-Pandas יכולה להקל על המשימה הזו הרבה יותר. כדי לעדכן את הטבלה הקיימת במסד הנתונים, ה- to_sql() ניתן להשתמש בפונקציה עם if_exists הפרמטר מוגדר ל"החלף". פעולה זו תחליף את הטבלה הקיימת בנתונים החדשים.

הנה דוגמה ל to_sql() שמעדכן את הקוד שנוצר Customer שולחן. נניח, ב- Customer בטבלה אנו רוצים לעדכן את גילו של לקוח בשם פול מ-9 ל-10. לשם כך, ראשית, נוכל לשנות את השורה המתאימה ב-DataFrame, ולאחר מכן להשתמש ב- to_sql() פונקציה לעדכון מסד הנתונים.

קוד:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql_table('Customer', engine) df.loc[df['Name'] == 'Paul', 'Age'] = 10 df.to_sql('Customer', con=engine, if_exists='replace') engine.dispose()

במסד הנתונים, גילו של פול מתעדכן:

פלט של to_sql()

סיכום

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

ספריית Pandas מקלה על מניפולציה של נתונים בתוך מסגרת נתונים, בעוד ש-SQL מספק שפה רבת עוצמה לשאילתת נתונים במסד נתונים. שימוש גם ב-Pandas וגם ב-SQL כדי לקרוא ולכתוב את הנתונים יכול לחסוך זמן ומאמץ במשימות ניתוח נתונים, במיוחד כאשר הנתונים גדולים מאוד. בסך הכל, מינוף של SQL ו-Pandas יחד יכול לעזור לנתחי נתונים ולמדענים לייעל את זרימת העבודה שלהם.

ספוט_ימג

המודיעין האחרון

ספוט_ימג

דבר איתנו

שלום שם! איך אני יכול לעזור לך?