Sagedased SQL-i ja Pythoni probleemid ja kuidas nendega toime tulla

Esileht » Python » Sagedased SQL-i ja Pythoni probleemid ja kuidas nendega toime tulla

SQL-i ja Pythoni kooskasutamine on üks võimsamaid kombinatsioone andmete ja taustaprogrammide arendamisel., aga see avab ka ukse pika nimekirja peentest vigadest, konfiguratsioonilõksudest ja jõudlusüllatustest. Kui olete kunagi vahtinud krüptilist tagasijälge, kuigi teie andmebaasiühendus "peaks lihtsalt töötama", või mõelnud, miks sama analüütiline skript töötab teie sülearvutis välkkiirelt, aga roomab SQL Serveris, siis pole te üksi.

See juhend koondab reaalse maailma SQL-Pythoni probleeme, SQL Serveri masinõppe teenuste madala taseme probleeme ja praktilisi mustreid mõlema keele kasutamiseks analüütikas.Ebamääraste nõuannete asemel leiate konkreetseid näiteid, tüüpilisi veateateid ja samm-sammult ideid probleemide diagnoosimiseks ja parandamiseks, lisaks täieliku ülevaate sellest, kuidas Pythonis andmebaase kujundada, päringuid esitada ja nendega manipuleerida, kasutades SQLite'i ja teisi mootoreid.

Levinud ühendusprobleemid SQL-i ja Pythoni vahel

Üks esimesi probleeme SQL-i ja Pythoni segamisel on lihtsalt stabiilse ühenduse saamine.Isegi kui volikirjad ja DSN-id näivad õiged olevat, võivad väikesed mittevastavused draiverites, radades või keskkondades põhjustada segadust tekitavaid käitusaja vigu hetkel, kui käivitate oma app.py või käivitate skripti käsurealt.

Virtualiseeritud keskkondades muutub see hapramaksNäiteks võite käivitada SQLite'i või SQL Serveri virtuaalmasinas, arendades samal ajal host-operatsioonisüsteemis ja testides ühendust GUI-tööriistaga nagu SQL Developer või SQL Server Management Studio. GUI loob ühenduse, kuid Pythoni skript ebaõnnestub, kuna see kasutab erinevat draiverit, puuduvat teeki või täiesti teist võrguteed.

Tüüpiliste ühendusprobleemide hulka kuuluvad puuduvad ODBC/DB API draiverid, vale DSN-i konfiguratsioon, blokeeritud pordid ja sobimatud autentimisrežiimid.On väga tavaline, et Python annab üldiseid erandeid, näiteks „ühendumist ei õnnestunud“, samas kui probleemi aluseks on see, et süsteem ei saa laadida jagatud teeki (näiteks libc++ või libc++abi Linuxis) või ei leia SQLite'i, PostgreSQL-i, MySQL-i või SQL Serveri jaoks oodatud ODBC-draiverit.

Pythonist ühenduse loomisel kasutatakse tavaliselt selliseid teeke nagu sqlite3, psycopg2, pyodbc, mysql-connector-python, PyMySQL või ORM-kihti nagu SQLAlchemy.Igal neist on oma ühendusstringi vorming, veatüübid ja sõltuvused. GUI-klient võib kasutada erinevat draiveripinu, mis neid probleeme peidab, seega kontrollige alati, millist täpset draiverit ja ühenduse parameetreid teie Pythoni kood kasutab.

Miks SQL-i ja Pythoni ühendamine on strateegiliselt võimas

Lisaks tehnilistele peavaludele on olemas strateegiline põhjus, miks arendajad ja analüütikud nõuavad jätkuvalt Pythoni ja SQL-i ühendamist.Iga keel hõlmab andmete elutsükli erinevat osa ja koos pakuvad need terviklikku töövoogu, mida on ühe tööriistaga raske sobitada.

SQL on endiselt relatsioonandmete haldamise standardSee paistab silma hästi struktureeritud andmete, relatsioonilise terviklikkuse, indekseerimise ja tehingute töökoormusega. SQL-iga saate kiire filtreerimise, ühendamise ja koondamise suurte andmekogumite puhul, ühtse juurdepääsu paljudele tööriistadele ja prognoositava jõudluse, mida toetab aastakümnete pikkune andmebaaside uurimine.

Python särab, kui andmed lahkuvad andmebaasi kontekstistSelliste teekidega nagu pandas, NumPy, matplotlib ja seaborn saate andmeid puhastada, ümber kujundada ja analüüsida meelevaldselt keerukatel viisidel, käivitada statistikat või masinõpet ning luua programmiliselt visualiseeringuid või aruandeid, sealhulgas andmete reaalajas analüüsPaljud SQL-is kohmakad või pikalt loetavad teisendused muutuvad lihtsateks Pythoni avaldisteks.

Praktikas tähendab see selget tööjaotust.: suruda SQL-i alla nii palju filtreerimist, agregeerimist ja põhilist teisendust kui võimalik ning seejärel tuua korralik andmestik tagasi Pythonisse põhjaliku analüüsi, modelleerimise või visualiseerimise jaoks. Analüütikud ja insenerid, kes valdavad mõlemat keelt, saavad kiiresti liikuda äriküsimuselt reprodutseeritava andmevoo juurde.

Pythoni ühendamine SQL-andmebaasidega: teegid ja mustrid

SQL-i ja Pythoni usaldusväärseks koostööks on vaja õigeid ühenduslülisid ja distsipliini andmebaasiseansside avamise, kasutamise ja sulgemise osas.Täpne pinu suurus sõltub andmebaasimootorist, kuid kontseptsioonid on sarnased.

Kergete, manustatud töövoogude jaoks on SQLite sageli lihtsaim valik.Pythoni standardteegis on kaasas sqlite3 moodul, nii et saate luua andmebaasifaili, defineerida tabeleid ja käivitada päringuid ilma lisatarkvara installimata. See sobib ideaalselt prototüüpide, väikeste analüüsiprojektide või relatsiooniliste kontseptsioonide õpetamiseks.

Serveriklassi andmebaaside puhul kasutatakse tavaliselt mootorispetsiifilisi draivereid või ORM-iPostgreSQL-i kasutatakse laialdaselt koos psycopg2-ga, SQL Server kasutab sageli pyodbc-d või Microsofti ODBC-draiverit ning MySQL/MariaDB tugineb mysql-connector-pythonile või PyMySQL-ile. Lisaks sellele pakub SQLAlchemy kõrgetasemelist abstraktsioonikihti, mis võimaldab kirjutada kaasaskantavaid SQL-avaldisi ja hallata ühendusvarusid.

Tugev ühendusmuster hõlmab keskkonnamuutujatest või salasõnade haldurist mandaatide lugemist, parameetriliste päringute kasutamist süstimise vältimiseks ja korraliku veakäsitluse rakendamist.Pärast iga tööüksust peaksite tehingud selgesõnaliselt kinnitama või tagasi võtma ja ühenduse basseiniga uuesti vabastama või selle sulgema, selle asemel, et hoida palju jõudeolevaid seansse avatuna.

SQLAlchemy ja pandadega muutub töövoog eriti sujuvaksSa lood ühenduse URL-i, lood mootori ja seejärel kasutad pandas.read_sql_query päringu tulemuste otse DataFrame'i toomiseks. Sealt edasi on sul Pythoni ökosüsteemi täielik võimsus andmete puhastamiseks, analüüsimiseks ja eksportimiseks.

Masinõppe teenused SQL Serveris: R ja Pythoni integratsiooniprobleemid

Microsoft SQL Server sisaldab funktsiooni nimega Machine Learning Services, mis manustab R ja Pythoni käituskeskkonnad andmebaasimootorisse., mis võimaldab teil kutsuda väliseid skripte sp_execute_external_script kaudu. See on võimas andmebaasisisese analüüsi jaoks, kuid sellega kaasneb pikk nimekiri versioonipõhistest vigadest ja piirangutest, millest peate aru saama.

Installi- ja versiooniuuendusprobleemid on eriti sagedased SQL Server 2016, 2017, 2019 ja 2022 puhul.Probleemid ulatuvad puuduvatest R-komponentidest teatud Azure'i virtuaalmasinate kujutistes kuni mittetäielike Pythoni installijateni varajastes SQL Server 2017 järkudes ja CU (kumulatiivsete värskenduste) pakettideni, mis ei küsi võrguühenduseta R-värskenduste kohta. Mõnel juhul peate käsureal edastama täiendavaid parameetreid, näiteks MRCACHEDIRECTORY, et suunata installiprogramm vahemällu salvestatud CAB-failidele.

Samuti on platvormispetsiifilisi sõltuvusprobleemeSQL Server 2019 ja uuemate Linuxi versioonide puhul võivad R-i ja Pythoni käituskeskkonnad ebaõnnestuda, kuna jagatud teegid, näiteks libc++.so.1 või libc++abi.so.1, pole laiendatavuse teegi teel saadaval. Sellest tulenevad vead ilmuvad SQL Serveris sageli üldiste teadetena „Ei saa käituskeskkonnaga suhelda”, samas kui käivitusriba logid näitavad puuduvat .so-faili. Parandused hõlmavad tavaliselt vajalike jagatud teekide kopeerimist kausta /opt/mssql-extensibility/lib või kataloogide avaldamist mssql.conf kaudu.

FIPS-krüptograafiasätetega konfigureeritud Windowsi serverites esineb veel üks installitõrke klassMasinõppe teenuste või keelelaiendite lubamine võib põhjustada vigu, mis viitavad AppContainer'i loomise mitteühilduvusele Windowsi platvormi FIPS-i valideeritud algoritmidega. Lahendus on FIPS-i ajutine keelamine, installimise või versiooniuuenduse lõpuleviimine ja seejärel FIPS-i uuesti lubamine pärast SQL Serveri täielikku konfigureerimist.

Mõned kumulatiivsed värskendused toovad kaasa ajutisi regressioone, mis mõjutavad skripti käivitamistNäiteks sisaldasid SQL Server 2017 värskendused 5–7 vea failis rlauncher.config, kus ajutise kataloogi tee sisaldas tühikuid, mistõttu R-skriptid ebaõnnestusid veateatega „cannot create R_TempDir“. Hilisemad värskendused parandasid selle, kuid seni pidid administraatorid välise skriptimiskeskkonna uuesti registreerima, kasutades RegisterRExt.exe koos desinstalli- ja installimislippudega.

Kliendi ja serveri käituskeskkonna versioonide mittevastavus

Teine korduv segaduse allikas on klienditööriistade (Microsoft R Client või Pythoni paketid) ja serveripoolsete käituskeskkondade (R Server või SQL Server Machine Learning Services) versioonide ühilduvus.Kui käivitate kliendilt kaugskripte vanema SQL Serveri eksemplari vastu, võib mittevastavus põhjustada otseseid vigu või peeneid serialiseerimisprobleeme.

SQL Server 2016 R teenustes peavad kliendi ja serveri R teegi versioonid täpselt ühtimaMicrosoft R Client 9.x käitamine serveris, kus on R Server 8.0.3, annab teateid, et teie klient on ühildumatu ja soovitab teil installida sobiva versiooni. Hilisemad versioonid leevendasid seda nõuet, kuid kui näete neid vigu, peate kontrollima mõlemat poolt ja kas serverit uuendama või ühilduva kliendi installima.

Treenitud mudelite serialiseerimine ja deserialiseerimine on versioonierinevuste suhtes eriti tundlikud.RevoScaleR-i kasutamisel R-is ja revoscalepy kasutamisel Pythonis ei pruugi uuema API-ga serialiseeritud mudeli deserialiseerimine vanemat serialiseerimisinfrastruktuuri kasutavas serveris ebaõnnestuda, mille tulemuseks on sisemised vead, näiteks memDecompressi tõrked R-is või NameError Pythonis, kui rx_unserialize_model pole määratletud. SQL Serveri eksemplari uuendamine vähemalt versioonile CU3 SQL Server 2017 jaoks lahendab tavaliselt need ebakõlad.

SQL Server 2017-le installitud eelkoolitatud mudelid võivad samuti tabada tee pikkuse piiranguidVarased versioonid salvestasid mudeli binaarfaile sügavatesse kataloogistruktuuridesse vaikimisi eksemplari tee alla ja Python ei saanud faile avada, kuna täielik tee ületas operatsioonisüsteemi piiranguid. Soovitatud paranduste hulka kuulusid mudelite installimine kohandatud lühemale teele, SQL Serveri installimine lühemasse juurkataloogi või isegi NTFS-i kõvalinkide loomine fsutil'iga, et avaldada samale failile lühem alias.

SQL Serveri masinõppe teenuste abil lahenduse loomisel tuleb juurutusplaani osana alati kindlaks määrata versioonid ja CU tasemed.Skriptide levitamine mitme erineva CU-tasemega serveri vahel ilma neid üksikasju jälgimata on retsept hilisemateks serialiseerimise ja käitusaja probleemideks, mida on raske siluda.

Ressursside haldamine, jõudlus ja külmkäivituse käitumine

Isegi kui SQL Serveri masinõppe teenused on õigesti installitud ja versiooniga sobitatud, võib ressursside haldamise ja protsesside koondamise tõttu jõudluse ülempiirini jõuda.Järjepideva latentsuse tagamiseks on võtmetähtsusega mõista, kuidas stardiplatvormi ja satelliidi protsessid käituvad.

SQL Server loob väliste skriptide jaoks kasutaja-, andmebaasi- ja keelepõhised protsesside kogumidEsimene kutse funktsioonile sp_execute_external_script pärast teatud mitteaktiivsuse perioodi käivitab Launchpadis uued satelliitprotsessid R-i või Pythoni jaoks. See külmkäivitus võib olla märgatavalt aeglane tugevalt koormatud serverites või piiratud virtuaalmasinates. Hilisemad kutseprotsessid kasutavad soojendatud kogumit uuesti, seega on teine ​​ja kolmas käivitamine palju kiiremad.

Kui esimese kõne latentsus on probleemiks – näiteks reaalajas hindamisstsenaariumides – saate basseinid soojana hoida, käivitades perioodiliselt kergeid skripte.Paljud meeskonnad ajastavad SQL Agendi kaudu iga paari minuti järel käivituma lihtsa „no-op” R- või Pythoni skripti, mis takistab jõudeoleku puhastusülesandel satelliitprotsesse sulgemast.

SQL Server 2016 Enterprise Editionis ehitatakse varakult piiratud väline skriptimälu umbes 20%-ni kogu RAM-ist.32 GB serveri puhul tähendas see, et R-i käivitatavate failide maht võidi piirata umbes 6.4 GB-ni päringu kohta. Suuremate mudelite või laiaulatuslike andmekogumite puhul muutub see kiiresti piiranguks, mis viib mälu eraldamise vigadeni või märkimisväärse lehekülgimise tekkeni. Administraatorid peavad üle vaatama praegused vaikesätted ja kohandama ressursihalduri seadeid, kui on oodata keerulisi masinõppe töökoormusi.

Paralleelisus on veel üks peen piirangKui kutsute Microsofti ML-i või RevoScaleR-i teeke väljastpoolt SQL Serverit (nt RGui), isegi kui aluseks olev väljaanne on Enterprise, töötavad need teegid sageli ühelõimelises režiimis. Samuti oli SQL Server 2019-s teadaolevaid vigu, kus RxLocalPar-kontekste või baasparalleelpaketti kasutavad R-skriptid võisid põhjustada SQL Serveri hangumise probleemide tõttu, mis tekkisid liivakastikeskkonnas nullseadmesse kirjutamisel.

Andmetüübi, kodeerimise ja skeemi piirangud väliste skriptide kutsumisel

Andmetüübid ja kodeeringud on SQL-andmete R-i või Pythonisse sp_execute_external_script kaudu edastamisel sageli ootamatu käitumise allikaks.Kõiki SQL-tüüpe ei toetata ja mõned on toetatud ainult osaliselt või teisendatakse vaikselt, mis võib põhjustada täpsuse kadu või vigastatud stringe, eriti keerukate struktuuride puhul, näiteks massiivid SQL-is.

Varasematel SQL Server 2017 kumulatiivsetel versioonidel olid Pythoni väljundskeemide numbriliste, kümnend- ja rahatüüpide osas ranged piirangud.Funktsioonidega WITH RESULT SETS ja Python kombineerituna tekitasid toetamata tüübid SqlSatelliteCall vigu ja teateid, mis näitasid, et lubatud on ainult bit, smallint, int, datetime, smallmoney, real ja float (lisaks osaliselt char/varchar). Hilisemad klienditoe uuendused parandasid selle, kuid peate siiski olema teadlik, milliseid andmetüüpe välistele käituskeskkondadele avaldate.

R-skriptide puhul teisendatakse nii raha-, numbri-, kümnend- kui ka bigint-tüüpi tüübid R-i numbritüübiks.Selle tagajärjel võivad suure suurusega või paljude kümnendkohtadega väärtused kaotada täpsuse; rahatüübid võivad käivitada hoiatusi sentide väärtuste ebatäpse esitamise kohta ja bigint ületab R-i 53-bitise täisarvu piiri, põhjustades ümardamist kõige vähem olulistes bittides.

Ka stringikodeeringud on olulisedVarchar-veergudes talletatud Unicode'i andmete edastamine võib rikkuda mitte-ASCII-märke, kuna SQL Serveri sortimised ei pruugi vastata R-i või Pythoni oodatavale UTF-8 kodeeringule. Soovitatavad lähenemisviisid on kasutada SQL Server 2019+-s saadaolevaid UTF-8 sortimisi või salvestada Unicode'i tekst nvarchar-vormingusse ja käsitleda teisendusi otseselt skriptis.

Mõned SQL-i funktsioonid on väliste skriptide jaoks täielikult keelatud.Always Encrypted veergudele või maskeeritud veergudele viitavaid päringuid ei saa teatud kontekstides otse R-skriptidele edastada; analüüsimiseks peate võib-olla kopeerima kaitstud andmed ajutistesse tabelitesse ilma krüptimise või maskeerimiseta. Lisaks ei saa SQL Serveri arvutuskontekstis R-i argumendid (nt colClasses) veerutüüpe alistada; enne andmete R-i edastamist peate T-SQL-is tegema CAST või CONVERT toimingu.

Binaarsetel kasulikel koormustel on ka erireeglidR-i toortüübi tagastamisel tuleb väärtus lisada väljundandmete raami, mitte siduda väljundparameetriga. Tegelikult toetatakse ainult ühte toorväljundite komplekti; kui vajate mitut binaarväljundit, peate võib-olla salvestatud protseduuri mitu korda kutsuma või andmed skripti seest ODBC kaudu SQL-i tagasi saatma.

Praktilised probleemid Pythoni installimisel ja laiendamisel SQL Serveris

SQL Serveri masinõppe teenustega komplekteeritud Pythoni keskkonna installimine ja laiendamine on piiratum kui eraldiseisva Anaconda või süsteemi Python.Paljud kasutajad satuvad pip-i või sqlmlutils-iga pakettide lisamisel vigadesse, eriti Windowsis, kus on SQL Server 2019.

Windowsis on pärast SQL Server 2019 installimist sageli probleemiks see, et pip annab teada TLS/SSL konfiguratsiooniprobleemidest.See kurdab, et ssl-moodul pole saadaval, kuigi Pythoni käivitamine on ilmselgelt võimalik. Tavaliselt on põhjuseks puuduvad OpenSSL-i DLL-id (libssl-1_1-x64.dll ja libcrypto-1_1-x64.dll) PYTHON_SERVICES-i DLL-ide alamkataloogis. Nende failide kopeerimine kaustast Library\bin DLL-idesse ja seejärel uue käsurea käivitamine taastab tavaliselt pip-i võime teha HTTPS-päringuid.

Mõnedel populaarsetel masinõppepakettidel, näiteks tensorflow'l, on ühildumatud sõltuvusnõudedTensorflow ratas võib vajada uuemat NumPy versiooni kui see, mis on SQL Serveri Pythoni keskkonda eelinstallitud. Kuna NumPy-d käsitletakse süsteemipaketina, ei saa te seda sqlmlutilsi kaudu uuendada, seega Tensorflow installimise katsed selle marsruudi kaudu ebaõnnestuvad. Selle asemel peate PYTHON_SERVICES käivitatava faili otse käivitama käsuga -m pip ja uuendama või installima pakette selles keskkonnas, mõnikord pärast levitatavate käituskeskkondade (nt Microsoft Visual C++) käsitsi värskendamist.

Linuxis saab komplekteeritud pip-sisenemispunkti kohe karbist võttes lahti murdaSQL Server 2019 puhul võib pip-käsu käivitamine kaustast /opt/mssql/mlservices/runtime/python/bin krahhida ja põhjustada vigase interpretaatori vea, mis osutab olematule pärand-ML Serveri asukohale. Lahendus on get-pip.py allalaadimine PyPA-st ja selle käivitamine õige Pythoni binaarfailiga kaustas /opt/mssql/mlservices/bin/python/python, mis käivitab pip-käsu selle käituskeskkonna jaoks sisuliselt uuesti.

Pythoni skriptides esineb ka varbinaarsete ja varchar-väljundparameetrite puhul peeneid käitumismustreid.Kui teie sp_execute_external_script kutse avaldab OUTPUT-parameetri tüübiga varbinary(max) või suure varchar-iga ja te ei määra Pythoni skriptis väärtust, võib BxlServer komponent tekitada vigu ja lakata töötamast. Turvaline muster on need parameetrid Pythoni koodis selgesõnaliselt initsialiseerida, isegi kui määrate neile lihtsalt tühja stringi või väärtuse 0x0.

Klassikaline SQL + Pythoni töövoog SQLite'iga

SQL Serveri eripäradest eemaldudes on väga produktiivne viis SQL-Pythoni integratsiooni õppimiseks ja prototüübi loomiseks SQLite'i kasutamine Pythoni sqlite3 mooduliga.SQLite salvestab andmeid ühte faili, ei vaja eraldi serveriprotsessi ja käitub nagu väike SQL-toega relatsioonandmebaas.

SQLite'is on andmebaas lihtsalt korrastatud fail, mis säilitab struktureeritud andmeid kettal.Nagu Pythoni sõnastik, seob see võtmed väärtustega, kuid lisab indekseerimise, tõhusa salvestusruumi suurte andmekogumite jaoks ja päringuvõimalused. Struktuurid keerlevad tabelite (sarnaselt arvutustabelitega), ridade (kirjete) ja veergude (väljade) ümber. Formaalsemas relatsiooniterminoloogias on need relatsioonid, tuupled ja atribuudid.

Alustamiseks looge ühendus andmebaasifailiga käsuga sqlite3.connectKui faili pole olemas, loob SQLite selle. Ühendusest luuakse kursoriobjekt, mis toimib käepidemena SQL-käskude täitmiseks ja tulemuste üle itereerimiseks. Töövoog on analoogne faili avamise ja rida-realt lugemisega, välja arvatud see, et lihtteksti lugemise asemel täidetakse SQL-lauseid.

Tabeli loomine nõuab veerunimede ja andmetüüpide määramistKuigi SQLite on tüüpimise osas üsna paindlik, aitab tüüpide defineerimine mootoril valida tõhusaid salvestusvorminguid ja indekseerimisstrateegiaid. Näiteks saab lihtsas laulude tabelis määratleda tekstipealkirja ja täisarvulise esituskordade arvu. Kui tabel on CREATE TABLE abil loodud, saate ridu lisada, kasutades INSERT-i ja parameetrite kohahoidjaid (küsimärke), et Pythoni väärtusi turvaliselt siduda.

Pythoni SQL-i kasutamine: INSERT, SELECT, UPDATE, DELETE

SQL pakub nelja põhioperatsiooni – INSERT, SELECT, UPDATE ja DELETE –, mis sobivad hästi sqlite3-ga töötava Pythoni koodiga.Iga toiming manipuleerib tabeli ridadega ja WHERE-klausel võimaldab teil sihtida konkreetseid kirjeid.

INSERT lisab tabelisse uusi kirjeidPythonis kutsutakse cursor.execute välja lausega nagu INSERT INTO Songs(title, plays) VALUES(?, ?), edastades parameetrite tuupli. Kohahoidjate kasutamine stringide liitmise asemel väldib SQL-süstimist ja käsitleb jutumärkide kasutamist õigesti. Pärast lisamist kutsutakse välja conn.commit, et muudatused tehingust andmebaasifaili salvestada.

SELECT loeb andmeid andmebaasist tagasi, valikuliselt filtreerides ja järjestades tulemusiLihtne SELECT-pealkiri, mis esitab FROM Songs'i, muudab kursori ridade kaupa itereeritavaks objektiks. Suurte tulemuste komplektide puhul ei lae SQLite kõiki ridu korraga mällu, vaid annab need for-tsükli iteratsiooni käigus. Saate valida kõik veerud tärniga (*) või määrata alamhulga ning kirjete piiramiseks ja sortimiseks saate kasutada funktsioone WHERE, ORDER BY ja LIMIT.

Kustuta read jäädavalt tingimuse aluselSelline käsk nagu DELETE FROM Songs WHERE plays < 100 kustutab kõik madala esituskorraga lood. Tagasivõtmise võimalust pole, seega on õpetustes tavaline, et skripti lõpus olevad read kustutatakse, et muuta taaskäivitatud näited idempotentseks. Kui soovite, et muudatused säiliksid, peate pärast kustutamist tegema commit'i.

UPDATE muudab olemasolevate ridade veergeSa määrad tabeli, SET-klausli uute väärtustega ja valikulise WHERE-loogika. Näiteks UPDATE Songs SET plays = 16 WHERE title = 'My Way' mõjutab kõiki ridu, mille pealkiri vastab sellele stringile. Kui jätad WHERE välja, uuendatakse kõiki tabeli ridu, mis on sageli juhuslike hulgimuudatuste allikas.

Twitteri indekseerija loomine SQLite'i ja Pythoni abil

SQL-i ja Pythoni kombineerimise praktiline demonstratsioon on väike Twitteri indekseerija, mis salvestab oleku SQLite'i andmebaasi.Kuigi Twitteri API-d ja poliitikad aja jooksul muutuvad, jääb arhitektuuriline idee õpetlikuks: soovite jälgida sõprussuhteid, vältida kontode uuesti külastamist ja jäädvustada populaarsuse näitajaid, olles samal ajal võimeline peatama ja jätkama ilma edusamme kaotamata.

Roomik peab Twitteri kontode tabelit ja jälgib, kas igaüks neist on alla laaditud ja mitu korda see sõbrana kuvatakse.Igas reas on konto nimi, lipp, mis näitab, kas olete juba selle sõbraloendi alla laadinud, ja loendur, mis näitab, mitu korda see konto teiste „sõprade” hulgas esines. See võimaldab teil hinnata populaarsust valimisse kuuluvas võrgustikus.

Põhitsükkel küsib kasutajalt Twitteri kasutajanime või käsku „quit” (väljapääs).Kui kasutaja lihtsalt vajutab sisestusklahvi (Enter), pärib skript andmebaasist järgmise konto, mille taastatud väärtus on 0, ja kasutab seda järgmise sihtmärgina. Seejärel kutsub see Twitteri sõprade/loendi lõpp-punkti, parsib JSON-vastuse, värskendab praeguse konto taastatud lippu ja lisab või värskendab iga sõpra andmebaasis, suurendades vajadusel nende sõprade loendureid.

Kuna kõik on salvestatud SQLite'i, saate indekseerija sulgeda ja hiljem taaskäivitada.Andmebaas toimib püsiva järjekorra ja oleku salvestusruumina. Eraldi abiskript saab Twitteri tabeli sisu välja võtta, võimaldades teil kontrollida, millised kontod on teada, milliseid on külastatud ja mitu korda igaüks on sõbrana esinenud. See muster – indekseerimise oleku püsimine relatsioonandmebaasis – üldistub hästi ka teistele veebi- või API-indekseerimisülesannetele.

Andmete modelleerimise põhitõed: primaarvõtmed, võõrvõtmed ja normaliseerimine

Kogu Twitteri teabe ühte tabelisse salvestamine tekitab kiiresti skaleeritavuse ja koondamisprobleeme.Tugevam lähenemisviis on andmete normaliseerimine, eraldades üksused (inimesed) suhetest (kes jälgib keda) ja sidudes need võtmete abil.

Inimeste tabel kasutab sisemise identifikaatorina tavaliselt täisarvulist primaarvõtit.SQLite'is saab deklareerida id INTEGER PRIMARY KEY ja mootor genereerib iga sisestatud rea jaoks automaatselt unikaalse täisarvu. Samuti tuleb lisada loogiline võti, näiteks Twitteri käepide, mis on märgitud unikaalseks, et vältida duplikaate. Loogiline võti on see, mida kasutab välismaailm, samas kui primaarvõti on see, millele viitab teie kood ja võõrvõtmed.

Seejärel jäädvustab eraldi jälgimistabel seosed võõrvõtmete abil.Iga rida sisaldab kasutaja ID-de paari, tavaliselt nimega from_id ja to_id (või sarnaseid), mis näitavad, et üks inimene järgneb teisele. Nende kahe veeru kombinatsioonile saab deklareerida UNIQUE piirangu, mis tagab, et sama seost ei saa kogemata kaks korda sisestada.

Normaliseerimine – iga teabe üks kord salvestamine ja sellele mujal võtmete abil viitamine – väldib dubleerimist, säästab ruumi ja parandab jõudlust.Selle asemel, et salvestada sama kasutajanime stringi miljonites suhte ridades, salvestate selle üks kord inimeste tabelisse ja seejärel osutate sellele täisarvuliste ID-de kaudu. Täisarvude võrdlemine ja indekseerimine on kiirem, mis muutub mastaapseldamisel ülioluliseks.

Pythoni koodis viib see disain tavaliste mustriteni kasutajate ja suhete sisestamiseks või otsimiseks.Enne seose lisamist peate veenduma, et mõlemad osalejad on inimeste tabelis olemas: te valite loogilise võtme abil ja kui rida ei leita, siis lisate INSERTi ning jäädvustate viimase rea ID uue isiku ID-na. Alles seejärel lisate või ignoreerite järgmisesse tabelisse rea, mis seob need ID-d. Piirangud ja OR IGNORE toimivad koos, et hoida teie andmed järjepidevad ilma liigsete käsitsi kontrollideta.

JOIN-i kasutamine seotud tabelite ühendamiseks SQL-is

Kui andmed on jaotatud mitme normaliseeritud tabeli vahel, tuginete vajaliku kombineeritud vaate rekonstrueerimiseks SQL JOIN-idele.JOIN ühendab kahe tabeli read vastavate võtmeväärtuste põhjal, luues iga vaste jaoks virtuaalse laia rea.

Twitteri näites võimaldab jälgimis- ja inimeste tabelite liitmine näha, keda konkreetne kasutaja jälgib või kes teda jälgib.Päring nagu SELECT * FROM Follow JOIN People ON Follow.to_id = People.id WHERE Follow.from_id = 2 leiab kõik inimesed, keda jälgib kasutaja, kelle sisemine ID on 2. JOIN-klausel käsib andmebaasil sobitada Follow.to_id People.id-ga iga rea ​​puhul ning WHERE-tingimus piirab allikakasutajat.

Tulemuste komplekt sisaldab veerge mõlemast tabelistVõite näha järgneva tabeli kahte täisarvulist ID-d, millele järgneb inimeste tabelist täielik isiku rida (ID, käepide, taastatud lipp). Kui kasutaja jälgib mitut kontot, saate iga seose kohta ühe kombineeritud rea, mis dubleerib mõned veerud lähteisikult, kuid annab teile hõlpsa juurdepääsu sihtisiku atribuutidele.

JOIN-e on mitut tüüpi – INNER (sisemine), LEFT (vasak), RIGHT (parem), FULL (täis) –, kuid normaliseeritud disainilahendused kasutavad tavaliselt INNER JOIN-e põhisuhete jaoks.. INNER JOIN säilitab ainult read, millel on vasted mõlemal poolel, mis on kooskõlas ideega, et seose rida peaks alati viitama olemasolevatele inimestele. Silumisel või uurimisel saate valida igast tabelist ja JOIN-päringust paar rida, et kontrollida, kas mudel käitub ootuspäraselt.

See suhteline muster esineb kõikjal: kasutajates ja rollides, klientides ja tellimustes, toodetes ja kategooriates, postitustes ja kommentaarides.Kui olete mugav primaar- ja võõrvõtmetega tabelite kujundamisel ning JOIN-päringute kirjutamisel, saate modelleerida ja päringuid teha keerukates domeenides, kasutades samal ajal Pythoni eeliseid kõrgema taseme loogika ja analüüsi jaoks.

Kokkuvõttes tähendab SQL-i ja Pythoni valdamine mitte ainult seda, kuidas kirjutada selgeid päringuid või skripte, vaid ka seda, kuidas käituskeskkonnad, draiverid, andmetüübid ja ressursipiirangud platvormide vahel omavahel suhtlevad.Alates krüptiliste masinõppeteenuste vigade diagnoosimisest SQL Serveris ja teekide sõltuvuste haldamisest liivakastis Pythoni keskkondades kuni normaliseeritud SQLite'i skeemide kujundamise ja otsast lõpuni analüüsitorustike orkestreerimiseni – mida sujuvamalt te andmebaasi ja koodi vahel liigute, seda töökindlamaks ja skaleeritavamaks muutuvad teie andmelahendused.