Indlela Yokuzijwayeza I-SQL ne-Python Kanye Nezinkinga Ezingokoqobo

Ikhaya » Python » Indlela Yokuzijwayeza I-SQL ne-Python Kanye Nezinkinga Ezingokoqobo

Uma uzama ukubuyela ku-SQL naku-Python ngemuva kweminyaka embalwa ungekho, kuyinto evamile ukuzizwa ulahlekile – ikakhulukazi uma indima yakho yokugcina isebenzise amathuluzi akhethekile kanye nama-notebook e-Databricks akhululekile ongasawatholi. Izikhala zemisebenzi zanamuhla ezifuna i-Python, i-SQL ngisho ne-PySpark zingabonakala zithusa lapho yonke inkomba iqala ngokuthile okufana nokuthi “layisha isethi yedatha yezimangalo zakho endaweni yakho yokugcina idatha” bese ucabanga ukuthi: “Yilokho kanye engingenakho.”

Izindaba ezinhle ukuthi ungaphinda wenze kabusha iningi lalolo lwazi lokufunda kukhompyutha yakho ephathekayo sisebenzisa amathuluzi wamahhala, amasethi edatha amancane esampula, kanye nesethi ehlelekile yezinkinga zokuzijwayeza. Kulesi siqondisi sizochaza, ngesiNgisi esilula, ukuthi ungakha kanjani indawo engokoqobo yendawo, ukuthi i-SQL isebenza kanjani (kusukela emibuzweni eyisisekelo kuya kuma-JOIN kanye nokuhlanganiswa), kanye nendlela yokusonga leyo mibuzo ye-SQL ku-Python ukuze ukwazi ukuzijwayeza uhlobo lwemisebenzi ozobhekana nayo emisebenzini yedatha yanamuhla.

Ukwakha indawo elula yokusebenza yendawo nge-SQLite ne-Python

Awudingi indawo yokugcina idatha egcwele noma i-Spark cluster ukuze uzijwayeze i-SQL kanye ne-Python. Ukuze ufunde futhi ulungiselele ingxoxo, isizindalwazi esilula esifakiwe njenge-SQLite sanele. I-SQLite igcina yonke idatha yayo kufayela elilodwa kudiski, okwenza ifaneleke kakhulu kumaphrojekthi amathoyizi, ama-prototypes kanye nokuzivocavoca kwezemfundo.

Ngomqondo, isizindalwazi se-SQLite sibukeka kakhulu njengespredishithi esinamaphepha amaningi: ishidi ngalinye liyi ithebula, umugqa ngamunye u- irekhodi, futhi ikholomu ngayinye iyi- fieldKu-jargon yedathabheyisi yobudlelwano, amathebula ngezinye izikhathi abizwa ngokuthi “ubudlelwano”, imigqa “ingama-tuple”, kanti amakholomu “ayizimfanelo”, kodwa uma usebenza kanzima unganamathela ngenjabulo kumagama ansuku zonke ethebula, umugqa kanye nekholomu.

I-Python ithunyelwa nge-driver ye-SQLite eyakhelwe ngaphakathi ebizwa ngokuthi i-sqlite3, okusho ukuthi awudingi ukufaka iseva yedatha ehlukile. Isikripthi sakho se-Python sizovula uxhumano ku- .sqlite ifayela (ukulidala uma lingekho), thola isikhombisi into (efana kakhulu nesiphathi sefayela), bese uthumela imiyalo ye-SQL ngaleyo khesa usebenzisa execute(). Bona wethu I-SQLite SELECT kanye ne-WHERE umhlahlandlela wezibonelo ezisebenzayo zokufunda nokuhlunga idatha.

Nakuba lesi sihloko sigxile ekushayeleni i-SQLite kusuka ku-Python, kukhona futhi ithuluzi le-GUI eliwusizo elibizwa ngokuthi “Isiphequluli Sedatha se-SQLite” (ngezinye izikhathi isatshalaliswa njenge-DB Browser ye-SQLite). Ngayo ungahlola amathebula ngokubona, ufake noma uhlele imigqa embalwa ngesandla, bese usebenzisa izitatimende ze-SQL ezilula. Kufana nomhleli wombhalo wamafayela edathabheyisi: ukulungisa okusheshayo ngesandla kulula ku-GUI, kodwa noma yini ephindaphindwayo noma eyinkimbinkimbi ibhalwa kangcono ku-Python.

Ama-database obudlelwano aqinile kakhulu kunezinhlu ze-Python noma ama-dicts: aphikelela ku-schema echaziweUma udala ithebula kufanele umemezele amagama ekholomu kanye nezinhlobo zedatha ozilindele (umbhalo, inombolo ephelele, usuku/isikhathi, njll.). I-SQLite izobe isigcina futhi ifake ku-index idatha ngendlela egcina ukubheka kusebenza kahle, noma ngabe isethi yakho yedatha ikhula ngaphezu kwalokho okuhambisana kahle nenkumbulo. Ukuze uthole izindlela zokufunda ezisebenzayo kanye nezibonelo ezisebenzayo, thintana idatha yedatha ku-SQL.

Ukudala amathebula nokufaka idatha nge-SQL ne-Python

Ukuze uqale ukuzijwayeza, udinga ithebula kuqala - cabanga ngalo njengokuklama isimo sedatha yakhoAke sithi ufuna itafula elincane lomtapo wolwazi womculo. Usebenzisa i-Python's sqlite3 Uma usebenzisa imojuli ongayixhuma kufayela ledathabheyisi, ulahle noma iyiphi inguqulo endala yetafula uma ikhona, bese udala ithebula elisha elinamakholomu abhalwe ngokucacile.

Nakhu ukuthi lokho kugeleza kubukeka kanjani ngokomqondo ku-Python: uyafona sqlite3.connect('music.sqlite') ukuvula noma ukudala ifayela ledathabheyisi, bese ushayela conn.cursor() ukuthola ikhesa. Ngaleso khesa ungasebenzisa imiyalo ye-SQL efana DROP TABLE IF EXISTS Songs ukusula noma yisiphi isimiso sangaphambilini, kulandelwe yi CREATE TABLE Songs (title TEXT, plays INTEGER) ukuchaza ithebula elisha elinamakholomu amabili.

Uma ithebula selikhona, ushintsha usuka ku-DDL (Ulimi Lokuchaza Idatha) uye ku-DML (Ulimi Lokukhohlisa Idatha) usebenzisa INSERT izitatimendeKu-Python kufanele usebenzise njalo imibuzo ebekwe ngamapharamitha: bhala INSERT INTO Songs (title, plays) VALUES (?, ?) bese udlula i-tuple efana ('Thunderstruck', 20) njengempikiswano yesibili ku- execute()Amamaki emibuzo ayizibambi-ndawo ezizoshintshwa yi-Python ngokuphephile, okukusiza ugweme izinkinga zokujova kwe-SQL kanye nokucaphuna amaphutha.

Ngemva kokwenza ukufakwa noma izibuyekezo, kufanele ushayele ucingo conn.commit() ukuze ushintshe izinguquko zakho kudiski. Kuze kube yilapho uzibophezele, imisebenzi ihlala kuphela ku-transaction buffer. Lokhu kuhlukile ekubhaleni okulula kwefayela, futhi kungenye yemikhuba ebalulekile yokwakha kusenesikhathi: ukubuza, ukuguqula, bese uvuma.

Ukuze ufunde idatha yakho emuva, usebenzisa i- SELECT isitatimende bese siphindaphinda phezu kwesikhombisi. Ngokwesibonelo, SELECT title, plays FROM Songs izosakaza umugqa ngamunye njenge-Python Tuple, njenge ('Thunderstruck', 20)Isikhombisi asilayishi yonke imiphumela ngesikhathi esisodwa; kunalokho silanda imigqa ngobuvila, okuwusizo uma ekugcineni ubhekana namasethi edatha amakhulu.

Izinto eziyinhloko zombuzo we-SQL kanye nokuhlunga nge-WHERE

Yonke imibuzo ye-SQL yakhelwe phezu kwesethi encane yemisho evela ngokulandelana okujwayelekile: SELECT, FROM, WHERE, GROUP BY, HAVING, Futhi ORDER BYOkungenani ucacisa ukuthi yimaphi amakholomu owafunayo (SELECT) futhi kuliphi itafula (FROM). Izigaba ozikhethela zona bese zicwengisisa, zihlanganise, zihlunge imiphumela ehlanganisiwe, bese zihlela umphumela.

The WHERE isigaba sihlunga imigqa ngaphambi kokuba noma yikuphi ukuhlanganiswa noma ukuhlanganiswa kwenzekeKumakholomu ezinombolo ungasebenzisa opharetha bokuqhathanisa abanjengo =, != (noma <>), >, <, >=, <=Amakholomu ombhalo asekela lokhu kufanisa amaphethini kanye ne-plus ngokusebenzisa LIKE kanye nokuhlolwa kobulungu nge INAmanani osuku/isikhathi asekela ukuqhathanisa okufanayo kobudlelwano, futhi uvame ukubona amabanga avezwa nge BETWEEN.

Ukuphathwa kwe-Null ku-SQL kuyinto engavamile kangangokuthi kufanele inakekelwe ngokucacile. Ukuqhathaniswa okuvamile okufana = futhi != ungaziphathi ngendlela obungalindela ngayo NULL, ngakho-ke i-SQL inikeza IS NULL futhi IS NOT NULL ukuhlola amanani angekho. Amakholomu e-Boolean avame ukusebenza nawo = futhi !=, kodwa usadinga IS NULL lapho i-boolean ngokwayo ingase ingabi khona.

Uma uhlanganisa izimo eziningi, khumbula lokho AND futhi OR landela imithetho yokulandelwaUma ubhala age < 5 OR age > 10 AND breed = 'Ragdoll', i-SQL izohlola AND kuqala. Ukuze uveze “amakati e-Ragdoll angaphansi kweminyaka emi-5 noma ngaphezulu kwengu-10”, kufanele usebenzise ama-parentheses: (age < 5 OR age > 10) AND breed = 'Ragdoll'Ukuzivumelanisa nezinhlanganisela ezinengqondo kubalulekile emsebenzini wokuhlaziya wangempela.

Ukufanisa amaphethini no LIKE ikuvumela ukuthi useshe izintambo eziqala, ziqede noma ziqukethe izingcezu ezithileUphawu lwephesenti % iyikhadi lasendle lanoma yiluphi uchungechunge lwezinhlamvu, ngakho-ke breed LIKE 'R%' ithola izinhlobo eziqala ngo-“R”, fav_toy LIKE 'ball%' uthola amathoyizi anamagama aqala ngokuthi “ibhola”, futhi coloration LIKE '%m' ithola amaphethini emibala aphela ngo-“m”. Kuhlanganiswe no AND/OR, lokhu kuba ithuluzi lokuhlunga umbhalo elinamandla.

Ukuzijwayeza imibuzo yetafula elilodwa ngesethi yedatha yamathoyizi

Indlela ewusizo yokwakha inkumbulo yemisipha ukulungisa i-schema encane ekhanda lakho nokuxazulula imibuzo eminingi ephikisana nayo. Cabanga a cat ithebula elinamakholomu afana id, name, breed, coloration, age, sex, Futhi fav_toyLokhu kukunika ukuhlukahluka okwanele - umbhalo, izinombolo, izigaba ezilula - ukuze uzijwayeze amaphethini amaningi emibuzo ayisisekelo.

Uma uhlola isitayela se-boolean, uvame ukuhlunga kukholomu eyodwa bese ubeka izimo ezengeziwe ezingqimbeniUkuze ubhale amakati amaduna “ayisicefe” angenalo ithoyizi alithandayo, ungakhetha name lapho sex = 'M' futhi fav_toy IS NULLLokhu kubonisa indlela ama-null checks ahambisana ngayo nokuqhathanisa okuqondile ukuze kuhlukaniswe isethi ethile yemigqa.

Ukuze uhlose izinhlobo ezithile noma uzikhiphe, uhlanganisa ukulingana nokuphika okunengqondoUkukhetha amakati e-Ragdoll kuphela anezisetshenziswa ezithile zobudala breed = 'Ragdoll'; ngaphandle kwamaPheresiya namaSiamese kungabukeka sengathi breed NOT LIKE 'Persian' AND breed NOT LIKE 'Siamese'Nakuba ezinye izizindalwazi zisekela NOT IN ('Persian', 'Siamese'), ukuzijwayeza indlela ecacile kusiza ukuqinisa ukuqonda kwakho NOT futhi LIKE.

Ukuzivocavoca okufana nokuthi “amakati esifazane athanda amathoyizi e-teaser futhi angewona amaPheresiya noma amaSiamese” kukuphoqa ukuthi uhlanganise izihlungi zombhalo, ukulingana kanye nezinhlelo zokusebenza ezinengqondoUngakhetha id, name, breed, coloration bese ucindezela imigqa usebenzisa sex = 'F', fav_toy = 'teaser', kanye nesimo esihlanganisiwe esingabandakanyi izinhlobo ezingafuneki. Ukunaka ama-bracket kuqinisekisa ukuthi yonke imibandela engaphansi isetshenziswa enhlanganisela ehlosiwe.

Uma usukhululekile ngalezi zibonelo zamathoyizi ku-SQL eluhlaza, zisebenzise kabusha nge-Python usebenzisa imibuzo ebekwe eceleniBhala imibhalo emifushane ebuza uhlobo, ubudala obuncane noma uhlobo lwethoyizi kusuka input(), zixhume ku WHERE izigaba, bese uphrinta imiphumela. Lokhu kuyibhuloho phakathi kokubhala imibuzo kanye nekhodi yesicelo yangempela elindelwe yizindima eziningi zedatha ezincane.

Ukuqonda nokusebenzisa ama-SQL JOIN

Uma nje usudlule ezinkingeni zamathoyizi, uzojoyina amatafula amaningi njalo. Ama-JOIN ayindlela oxhumanisa ngayo amasethi edatha ahlobene: amakhasimende kuma-oda, abaculi emisebenzini yobuciko, imidlalo ezinkampanini, njalo njalo. Ku-SQL, uchaza ukuthi yimaphi amakholomu okufanele afane phakathi kwamathebula, bese injini yedatha ihlanganisa imigqa ibe yisethi yemiphumela ehlanganisiwe.

Kunezinhlobo ezine zokujoyina eziyinhloko ozohlangana nazo ezingxoxweni nasemaphrojekthini angempela: INNER JOIN (ngokuvamile kubhalwa nje JOIN), LEFT JOIN, RIGHT JOIN, Futhi FULL OUTER JOIN. Ukujoyina kwangaphakathi kubuyisela imigqa kuphela lapho amatafula womabili enezihluthulelo ezifanayo; ukujoyina kwesobunxele kugcina yonke imigqa etafuleni lesobunxele, kugcwalisa NULLs lapho ithebula elifanele lingenawo umdlalo; ukujoyina kwesokudla kwenza into elinganayo; futhi ukujoyina okungaphandle okugcwele kubuyisela yonke imigqa kusuka ezinhlangothini zombili, kufana lapho kungenzeka khona futhi kusetshenziswa NULL lapho kungenjalo.

Cabanga LEFT JOIN futhi RIGHT JOIN njengemisebenzi "yokwethemba lolu hlangothi kakhulu". Ngokujoyina kwesobunxele, ithebula lesobunxele lingumthombo oyinhloko weqiniso: umugqa ngamunye ovela kulo uvela okungenani kanye ekuphumeni, noma ngabe ithebula lesokudla alinikezi lutho. Ngokujoyina okugcwele, alukho uhlangothi olunelungelo – umane uhlanganisa zonke izinkinobho ezivela kumathebula womabili bese uzivumelanisa lapho zihlangana khona.

Ukuze ugcine imibuzo enamathebula amaningi ifundeka, hlala ubiza amatafula akho ngokuthi amatafulaEsikhundleni sokubhala SELECT artist.name ngokuphindaphindiwe, bhala FROM artist AS a bese kuba amakholomu okubhekisela kuwo njenge a.name. Ngokufanayo, piece_of_art kungaba poa, Futhi museum kungaba njalo mUma umbuzo wakho ukhula ube ukuhlangana okuthathu noma ngaphezulu, amagama angasho lutho amahle ahlukanisa ukucaca kanye nokudideka.

Ukusetha ukuqeqeshwa okuvamile kusebenzisa amatafula amathathu: artist, museum, Futhi piece_of_art. The artist itafula lingase libambe id, name, birth_year, death_year kanye nensimu eyinhloko efana ne-watercolor noma isithombe esiqoshiwe. museum izitolo zamatafula id, name futhi country. The piece_of_art ukubamba itafula id, name, artist_id futhi museum_idLawo makholomu amabili okugcina ayizihluthulelo zangaphandle ezixhumanisa umsebenzi wobuciko ngamunye nomdali wawo nendawo yawo.

Ngaleso schema, ungazijwayeza ukujoyina kwangaphakathi, ukujoyina kwesobunxele kanye nezihlungi ezinemibandelaIsibonelo, ukuze ubhale uhlu lwabaculi abazalwa ngemva kuka-1800 abaphila iminyaka engaphezu kwengu-50, kanye namagama emisebenzi yabo, ungajoyina. artist futhi piece_of_art on artist.id = piece_of_art.artist_id bese uhlunga nge death_year - birth_year > 50 futhi birth_year > 1800Igama eliyimfihlo amakholomu akhethiwe njenge artist_name futhi piece_name ukuze kucaciswe.

Ukubona yonke imisebenzi yobuciko kanye namagama emyuziyamu namazwe - kufaka phakathi izingcezu "ezilahlekile" ezingenayo imyuziyamu – ungasebenzisa i- LEFT JOIN kusukela piece_of_art kuya museum on museum_idNgaleyo ndlela, imisebenzi yobuciko engenamnyuziyamu ehlobene nayo isabonakala emiphumeleni, kanye NULL kumakholomu omnyuziyamu. Ukuhlunga imigqa lapho artist_id IS NULL ikuvumela ukuthi uthole imisebenzi yabaculi abangaziwa ngenkathi usajoyina iminyuziyamu ebabambe.

Ukuzivocavoca okuthuthukile kakhulu kukwenza uhlanganise amatafula amathathu ngesikhathi esisodwaUkuze ubhale umsebenzi wobuciko ngamunye ngomculi wawo kanye negama lomnyuziyamu, ungajoyina museum kuya piece_of_art on museum.id = piece_of_art.museum_id, bese ujoyina artist on artist.id = piece_of_art.artist_idUkusebenzisa okucacile JOIN (i-inner join) ngamabomu ilahla imisebenzi yobuciko engenawo umculi noma imyuziyamu, okukunikeza umuzwa wokuthi uhlobo lokujoyina luthinta kanjani inani lemigqa.

Ukuhlanganiswa, iQEMBU NGE-GROUP, kanye NOKUBA NGEMPELA ekusebenzeni

Uma usukwazi ukuthola nokujoyina idatha, ikhono elikhulu elilandelayo ukuyifingqaImisebenzi yokuhlanganisa efana SUM(), AVG(), COUNT(), MAX(), Futhi MIN() bala amamethrikhi ngamasethi emigqa. GROUP BY ihlukanisa isethi yakho yedatha ibe ngamaqembu bese isebenzisa leyo misebenzi ngaphakathi kweqembu ngalinye - isibonelo, iqembu elilodwa ngonyaka, inkampani ngayinye, noma umculi ngamunye. Uma ukhetha izifundo ezihlelekile ukuze usebenzise le mibono, bheka i- inkambo ephelele ye-SQL.

Cabanga nje okulula sales_table ngamakholomu year, month, Futhi sales. Indawo ecacile SELECT SUM(sales) AS total_sales FROM sales_table ikunikeza isamba esiphelele kuyo yonke imigqa. Ukwengeza GROUP BY year kushintsha umbuzo: manje ucela inani lokuthengisa ngonyaka esikhundleni senombolo eyodwa iyonke.

Umthetho oyinhloko ukuthi yonke ikholomu engahlanganisiwe ku- SELECT kumele ivele ku- GROUP BYUma ukhetha year futhi SUM(sales), niqoqana ngokweqembu yearUma ukhetha year futhi month kanye nama-aggregates, bese uqoqa kokubili year futhi monthNgokomqondo, inhlanganisela ehlukile yamakholomu aqoqwe ngamaqoqo ichaza amaqembu.

WHERE futhi HAVING zombili ziyizihlungi, kodwa zisebenza ngezigaba ezahlukene. WHERE ihlunga imigqa eluhlaza ngaphambi kokuba kwenzeke noma yikuphi ukuhlanganiswa noma ukuhlanganiswa. HAVING ihlunga imiphumela eqoqwe ndawonye isebenzisa izinkulumo ezihlanganisiwe. Isibonelo, ungase WHERE production_year BETWEEN 2000 AND 2009 bese HAVING SUM(revenue) > 4000000 ukugcina izinkampani kuphela “imidlalo yazo emihle” ekhiqize imali engenayo engaphezu kwezigidi ezine.

Uhlelo lokusebenza olungokoqobo kakhulu luyi- games ithebula ngamakholomu afana id, title, company, type, production_year, system, production_cost, revenue, Futhi ratingNgaleli thebula elilodwa ungasebenzisa isilinganiso, ukubala, izibalo, ukuqoqwa kanye nokubekwa ezingeni – isinkwa nebhotela le-SQL yokuhlaziya.

Isibonelo, ukubala izindleko zokukhiqiza ezimaphakathi zemidlalo ekhishwe kusukela ngo-2010 kuya ku-2015 ngesilinganiso esingaphezu kuka-7, ungakhetha AVG(production_cost) futhi uvimbele imigqa nge WHERE production_year BETWEEN 2010 AND 2015 AND rating > 7Lowo ngumbuzo wesitayela sengxoxo esijwayelekile, futhi ungawufaka kalula ku-Python bese uphrinta inombolo eyodwa ephumayo.

Ungakhiqiza futhi izibalo zezinga lonyaka ngqo kusuka kulokho okufanayo games ithebula. Qoqa ngeqembu production_year, bese kubalwa COUNT(*) AS count, AVG(production_cost) AS avg_cost, Futhi AVG(revenue) AS avg_revenueLolu hlobo lombuzo lukunikeza ukubuka kochungechunge lwesikhathi oluncane oluvame kakhulu kumadeshibhodi e-BI namathuluzi okubika.

Ukuze uhlele izinkampani ngenzuzo enkulu kuyo yonke iminyaka, ungahlanganisa companyIphethini ewusizo yi- SELECT company, SUM(revenue - production_cost) AS gross_profit_sum FROM games GROUP BY 1 ORDER BY 2 DESC. Lapha GROUP BY 1 futhi ORDER BY 2 sebenzisa izikhundla zekholomu ku SELECT uhlu, olungagcina izinto zimfushane kodwa kumele lusetshenziswe ngokucophelela ukuze ungaphuli imibuzo ngokuhlela kabusha amakholomu kamuva.

Iziphakamiso eziyinkimbinkimbi kakhulu zihlanganisa izihlungi, ukuqoqa kanye nezihlungi zangemuva kokuhlanganiswaAke sithi uchaza “imidlalo emihle” njengaleyo ekhiqizwe phakathi kuka-2000 no-2009, enesilinganiso esingaphezu kuka-6 kanye nemali engenayo engaphezu kwezindleko zokukhiqiza. Enkampanini ngayinye, ufuna inani lemidlalo enjalo kanye nemali engenayo iyonke, kodwa kuphela ezinkampanini ezithola imali engenayo emidlalweni emihle engaphezu kuka-4,000,000. Ubungahlunga imigqa nge WHERE on production_year, rating, kanye nenzuzo, iqembu ngokwe company, ukubala COUNT(company) futhi SUM(revenue), bese ufaka isicelo HAVING SUM(revenue) > 4000000Lo mbuzo owodwa ubamba iningi lezinyathelo zengqondo zomhlaba wangempela ozobhekana nazo emisebenzini yokuhlaziya.

Ukumodela idatha ngamatafula nezinkinobho eziningi

Imiklamo yetafula elilodwa ikunikeza ithuba elikhulu, kodwa izizindalwazi zobudlelwano ziyakhanya uma ulungisa idatha kumathebula amaningi. Ukulungisa kuyinqubo yokususa isitoreji esingafuneki nokumelela ubudlelwano ngezihluthulelo. Lokho kugcina isizindalwazi sakho sincane, sishesha, futhi singanamaphutha amaningi.

Isibonelo esilula kodwa esifundisayo sivela ekukhaselweni kwamagrafu omphakathi afana ne-TwitterAke sithi ufuna ukulandelela ama-akhawunti abasebenzisi kanye nobudlelwano "bokulandela" phakathi kwabo. Indlela eyodwa engenangqondo ingaba ithebula elilodwa lapho umugqa ngamunye uphinda amagama abalandeli nabalandeli njengombhalo. Lokho kuholela ngokushesha ekuphindaphindweni okunzima kanye nokupela okungahambisani.

Kunalokho, uhlukanisa izinto zibe People itafula kanye Follows ithebula. People kungaba nenombolo ephelele id njengesihluthulelo esiyinhloko, okuhlukile name (igama lesikrini noma isibambo), kanye retrieved ifulegi elibonisa ukuthi usuvele ufinyelele ohlwini lwabangani baleyo akhawunti. Follows ibamba amabhangqa ezinqala from_id futhi to_id, emele ukuxhumana okuqondiswe kusuka komunye umsebenzisi kuya komunye.

Imiqondo emithathu ebalulekile yakha lo modeli: okhiye abanengqondo, okhiye abayinhloko, kanye nokhiye bangaphandleIsihluthulelo esinengqondo yilokho umhlaba wangaphandle okusebenzisayo ukubhekisela kurekhodi - lapha, isibambo se-Twitter singaphakathi nameUkhiye oyinhloko ngokuvamile uyinombolo ephelele ekhiqizwe yisizindalwazi (id) ekhomba umugqa ngamunye ngendlela ehlukile futhi eshibhile ukuyikhomba nokuyiqhathanisa. Ukhiye wangaphandle uyinombolo ephelele ekhomba ukhiye oyinhloko kwelinye ithebula – from_id futhi to_id ku Follows ithebula liyizinkomba zezinkinobho zakwamanye amazwe People.id.

Ukuze uphoqelele ikhwalithi yedatha, umemezela imikhawulo ezincazelweni zakho zethebula. Ngokwesibonelo, name TEXT UNIQUE in People kuqinisekisa ukuthi awukwazi ukufaka ngephutha imigqa emibili enesibambo esifanayo. UNIQUE(from_id, to_id) umkhawulo ku Follows Kukuvimbela ukugcina umkhawulo wokulandela ofanayo izikhathi ezingaphezu kwesisodwa. Le mingcele iphinda kabili njenge-safety nets uma uqala ukubhala i-upsert logic ku-Python.

KuPython's sqlite3 imodyuli, iphethini evamile okufanele isetshenziswe INSERT OR IGNORE ukuhlonipha leyo mingcele ngomusaUma uzama ukufaka i- name njengoba isivele ikhona, i-SQLite izokweqa ukusebenza buthule kunokuba ikhiphe amaphutha. Ungabe usuhlola cursor.rowcount ukubona ukuthi umugqa wenezelwe ngempela, futhi uthembele ku cursor.lastrowid ukuthola okwabelwe id kubasebenzisi abasanda kufakwa.

Uma ikhodi yakho ithola igama elisha lesikrini, kufanele iqale izame ukubheka elihambisanayo id. Uma a SELECT id FROM People WHERE name = ? uma ubuyisela umugqa, uphinda usebenzise leyo nhlobo. Uma kungenjalo, ufaka igama nge retrieved = 0, bophezela, bese ufunda lastrowidLeyo ndlela "yokuthola noma ukufaka" iyisisekelo sezikripthi eziningi zokungenisa idatha.

Uma bobabili ama-ID omlandeli kanye nomlandeli sebewaziwa, qopha ubudlelwano ku Follows kungenye nje INSERT OR IGNORE. Wakho UNIQUE(from_id, to_id) Imingcele ibhekana nokuphindaphindwa, futhi ungagxila ekuqondeni okuseqophelweni eliphezulu kokuthi yimaphi amaphrofayili okufanele uwakhase ngokulandelayo, kunokuba uhlukanise umugqa nge-micromanaging.

Ukusebenzisa i-JOIN ukwakha kabusha ubudlelwano obuvela kumathebula ajwayelekile

Ama-schema ajwayelekile asebenzisa i-redundancy yokuhweba ngokuqondiswa: ugcina izinombolo eziphelele esikhundleni sezintambo eziphindaphindwayo, kodwa manje kufanele ujoyine amathebula ukuze wakhe kabusha isithombe esigcweleYilokhu kanye okushiwo yi-SQL JOIN yaklanywa, futhi uma usuyijwayele, imibuzo enzima ye-JOIN izwakala ingokwemvelo ngokuphelele.

Esibonelweni segrafu yezenhlalo, uma ufuna ukubona ukuthi ubani osebenzisa id = 2 uyalandela, ubungajoyina Follows kuya People ohlangothini oluqondiwe. Ngomqondo, uyagijima SELECT * FROM Follows JOIN People ON Follows.to_id = People.id WHERE Follows.from_id = 2Lokhu kukhiqiza imigqa ehlanganisiwe equkethe kokubili umkhawulo wezinombolo kanye negama elifundwa ngumuntu labo bonke abalandelwayo.

Umugqa ngamunye kulowo mphumela “umugqa we-meta” ohlanganisa amakholomu avela kumathebula womabiliAmakholomu amabili okuqala angaba (from_id, to_id) kusukela Follows, kuyilapho amakholomu alandelayo engeka People - njengawo (id, name, retrieved). Ngoba JOIN isimo siyaphoqelela Follows.to_id = People.id, ungabona lobo budlelwano ngokucacile: ikholomu yesibili kanye nekholomu yesithathu yomugqa ngamunye kuyahambisana.

Le ndlela efanayo idlulela ngokwemvelo ematafuleni amaningiUsuvele ukubonile nge artist, piece_of_art, Futhi museum, futhi i-Twitter crawler iyibonisa nge People futhi Follows. Emigqeni yokuhlaziya eyinkimbinkimbi kakhulu, ungase uhlanganise amathebula amaqiniso (imicimbi, ama-oda) kumathebula amaningi obukhulu (abasebenzisi, imikhiqizo, imikhankaso) ukuze uphendule imibuzo enezici eziningi.

Uma ulungisa iphutha lekhodi yakho noma ufunda ukuthi i-schema ihlangana kanjani, ukuhamba komsebenzi kwe-“run Python, then inspect with DB Browser for SQLite” kusebenza kahle kakhulu.. Sebenzisa iskripthi sakho ukuze ugcwalise isizindalwazi, vala noma yisiphi isibonelo se-GUI esigcina ifayela likhiyiwe, bese uvula i- .sqlite ifayela kusiphequluli. Ukusuka lapho ungahlola okuqukethwe kwethebula ngalinye bese usebenzisa i-ad-hoc SELECT imibuzo yokuqinisekisa izibikezelo zakho.

I-caveat eyodwa: I-SQLite iphoqelela ukukhiya kwamafayela, ngakho-ke uma i-DB Browser inesizindalwazi esivulekile kumodi yokuhlela, iskripthi sakho se-Python singase sehluleke ukuxhuma noma ukuzibophezela. Isixazululo ukuvala isizindalwazi ku-GUI (noma ukuphuma ngokuphelele kusiphequluli) ngaphambi kokusebenzisa ikhodi yakho ye-Python futhi. Ukujwayela ukuvala amathuluzi avala ifayela lakho le-DB kuzokusindisa emaphutheni angaqondakali athi “isizindalwazi sikhiyiwe”.

Ukuhlanganisa lawa masu ndawonye - ukwakheka kweskimu, imikhawulo, imibuzo ehlukaniswe ngamapharamitha ku-Python, JOINs, GROUP BY kanye ne-HAVING - kukunikeza ilebhu yendawo enamandla ukuze uzijwayeze uhlobo lomsebenzi we-SQL kanye ne-Python ozowenza emsebenzini. Ngaphandle kwe-SQLite namathebula ambalwa esampula ahlelwe kahle, ungazijwayeza imibuzo yesitayela sengxoxo, i-prototype analytic logic, futhi uphinde uthole ukuzethemba kwakho ngethuluzi ledatha lesimanje.

Lapho amapulatifomu afana ne-DataLemur nezifundo ezisebenzisanayo zingena khona

Ngaphezu komkhuba wakho wendawo, amapulatifomu asebenzisanayo angakunikeza ulwazi oluqondiswe kakhudlwana ngempendulo esheshayoAmathuluzi azalwa ngenxa yokuhlangenwe nakho kwemboni yangempela – isibonelo, amapulatifomu adalwe onjiniyela bedatha bangaphambilini be-Facebook ne-Google abachitha izinsuku zabo bebhala i-SQL ne-Python futhi beqhuba izivivinyo ze-A/B – bavame ukugxila kokuqukethwe kwabo emibuzweni yangempela yengxoxo kanye nezimo zokuhlaziya.

Izincwadi ezimboza izibalo, ukufunda komshini kanye nokuqonda kwebhizinisi kwezingxoxo zedatha zilungele ithiyori, kodwa abahlali behlinzeka ngendawo yokudlala ye-SQL esetshenziswa abafundi abaningi. Leso sikhala yilokho kanye amanye amathuluzi esimanje ahlose ukukugcwalisa: baphinda bapakishe amakhulu ezicelo zesitayela sengxoxo baye endaweni ye-SQL kanye ne-analytics engaphakathi kwesiphequluli ukuze ukwazi ukusebenzisa, ukulungisa nokuqalisa kabusha imibuzo yakho ngaphandle kokukhathazeka ngokusetha kwendawo. Ungazama futhi izibonelo ezisetshenzisiwe ezifana ukuhlolwa kwengozi yokungatholakali kwamakhasimende ukuhlanganisa i-SQL nemisebenzi yokufunda komshini eyisisekelo.

Uzothola nezifundo ze-SQL ezisebenzisanayo ezibonisa izihloko esizihambile lapha: imibuzo yetafula elilodwa ene SELECT futhi WHERE, ihlangana emathebula amabili noma amathathu, ukuhlanganiswa kanye nokuhlanganiswa, imibuzo engezansi, nokuningi. Eziningi zalezi zifundo zithembele kumasethi edatha angokoqobo - cabanga ngemidlalo, iminyuziyamu, noma ukuthengiswa kwezentengiselwano - ukuze imibuzo izwakale njengezinkinga zebhizinisi zangempela kunokuba kube amaphazili aqanjiwe.

Uma uzizwa ukhungathekile ngamadokhumenti amathuluzi afana ne-PySpark, i-DuckDB noma i-dbt, kunengqondo ukuwahlehlisa kuze kube yilapho izisekelo zakho ze-SQL zizwakala ziqinile.Ukugxila kuqala ku-SQLite kanye ne-Python kukuvumela ukuthi ugcwalise amaphethini ombuzo oyinhloko ngaphandle kokulwa nokucushwa kweqoqo noma izimvume zamafu. Uma izisekelo seziyimvelo yesibili, ukufunda i-PySpark kuba kakhulu mayelana nokusebenza okusatshalaliswayo kunemiqondo emisha yombuzo.

Ekugcineni, inhlanganisela yokusetha okulula kwendawo, izinkinga zokuzijwayeza ezihlelekile, kanye nokusetshenziswa ngezikhathi ezithile kwamapulatifomu asebenzisanayo ikunikeza okungcono kakhulu emhlabeni wonke: ukulawula okugcwele indawo okuyo, isisekelo esiqinile somqondo, kanye nokuvezwa kwesitayela semibuzo abaqashi abayithandayo kakhulu. Ngokuzijwayeza okuqhubekayo, ingxube eyayisabisa yamathuluzi obunjiniyela be-SQL, i-Python kanye nedatha iba ithuluzi elijwayelekile, elijabulisayo nelijwayelekile ongalisebenzisa ngokuzethemba ezindimeni ezintsha.

Uma konke kuhlanganisiwe, indlela yakho eya phambili icacile: vula isizindalwazi se-SQLite nge-Python, klama amatafula ambalwa angokoqobo, vula amaphethini e-SQL ayisisekelo naphakathi (izihlungi, ukujoyina, ukuhlanganisa, ukuqoqa, ukubamba amaqembu, ukuba ne-HAVING), songa leyo mibuzo ngezikripthi ze-Python, bese ungeza ngokuzithandela ukufunda kwakho ngamapulatifomu e-SQL asebenzisanayo akhiwe ochwepheshe abaye baba lapho ukhona manje.; ngokwenza kanjalo, uzokwakha kabusha imizwa yakho yobuchwepheshe, unciphise ukukhathazeka okuzungeze izitaki zedatha zanamuhla, futhi ulungele ukusingatha izidingo ze-SQL kanye ne-Python zezindima zedatha zanamuhla.