Česti problemi sa SQL-om i Pythonom i kako ih riješiti

Početna » piton » Česti problemi sa SQL-om i Pythonom i kako ih riješiti

Zajednički rad sa SQL-om i Pythonom jedna je od najmoćnijih kombinacija u razvoju podataka i backenda., ali također otvara vrata dugoj listi suptilnih grešaka, konfiguracijskih zamki i iznenađenja u performansama. Ako ste ikada zurili u zagonetni traceback dok je vaša veza s bazom podataka "trebala samo da radi" ili se pitali zašto isti analitički skript radi munjevito brzo na vašem laptopu, ali se uvlači unutar SQL Servera, niste sami.

Ovaj vodič objedinjuje probleme iz stvarnog svijeta sa SQL-Python programiranjem, probleme niskog nivoa sa SQL Server Machine Learning Services i praktične obrasce za korištenje oba jezika u analitici.Umjesto nejasnih savjeta, naći ćete konkretne primjere, tipične poruke o greškama i detaljne ideje za dijagnosticiranje i rješavanje problema, plus kompletan vodič o tome kako dizajnirati, upitati i manipulirati bazama podataka u Pythonu koristeći SQLite i druge mehanizme.

Uobičajeni problemi s povezivanjem između SQL-a i Pythona

Jedna od prvih problema pri kombinovanju SQL-a i Pythona je jednostavno dobijanje stabilne veze.Čak i kada akreditivi i DSN-ovi izgledaju ispravno, male neusklađenosti u drajverima, putanjama ili okruženjima mogu izazvati zbunjujuće greške prilikom izvršavanja u trenutku kada pokrenete app.py ili pokrenete skriptu iz komandne linije.

U virtualiziranim okruženjima ovo postaje krhkijeNa primjer, možete pokrenuti SQLite ili SQL Server unutar virtualne mašine dok razvijate na host operativnom sistemu i testirate vezu pomoću GUI alata kao što je SQL Developer ili SQL Server Management Studio. GUI se povezuje bez problema, ali Python skripta ne uspijeva jer koristi drugi drajver, nedostaje biblioteka ili potpuno drugu mrežnu putanju.

Tipični problemi s vezom uključuju nedostajuće ODBC/DB API drajvere, pogrešnu DSN konfiguraciju, blokirane portove i neusklađene načine autentifikacije.Vrlo je često vidjeti Python kako izaziva generičke izuzetke poput „nije se moguće povezati“, dok je osnovni problem to što sistem ne može učitati dijeljenu biblioteku (na primjer, libc++ ili libc++abi na Linuxu) ili ne pronalazi očekivani ODBC drajver za SQLite, PostgreSQL, MySQL ili SQL Server.

Kada se povezujete iz Pythona, obično koristite biblioteke kao što su sqlite3, psycopg2, pyodbc, mysql-connector-python, PyMySQL ili ORM sloj poput SQLAlchemy.Svaki od njih ima svoj format stringa za povezivanje, tipove grešaka i zavisnosti. GUI klijent može koristiti drugačiji stek drajvera koji skriva te probleme, stoga uvijek provjerite koji tačno drajver i parametre veze koristi vaš Python kod.

Zašto je kombinovanje SQL-a i Pythona strateški moćno

Pored tehničkih problema, postoji strateški razlog zašto programeri i analitičari i dalje insistiraju na kombinovanju Pythona sa SQL-om.Svaki jezik pokriva drugačiji dio životnog ciklusa podataka, a zajedno vam pružaju cjelovit radni proces koji je teško uskladiti s jednim alatom.

SQL je i dalje standard za upravljanje relacijskim podacimaOdlikuje se dobro strukturiranim podacima, relacijskom integritetu, indeksiranju i transakcijskim opterećenjima. Sa SQL-om dobijate brzo filtriranje, spajanje i agregiranje velikih skupova podataka, objedinjen pristup mnogim alatima i predvidljive performanse potkrijepljene decenijama istraživanja baza podataka.

Python zablista kada podaci napuste kontekst baze podatakaPomoću biblioteka poput pandas, NumPy, matplotlib i seaborn možete čistiti, preoblikovati i analizirati podatke na proizvoljno složene načine, pokretati statistiku ili mašinsko učenje i programski graditi vizualizacije ili izvještaje, uključujući analiza podataka u stvarnom vremenuMnoge transformacije koje su nespretne ili opširne u SQL-u postaju jednostavni Python izrazi.

U praksi to znači jasnu podjelu radaUbaciti što više filtriranja, agregacije i osnovne transformacije u SQL, a zatim vratiti uredan skup podataka u Python za tešku analitiku, modeliranje ili vizualizaciju. Analitičari i inženjeri koji tečno govore oba jezika mogu brzo preći s poslovnog pitanja na reproducibilan protok podataka.

Povezivanje Pythona sa SQL bazama podataka: biblioteke i obrasci

Da bi SQL i Python pouzdano funkcionisali zajedno, potrebni su vam pravi konektori i određena disciplina oko načina otvaranja, korištenja i zatvaranja sesija baze podataka.Tačan stek zavisi od mehanizma baze podataka, ali koncepti su slični.

Za lagane, ugrađene tokove rada, SQLite je često najjednostavniji izbor.Python dolazi s modulom sqlite3 u standardnoj biblioteci, tako da možete kreirati datoteku baze podataka, definirati tabele i pokretati upite bez instaliranja dodatnog softvera. Ovo je savršeno za prototipove, male analitičke projekte ili podučavanje relacijskih koncepata.

Za baze podataka serverskog nivoa obično se koriste drajveri specifični za engine ili ORM.PostgreSQL se široko koristi sa psycopg2, SQL Server često koristi pyodbc ili Microsoftov ODBC drajver, a MySQL/MariaDB se oslanjaju na mysql-connector-python ili PyMySQL. Pored toga, SQLAlchemy pruža sloj apstrakcije visokog nivoa koji vam omogućava pisanje prenosivih SQL izraza i upravljanje skupovima konekcija.

Robustan obrazac povezivanja uključuje čitanje vjerodajnica iz varijabli okruženja ili upravitelja tajni, korištenje parametriziranih upita kako bi se izbjeglo ubrizgavanje i primjenu odgovarajućeg rukovanja greškama.Nakon svake jedinice rada, trebali biste eksplicitno potvrditi (commit) ili poništiti (rollback) transakcije i vratiti vezu nazad u skup (pool) ili je zatvoriti, umjesto da držite mnogo neaktivnih sesija otvorenim.

Sa SQLAlchemy i pandas-om, radni proces postaje posebno gladak.: konstruišete URL za povezivanje, kreirate mehanizam, a zatim koristite pandas.read_sql_query za direktno dohvaćanje rezultata upita u DataFrame. Odatle imate punu snagu Python ekosistema za čišćenje, analizu i izvoz podataka.

Usluge mašinskog učenja u SQL Serveru: Problemi integracije R i Python

Microsoft SQL Server uključuje funkciju pod nazivom Machine Learning Services koja ugrađuje R i Python runtime okruženja unutar mehanizma baze podataka., što vam omogućava pozivanje eksternih skripti putem sp_execute_external_script. Ovo je moćno za analitiku unutar baze podataka, ali dolazi s dugom listom grešaka i ograničenja specifičnih za verziju koje morate razumjeti.

Problemi s instalacijom i nadogradnjom su posebno česti u SQL Serveru 2016, 2017, 2019 i 2022.Problemi se kreću od nedostajućih R komponenti na određenim Azure VM slikama, preko nepotpunih Python instalacijskih programa na ranim verzijama SQL Servera 2017, do CU (kumulativnih ažuriranja) paketa koji ne traže vanmrežna R ažuriranja. U nekim slučajevima morate proslijediti dodatne parametre kao što je MRCACHEDIRECTORY u komandnoj liniji da biste usmjerili instalaciju na keširane CAB datoteke.

Postoje i problemi sa zavisnošću specifičnim za platformuNa Linux verzijama SQL Servera 2019 i novijim, R i Python runtime okruženja se mogu neuspješno pokrenuti jer dijeljene biblioteke poput libc++.so.1 ili libc++abi.so.1 nisu dostupne u putanji biblioteke za proširivanje. Rezultirajuće greške se često pojavljuju kao generičke poruke „Nije moguće komunicirati s runtime okruženjem“ u SQL Serveru, dok zapisnici launchpad-a otkrivaju nedostajuću .so datoteku. Ispravke obično uključuju kopiranje potrebnih dijeljenih biblioteka u /opt/mssql-extensibility/lib ili izlaganje direktorija putem mssql.conf.

Na Windows serverima konfiguriranim s FIPS kriptografskim postavkama postoji još jedna klasa kvarova instalacije.Pokušaj omogućavanja usluga mašinskog učenja ili jezičkih proširenja može uzrokovati greške o tome da kreiranje AppContainer-a nije kompatibilno s algoritmima validiranim od strane Windows platforme FIPS. Zaobilazno rješenje je privremeno onemogućavanje FIPS-a, dovršavanje instalacije ili nadogradnje, a zatim ponovno omogućavanje FIPS-a nakon što je SQL Server u potpunosti konfigurisan.

Neka kumulativna ažuriranja uvode privremene regresije koje utiču na izvršavanje skriptiNa primjer, SQL Server 2017 CU verzije 5-7 uključivale su grešku u rlauncher.config kada je putanja privremenog direktorija sadržavala razmake, što je uzrokovalo grešku R skripti s greškom „ne može se kreirati R_TempDir“. Kasnije su CU verzije ispravile ovaj problem, ali do tada su administratori morali ponovo registrirati vanjsko okruženje za skriptiranje koristeći RegisterRExt.exe sa zastavicama za deinstalaciju i instalaciju.

Neusklađenosti verzija između klijentskog i serverskog okruženja

Još jedan ponavljajući izvor zabune je kompatibilnost verzija između klijentskih alata (Microsoft R Client ili Python paketi) i serverskih okruženja za izvršavanje (R Server ili SQL Server Machine Learning Services).Kada pokrećete udaljene skripte s klijenta na starijoj instanci SQL Servera, neusklađenost može izazvati eksplicitne greške ili suptilne probleme sa serijalizacijom.

U SQL Server 2016 R Services, verzije R biblioteke klijenta i servera moraju se potpuno podudarati.Pokretanje Microsoft R Client 9.x na serveru sa R ​​Server 8.0.3 generiše poruke koje navode da je vaš klijent nekompatibilan i predlažu vam da instalirate odgovarajuću verziju. Kasnije verzije su ublažile ovaj zahtjev, ali ako vidite ove greške, morate provjeriti obje strane i ili nadograditi server ili instalirati kompatibilnog klijenta.

Serijalizacija i deserijalizacija obučenih modela su posebno osjetljive na razlike u verzijama.S RevoScaleR u R-u i revoscalepy u Pythonu, model serijaliziran s novijim API-jem možda neće uspjeti deserializirati na serveru koji koristi stariju infrastrukturu serijalizacije, što rezultira internim greškama poput grešaka memDecompress u R-u ili NameError u Pythonu kada rx_unserialize_model nije definiran. Nadogradnja instance SQL Servera na barem CU3 za SQL Server 2017 obično rješava ove neusklađenosti.

Prethodno obučeni modeli instalirani na SQL Serveru 2017 također mogu naići na ograničenja dužine putanjeRane verzije su pohranjivale binarne datoteke modela u duboke strukture direktorija pod zadanom putanjom instance, a Python nije mogao otvoriti datoteke jer je puna putanja premašivala ograničenja operativnog sistema. Predložena rješenja uključivala su instaliranje modela na prilagođenu kraću putanju, instaliranje SQL Servera u kraći korijenski direktorij ili čak kreiranje NTFS tvrdih veza pomoću fsutil-a kako bi se istoj datoteci prikazao kraći alias.

Kada projektujete rješenje koristeći SQL Server Machine Learning Services, uvijek zaključajte svoje verzije i CU nivoe kao dio plana implementacije.Širenje skripti na više servera s različitim CU nivoima bez praćenja ovih detalja recept je za kasnije probleme sa serijalizacijom i vremenom izvršavanja koje je teško otkloniti.

Upravljanje resursima, performanse i ponašanje pri hladnom pokretanju

Čak i kada su SQL Server Machine Learning Services ispravno instalirani i verzija im je usklađena, možete dostići ograničenja performansi zbog upravljanja resursima i objedinjavanja procesa.Razumijevanje ponašanja procesa na lansirnoj rampi i satelitima ključno je za postizanje konzistentne latencije.

SQL Server kreira procesne skupove za vanjske skripte po korisniku, po bazi podataka i po jeziku.Prvi poziv funkcije sp_execute_external_script nakon perioda neaktivnosti uzrokuje da launchpad pokrene nove satelitske procese za R ili Python. Ovaj hladni start može biti primjetno spor na jako opterećenim serverima ili ograničenim virtuelnim mašinama. Kasniji pozivi ponovo koriste zagrijani bazen, tako da su drugo i treće izvršavanje mnogo brže.

Ako je latencija prvog poziva problem - kao što je slučaj u scenarijima bodovanja u stvarnom vremenu - možete održavati bazene toplima periodičnim pokretanjem laganih skripti.Mnogi timovi zakazuju jednostavnu "no-op" R ili Python skriptu putem SQL Agenta da se pokreće svakih nekoliko minuta, sprječavajući zadatak čišćenja u stanju mirovanja da isključi satelitske procese.

Na SQL Serveru 2016 Enterprise Edition, rane verzije ograničavaju eksternu memoriju skripti na oko 20% ukupne RAM memorije.Za server od 32 GB, ovo je značilo da bi R izvršne datoteke mogle biti ograničene na oko 6.4 GB po zahtjevu. Za veće modele ili široke skupove podataka, ovo brzo postaje ograničenje, što dovodi do grešaka u dodjeli memorije ili značajnog straničenja. Administratori moraju pregledati trenutne zadane postavke i prilagoditi postavke regulatora resursa kada se očekuju složena ML opterećenja.

Paralelizam je još jedno suptilno ograničenjeKada pozivate Microsoft ML ili RevoScaleR biblioteke izvan SQL Servera (npr. RGui), čak i ako je osnovno izdanje Enterprise, te biblioteke često rade u jednonitnom režimu. Slično tome, postojale su poznate greške u SQL Serveru 2019 gdje su R skripte koje koriste RxLocalPar kontekste ili osnovni paralelni paket mogle uzrokovati zamrzavanje SQL Servera zbog problema sa pisanjem na null uređaj u sandbox okruženju.

Ograničenja tipa podataka, kodiranja i sheme prilikom pozivanja vanjskih skripti

Tipovi podataka i kodiranja su čest izvor neočekivanog ponašanja prilikom prenošenja SQL podataka u R ili Python putem sp_execute_external_script.Nisu podržani svi SQL tipovi, a neki su samo djelimično podržani ili se tiho konvertuju, što može rezultirati gubitkom preciznosti ili oštećenim stringovima, posebno kod složenih struktura kao što su nizovi u SQL-u.

Ranije SQL Server 2017 CU-ove su imale stroga ograničenja za numeričke, decimalne i novčane tipove za Python izlazne sheme.U kombinaciji sa WITH RESULT SETS i Pythonom, nepodržani tipovi su proizvodili SqlSatelliteCall greške i poruke koje su ukazivale da su dozvoljeni samo bit, smallint, int, datetime, smallmoney, real i float (plus djelomično char/varchar). Kasniji CU-ovi su ovo ispravili, ali i dalje morate biti svjesni koje tipove podataka izlažete vanjskim okruženjima za izvršavanje.

Za R skripte, money, numeric, decimal i bigint se konvertuju u numerički tip R-a.Kao posljedica toga, vrijednosti velike magnitude ili one s mnogo decimalnih mjesta mogu izgubiti preciznost; tipovi novca mogu izazvati upozorenja o centima koji se ne mogu tačno predstaviti, a bigint prelazi ograničenje od 53 bita u R-u, uzrokujući zaokruživanje najmanje značajnih bitova.

Kodiranje stringova je takođe važnoProsljeđivanje Unicode podataka pohranjenih u varchar kolonama može oštetiti znakove koji nisu ASCII jer se SQL Server sortiranja možda ne podudaraju s UTF-8 kodiranjem koje očekuju R ili Python. Preporučeni pristupi su korištenje UTF-8 sortiranja dostupnih u SQL Serveru 2019+ ili pohranjivanje Unicode teksta u nvarchar i eksplicitno rukovanje konverzijama u vašem skriptu.

Neke SQL funkcije su u potpunosti zabranjene za eksterne skripteUpiti koji referenciraju uvijek šifrirane ili maskirane kolone ne mogu se direktno proslijediti R skriptama u određenim kontekstima; možda ćete morati kopirati zaštićene podatke u privremene tabele bez šifriranja ili maskiranja radi analize. Osim toga, u kontekstu računanja SQL Servera, argumenti poput colClasses u R-u ne mogu nadjačati tipove kolona; morate izvršiti CAST ili CONVERT u T-SQL-u prije nego što predate podatke R-u.

Binarni korisni tereti također imaju posebna pravilaPrilikom vraćanja sirovog tipa R-a, vrijednost mora biti uključena u okvir izlaznih podataka, a ne vezana za izlazni parametar. Efektivno je podržan samo jedan skup sirovih izlaza; ako vam je potrebno više binarnih izlaza, možda ćete morati pozvati pohranjenu proceduru nekoliko puta ili vratiti podatke u SQL putem ODBC-a iz skripte.

Praktični problemi prilikom instaliranja i proširivanja Pythona u SQL Serveru

Instaliranje i proširivanje Python okruženja u paketu sa SQL Server Machine Learning Services je ograničenije od samostalnog Anaconda ili sistemskog Pythona.Mnogi korisnici nailaze na greške prilikom pokušaja dodavanja paketa pomoću pip-a ili sqlmlutils-a, posebno na Windowsu sa SQL Serverom 2019.

Na Windowsu, čest problem nakon instaliranja SQL Servera 2019 je taj što pip prijavljuje probleme s konfiguracijom TLS/SSL-a.Žali se da ssl modul nije dostupan, iako očigledno možete pokrenuti Python. Uzrok je obično nedostatak OpenSSL DLL-ova (libssl-1_1-x64.dll i libcrypto-1_1-x64.dll) u poddirektoriju DLLs usluge PYTHON_SERVICES. Kopiranje ovih datoteka iz mape Library\bin u DLL-ove, a zatim pokretanje novog komandnog retka obično vraća pip-ovu sposobnost slanja HTTPS zahtjeva.

Neki popularni ML paketi poput tensorflow-a imaju nekompatibilne zahtjeve za zavisnostimaTensorflow kotač može zahtijevati noviju verziju NumPy-a od one koja je unaprijed instalirana u Python okruženju SQL Servera. Budući da se NumPy tretira kao sistemski paket, ne možete ga nadograditi putem sqlmlutils, tako da pokušaji instaliranja tensorflowa putem te rute ne uspijevaju. Umjesto toga, morate direktno pozvati izvršnu datoteku PYTHON_SERVICES sa -m pip i nadograditi ili instalirati pakete u tom okruženju, ponekad nakon ručnog ažuriranja redistribubilnih runtime okruženja poput Microsoft Visual C++.

Na Linuxu, uključena pip ulazna tačka može se odmah izdvojiti.Za SQL Server 2019, pokretanje pip-a iz /opt/mssql/mlservices/runtime/python/bin može se srušiti s greškom lošeg interpretera koja ukazuje na nepostojeću lokaciju naslijeđenog ML Servera. Rješenje je preuzimanje get-pip.py sa PyPA i pokretanje s ispravnom Python binarnom datotekom pod /opt/mssql/mlservices/bin/python/python, efektivno ponovo pokretajući pip za to okruženje za izvršavanje.

Postoje i suptilna ponašanja oko izlaznih parametara varbinary i varchar u Python skriptama.Ako vaš poziv sp_execute_external_script izloži OUTPUT parametar tipa varbinary(max) ili large varchar i ne uspijete dodijeliti vrijednost unutar Python skripte, komponenta BxlServer može izazvati greške i prestati raditi. Siguran obrazac je eksplicitna inicijalizacija tih parametara unutar vašeg Python koda, čak i ako ih samo postavite na prazan string ili 0x0.

Klasični SQL + Python radni tok sa SQLite-om

Udaljavajući se od specifičnosti SQL Servera, vrlo produktivan način za učenje i prototipiranje SQL-Python integracije je korištenje SQLite-a s Python-ovim sqlite3 modulom.SQLite pohranjuje podatke u jednu datoteku, ne zahtijeva zaseban serverski proces i ponaša se kao mala relacijska baza podataka sa SQL podrškom.

U SQLite-u, ​​baza podataka je samo organizovana datoteka koja čuva strukturirane podatke na disku.Poput Python rječnika, mapira ključeve na vrijednosti, ali dodaje indeksiranje, efikasno pohranjivanje velikih skupova podataka i mogućnosti upita. Strukture se vrte oko tabela (slično proračunskim tablicama), redova (zapisa) i kolona (polja). U formalnijoj relacijskoj terminologiji, to su relacije, tuple-ovi i atributi.

Za početak, povezujete se s datotekom baze podataka pomoću sqlite3.connect.Ako datoteka ne postoji, SQLite je kreira. Iz veze kreirate objekt kursora koji djeluje kao ručka za izvršavanje SQL naredbi i iteriranje kroz rezultate. Tok rada je analogan otvaranju datoteke i čitanju red po red, osim što izvršavate SQL naredbe umjesto čitanja običnog teksta.

Kreiranje tabele zahtijeva navođenje naziva kolona i tipova podatakaIako je SQLite prilično fleksibilan po pitanju tipkanja, definiranje tipova pomaže engine-u da odabere efikasne formate pohrane i strategije indeksiranja. Na primjer, jednostavna tabela za pjesme može definirati tekstualni naslov i cijeli broj reprodukcija. Nakon što je tabela kreirana pomoću CREATE TABLE, možete umetnuti redove koristeći INSERT i rezervirane mjesta za parametre (upitnike) za sigurno povezivanje Python vrijednosti.

Korištenje SQL-a iz Pythona: INSERT, SELECT, UPDATE, DELETE

SQL pruža četiri osnovne operacije - INSERT, SELECT, UPDATE i DELETE - koje se lijepo preslikavaju na Python kod koji radi sa sqlite3.Svaka operacija manipuliše redovima u tabeli, a klauzula WHERE vam omogućava da ciljate određene zapise.

INSERT dodaje nove zapise u tabeluU Pythonu, cursor.execute se poziva naredbom poput INSERT INTO Songs (title, plays) VALUES (?, ?), prosljeđujući skup parametara. Korištenje rezerviranih mjesta umjesto spajanja stringova izbjegava SQL injekciju i ispravno obrađuje citiranje. Nakon umetanja, pozivate conn.commit da biste promjene iz transakcije pohranili u datoteku baze podataka.

SELECT čita podatke iz baze podataka, opcionalno filtrirajući i sortirajući rezultateJednostavan SELECT naslov, reproducira FROM Songs, pretvara kursor u iterabilan objekt koji prelazi preko redova. Za velike skupove rezultata, SQLite ne učitava sve redove odjednom u memoriju; umjesto toga ih vraća kada se for petlja ponavlja. Možete odabrati sve kolone sa * ili odrediti podskup, a možete koristiti WHERE, ORDER BY i LIMIT za ograničavanje i sortiranje zapisa.

DELETE trajno uklanja redove na osnovu uslovaNaredba poput DELETE FROM Songs WHERE plays < 100 briše sve pjesme s malim brojem reprodukcija. Nema poništavanja, pa je u tutorijalima uobičajeno brisati redove na kraju skripte kako bi se ponovno pokretanje primjera učinilo idempotentnim. Morate potvrditi promjene nakon brisanja ako želite da se promjene zadrže.

UPDATE mijenja kolone u postojećim redovima. Navodite tabelu, SET klauzulu sa novim vrijednostima i opcionalnu WHERE logiku. Na primjer, UPDATE Songs SET reproduces = 16 WHERE title = 'My Way' utiče na svaki red čiji se naslov podudara sa tim nizom. Ako izostavite WHERE, ažurirat ćete svaki red u tabeli, što je često izvor slučajnih masovnih promjena.

Izrada Twitter crawlera pomoću SQLite-a i Pythona

Praktična demonstracija miješanja SQL-a i Pythona je mali Twitter crawler koji pohranjuje stanje u SQLite bazu podataka.Iako se Twitterovi API-ji i politike mijenjaju tokom vremena, arhitektonska ideja ostaje poučna: želite pregledati odnose prijatelja, izbjeći ponovno posjećivanje računa i uhvatiti metrike popularnosti, a sve to uz mogućnost zaustavljanja i nastavka bez gubitka napretka.

Pretraživač održava tabelu Twitter naloga i prati da li je svaki od njih preuzet i koliko puta se pojavljuje kao prijatelj.Svaki red sadrži naziv računa, zastavicu koja označava da li ste već preuzeli njegovu listu prijatelja i brojač koliko se puta taj račun pojavio među "prijateljima" drugih. Ovo vam omogućava da procijenite popularnost unutar uzorkovane mreže.

Glavna petlja traži od korisnika da unese Twitter identifikator ili komandu za izlaz.Ako korisnik jednostavno pritisne Enter, skripta upituje bazu podataka za sljedeći račun sa recovered = 0 i koristi ga kao sljedeći cilj. Zatim poziva Twitterovu krajnju tačku friends/list, analizira JSON odgovor, ažurira oznaku recovered za trenutni račun i ili ubacuje ili ažurira svakog prijatelja u bazu podataka, povećavajući njihove brojače prijatelja po potrebi.

Budući da je sve pohranjeno u SQLiteu, možete prekinuti indeksiranje i ponovo ga pokrenuti kasnije.Baza podataka služi kao izdržljivo skladište reda čekanja i stanja. Odvojeni pomoćni skript može prikazati sadržaj Twitter tabele, omogućavajući vam da pregledate koji su računi poznati, koji su posjećeni i koliko se puta svaki pojavio kao prijatelj. Ovaj obrazac - očuvanje stanja indeksiranja u relacijskoj bazi podataka - dobro se generalizuje na druge zadatke indeksiranja weba ili API-ja.

Osnove modeliranja podataka: primarni ključevi, strani ključevi i normalizacija

Pohranjivanje svih informacija sa Twittera u jednoj tabeli brzo nailazi na probleme skalabilnosti i redundantnosti.Robusniji pristup je normalizacija podataka odvajanjem entiteta (ljudi) od odnosa (ko koga prati) i njihovim povezivanjem putem ključeva.

Tabela ljudi obično koristi primarni ključ tipa cijeli broj kao interni identifikator.U SQLite-u možete deklarisati id INTEGER PRIMARY KEY, a engine će automatski generisati jedinstveni cijeli broj za svaki umetnuti red. Također uključujete logički ključ kao što je Twitter handle, označen kao UNIQUE kako biste spriječili duplikate. Logički ključ je ono što vanjski svijet koristi, dok je primarni ključ ono na što se vaš kod i strani ključevi pozivaju.

Odvojena tabela praćenja zatim bilježi odnose koristeći strane ključeveSvaki red sadrži par korisničkih ID-ova, obično nazvanih from_id i to_id (ili slično), što ukazuje na to da jedna osoba prati drugu. Možete deklarirati UNIQUE ograničenje na kombinaciju ove dvije kolone, što osigurava da ne možete slučajno umetnuti isti odnos dva puta.

Normalizacija - pohranjivanje svake informacije jednom i njeno pozivanje na druge lokacije pomoću ključeva - izbjegava dupliranje, štedi prostor i poboljšava performanse.Umjesto spremanja istog niza korisničkog imena u milionima redova za odnose, spremate ga jednom u tabeli osoba, a zatim na njega ukazujete putem cjelobrojnih ID-ova. Cjelobrojni brojevi se brže porede i indeksiraju, što postaje ključno pri velikim razmjerima.

U Python kodu ovaj dizajn vodi do uobičajenih obrazaca za umetanje ili preuzimanje korisnika i odnosa.Prije umetanja relacije morate se uvjeriti da oba učesnika postoje u tabeli osoba: koristite SELECT pomoću logičkog ključa, a ako nijedan red nije pronađen, koristite INSERT i bilježite zadnji identifikator osobe kao ID nove osobe. Tek tada koristite INSERT OR IGNORE u tabeli koja povezuje te ID-ove. Ograničenja i ILI IGNORE rade zajedno kako bi vaši podaci bili konzistentni bez prekomjernih ručnih provjera.

Korištenje JOIN-a za kombinovanje povezanih tabela u SQL-u

Nakon što se podaci rasporede po više normalizovanih tabela, oslanjate se na SQL JOIN-ove da biste rekonstruisali kombinovani prikaz koji vam je potreban.JOIN spaja redove iz dvije tabele na osnovu podudaranja ključnih vrijednosti, efektivno kreirajući virtuelni široki red za svako podudaranje.

U primjeru s Twitterom, spajanje tabela "prati" i "ljudi" omogućava vam da vidite koga određeni korisnik prati ili ko prati njega.Upit poput SELECT * FROM Follow JOIN People ON Follow.to_id = People.id WHERE Follow.from_id = 2 dohvaća sve osobe koje prati korisnik čiji je interni ID 2. Klauzula JOIN govori bazi podataka da upari Follow.to_id sa People.id za svaki red, a uvjet WHERE ograničava izvornog korisnika.

Skup rezultata sadrži kolone iz obje tabeleMožda ćete vidjeti dva cjelobrojna ID-a iz tabele praćenja, a zatim cijeli red osobe (ID, nadimak, oporavljena zastavica) iz tabele osoba. Kada korisnik prati više računa, dobijate jedan kombinovani red po odnosu, duplicirajući neke kolone iz izvorne osobe, ali vam dajući lak pristup atributima ciljne osobe.

JOIN-ovi dolaze u nekoliko varijanti - INNER, LEFT, RIGHT, FULL - ali normalizovani dizajni obično koriste INNER JOIN-ove za osnovne odnose.INNER JOIN zadržava samo redove koji imaju podudaranja na obje strane, što je u skladu s idejom da red relacije uvijek treba referencirati postojeće osobe. Prilikom otklanjanja grešaka ili istraživanja, možete SELECT nekoliko redova iz svake tabele i iz JOIN upita kako biste provjerili da li se model ponaša kako se očekuje.

Ovaj relacijski obrazac pojavljuje se svugdje: korisnici i uloge, kupci i narudžbe, proizvodi i kategorije, objave i komentari.Kada se jednom upoznate s dizajniranjem tabela s primarnim i stranim ključevima te pisanjem JOIN upita, možete modelirati i ispitivati ​​složene domene, a istovremeno koristiti prednosti Pythona za logiku i analizu višeg nivoa.

Sve u svemu, savladavanje SQL-a i Pythona znači razumijevanje ne samo pisanja čistih upita ili skripti, već i interakcije između okruženja za izvršavanje, drajvera, tipova podataka i ograničenja resursa na različitim platformama.Od dijagnosticiranja kriptičnih grešaka Machine Learning Services u SQL Serveru i upravljanja zavisnostima biblioteka u sandbox Python okruženjima, do dizajniranja normaliziranih SQLite shema i orkestriranja end-to-end analitičkih cjevovoda, što se tečnije krećete između baze podataka i koda, to će vaša rješenja za podatke postati robusnija i skalabilnija.