ذكاء البيانات التوليدية

قراءة وكتابة ملفات SQL في Pandas

التاريخ:

عندما بدأت في تعلم تحليل البيانات قبل بضع سنوات ، كان أول شيء تعلمته هو SQL و Pandas. كمحلل بيانات ، من الأهمية بمكان أن يكون لديك أساس قوي في العمل مع SQL و Pandas. كلاهما من الأدوات القوية التي تساعد محللي البيانات على تحليل البيانات المخزنة في قواعد البيانات ومعالجتها بكفاءة.

نظرة عامة على SQL و Pandas

SQL (لغة الاستعلام الهيكلية) هي لغة برمجة تستخدم لإدارة قواعد البيانات العلائقية ومعالجتها. من ناحية أخرى ، Pandas هي مكتبة Python تستخدم لمعالجة البيانات وتحليلها.

يتضمن تحليل البيانات العمل مع كميات كبيرة من البيانات ، وغالبًا ما تُستخدم قواعد البيانات لتخزين هذه البيانات. توفر SQL و Pandas أدوات قوية للعمل مع قواعد البيانات ، مما يسمح لمحللي البيانات باستخراج البيانات ومعالجتها وتحليلها بكفاءة. من خلال الاستفادة من هذه الأدوات ، يمكن لمحللي البيانات الحصول على رؤى قيمة من البيانات التي كان من الصعب الحصول عليها بخلاف ذلك.

في هذه المقالة ، سوف نستكشف كيفية استخدام SQL و Pandas لقراءة قاعدة بيانات والكتابة فيها.

الاتصال بقاعدة البيانات

تركيب المكتبات

يجب علينا أولاً تثبيت المكتبات الضرورية قبل أن نتمكن من الاتصال بقاعدة بيانات SQL باستخدام Pandas. المكتبتان الرئيسيتان المطلوبتان هما Pandas و SQLAlchemy. Pandas هي مكتبة شائعة لمعالجة البيانات تسمح بتخزين هياكل البيانات الكبيرة ، كما هو مذكور في المقدمة. في المقابل ، يوفر SQLAlchemy واجهة برمجة تطبيقات للاتصال والتفاعل مع قاعدة بيانات 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 مع Pandas

دعنا نقرأ البيانات الآن بعد أن أنشأنا اتصالاً. في هذا القسم ، سوف ننظر في read_sql, read_sql_tableو read_sql_query الدالات وكيفية استخدامها للعمل مع قاعدة البيانات.

تنفيذ استعلامات SQL باستخدام Panda 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: تحدد هذه المعلمة ما إذا كان يجب تحويل القيم غير الرقمية إلى أرقام عائمة أو تركها كسلاسل. تم تعيينه على صحيح بشكل افتراضي. إذا كان ذلك ممكنًا ، فإنه يحول القيم غير الرقمية إلى أنواع عائمة.
  • 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() هي وظيفة عامة.

تحميل جداول معينة أو طرق عرض من قاعدة البيانات

تحميل جدول معين أو عرض مع الباندا read_sql_table() هي تقنية أخرى لقراءة البيانات من قاعدة البيانات إلى إطار بيانات Pandas.

ما هي تفاصيل read_sql_table?

توفر مكتبة Pandas ملفات read_sql_table ، والتي تم تصميمها خصيصًا لقراءة جدول SQL بالكامل دون تنفيذ أي استعلامات وإرجاع النتيجة كإطار بيانات 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 ، مع أفضل الممارسات ، والمعايير المقبولة في الصناعة ، وورقة الغش المضمنة. توقف عن أوامر Googling 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 سجلات فقط في الجدول الخاص بي.

الاستعلام عن قاعدة البيانات مباشرة باستخدام بناء جملة 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 واحد. ومع ذلك ، لا تدعم جميع قواعد البيانات الإدراج متعدد الصفوف.
    • وظيفة قابلة للاستدعاء: هنا ، يمكننا كتابة دالة مخصصة للإدراج واستدعائها باستخدام معلمات الطريقة.

إليك مثال على استخدام 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()

يتم إنشاء جدول جديد يسمى "العميل" في قاعدة البيانات ، مع حقلين يسمى "الاسم" و "العمر".

لقطة قاعدة البيانات:

إخراج to_sql ()

تحديث الجداول الموجودة مع Pandas Dataframes

يعد تحديث البيانات في قاعدة البيانات مهمة معقدة ، خاصة عند التعامل مع البيانات الكبيرة. ومع ذلك ، فإن استخدام ملف to_sql() وظيفة في Pandas يمكن أن تجعل هذه المهمة أسهل بكثير. لتحديث الجدول الموجود في قاعدة البيانات ، فإن ملف to_sql() يمكن استخدام الوظيفة مع if_exists تم تعيين المعلمة على "استبدال". سيؤدي هذا إلى الكتابة فوق الجدول الموجود بالبيانات الجديدة.

هنا مثال على to_sql() يقوم بتحديث ملف Customer طاولة. افترض ، في Customer الجدول نريد تحديث عمر عميل اسمه Paul من 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 معًا محللي البيانات والعلماء في تبسيط سير عملهم.

بقعة_صورة

أحدث المعلومات الاستخباراتية

بقعة_صورة

الدردشة معنا

أهلاً! كيف يمكنني مساعدك؟