PostgreSQL के लिए शुरुआती मार्गदर्शिका

रिलेशनल डेटाबेस सिस्टम पर क्वेरी चलाने की क्षमता किसी भी डेटा वैज्ञानिक के लिए अनिवार्य कौशल है। SQL यानी Structured Query Language आपको यह काम बहुत कुशलता से करने देता है। SQL न केवल आपको डेटा से सार्थक सवाल पूछने देता है, बल्कि कई अलग-अलग तरीकों से डेटा के साथ काम करने में भी सक्षम बनाता है। डेटाबेस के बिना, व्यवहार में कोई भी वास्तविक‑दुनिया अनुप्रयोग संभव नहीं है। इसलिए, डेटाबेस का ज्ञान और उन्हें संभालने की क्षमता डेटा वैज्ञानिक के औज़ारों में बेहद अहम है।

त्वरित तथ्य: SQL को SE-QU-EL भी कहा जाता है। इसका ऐतिहासिक महत्व है — SQL का प्रारंभिक नाम था Simple English Query Language।

आम तौर पर रिलेशनल डेटाबेस कुछ इस तरह दिखते हैं —

रिलेशंस को टेबल भी कहा जाता है। डेटाबेस को दर्शाने के कई तरीके हैं। यह उनमें से एक है और सबसे लोकप्रिय भी।

यह ट्यूटोरियल SQL के चार सबसे सामान्य ऑपरेशन का परिचय देता है: Create, Read, Update और Delete। सामूहिक रूप से इन चार ऑपरेशन को अक्सर CRUD कहा जाता है। जिन भी अनुप्रयोगों में उपयोगकर्ता सहभागिता होती है, उनमें ये चारों ऑपरेशन मौजूद रहते हैं।

आप PostgreSQL को रिलेशनल डेटाबेस मैनेजमेंट सिस्टम के रूप में उपयोग करेंगे। PostgreSQL बहुत हल्का है और यह निःशुल्क भी है। इस ट्यूटोरियल में, आप —

• PostgreSQL के साथ सेटअप और शुरुआत करेंगे
• किसी PostgreSQL डेटाबेस से कनेक्ट करेंगे
• उस डेटाबेस में टेबल बनाएंगे, पढ़ेंगे, अपडेट करेंगे और डिलीट करेंगे
• Jupyter Notebook पर SQL चलाएंगे
• Python में SQL चलाएंगे

चलिए शुरू करते हैं।

PostgreSQL के साथ सेटअप और शुरुआत

PostgreSQL एक हल्का और ओपन सोर्स RDBMS है। उद्योग जगत में इसे अत्यधिक स्वीकार्यता मिली है। आप PostgreSQL के बारे में अधिक इसकी आधिकारिक वेबसाइट से जान सकते हैं।

PostgreSQL में क्वेरी लिखने और चलाने के लिए इसे आपके मशीन पर स्थापित होना चाहिए। इंस्टॉलेशन बेहद आसान है। निम्नलिखित दो छोटे वीडियो दिखाते हैं कि 32‑bit Windows‑7 मशीन पर PostgreSQL कैसे डाउनलोड और इंस्टॉल किया जा सकता है:

• PostgreSQL डाउनलोड करें
• PostgreSQL इंस्टॉल करें

ध्यान दें: PostgreSQL इंस्टॉल करते समय आप जो पासवर्ड और पोर्ट नंबर दर्ज कर रहे हैं, उनका ध्यान रखें।

जब आपने सफलतापूर्वक PostgreSQL इंस्टॉल कर लिया हो, तो pgAdmin खोलें। pgAdmin एक उपयोगी यूटिलिटी है जो PostgreSQL इंस्टॉलेशन के साथ आती है और यह आपको सुंदर ग्राफिकल इंटरफेस के माध्यम से नियमित डेटाबेस से जुड़ी गतिविधियाँ करने देती है। pgAdmin का इंटरफेस कुछ इस तरह दिखता है —

pgAdmin खोलने पर आप इंटरफेस में "PostgreSQL 9.4 (localhost:5432)" नाम का सर्वर सूचीबद्ध देखेंगे —

ध्यान दें: आपका वर्ज़न ऊपर दिए गए से भिन्न हो सकता है और इसलिए पोर्ट नंबर (5432) भी।

इंस्टॉलेशन के दौरान जो पासवर्ड आपने दिया था, उसे दर्ज कर सर्वर से कनेक्ट करें। संदर्भ हेतु — https://www.loom.com/share/71708f6a99ee483a977991e9e8a25808।

लोकल डेटाबेस सर्वर से सफलतापूर्वक कनेक्ट होने के बाद, आपको निम्नलिखित जैसा इंटरफेस दिखाई देगा —

आपका पहला काम एक डेटाबेस बनाना होगा, जिसे आप Databases टैब पर राइट‑क्लिक कर और ड्रॉप‑डाउन विकल्पों में से New Database चुनकर कर सकते हैं। आइए DataCamp_Courses नाम का डेटाबेस बनाते हैं। एक बार डेटाबेस बन जाने पर आप इस ट्यूटोरियल के अगले भागों पर आगे बढ़ सकते हैं।

PostgreSQL में CRUD ऑपरेशन

दिए गए विनिर्देश के अनुसार टेबल बनाना -

किसी डेटाबेस पर काम करने के लिए आपको एक टेबल की आवश्यकता होगी। तो चलिए एक सरल टेबल (जिसे रिलेशन भी कहा जाता है) datacamp_courses नाम से निम्नलिखित विनिर्देश (स्कीमा) के साथ बनाते हैं —

यह विनिर्देश टेबल के कॉलम के बारे में कई बातें बताता है —

• टेबल की प्राइमरी की course_id होनी चाहिए (ध्यान दें कि केवल यही बोल्ड है) और इसका डेटा‑टाइप integer होना चाहिए। प्राइमरी की एक constraint है जो कॉलम के मानों को non‑null और यूनिक रहने के लिए बाध्य करती है। यह आपको टेबल में मौजूद किसी विशिष्ट instance या instance‑समूह की अद्वितीय पहचान करने देती है।
• विनिर्देश में बाकी जानकारी अब आसानी से समझ में आ जानी चाहिए।

टेबल बनाने के लिए, नए बने डेटाबेस DataCamp_Courses पर राइट‑क्लिक करें और विकल्पों में से CREATE Script चुनें। आपको निम्न जैसा कुछ दिखना चाहिए —

अब निम्नलिखित क्वेरी चलाते हैं —

CREATE TABLE datacamp_courses(
 course_id SERIAL PRIMARY KEY,
 course_name VARCHAR (50) UNIQUE NOT NULL,
 course_instructor VARCHAR (100) NOT NULL,
 topic VARCHAR (20) NOT NULL
);
संचालित द्वारा

क्वेरी चलाने के लिए बस इसे चुनें और मेन्यू बार से execute बटन पर क्लिक करें —

आउटपुट कुछ इस प्रकार होना चाहिए —

PostgreSQL में टेबल बनाने की क्वेरी की सामान्य संरचना इस प्रकार दिखती है —

CREATE TABLE table_name (
 column_name TYPE column_constraint,
 table_constraint table_constraint
)
संचालित द्वारा

टेबल बनाते समय हमने कोई table_constraints निर्दिष्ट नहीं किया। अभी के लिए इसे छोड़ा जा सकता है। बाकी सब कुछ काफ़ी पठनीय है सिवाय SERIAL कीवर्ड के। PostgreSQL में Serial आपको ऑटो‑इन्क्रिमेंट कॉलम बनाने देता है। डिफ़ॉल्ट रूप से यह integer प्रकार के मान बनाता है। Serial हमें किसी टेबल की आखिरी डाली/अपडेट की गई प्राइमरी की याद रखने के बोझ से मुक्त करता है, और प्राइमरी की के लिए ऑटो‑इन्क्रिमेंट का उपयोग करना एक अच्छा अभ्यास है। Serial के बारे में और जानें यहाँ।

नव-निर्मित टेबल में कुछ रिकॉर्ड डालना —

इस चरण में, आप टेबल में कुछ रिकॉर्ड डालेंगे। आपके रिकॉर्ड में ये होने चाहिए —

• कोर्स का नाम
• कोर्स के प्रशिक्षक का नाम
• कोर्स का विषय

course_id कॉलम के मान PostgreSQL स्वयं संभाल लेगा। PostgreSQL में insert क्वेरी की सामान्य संरचना इस प्रकार होती है —

INSERT INTO table(column1, column2, …)
VALUES
 (value1, value2, …);
संचालित द्वारा

आइए कुछ रिकॉर्ड डालते हैं —

INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Deep Learning in Python','Dan Becker','Python');

INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Joining Data in PostgreSQL','Chester Ismay','SQL');
संचालित द्वारा

ध्यान दें कि आपने प्राइमरी की को स्पष्ट रूप से निर्दिष्ट नहीं किया। आप इसका प्रभाव थोड़ी देर में देखेंगे।

जब आप ऊपर की दोनों क्वेरियाँ चलाएँगे, तो सफल insertion पर आपको निम्नलिखित परिणाम मिलना चाहिए —

Query returned successfully: one row affected, 11 ms execution time.

टेबल से डेटा पढ़ना/देखना —

यह शायद वह काम है जो आप अपने डेटा साइंस सफर में बहुत करेंगे। फिलहाल देखें कि टेबल datacamp_courses कैसी दिख रही है।

इसे सामान्यतः select क्वेरी कहा जाता है, और select क्वेरी की सामान्य संरचना इस प्रकार होती है —

SELECT
 column_1,
 column_2,
 ...
FROM
 table_name;
संचालित द्वारा

आइए टेबल datacamp_courses से सभी कॉलम चुनते हैं

SELECT * FROM datacamp_courses;
संचालित द्वारा

और आपको यह मिलता है —

अब प्राइमरी की पर ध्यान दें। यदि आप केवल कोर्स के नाम देखना चाहते हैं, तो आप इस तरह कर सकते हैं —

SELECT course_name from datacamp_courses;
संचालित द्वारा

और आपको यह मिलता है —

आप जितने चाहें कॉलम नाम निर्दिष्ट कर सकते हैं जिनके परिणाम आप देखना चाहते हैं, बशर्ते वे टेबल में मौजूद हों। यदि आप select course_name, number_particpants from datacamp_courses; चलाते हैं, तो आपको त्रुटि मिलेगी क्योंकि number_particpants कॉलम टेबल में मौजूद नहीं है। अब आप देखेंगे कि किसी विशिष्ट रिकॉर्ड को कैसे अपडेट किया जाता है।

टेबल में किसी रिकॉर्ड को अपडेट करना —

SQL में update क्वेरी की सामान्य संरचना निम्नलिखित है:

UPDATE table
SET column1 = value1,
    column2 = value2 ,...
WHERE
 condition;
संचालित द्वारा

आप उस रिकॉर्ड को अपडेट करने जा रहे हैं जहाँ course_instructor = "Chester Ismay" है और course_name को "Joining Data in SQL" पर सेट करेंगे। फिर आप जाँचेंगे कि रिकॉर्ड अपडेट हुआ या नहीं। इसके लिए क्वेरी होगी —

UPDATE datacamp_courses SET course_name = 'Joining Data in SQL'
WHERE course_instructor = 'Chester Ismay';
संचालित द्वारा

आइए select क्वेरी चलाकर देखें कि आपकी update क्वेरी का इच्छित प्रभाव पड़ा या नहीं —

आप देख सकते हैं कि आपकी update क्वेरी ठीक वैसे ही चली जैसा आप चाहते थे। अब आप देखेंगे कि किसी रिकॉर्ड को टेबल से कैसे हटाते हैं।

टेबल में किसी रिकॉर्ड को डिलीट करना —

SQL में delete क्वेरी की सामान्य संरचना इस प्रकार होती है:

DELETE FROM table
WHERE condition;
संचालित द्वारा

आप उस रिकॉर्ड को डिलीट करने जा रहे हैं जहाँ course_name = "Deep Learning in Python" है और फिर सत्यापित करेंगे कि रिकॉर्ड डिलीट हुआ या नहीं। संरचना का अनुसरण करते हुए, निम्नलिखित क्वेरी यह कर देगी —

DELETE from datacamp_courses
WHERE course_name = 'Deep Learning in Python';
संचालित द्वारा

ध्यान रखें कि SQL में कीवर्ड case‑sensitive नहीं होते, लेकिन डेटा case‑sensitive होता है। यही कारण है कि आप क्वेरियों में बड़े और छोटे अक्षरों का मिश्रण देखते हैं।

आइए देखें कि इच्छित रिकॉर्ड टेबल से डिलीट हुआ या नहीं —

और हाँ, इच्छित रिकॉर्ड वाकई डिलीट हो गया।

ट्यूटोरियल में उल्लिखित क्वेरियों की सामान्य संरचनाएँ PostgreSQL Tutorial से संदर्भित हैं।

अब आप SQL में बुनियादी CRUD क्वेरियाँ चलाना जानते हैं। आपमें से कुछ Jupyter Notebooks का व्यापक रूप से उपयोग करते हैं और सोच रहे होंगे कि काश इन क्वेरियों को सीधे Jupyter Notebook से चलाने का विकल्प होता। अगले भाग में, आप देखेंगे कि यह कैसे किया जा सकता है।

SQL + Jupyter Notebooks

Jupyter Notebooks से SQL क्वेरियाँ चलाने के लिए पहला कदम ipython-sql पैकेज इंस्टॉल करना होगा।

यदि यह इंस्टॉल नहीं है, तो इसे इस तरह इंस्टॉल करें:

pip install ipython-sql
संचालित द्वारा

इसके बाद, अपने Jupyter Notebook में sql एक्सटेंशन को चलाकर लोड करें —

%load_ext sql
संचालित द्वारा

अगला कदम किसी PostgreSQL डेटाबेस से कनेक्ट करना होगा। आप उसी डेटाबेस से कनेक्ट करेंगे जिसे आपने बनाया था — DataCamp_Courses।

अपने सिस्टम में पहले से बने डेटाबेस से कनेक्ट होने के लिए आपको Python को उसका dialect पहचानने के लिए बताना होगा। सरल शब्दों में, आपको Python को बताना होगा कि यह PostgreSQL डेटाबेस है। इसके लिए आपको psycopg2 की आवश्यकता होगी, जिसे इस तरह इंस्टॉल किया जा सकता है:

pip install psycopg2
संचालित द्वारा

psycopg इंस्टॉल करने के बाद डेटाबेस से इस तरह कनेक्ट करें —

%sql postgresql://postgres:postgres@localhost:5432/DataCamp_Courses
संचालित द्वारा

'Connected: postgres@DataCamp_Courses'संचालित द्वारा

%sql के उपयोग पर ध्यान दें। यह एक मैजिक कमांड है। यह आपको Jupyter Notebook से SQL कथन चलाने देता है। %sql के बाद जो आता है उसे डेटाबेस कनेक्शन URL कहते हैं, जिसमें आप निर्दिष्ट करते हैं —

• Dialect (postgres)
• Username (postgres)
• Password (postgres)
• Server address (localhost)
• Port number (5432)
• Database name (DaaCamp_Courses)

अब आप Jupyter Notebook से वह सब कुछ कर सकते हैं जो आपने pgAdmin इंटरफेस में किया था। आइए बिल्कुल वही स्कीमा रखते हुए टेबल _datacampcourses बनाकर शुरू करते हैं।

लेकिन इससे पहले आपको टेबल को ड्रॉप करना होगा क्योंकि SQL एक ही नाम की दो टेबल स्टोर करने नहीं देगा। आप टेबल को इस तरह ड्रॉप कर सकते हैं —

%sql DROP table datacamp_courses;
संचालित द्वारा

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
Done.





[] संचालित द्वारा

टेबल _datacampcourses अब PostgreSQL से डिलीट हो चुकी है, अतः आप इसी नाम से नई टेबल बना सकते हैं।

%%sql
CREATE TABLE datacamp_courses(
 course_id SERIAL PRIMARY KEY,
 course_name VARCHAR (50) UNIQUE NOT NULL,
 course_instructor VARCHAR (100) NOT NULL,
 topic VARCHAR (20) NOT NULL
);
संचालित द्वारा

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
Done.





[] संचालित द्वारा

यहाँ %sql और %%sql के उपयोग पर ध्यान दें। एक पंक्ति की क्वेरी चलाने के लिए आप %sql का उपयोग कर सकते हैं, लेकिन यदि आप एक साथ कई क्वेरियाँ चलाना चाहते हैं, तो आपको %%sql का उपयोग करना होगा।

आइए कुछ रिकॉर्ड डालते हैं —

%%sql
INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Deep Learning in Python','Dan Becker','Python');
INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Joining Data in PostgreSQL','Chester Ismay','SQL');
संचालित द्वारा

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.
1 rows affected.





[] संचालित द्वारा

यह सुनिश्चित करने के लिए टेबल देखें कि insert अपेक्षित रूप से हुए —

%%sql
select * from datacamp_courses;
संचालित द्वारा

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
2 rows affected. संचालित द्वारा

क्रम बनाए रखें। अगले चरण में, आप टेबल में एक रिकॉर्ड अपडेट करेंगे —

%sql update datacamp_courses set course_name = 'Joining Data in SQL' where course_instructor = 'Chester Ismay';
संचालित द्वारा

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.





[] संचालित द्वारा

SQL में स्ट्रिंग्स के साथ काम करते समय विशेष ध्यान दें। पारंपरिक प्रोग्रामिंग भाषाओं के विपरीत, स्ट्रिंग मानों को single quotes में रखना होता है।

अब सत्यापित करें कि आपकी update क्वेरी का इच्छित प्रभाव पड़ा —

%%sql
select * from datacamp_courses;
संचालित द्वारा

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
2 rows affected. संचालित द्वारा

अब एक रिकॉर्ड डिलीट करें और जाँचें —

%%sql
delete from datacamp_courses where course_name = 'Deep Learning in Python';
संचालित द्वारा

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.





[] संचालित द्वारा

%%sql
select * from datacamp_courses;
संचालित द्वारा

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected. संचालित द्वारा

अब तक आप PostgreSQL में CRUD ऑपरेशन चलाने और उन्हें Jupyter Notebook के माध्यम से करने का स्पष्ट तरीका समझ चुके हैं। यदि आप Python से परिचित हैं और अपने Python कोड के माध्यम से डेटाबेस तक पहुँचने में रुचि रखते हैं, तो आप ऐसा भी कर सकते हैं। अगला भाग इसी के बारे में है।

SQLAlchemy से शुरुआत और इसे SQL मैजिक कमांड्स के साथ जोड़ना

इस भाग के लिए आपको SQLAlchemy पैकेज की आवश्यकता होगी। यह आमतौर पर Anaconda डिस्ट्रीब्यूशन के साथ आता है। आप इसे pip से भी इंस्टॉल कर सकते हैं। इंस्टॉल होने के बाद, आप इसे इस तरह इम्पोर्ट कर सकते हैं —

import sqlalchemy
संचालित द्वारा

SQLAlchemy का उपयोग करके अपने डेटाबेस के साथ इंटरैक्ट करने के लिए आपको उस RDBMS के लिए एक engine बनाना होगा जहाँ आपके डेटाबेस स्टोर हैं। आपके मामले में यह PostgreSQL है। SQLAlchemy आपको केवल create_engine() के एक कॉल में ही RDBMS का engine बनाने देता है, और यह विधि वही डेटाबेस कनेक्शन URL लेती है जिसे आप पहले देख चुके हैं।

from sqlalchemy import create_engine
संचालित द्वारा

engine = create_engine('postgresql://postgres:postgres@localhost:5432/DataCamp_Courses')
print(engine.table_names()) # Lets you see the names of the tables present in the database
संचालित द्वारा

['datacamp_courses']संचालित द्वारा

आप _datacampcourses नाम की टेबल देख सकते हैं, जो आगे पुष्टि करता है कि आप engine बनाने में सफल रहे। आइए टेबल _datacampcourses के रिकॉर्ड देखने के लिए एक सरल select क्वेरी चलाते हैं और इसे pandas DataFrame ऑब्जेक्ट में स्टोर करते हैं।

आप read_sql() मेथड (pandas द्वारा प्रदत्त) का उपयोग करेंगे, जो एक SQL क्वेरी स्ट्रिंग और एक engine लेता है।

import pandas as pd

df = pd.read_sql('select * from datacamp_courses', engine)
df.head()
संचालित द्वारा

आप अपने Python कोड के भीतर %sql मैजिक कमांड को भी जोड़ सकते हैं।

df_new = %sql select * from datacamp_courses
df_new.DataFrame().head()
संचालित द्वारा

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected. संचालित द्वारा

आप बहुत आगे आ चुके हैं!

बधाई हो और खुद की पीठ थपथपाइए! इस ट्यूटोरियल में आपने न केवल PostgreSQL के साथ शुरुआत की, बल्कि आपने यह भी देखा कि SQL क्वेरियाँ कई तरीकों से कैसे चलाई जा सकती हैं। अगला कदम है यह वेबिनार देखें जिसे David Robinson (DataCamp के मुख्य डेटा वैज्ञानिक) ने प्रस्तुत किया है, जहाँ वे दिखाते हैं कि वे अपने दैनिक कार्यों में SQL का उपयोग कैसे करते हैं। हमारा Windows और Mac OS X पर PostgreSQL कैसे इंस्टॉल करें ट्यूटोरियल भी देखें। यदि आप अपने SQL कौशल सुधारना चाहते हैं, तो निम्नलिखित शुरुआत के लिए बेहतरीन संसाधन हैं —

• Intro to SQL for Data Science
• Kaggle Learn द्वारा SQL
• Joining Data in SQL