Paano Magsanay ng SQL at Python Kasama ang mga Makatotohanang Problema

Tahanan » Python » Paano Magsanay ng SQL at Python nang Kasama ang mga Makatotohanang Problema

Kung sinusubukan mong bumalik sa SQL at Python pagkatapos ng ilang taon na pagkawala, normal lang na makaramdam ng pagkaligaw – lalo na kung ang iyong huling trabaho ay gumamit ng mga proprietary tool at komportableng Databricks notebook na wala ka na. Ang mga modernong posting ng trabaho na nangangailangan ng Python, SQL at maging ng PySpark ay maaaring magmukhang nakakatakot kapag ang bawat gabay ay nagsisimula sa isang bagay tulad ng "i-load ang iyong claims dataset sa iyong data warehouse" at iniisip mo: "Iyon mismo ang wala ako."

Ang magandang balita ay maaari mong muling likhain ang karamihan sa karanasan sa pagkatuto na iyon sa iyong sariling laptop gamit ang mga libreng tool, maliliit na sample dataset, at isang nakabalangkas na hanay ng mga praktikal na problema. Sa gabay na ito, tatalakayin natin, sa simpleng Ingles, kung paano bumuo ng isang makatotohanang lokal na kapaligiran, kung paano gumagana ang SQL (mula sa mga pangunahing query hanggang sa mga JOIN at pagsasama-sama), at kung paano ibalot ang mga SQL query na iyon sa Python upang masanay ka nang eksakto sa uri ng mga gawain na haharapin mo sa mga modernong trabaho sa data.

Pagbuo ng isang simpleng lokal na kapaligiran sa pagsasanay gamit ang SQLite at Python

Hindi mo kailangan ng isang kumpletong data warehouse o Spark cluster para magsanay ng SQL at Python . Para sa pag-aaral at paghahanda para sa panayam, sapat na ang isang magaan na naka-embed na database tulad ng SQLite. Iniimbak ng SQLite ang lahat ng data nito sa isang file sa disk, na ginagawa itong perpekto para sa mga proyekto ng laruan, prototype, at mga pagsasanay sa edukasyon.

Sa konsepto, ang isang SQLite database ay halos kapareho ng isang spreadsheet na may maraming sheet : ang bawat sheet ay isang table , ang bawat row ay isang record , at ang bawat column ay isang field . Sa jargon ng relational database, ang mga table ay minsang tinatawag na "relations", ang mga row ay "tuples", at ang mga column ay "attributes", ngunit para sa praktikal na gawain, maaari mong gamitin ang pang-araw-araw na terminong table, row, at column.

Ang Python ay may kasamang built-in na SQLite driver na tinatawag na sqlite3, na nangangahulugang hindi mo kailangang mag-install ng hiwalay na database server. Magbubukas ang iyong Python script ng koneksyon sa isang .sqlite file (paglikha nito kung wala ito), kumuha ng panturo object (halos katulad ng isang file handle), at pagkatapos ay magpadala ng mga SQL command sa pamamagitan ng cursor na iyon gamit ang execute(). Tingnan ang aming mga SQLite PUMILI at SAAN gabay para sa mga praktikal na halimbawa ng pagbabasa at pagsala ng datos.

Bagama't nakatuon ang artikulong ito sa pagpapatakbo ng SQLite mula sa Python, mayroon ding madaling gamiting GUI tool na tinatawag na "Database Browser for SQLite" (minsan ay ipinamamahagi bilang DB Browser for SQLite). Gamit ito, maaari mong biswal na siyasatin ang mga talahanayan, magpasok o mag-edit ng ilang row nang manu-mano, at magpatakbo ng mga simpleng SQL statement. Para itong text editor para sa mga database file: mas madali ang mabilisang manu-manong pag-aayos sa GUI, ngunit mas mainam na i-script ang anumang paulit-ulit o kumplikado sa Python.

Mas mahigpit ang mga relational database kaysa sa mga listahan o dict ng Python: iginigiit nila ang isang tinukoy na schema . Kapag lumikha ka ng isang talahanayan, dapat mong ideklara ang mga pangalan ng column at ang mga uri ng data na inaasahan mo (teksto, integer, petsa/oras, atbp.). Pagkatapos ay iimbak at ii-index ng SQLite ang data sa paraang nagpapanatili sa kahusayan ng mga lookup, kahit na lumalago ang iyong dataset nang higit sa kung ano ang komportableng akma sa memorya. Para sa mga praktikal na landas sa pag-aaral at mga praktikal na halimbawa, sumangguni sa pagsusuri ng data gamit ang SQL.

Paggawa ng mga talahanayan at paglalagay ng datos gamit ang SQL at Python

Para magsimulang magsanay, kailangan mo muna ng isang talahanayan – isipin ito bilang pagdidisenyo ng hugis ng iyong datos. Ipagpalagay na gusto mo ng maliit na mesa ng library ng musika. Gamit ang Python's sqlite3 module na maaari kang kumonekta sa isang database file, tanggalin ang anumang lumang bersyon ng talahanayan kung mayroon ito, at pagkatapos ay lumikha ng isang bagong talahanayan na may malinaw na na-type na mga column.

Ganito ang hitsura ng daloy na iyon sa konsepto sa Python: tumawag ka sqlite3.connect('music.sqlite') para buksan o likhain ang database file, pagkatapos ay tawagin ang conn.cursor() para makakuha ng cursor. Sa pamamagitan ng cursor na iyon, maaari mong patakbuhin ang mga SQL command tulad ng DROP TABLE IF EXISTS Songs upang linisin ang anumang naunang iskema, na susundan ng CREATE TABLE Songs (title TEXT, plays INTEGER) para magtakda ng bagong talahanayan na may dalawang kolum.

Kapag nailabas na ang talahanayan, lilipat ka mula sa DDL (Data Definition Language) patungong DML (Data Manipulation Language) gamit ang INSERT pahayagSa Python, dapat mong palaging gamitin ang mga parameterized query: write INSERT INTO Songs (title, plays) VALUES (?, ?) at magpasa ng tuple tulad ng ('Thunderstruck', 20) bilang pangalawang argumento sa execute()Ang mga tandang pananong ay mga placeholder na ligtas na papalitan ng Python, na tutulong sa iyong maiwasan ang mga isyu sa SQL injection at pagbanggit ng mga bug.

Pagkatapos magsagawa ng mga pagsingit o pag-update, dapat kang tumawag conn.commit() para i-flush ang iyong mga pagbabago sa diskHangga't hindi ka nagko-commit, ang mga operasyon ay nabubuhay lamang sa isang transaction buffer. Ito ay naiiba sa mga simpleng pagsusulat ng file, at ito ay isa sa mga pangunahing gawi na dapat buuin nang maaga: mag-query, mag-modify, pagkatapos ay mag-commit.

Para mabasa pabalik ang iyong datos, gagamit ka ng SELECT pahayag at ulitin sa ibabaw ng cursor. Halimbawa, SELECT title, plays FROM Songs i-stream ang bawat hilera bilang isang Python tuple, tulad ng ('Thunderstruck', 20)Hindi nilo-load ng cursor ang lahat ng resulta nang sabay-sabay; sa halip ay mabagal nitong kinukuha ang mga row, na kapaki-pakinabang kapag sa kalaunan ay humarap ka sa mas malalaking dataset.

Mga pangunahing elemento ng query sa SQL at pag-filter gamit ang WHERE

Ang bawat query sa SQL ay binuo sa isang maliit na hanay ng mga sugnay na lumilitaw sa isang karaniwang pagkakasunud-sunod.: SELECT, FROM, WHERE, GROUP BY, HAVING, at ORDER BY. Sa pinakamababa, tukuyin mo kung anong mga column ang gusto mo (SELECT) at mula sa aling talahanayan (FROM). Pagkatapos, pinipino, pinagsasama-sama, sinasala ng mga opsyonal na sugnay ang mga pinagsama-samang resulta, at inaayos ang output.

Ang WHERE sinasala ng sugnay ang mga hilera bago maganap ang anumang pagpapangkat o pagsasama-samaPara sa mga numeric column, maaari mong gamitin ang mga comparison operator tulad ng =, != (O <>), >, <, >=, <=Sinusuportahan ng mga hanay ng teksto ang mga ito kasama ang pagtutugma ng pattern sa pamamagitan ng LIKE at mga pagsusuri ng pagiging miyembro sa pamamagitan ng INSinusuportahan ng mga halaga ng petsa/oras ang parehong mga paghahambing na may kaugnayan, at madalas kang makakita ng mga saklaw na ipinapahayag gamit ang BETWEEN.

Ang null handling sa SQL ay sapat na kakaiba kaya nararapat itong bigyang-pansinMga regular na paghahambing tulad ng = at != huwag kumilos ayon sa inaasahan mo NULL, kaya nagbibigay ang SQL IS NULL at IS NOT NULL para tingnan ang mga nawawalang halaga. Karaniwang gumagana ang mga hanay ng Boolean sa = at !=, pero kailangan mo pa rin IS NULL kapag ang boolean mismo ay maaaring nawawala.

Kapag pinagsama mo ang maraming kundisyon, tandaan na AND at OR sundin ang mga tuntunin ng prayoridadKung magsusulat ka age < 5 OR age > 10 AND breed = 'Ragdoll', susuriin ng SQL ang AND una. Para ipahayag ang "Mga pusang Ragdoll na mas bata sa 5 o mas matanda sa 10", dapat mong gamitin ang panaklong: (age < 5 OR age > 10) AND breed = 'Ragdoll'Ang pagiging komportable sa mga lohikal na kumbinasyong ito ay mahalaga para sa gawaing analytics sa totoong buhay.

Pagtutugma ng pattern sa LIKE hinahayaan kang maghanap ng mga string na nagsisimula, nagtatapos o naglalaman ng ilang partikular na fragmentAng simbolo ng porsyento % ay isang wildcard para sa anumang pagkakasunod-sunod ng mga karakter, kaya breed LIKE 'R%' nakakahanap ng mga lahi na nagsisimula sa "R", fav_toy LIKE 'ball%' nakakahanap ng mga laruan na ang mga pangalan ay nagsisimula sa "bola", at coloration LIKE '%m' nakakahanap ng mga disenyo ng kulay na nagtatapos sa "m". Ipinares sa AND/OR, ito ay nagiging isang makapangyarihang toolkit sa pagsala ng teksto.

Pagsasanay sa mga single-table query gamit ang isang toy dataset

Ang isang kapaki-pakinabang na paraan upang mapalago ang memorya ng kalamnan ay ang pag-aayos ng isang maliit na eskema sa iyong ulo at lutasin ang maraming tanong laban dito.. Isipin mo a cat talahanayan na may mga kolum tulad ng id, name, breed, coloration, age, sex, at fav_toyNagbibigay ito sa iyo ng sapat na pagkakaiba-iba – teksto, numero, simpleng mga kategorya – upang maisagawa ang karamihan sa mga pangunahing pattern ng query.

Para sa mga boolean-style na pagsusuri, madalas kang nagfi-filter sa isang column at pagkatapos ay naglalagay ng karagdagang mga kondisyon sa layer.Para ilista ang mga "nakakabagot" na lalaking pusa na walang naitalang paboritong laruan, pipiliin mo ang name saan sex = 'M' at fav_toy IS NULLInilalarawan nito kung paano ipinapares ang mga null check sa mga direktang paghahambing upang ibukod ang isang partikular na subset ng mga row.

Para ma-target ang mga partikular na lahi o ibukod ang mga ito, pinagsasama mo ang pagkakapantay-pantay at lohikal na negasyon.Pagpili lamang ng mga pusang Ragdoll na may ilang partikular na edad breed = 'Ragdoll'; kung hindi kasama ang mga Persiano at Siamese ay maaaring magmukhang ganito breed NOT LIKE 'Persian' AND breed NOT LIKE 'Siamese'Bagama't sinusuportahan ng ilang database ang NOT IN ('Persian', 'Siamese'), ang pagsasanay sa tahasang padron ay nakakatulong na mapatibay ang iyong pag-unawa sa NOT at LIKE.

Ang mga pagsasanay tulad ng "mga babaeng pusa na mahilig sa mga laruang pang-aasar at hindi Persian o Siamese" ay pinipilit kang maghalo ng mga filter ng teksto, pagkakapantay-pantay at mga lohikal na operatorPipiliin mo id, name, breed, coloration at limitahan ang mga hilera gamit ang sex = 'F', fav_toy = 'teaser', at isang pinagsamang kondisyon na nagbubukod sa mga hindi gustong lahi. Ang pagbibigay-pansin sa mga panaklong ay tinitiyak na ang lahat ng mga subkondisyon ay nailalapat sa nilalayong kumbinasyon.

Kapag komportable ka na sa mga halimbawang ito ng laruang nasa raw SQL, muling ipatupad ang mga ito sa pamamagitan ng Python gamit ang mga parameterized query.Sumulat ng maiikling iskrip na humihingi ng lahi, minimum na edad o uri ng laruan mula sa input(), isaksak ang mga ito sa WHERE mga sugnay, at i-print ang mga resulta. Ito mismo ang tulay sa pagitan ng pagsulat ng query at totoong application code na inaasahan ng maraming junior data roles.

Pag-unawa at pagsasanay sa mga SQL JOIN

Sa sandaling lumampas ka na sa mga problema sa laruan, palagi kang magsasama-sama ng maraming talahanayan . Ang mga JOIN ay kung paano mo ikinokonekta ang mga kaugnay na dataset: mga customer sa mga order, mga artist sa mga likhang sining, mga laro sa mga kumpanya, at iba pa. Sa SQL, inilalarawan mo kung aling mga column ang dapat tumugma sa pagitan ng mga talahanayan, at pinagsasama ng database engine ang mga row sa isang pinagsamang set ng resulta.

May apat na pangunahing uri ng pagsali na makikilala mo sa mga panayam at totoong proyekto: INNER JOIN (madalas isinusulat lamang JOIN), LEFT JOIN, RIGHT JOIN, at FULL OUTER JOINAng isang inner join ay nagbabalik lamang ng mga row kung saan ang parehong table ay may magkatugmang key; ang isang left join ay nagpapanatili sa lahat ng row mula sa kaliwang table, na pinupunan ang NULLs kapag ang kanang talahanayan ay walang tugma; ang kanang join ay gumagawa ng simetrikong bagay; at ang buong panlabas na join ay nagbabalik ng bawat hilera mula sa magkabilang panig, na tumutugma kung saan posible at ginagamit NULL kung saan hindi.

Isipin LEFT JOIN at RIGHT JOIN bilang mga operasyong "mas magtiwala sa panig na ito"Sa pamamagitan ng left join, ang kaliwang talahanayan ang pangunahing pinagmumulan ng katotohanan: bawat hilera mula rito ay lilitaw nang kahit isang beses sa output, kahit na walang naiaambag ang kanang talahanayan. Sa isang full join, walang pribilehiyo ang alinmang panig – pagsasamahin mo lang ang lahat ng key mula sa parehong talahanayan at ihanay ang mga ito kung saan sila nagsasapawan.

Para mapanatiling nababasa ang mga query sa maraming talahanayan, palaging gamitin ang alias sa iyong mga talahanayanSa halip na magsulat SELECT artist.name paulit-ulit, isulat FROM artist AS a at pagkatapos ay i-reference ang mga column bilang a.name. Katulad nito, piece_of_art maaaring maging poa, at museum ay maaaring maging mKapag ang iyong query ay lumaki sa tatlo o higit pang mga pagsali, ang magagandang alias ang siyang magiging pagkakaiba sa pagitan ng kalinawan at kaguluhan.

Ang isang klasikong setup ng pagsasanay ay gumagamit ng tatlong mesa: artist, museum, at piece_of_art. ang artist maaaring magkasya ang mesa id, name, birth_year, death_year at isang pangunahing larangan tulad ng watercolor o iskultura. Ang museum mga tindahan ng mesa id, name at country. ang piece_of_art mga hawakan ng mesa id, name, artist_id at museum_idAng huling dalawang kolum na iyon ay mga foreign key na nag-uugnay sa bawat likhang sining sa lumikha at lokasyon nito.

Gamit ang schema na iyon, maaari kang magsanay ng mga inner join, left join at conditional filter.Halimbawa, para ilista ang mga artistang ipinanganak pagkatapos ng 1800 na nabuhay nang mahigit 50 taon, kasama ang mga pangalan ng kanilang mga gawa, isasama mo ang artist at piece_of_art on artist.id = piece_of_art.artist_id at pagkatapos ay i-filter gamit ang death_year - birth_year > 50 at birth_year > 1800. Alyas ang mga napiling kolum bilang artist_name at piece_name para sa kaliwanagan.

Para makita ang lahat ng likhang sining kasama ang mga pangalan at bansa ng museo – kabilang ang mga "nawawalang" piraso na walang museo – gagamit ka ng LEFT JOIN mula piece_of_art sa museum on museum_idSa ganoong paraan, ang mga likhang sining na walang kaugnay na museo ay lilitaw pa rin sa resulta, na may NULL sa mga kolum ng museo. Pagsala ng mga hanay kung saan artist_id IS NULL hinahayaan kang matukoy ang mga gawa ng mga hindi kilalang artista habang sumasali pa rin sa mga museong naglalaman ng mga ito.

Ang mas advanced na mga ehersisyo ay magpapasali sa iyo sa tatlong mesa nang sabay-sabayPara ilista ang bawat likhang sining kasama ang pangalan ng artista at museo nito, sasali ka sa museum sa piece_of_art on museum.id = piece_of_art.museum_id, pagkatapos ay sumali artist on artist.id = piece_of_art.artist_idPaggamit ng payak JOIN Sinasadyang tanggalin ng (inner join) ang mga likhang sining na walang artista o museo, na nagbibigay sa iyo ng ideya kung paano nakakaapekto ang uri ng pagsali sa bilang ng hilera.

Pagsasagawa ng pagsasama-sama, GROUP BY, at HAVING

Kapag kaya mo nang kunin at pagsamahin ang datos, ang susunod na malaking kasanayan ay ang pagbubuod nito.Ang mga tungkulin ng pagsasama-sama ay tulad ng SUM(), AVG(), COUNT(), MAX(), at MIN() kalkulahin ang mga sukatan sa mga hanay ng mga hilera. GROUP BY hinahati ang iyong dataset sa mga grupo at inilalapat ang mga function na iyon sa loob ng bawat grupo – halimbawa, isang grupo bawat taon, bawat kumpanya, o bawat artist. Kung mas gusto mo ang mga nakabalangkas na kurso para sa pagsasanay ng mga konseptong ito, tingnan ang isang komprehensibong kurso sa SQL.

Isipin ang isang simpleng sales_table may mga kolum year, month, at salesIsang kapatagan SELECT SUM(sales) AS total_sales FROM sales_table nagbibigay sa iyo ng kabuuang kabuuan sa lahat ng mga hilera. Pagdaragdag GROUP BY year binabago ang tanong: ngayon ay humihingi ka ng kabuuang benta bawat taon sa halip na isang kabuuang numero lamang.

Ang pangunahing tuntunin ay ang bawat hindi pinagsama-samang kolum sa iyong SELECT dapat lumitaw sa GROUP BY. Kung pipiliin mo year at SUM(sales), pangkatin mo ayon sa year. Kung pipiliin mo year at month kasama ang mga pinagsama-samang bagay, pagkatapos ay igrupo mo ayon sa pareho year at monthSa konseptwal na paraan, ang magkakaibang kombinasyon ng mga nakapangkat na kolum ang tumutukoy sa mga grupo.

WHERE at HAVING parehong mga filter, ngunit gumagana ang mga ito sa magkaibang yugto. WHERE Sinasala ang mga hilaw na hilera bago mangyari ang anumang pagpapangkat o pagsasama-sama. HAVING sinasala ang mga pinagsama-samang resulta gamit ang mga pinagsama-samang ekspresyon. Halimbawa, maaari mong WHERE production_year BETWEEN 2000 AND 2009 at pagkatapos ay HAVING SUM(revenue) > 4000000 na panatilihin lamang ang mga kumpanyang ang "magagandang laro" ay nakalikha ng mahigit apat na milyon na kita.

Ang isang mas makatotohanang iskema ng pagsasanay ay isang games mesa na may mga column tulad ng id, title, company, type, production_year, system, production_cost, revenue, at ratingGamit ang nag-iisang talahanayan na ito, maaari mong gamitin ang mga average, count, sums, grouping at ranking – ang pundasyon ng analytics SQL.

Halimbawa, upang kalkulahin ang average na gastos sa produksyon ng mga larong inilabas mula 2010 hanggang 2015 na may rating na higit sa 7, pipiliin mo AVG(production_cost) at limitahan ang mga hilera gamit ang WHERE production_year BETWEEN 2010 AND 2015 AND rating > 7Iyan ay isang klasikong tanong na istilo ng panayam, at madali mo itong mai-embed sa Python at mai-print ang resultang isang numero.

Maaari ka ring gumawa ng mga istatistika sa antas ng taon nang direkta mula sa parehong games mesa. Ipangkat ayon sa production_year, pagkatapos ay kalkulahin COUNT(*) AS count, AVG(production_cost) AS avg_cost, at AVG(revenue) AS avg_revenueAng ganitong uri ng query ay nagbibigay sa iyo ng isang compact time series view na lubhang karaniwan sa mga BI dashboard at reporting tool.

Para i-ranggo ang mga kumpanya ayon sa kabuuang kita sa lahat ng taon, maaari mong pagsama-samahin ang companyAng isang madaling gamiting padron ay SELECT company, SUM(revenue - production_cost) AS gross_profit_sum FROM games GROUP BY 1 ORDER BY 2 DESC. Dito GROUP BY 1 at ORDER BY 2 gamitin ang mga posisyon ng kolum sa SELECT list, na maaaring magpanatili ng maigsi ngunit dapat gamitin nang maingat upang hindi mo masira ang mga query sa pamamagitan ng muling pagsasaayos ng mga column sa ibang pagkakataon.

Pinagsasama-sama ng mas kumplikadong mga prompt ang mga filter, grouping, at post-aggregation filterIpagpalagay na binibigyang-kahulugan mo ang "magagandang laro" bilang mga ginawa sa pagitan ng 2000 at 2009, na may rating na higit sa 6 at kita na mas malaki kaysa sa gastos sa produksyon. Para sa bawat kumpanya, gusto mo ang bilang ng mga naturang laro kasama ang kanilang kabuuang kita, ngunit para lamang sa mga kumpanyang ang kita mula sa magagandang laro ay lumampas sa 4,000,000. Ifi-filter mo ang mga hilera gamit ang WHERE on production_year, rating, at kakayahang kumita, pangkatin ayon sa company, magcompute COUNT(company) at SUM(revenue), pagkatapos ay mag-apply HAVING SUM(revenue) > 4000000Nakukuha ng isang query na ito ang karamihan sa mga hakbang sa pag-iisip sa totoong mundo na iyong haharapin sa mga gawain sa analytics.

Pagmomodelo ng datos gamit ang maraming talahanayan at susi

Malaki ang maitutulong ng mga single-table design, ngunit mas maganda ang mga relational database kapag ni-normalize mo ang data sa maraming table . Ang normalization ay ang proseso ng pag-aalis ng paulit-ulit na storage at pagrepresenta ng mga relasyon gamit ang mga key. Pinapanatili nitong mas maliit, mas mabilis, at hindi gaanong madaling magkamali ang iyong database.

Isang simple ngunit nakapagtuturong halimbawa ang nagmumula sa pag-crawl ng mga social graph na parang Twitter . Sabihin nating gusto mong subaybayan ang mga user account at ang mga relasyon ng "mga tagasunod" sa pagitan ng mga ito. Ang isang simpleng paraan ay ang isang talahanayan kung saan ang bawat row ay nagdodoble ng parehong pangalan ng tagasunod at sinusundan bilang teksto. Mabilis itong humahantong sa matinding pag-uulit at hindi pare-parehong pagbaybay.

Sa halip, hinahati mo ang mga bagay sa isang People mesa at isang Follows mesa. People maaaring may integer id bilang pangunahing susi, isang natatanging name (ang screen name o handle), at isang retrieved bandila na nagpapahiwatig kung na-crawl mo na ang listahan ng mga kaibigan ng account na iyon. Follows naglalaman ng mga pares ng integer from_id at to_id, na kumakatawan sa mga direktang koneksyon mula sa isang gumagamit patungo sa isa pa.

Tatlong pangunahing konsepto ang bumubuo sa modelong ito: mga logical key, primary key, at foreign keyAng lohikal na susi ay ang ginagamit ng labas ng mundo upang tumukoy sa isang rekord – dito, ang Twitter handle sa nameAng pangunahing susi ay karaniwang isang integer na nabuo sa database (id) na natatanging tumutukoy sa bawat hilera at mura i-index at ikumpara. Ang foreign key ay isang integer na nakaturo sa isang primary key sa ibang talahanayan – from_id at to_id nasa Follows ang talahanayan ay mga foreign key na tumutukoy People.id.

Para maipatupad ang kalidad ng datos, magdedeklara ka ng mga limitasyon sa mga kahulugan ng iyong talahanayan. Halimbawa, name TEXT UNIQUE in People tinitiyak na hindi mo aksidenteng mailalagay ang dalawang hanay gamit ang parehong hawakan. A UNIQUE(from_id, to_id) limitasyon sa Follows Pinipigilan ka nitong mag-imbak ng parehong follow edge nang higit sa isang beses. Ang mga limitasyong ito ay nagsisilbing mga lambat pangkaligtasan kapag nagsimula kang magsulat ng upsert logic sa Python.

Sa Python sqlite3 modyul, isang karaniwang padron ang paggamit INSERT OR IGNORE igalang nang may kagandahang-loob ang mga paghihigpit na iyonKung susubukan mong magpasok ng name na umiiral na, tahimik na lalaktawan ng SQLite ang operasyon sa halip na mag-error out. Pagkatapos ay maaari mong suriin cursor.rowcount para makita kung ang isang hilera ay tunay na naidagdag, at umasa sa cursor.lastrowid upang matuklasan ang nakatalagang id para sa mga bagong dating na user.

Kapag nakatanggap ang iyong code ng bagong screen name, dapat muna nitong subukang hanapin ang katumbas nito id. Kung ang SELECT id FROM People WHERE name = ? nagbabalik ng isang hilera, gagamitin mo ulit ang integer na iyon. Kung hindi, ilalagay mo ang pangalan gamit ang retrieved = 0, i-commit, at pagkatapos ay basahin lastrowidAng pattern na "hanapin o insert" ang siyang puso ng maraming script sa pagkuha ng data.

Kapag alam na ang parehong ID ng tagasunod at tagasunod, itinatala ang relasyon sa Follows ay isa pa lang INSERT OR IGNORE. Iyong UNIQUE(from_id, to_id) Tinutugunan ng constraint ang mga duplicate, at maaari kang magtuon sa mas mataas na antas ng lohika kung aling mga profile ang susunod na iko-crawl, sa halip na pamahalaan nang kaunti ang row deduplication.

Paggamit ng JOIN upang muling buuin ang mga relasyon mula sa mga normalized na talahanayan

Ang mga normalized na schema ay nagpapalit ng redundancy para sa indirection: nag-iimbak ka ng mga integer sa halip na mga paulit-ulit na string, ngunit ngayon ay kailangan mong pagdugtungin ang mga talahanayan upang muling buuin ang buong larawan.Ito mismo ang SQL JOIN ay dinisenyo para sa, at kapag nasanay ka na, ang mga query na maraming JOIN ay magiging natural lang sa pakiramdam.

Sa halimbawa ng social-graph, kung gusto mong makita kung sino ang gumagamit ng id = 2 ay sumusunod, sasali ka Follows sa People sa panig ng target. Sa konsepto, tatakbo ka SELECT * FROM Follows JOIN People ON Follows.to_id = People.id WHERE Follows.from_id = 2Gumagawa ito ng pinagsamang mga hanay na naglalaman ng parehong numerical edge at ng pangalang nababasa ng tao para sa bawat followee.

Ang bawat hilera sa resultang iyon ay isang "meta-row" na nagsasama ng mga hanay mula sa parehong talahanayanAng unang dalawang kolum ay maaaring (from_id, to_id) mula Follows, habang ang mga kasunod na kolum ay kabilang sa People - gaya ng (id, name, retrieved). Dahil ang JOIN nagpapatupad ng kondisyon Follows.to_id = People.id, makikita mo nang malinaw ang ugnayang iyon: magkatugma ang pangalawang column at ang ikatlong column ng bawat row.

Ang parehong pattern na ito ay natural na umaabot sa mas maraming mga talahanayanNakita mo na ito gamit ang artist, piece_of_art, at museum, at inilalarawan ito ng Twitter crawler gamit ang People at FollowsSa mas kumplikadong mga analytical pipeline, maaari mong pagdugtungin ang mga fact table (mga kaganapan, order) sa mga multiple dimension table (mga user, produkto, kampanya) upang masagot ang mga tanong na may maraming aspeto.

Kapag nagde-debug ng iyong code o natututo kung paano magkakaugnay ang schema, ang isang daloy ng trabaho na "patakbuhin ang Python, pagkatapos ay suriin gamit ang DB Browser para sa SQLite" ay lubos na epektibo.Isagawa ang iyong script upang punan ang database, isara ang anumang GUI instance na nagpapanatili sa file na naka-lock, pagkatapos ay buksan ang .sqlite file sa browser. Mula doon ay maaari mong siyasatin ang mga nilalaman ng bawat talahanayan at patakbuhin ang ad-hoc SELECT mga tanong upang mapatunayan ang iyong mga palagay.

Isang paalala: Ipinapatupad ng SQLite ang mga file lock, kaya kung nakabukas ang database sa DB Browser sa edit mode, maaaring hindi makakonekta o makapag-commit ang iyong Python script . Ang solusyon ay isara ang database sa GUI (o tuluyang lumabas sa browser) bago muling patakbuhin ang iyong Python code. Ang ugaliing isara ang mga tool na nagla-lock sa iyong DB file ay makakapagligtas sa iyo mula sa mahiwagang mga error na "naka-lock ang database".

Ang pagsasama-sama ng mga pamamaraang ito – disenyo ng schema, mga constraint, mga parameterized query sa Python, JOIN, GROUP BY at HAVING – ay magbibigay sa iyo ng isang makapangyarihang lokal na lab para sa pagsasanay ng eksaktong uri ng SQL at Python na gagawin mo sa trabaho. Gamit lamang ang SQLite at ilang mahusay na istrukturang sample table, maaari kang magsanay ng mga tanong na istilo ng panayam, gumawa ng prototype ng analytic logic, at mabawi ang iyong kumpiyansa gamit ang modernong data tooling.

Kung saan nababagay ang mga platform tulad ng DataLemur at mga interactive na kurso

Bukod sa iyong lokal na kasanayan, ang mga interactive na platform ay maaaring magbigay sa iyo ng mas may gabay na karanasan na may agarang feedback . Ang mga tool na nagmula sa totoong karanasan sa industriya – halimbawa, ang mga platform na nilikha ng mga dating data engineer ng Facebook at Google na gumugol ng kanilang mga araw sa pagsusulat ng SQL at Python at pagpapatakbo ng mga A/B test – ay kadalasang nakasentro sa kanilang nilalaman sa mga tunay na tanong sa panayam at mga senaryo ng analytics.

Ang mga aklat na tumatalakay sa istatistika, machine learning, at business intuition para sa mga panayam sa datos ay mainam para sa teorya , ngunit hindi nila palaging naibibigay ang praktikal na SQL playground na hinahangad ng maraming mag-aaral. Ang kakulangang iyon ang eksaktong layunin ng ilang modernong tool na punan: nire-repackage nila ang daan-daang prompt na parang panayam sa isang in-browser SQL at analytics environment para mapatakbo, ma-tweak, at mapatakbo muli ang iyong mga query nang hindi nababahala tungkol sa lokal na pag-setup. Maaari mo ring subukan ang mga inilapat na halimbawa tulad ng customer churn risk evaluation upang pagsamahin ang SQL sa mga pangunahing workflow ng machine learning.

Makakakita ka rin ng mga interactive na kurso sa SQL na sumasalamin sa mga paksang tinalakay natin dito.: mga query na may iisang talahanayan na may SELECT at WHERE, sumasama sa dalawa o tatlong talahanayan, pagsasama-sama at pagpapangkat, mga subquery, at marami pang iba. Marami sa mga kursong ito ay umaasa sa mga makatotohanang dataset – halimbawa, mga laro, museo, o mga transaksyonal na benta – kaya ang mga tanong ay parang mga tunay na problema sa negosyo sa halip na mga gawa-gawang palaisipan.

Kung pakiramdam mo ay nabibigatan ka sa dokumentasyon para sa mga tool tulad ng PySpark, DuckDB o dbt, makatuwiran lamang na ipagpaliban ang mga ito hanggang sa maging maayos na ang iyong mga pangunahing kaalaman sa SQL . Ang pagtutuon muna sa SQLite at Python ay nagbibigay-daan sa iyong maisabuhay ang mga pangunahing pattern ng query nang hindi nakikipaglaban sa cluster configuration o mga pahintulot sa cloud. Kapag ang mga pangunahing kaalaman ay likas na sa iyo, ang pag-aaral ng PySpark ay nagiging higit na tungkol sa distributed execution kaysa sa mga bagong konsepto ng query.

Sa huli, ang kombinasyon ng simpleng lokal na pag-setup, nakabalangkas na mga problema sa pagsasanay, at paminsan-minsang paggamit ng mga interactive na platform ay magbibigay sa iyo ng pinakamahusay sa lahat ng mundo: ganap na kontrol sa iyong kapaligiran, matibay na konseptwal na batayan, at pagkakalantad sa istilo ng mga tanong na gustung-gusto ng mga nangungunang employer. Sa pamamagitan ng patuloy na pagsasanay, ang dating nakakatakot na halo ng SQL, Python at mga tool sa data engineering ay nagiging isang pamilyar, kahit na kasiya-siyang, toolkit na maaari mong gamitin nang may kumpiyansa sa mga bagong tungkulin.

Kung pagsasama-samahin ang lahat, malinaw ang iyong landas: lumikha ng isang SQLite database gamit ang Python, magdisenyo ng ilang makatotohanang talahanayan, magsanay ng mga basic at intermediate na SQL pattern (mga filter, join, aggregation, grouping, HAVING), balutin ang mga query na iyon sa mga Python script, at opsyonal na dagdagan ang iyong pagkatuto gamit ang mga interactive SQL platform na binuo ng mga practitioner na nasa eksaktong kinalalagyan mo ngayon ; sa paggawa nito, mabubuo mo muli ang iyong mga teknikal na instinct, mababawasan ang pagkabalisa tungkol sa mga modernong data stack, at magiging handa na harapin ang mga pangangailangan ng SQL at Python ng mga data roles ngayon.