مقدمه‌ای بر SQL در Python

تو این مطلب در مورد بکارگیری زبان SQL در محیط پایتون صحبت می‌کنیم و شرح می‌دیم که چطور SQL Engine تو پایتون شبیه سازی میشه تا امکان کوئری زدن به دیتابیس رو فراهم کنه.

به عنوان یک مهندس داده که با پایتون کار میکنه، شما معمولا نیاز دارین که دیتا رو از یک دیتابیس رابطه ای که توی سرور لوکال یا Cloud نگهداری میشه استخراج کنین. راه های زیادی برای انجام اینکار وجود داره، برای مثال شما می‌تونید کوئری رو توی دیتابیس SQL بزنین و نتیجه رو به عنوان فایل csv. ذخیره کنین تا اون رو در پایتون وارد کنین. البته بهترین کار استفاده مستقیم از SQL تو پایتون هست. استفاده همزمان Panda و SQL میتونه براتون امکان کوئری زدن ، پردازش و استفاده از دیتا تو پایتون رو فراهم کنه.

پرسش مهم : SQL چیست ؟

کلمه SQL تشکیل شده از Structured Query Language، یه زبان برنامه نویسی که برای مدیریت یا کوئری زدن برروی دیتایی که در یک دیتابیس رابطه ای ذخیره شده، استفاده میشه . RDBMS - relational database management system

زبان SQL، زبان غالب برای کار با دیتاهای ساخت یافته که در موجودیت های دیتابیس ذخیره شده اند ( مثل جدول پایگاه داده) هست. زبان های دیگه ای هم برای انجام اینکار وجود داره اما SQL بسیار شناخته شده است و به طور گسترده بکار برده میشه.

تلفظ درست SQL چیست ؟

اوایل دهه 1970 توسعه SQL در IBM انجام شد که در ابتدا SEQUEL گفته میشد ، به معنی Structured English Query Language، ولی بعدها به دلایل تجاری اسم اون به SQL به معنی Structured Query Language تغییر کرد. تلفظ /see-qu-el" - /ˈsiːkwəl" بخاطر اشاره به کلمه English گفته می شه. این تلفظ رایج توسط آموزش دهنده ها است. در نهایت اینکه برخی "S-Q-L" و عده ای "sequel" تلفظ میکنن که هیچ کدون نادرست نیست.

دیتابیس رابطه ای چیست ؟

یکی از بهترین تعریف‌های دیتابیس رابطه ای رو Amazon Web Services ارائه کرده :

"یک دیتابیس رابطه ای مجموعه ای از آیتم های دیتا هست با رابطه های از پیش تعریف شده میانشان. این آیتم ها به عنوان مجموعه جداول شامل ستون ها و سطرها سازمان یافته اند. جداول برای نگهداری اطلاعات در مورد Objectهایی که باید در پایگاه داده بیان شوند استفاده میشود. هر ستون در جدول نوع خاصی از دیتا و هر فیلد Value مربوط به attribute را نگهداری میکند. سطر در جدول بیان کننده ی مجموعه ای از مقادیر بهم وابسته مربوط به یک Object یا entity است. هر سطر در جدول میتواند با یک کلید شناسایی انحصاری به اسم primary key مشخص شده و سطرها در بین جداول مختلف می توانند توسط foreign keyها بهم مربوط شوند. این دیتا به روش های مختلف میتواند در دسترس قرار بگیره بدون اینکه نیاز به سازماندهی مجدد دیتابیس باشد ".

دیتابیس ها میتونن ساختار خیلی پیچیده ای داشته باشن ، با چندین جدول، چندین ستون و سطر برای هر جدول. اگه روابط بین جدول‌ها نامشخص باشن کار کردن و کوئری زدن در دیتابیس سخت و حتی غیرممکن میشه. ERD - Entity Relationship Diagram برای ویژوال کردن این روابط و نشون دادن موجودیت (ستون)های هر جدول و نوع اونها بکار میره . ادمین پایگاه داده سازمان شما باید بتونه این دیاگرام دیتابیس رو در اختیارتون قرار بده.

استفاده از SQL در Python

کتابخانه های زیادی برای استفاده از SQL در پایتون وجود داره. چندین کتابخانه برای این هدف توسعه داده شده مثل SQLite ، MySQL pyodbc ، MYSQLdb .

تو این مطلب از کتابخانه Panda به همراه کتابخانه sqlalchemy برای اتصال به دیتابیس SQL تو پایتون استفاده می کنیم. دستورات این کتابخانه مشابه کتابخونه pyodbc هست و با دنبال کردن چند مرحله ساده میشه به دیتابیس SQL وصل شذ.

ساخت SQL Engine

خب برای شروع، اول باید کتابخونه مورد نیاز رو نصب و import کنیم. در نهایت میخواهیم قابلیت create_engine کتابخونه رو استفاده کنیم.

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

pip install sqlalchemy

یا

در ادامه کتابخانه های مورد استفاده رو import می‌کنیم.

import pandas as pd
from sqlalchemy import create_engine
import pandas as pd

ایجاد کانکشن به دیتابیس

وقتی کتابخانه import شد، به کمک دستور زیر SQL engine رو میسازیم. در واقع کلاس جدید Engine رو ایجاد می کنیم.نیاز به یک درایور مخصوص دیتابیس مورد استفاده هست ، مثلا برای اتصال به دیتابیس SQL Server از pyodbc استفاده میشه، برای دیتابیس های متفاوت درایور های مخصوص مثل psycopg2 cx_oracle وجود داره .اطلاعات بیشتر درمورد ساخت این کانکشن رو میتونین از www.sqlalchemy.org ببینین.

engine = sa.create_engine('mssql+pyodbc://user:password@server/database')

نیاز به تنظیم server و database با مقدار درست داریم.

server : اسم سرور میزبان دیتابیس
database : اسم دیتابیس مورد نظر

# establishing the connection to the databse using engine as an interface
conn = engine.connect()

نمایش داده ها با استفاده از دستورات SQL

خب تا اینجا به دیتابیس وصل شدیم حالا امکان کوئری زدن برروی جداول ، بروزرسانی اونها و ساخت جدول جدید رو داریم. برای استفاده از sqlalchemy نیاز هست دستورات SQL رو در یک container برای دیتابیس ارسال کنید و پس از دریافت نتیجه اون رو روی دیتافریم panda قرار بدین. دو فاکتوری که در هر کوئری اولیه باید وجود داشته باشه select , from هست .

دستور SELECT امکان انتخاب مجموعه ای از ستون ها یا همه اونها رو از جدول میده.
دستور FROM مشخص میکنه کدوم جدول برای انتخاب ستون ها مورد نظر هست.

نمایش نام جداول موجود در دیتابیس

# printing names of the tables present in the database
print(engine.table_names())

استخراج رکورد های موجود در جدول

# checking whether the connection was actually established by selecting and displaying contents of table from the database
result = engine.execute(“select * from tablename”)
for row in result:
print (row)
result.close()

میتونیم جداول رو در دیتافریم Panda ذخیره کنیم و بعدا با دیتا ذخیره شده کار کنیم.

خواندن و ذخیره جدول در دیتافریم Panda

# reading a SQL query using pandas
sql_query = pd.read_sql_query(‘SELECT * FROM database_name.dbo.tablename’, engine)
# saving SQL table in a pandas data frame
df = pd.DataFrame(sql_query, columns = [‘column1’,‘column2’,…..])
# printing the dataframe
df

همچنین میتونیم یک اکسترنال فایل مثل CSV, excel رو بخونیم و توی جدول SQL ذخیره کنیم.

df = pd.read_csv(‘tablename’)
# create a new table and append data frame values to this table
df.to_sql(‘tablename’, con=engine, if_exists=’append’,index=False,chunksize=1000)

و در نهایت بستن کانکشن :