Časté problémy s SQL a Pythonem a jak je řešit

Home » PYTHON » Časté problémy s SQL a Pythonem a jak je řešit

Společná práce s SQL a Pythonem je jednou z nejúčinnějších kombinací v oblasti vývoje dat a backendu., ale také otevírá dveře dlouhému seznamu nenápadných chyb, konfiguračních pastí a překvapení ohledně výkonu. Pokud jste někdy zírali na záhadný traceback, zatímco vaše databázové připojení „by mělo prostě fungovat“, nebo jste se divili, proč stejný analytický skript běží na vašem notebooku bleskově rychle, ale plazí se uvnitř SQL Serveru, nejste sami.

Tato příručka spojuje reálné problémy s SQL a Pythonem, problémy s nízkoúrovňovými službami strojového učení SQL Serveru a praktické vzory pro použití obou jazyků v analytice.Místo vágních rad zde najdete konkrétní příklady, typické chybové zprávy a podrobné nápady na diagnostiku a opravu problémů, a také kompletní návod, jak navrhovat, dotazovat a manipulovat s databázemi v Pythonu pomocí SQLite a dalších enginů.

Běžné problémy s propojením mezi SQL a Pythonem

Jedním z prvních problémů při kombinaci SQL a Pythonu je jednoduše získání stabilního připojení.I když přihlašovací údaje a DSN vypadají správně, malé nesrovnalosti v ovladačích, cestách nebo prostředích mohou způsobit matoucí chyby za běhu v okamžiku spuštění app.py nebo skriptu z příkazového řádku.

Ve virtualizovaných prostředích se to stává křehčímNapříklad můžete spustit SQLite nebo SQL Server uvnitř virtuálního počítače a zároveň vyvíjet v hostitelském operačním systému a testovat připojení pomocí nástroje s grafickým uživatelským rozhraním, jako je SQL Developer nebo SQL Server Management Studio. Grafické uživatelské rozhraní se připojí bez problémů, ale skript v Pythonu selže, protože používá jiný ovladač, chybějící knihovnu nebo zcela jinou síťovou cestu.

Mezi typické problémy s připojením patří chybějící ovladače ODBC/DB API, nesprávná konfigurace DSN, blokované porty a neshodné režimy ověřování.Je velmi běžné vidět, jak Python vyvolává obecné výjimky, jako například „nepodařilo se připojit“, zatímco základní problém spočívá v tom, že systém nemůže načíst sdílenou knihovnu (například libc++ nebo libc++abi v Linuxu) nebo nenajde očekávaný ovladač ODBC pro SQLite, PostgreSQL, MySQL nebo SQL Server.

Při připojování z Pythonu obvykle používáte knihovny jako sqlite3, psycopg2, pyodbc, mysql-connector-python, PyMySQL nebo vrstvu ORM, jako je SQLAlchemy.Každý z nich má svůj vlastní formát připojovacího řetězce, typy chyb a závislosti. Klient s grafickým uživatelským rozhraním může používat jiný stack ovladačů, který tyto problémy skrývá, proto si vždy ověřte, který přesný ovladač a parametry připojení váš kód v Pythonu používá.

Proč je kombinace SQL a Pythonu strategicky účinná

Kromě technických problémů existuje strategický důvod, proč vývojáři a analytici stále trvají na kombinaci Pythonu s SQL.Každý jazyk pokrývá jinou část životního cyklu dat a společně poskytují komplexní pracovní postup, který je těžké srovnat s jediným nástrojem.

SQL je stále standardem pro správu relačních datVyniká v dobře strukturovaných datech, relační integritě, indexování a transakčních úlohách. S SQL získáte rychlé filtrování, spojování a agregaci velkých datových sad, jednotný přístup k mnoha nástrojům a předvídatelný výkon podpořený desítkami let výzkumu databází.

Python zazáří, jakmile data opustí kontext databázeS knihovnami jako pandas, NumPy, matplotlib a seaborn můžete čistit, přetvářet a analyzovat data libovolně složitými způsoby, spouštět statistiky nebo strojové učení a programově vytvářet vizualizace nebo reporty, včetně... analýza dat v reálném časeMnoho transformací, které jsou v SQL nepraktické nebo podrobné, se stávají jednoduchými výrazy v Pythonu.

V praxi to znamená jasnou dělbu práce: co nejvíce filtrování, agregace a základní transformace převést do SQL a poté přenést úhlednou datovou sadu zpět do Pythonu pro náročnou analýzu, modelování nebo vizualizaci. Analytici a inženýři, kteří plynně ovládají oba jazyky, se mohou rychle přesunout od obchodní otázky k reprodukovatelnému datovému kanálu.

Propojení Pythonu s databázemi SQL: knihovny a vzory

Aby SQL a Python spolehlivě spolupracovaly, potřebujete správné konektory a určitou disciplínu v tom, jak otevíráte, používáte a zavíráte databázové relace.Přesný zásobník závisí na databázovém enginu, ale koncepty jsou podobné.

Pro lehké, integrované pracovní postupy je SQLite často nejjednodušší volbou.Python je dodáván s modulem sqlite3 ve standardní knihovně, takže můžete vytvořit databázový soubor, definovat tabulky a spouštět dotazy bez instalace dalšího softwaru. To je ideální pro prototypy, malé analytické projekty nebo výuku relačních konceptů.

Pro databáze serverové úrovně se obvykle používají ovladače specifické pro engine nebo ORM.PostgreSQL se široce používá s psycopg2, SQL Server často používá pyodbc nebo ovladač ODBC od Microsoftu a MySQL/MariaDB se spoléhají na mysql-connector-python nebo PyMySQL. Kromě toho SQLAlchemy poskytuje vrstvu vysoké úrovně abstrakce, která umožňuje psát přenositelné SQL výrazy a spravovat fondy připojení.

Robustní vzor připojení zahrnuje čtení přihlašovacích údajů z proměnných prostředí nebo správce tajných kódů, použití parametrizovaných dotazů k zamezení vkládání chyb a správné ošetření chyb.Po každé pracovní jednotce byste měli explicitně potvrdit nebo vrátit transakce a uvolnit připojení zpět do fondu nebo ho zavřít, místo abyste nechávali otevřené mnoho nečinných relací.

S SQLAlchemy a PANDAS je pracovní postup obzvláště plynulý.: vytvoříte URL připojení, vytvoříte engine a poté pomocí pandas.read_sql_query načtete výsledky dotazu přímo do DataFrame. Odtud máte k dispozici plný výkon ekosystému Pythonu pro čištění, analýzu a export dat.

Služby strojového učení v SQL Serveru: Problémy s integrací R a Pythonu

Microsoft SQL Server obsahuje funkci s názvem Machine Learning Services, která integruje běhové prostředí R a Pythonu do databázového enginu., což vám umožňuje volat externí skripty pomocí sp_execute_external_script. To je sice výkonné pro analýzy v databázi, ale je to spojeno s dlouhým seznamem chyb a omezení specifických pro danou verzi, kterým musíte porozumět.

Problémy s instalací a upgradem jsou obzvláště časté v SQL Serveru 2016, 2017, 2019 a 2022.Problémy sahají od chybějících komponent R na konkrétních imagích virtuálních počítačů Azure, přes neúplné instalační programy Pythonu v dřívějších sestaveních SQL Serveru 2017, až po balíčky kumulativní aktualizace (CU), které se nevyzývají k offline aktualizacím R. V některých případech je nutné na příkazovém řádku předat další parametry, například MRCACHEDIRECTORY, aby se nastavení nasměrovalo na soubory CAB uložené v mezipaměti.

Existují také problémy se závislostmi specifickými pro platformuV sestaveních SQL Serveru 2019 a novějších pro Linux se běhové prostředí R a Pythonu nemusí spustit, protože sdílené knihovny, jako například libc++.so.1 nebo libc++abi.so.1, nejsou v cestě k rozšiřitelným knihovnám k dispozici. Výsledné chyby se v SQL Serveru často zobrazují jako obecné zprávy „Nelze komunikovat s běhovým prostředím“, zatímco protokoly launchpadu odhalují chybějící soubor .so. Opravy obvykle zahrnují kopírování požadovaných sdílených knihoven do souboru /opt/mssql-extensibility/lib nebo zpřístupnění adresářů pomocí souboru mssql.conf.

Na serverech Windows konfigurovaných s nastavením kryptografie FIPS existuje další třída selhání instalace.Pokus o povolení služeb strojového učení nebo jazykových rozšíření může způsobit chyby, že vytváření kontejnerů aplikací AppContainer není kompatibilní s algoritmy ověřenými standardem FIPS pro platformu Windows. Řešením je dočasně zakázat FIPS, dokončit instalaci nebo upgrade a poté znovu povolit FIPS po úplné konfiguraci serveru SQL Server.

Některé kumulativní aktualizace zavádějí přechodné regrese, které ovlivňují provádění skriptů.Například SQL Server 2017 CU verze 5-7 obsahovaly chybu v souboru rlauncher.config, kdy cesta k dočasnému adresáři obsahovala mezery, což způsobovalo selhání R skriptů s chybou „nelze vytvořit R_TempDir“. Pozdější CU tento problém opravily, ale do té doby museli administrátoři znovu registrovat externí skriptovací prostředí pomocí RegisterRExt.exe s příznaky odinstalace a instalace.

Neshody verzí mezi běhovými prostředími klienta a serveru

Dalším opakujícím se zdrojem nejasností je kompatibilita verzí mezi klientskými nástroji (Microsoft R Client nebo balíčky Pythonu) a běhovými prostředími na straně serveru (R Server nebo SQL Server Machine Learning Services).Při spuštění vzdálených skriptů z klienta na starší instanci SQL Serveru může neshoda způsobit explicitní chyby nebo jemné problémy se serializací.

V SQL Server 2016 R Services se musí verze knihoven R klienta a serveru přesně shodovat.Spuštění klienta Microsoft R Client 9.x na serveru s verzí R Server 8.0.3 zobrazuje zprávy, že váš klient je nekompatibilní, a doporučuje instalaci odpovídající verze. Novější verze tento požadavek zmírnily, ale pokud se tyto chyby objeví, musíte ověřit obě strany a buď upgradovat server, nebo nainstalovat kompatibilního klienta.

Serializace a deserializace trénovaných modelů jsou obzvláště citlivé na rozdíly ve verzích.S RevoScaleR v R a revoscalepy v Pythonu se model serializovaný pomocí novějšího API nemusí deserializovat na serveru používajícím starší serializační infrastrukturu, což vede k interním chybám, jako jsou selhání memDecompress v R nebo NameError v Pythonu, když není definována vlastnost rx_unserialize_model. Tyto neshody obvykle vyřeší upgrade instance SQL Serveru alespoň na CU3 pro SQL Server 2017.

Předtrénované modely nainstalované na SQL Serveru 2017 mohou také narazit na omezení délky cesty.Dřívější sestavení ukládala binární soubory modelů do hlubokých adresářových struktur pod výchozí cestou instance a Python nemohl soubory otevřít, protože úplná cesta překračovala limity operačního systému. Mezi navrhované opravy patřila instalace modelů do vlastní kratší cesty, instalace SQL Serveru do kratšího kořenového adresáře nebo dokonce vytvoření pevných odkazů NTFS pomocí fsutil pro zpřístupnění kratšího aliasu stejnému souboru.

Při návrhu řešení pomocí služeb SQL Server Machine Learning Services vždy v rámci plánu nasazení uzamkněte verze a úrovně CU.Rozptýlení skriptů na více serverů s různými úrovněmi CU bez sledování těchto detailů je receptem na obtížně laditelné problémy se serializací a běhovým prostředím později.

Správa zdrojů, výkon a chování při studeném startu

I když jsou služby SQL Server Machine Learning Services správně nainstalovány a verze shodují se s požadavky, můžete dosáhnout výkonnostních limitů kvůli správě zdrojů a sdružování procesů.Pochopení toho, jak se chovají procesy na odpalovacích rampách a satelitech, je klíčem k zajištění konzistentní latence.

SQL Server vytváří pro externí skripty fondy procesů pro jednotlivé uživatele, databáze a jazyky.První volání sp_execute_external_script po určité době nečinnosti způsobí, že launchpad spustí nové satelitní procesy pro R nebo Python. Tento studený start může být znatelně pomalý na silně zatížených serverech nebo virtuálních počítačích s omezeními. Pozdější volání znovu používají zahřátý fond, takže druhé a třetí spuštění jsou mnohem rychlejší.

Pokud je latence prvního volání problémem – například v reálném čase – můžete udržovat fondy v teple pravidelným spouštěním odlehčených skriptů.Mnoho týmů naplánuje jednoduchý „no-op“ skript v jazyce R nebo Python pomocí SQL Agenta, který se spustí každých několik minut, čímž se zabrání tomu, aby úloha čištění v nečinnosti vypnula satelitní procesy.

V SQL Serveru 2016 Enterprise Edition se v raných verzích omezuje externí paměť skriptů na přibližně 20 % celkové RAM.Pro 32GB server to znamenalo, že spustitelné soubory R mohly být omezeny na přibližně 6.4 GB na požadavek. U větších modelů nebo rozsáhlých datových sad se to rychle stává omezením, což vede k chybám v alokaci paměti nebo k významnému stránkování. Administrátoři musí zkontrolovat aktuální výchozí hodnoty a upravit nastavení regulátoru zdrojů, pokud se očekávají složité úlohy strojového učení.

Paralelismus je dalším jemným omezenímKdyž voláte knihovny Microsoft ML nebo RevoScaleR z vnějšku SQL Serveru (např. RGui), i když se jedná o edici Enterprise, tyto knihovny často fungují v režimu s jedním vláknem. Podobně existovaly známé chyby v SQL Serveru 2019, kdy skripty R používající kontexty RxLocalPar nebo základní paralelní balíček mohly způsobit zablokování SQL Serveru kvůli problémům se zápisem na null zařízení v sandboxovém běhovém prostředí.

Omezení datových typů, kódování a schématu při volání externích skriptů

Datové typy a kódování jsou častým zdrojem neočekávaného chování při přenosu SQL dat do R nebo Pythonu pomocí sp_execute_external_script.Ne všechny typy SQL jsou podporovány a některé jsou podporovány pouze částečně nebo jsou tiše převedeny, což může vést ke ztrátě přesnosti nebo poškození řetězců, zejména u složitých struktur, jako je pole v SQL.

Starší CU SQL Serveru 2017 měly silná omezení pro číselné, desetinné a peněžní typy pro výstupní schémata Pythonu.V kombinaci s WITH RESULT SETS a Pythonem nepodporované typy produkovaly chyby SqlSatelliteCall a zprávy označující, že jsou povoleny pouze typy bit, smallint, int, datetime, smallmoney, real a float (plus částečně char/varchar). Pozdější CU to opravily, ale stále je třeba si dávat pozor na to, které datové typy vystavujete externím běhovým prostředím.

Pro skripty R se všechny typy money, numeric, decimal a bigint převádějí na numerický typ R.V důsledku toho mohou hodnoty s vysokou magnitudou nebo hodnoty s mnoha desetinnými místy ztratit přesnost; typy peněz mohou spouštět varování o tom, že hodnoty centů nejsou přesně reprezentovatelné, a bigint překračuje 53bitový celočíselný limit v R, což způsobuje zaokrouhlování nejméně významných bitů.

Kódování řetězců je také důležitéPředávání dat Unicode uložených ve sloupcích varchar může poškodit znaky jiné než ASCII, protože řazení SQL Serveru nemusí odpovídat kódování UTF-8 očekávanému jazykem R nebo Pythonem. Doporučené přístupy jsou použití řazení UTF-8 dostupných v SQL Serveru 2019+ nebo ukládání textu Unicode do sloupce nvarchar a explicitní zpracování konverzí ve skriptu.

Některé funkce SQL jsou pro externí skripty zcela tabu.Dotazy odkazující na sloupce Always Encrypted nebo maskované sloupce nelze v určitých kontextech přímo předat skriptům R; pro analýzu může být nutné zkopírovat chráněná data do dočasných tabulek bez šifrování nebo maskování. Navíc v kontextu výpočetních systémů SQL Serveru nemohou argumenty jako colClasses v jazyce R přepsat typy sloupců; před předáním dat do R je nutné v T-SQL provést CAST nebo CONVERT.

Binární datové části mají také speciální pravidlaPři vrácení nezpracovaného typu R musí být hodnota zahrnuta ve výstupním datovém rámci, nikoli vázána na výstupní parametr. Efektivně je podporována pouze jedna sada nezpracovaných výstupů; pokud potřebujete více binárních výstupů, budete možná muset uloženou proceduru volat několikrát nebo vložit data zpět do SQL přes ODBC ze skriptu.

Praktické problémy při instalaci a rozšiřování Pythonu v SQL Serveru

Instalace a rozšiřování prostředí Python v rámci balíčku SQL Server Machine Learning Services je omezenější než samostatný Anaconda nebo systémový Python.Mnoho uživatelů narazí na chyby při pokusu o přidání balíčků pomocí pip nebo sqlmlutils, zejména ve Windows s SQL Serverem 2019.

Ve Windows je po instalaci SQL Serveru 2019 častým problémem, že pip hlásí problémy s konfigurací TLS/SSL.Hlásí, že modul ssl není k dispozici, i když je zjevné, že Python umíte spustit. Příčinou jsou obvykle chybějící knihovny DLL OpenSSL (libssl-1_1-x64.dll a libcrypto-1_1-x64.dll) v podadresáři DLLs služby PYTHON_SERVICES. Zkopírování těchto souborů ze složky Library\bin do knihoven DLL a následné spuštění nového příkazového řádku obvykle obnoví schopnost pipu vytvářet požadavky HTTPS.

Některé populární balíčky ML, jako například tensorflow, mají nekompatibilní požadavky na závislosti.Kolečko tensorflow může vyžadovat novější verzi NumPy, než je ta, která je předinstalována v prostředí Pythonu SQL Serveru. Protože je NumPy považován za systémový balíček, nelze jej upgradovat pomocí sqlmlutils, takže pokusy o instalaci tensorflow touto cestou selžou. Místo toho je nutné spustit spustitelný soubor PYTHON_SERVICES přímo pomocí -m pip a upgradovat nebo instalovat balíčky v tomto prostředí, někdy po ruční aktualizaci redistribuovatelných běhových prostředí, jako je Microsoft Visual C++.

V Linuxu lze vstupní bod pip, který je součástí balíčku, oddělit ihned po vybalení z krabice.V SQL Serveru 2019 může spuštění příkazu pip z adresáře /opt/mssql/mlservices/runtime/python/bin vést ke zhroucení s chybou interpretu odkazující na neexistující umístění starší verze ML Serveru. Oprava spočívá ve stažení souboru get-pip.py z PyPA a jeho spuštění se správným binárním souborem Pythonu v adresáři /opt/mssql/mlservices/bin/python/python, čímž se efektivně restartuje příkaz pip pro daný běhový modul.

V Pythonových skriptech existují také jemné projevy chování kolem výstupních parametrů varbinary a varchar.Pokud volání sp_execute_external_script zpřístupní výstupní parametr typu varbinary(max) nebo large varchar a v Python skriptu se vám nepodaří přiřadit hodnotu, komponenta BxlServer může vyvolat chyby a přestat fungovat. Bezpečným postupem je explicitně inicializovat tyto parametry v Pythonu, i když je pouze nastavíte na prázdný řetězec nebo 0x0.

Klasický pracovní postup SQL + Python s SQLite

Odklon od specifik SQL Serveru: velmi produktivním způsobem, jak se naučit a vytvořit prototyp integrace SQL-Python, je použití SQLite s modulem sqlite3 v Pythonu.SQLite ukládá data do jednoho souboru, nevyžaduje žádný samostatný serverový proces a chová se jako malá relační databáze s podporou SQL.

V SQLite je databáze pouze organizovaný soubor, který uchovává strukturovaná data na disku.Stejně jako slovník Pythonu mapuje klíče na hodnoty, ale přidává indexování, efektivní úložiště pro velké datové sady a možnosti dotazování. Struktury se točí kolem tabulek (podobně jako tabulky), řádků (záznamů) a sloupců (polí). Ve formálnější relační terminologii se jedná o relace, n-tice a atributy.

Pro začátek se připojíte k databázovému souboru pomocí sqlite3.connect.Pokud soubor neexistuje, SQLite jej vytvoří. Z připojení vytvoříte objekt kurzoru, který funguje jako popisovač pro provádění příkazů SQL a iteraci výsledků. Pracovní postup je analogický s otevřením souboru a čtením řádek po řádku, až na to, že místo čtení prostého textu spouštíte příkazy SQL.

Vytvoření tabulky vyžaduje zadání názvů sloupců a datových typůPřestože je SQLite poměrně flexibilní, co se týče typování, definování typů pomáhá enginu volit efektivní formáty ukládání a strategie indexování. Například jednoduchá tabulka pro skladby může definovat textový název a celočíselný počet přehrání. Jakmile je tabulka vytvořena pomocí CREATE TABLE, můžete vkládat řádky pomocí INSERT a zástupných symbolů parametrů (otazníků) pro bezpečné navázání hodnot Pythonu.

Použití SQL z Pythonu: INSERT, SELECT, UPDATE, DELETE

SQL nabízí čtyři základní operace – INSERT, SELECT, UPDATE a DELETE – které se pěkně mapují na kód Pythonu pracující se SQLite3.Každá operace manipuluje s řádky v tabulce a klauzule WHERE umožňuje cílit na konkrétní záznamy.

INSERT přidá nové záznamy do tabulkyV Pythonu voláte cursor.execute příkazem jako INSERT INTO Songs (title, plays) VALUES (?, ?), který předá n-tici parametrů. Použití zástupných symbolů místo zřetězení řetězců zabraňuje SQL injection a správně zpracovává citace. Po vložení voláte conn.commit pro vyprázdnění změn z transakce do databázového souboru.

SELECT načítá data zpět z databáze, volitelně filtruje a řadí výsledkyJednoduchý název SELECT, přehrává FROM Songs, změní kurzor na iterovatelný objekt přes řádky. U velkých sad výsledků SQLite nenačítá všechny řádky do paměti najednou; místo toho je vrací při iteraci smyčky for. Můžete vybrat všechny sloupce pomocí * nebo zadat podmnožinu a k omezení a řazení záznamů můžete použít WHERE, ORDER BY a LIMIT.

DELETE trvale odstraní řádky na základě podmínky.Příkaz jako DELETE FROM Songs WHERE plays < 100 smaže všechny skladby s nízkým počtem přehrání. Neexistuje možnost vrácení zpět, takže je v tutoriálech běžné mazat řádky na konci skriptu, aby se opakovaně spouštěné příklady staly idempotentními. Pokud chcete, aby se změny zachovaly, musíte po smazání provést commit.

Příkaz UPDATE upraví sloupce v existujících řádcích.Zadáte tabulku, klauzuli SET s novými hodnotami a volitelnou logiku WHERE. Například UPDATE Songs SET plays = 16 WHERE title = 'My Way' ovlivní každý řádek, jehož název odpovídá danému řetězci. Pokud vynecháte WHERE, aktualizujete každý řádek v tabulce, což je často zdrojem nechtěných hromadných změn.

Vytvoření prohledávače Twitteru pomocí SQLite a Pythonu

Praktickou ukázkou kombinace SQL a Pythonu je malý prohledávač Twitteru, který ukládá stav do databáze SQLite.Přestože se API a zásady Twitteru v průběhu času mění, architektonická myšlenka zůstává poučná: chcete procházet vztahy s přáteli, vyhnout se opětovnému navštěvování účtů a zaznamenávat metriky popularity, a to vše při možnosti zastavit a obnovit bez ztráty postupu.

Prohledávač udržuje tabulku účtů na Twitteru a sleduje, zda byl každý z nich načten a kolikrát se objevil jako přítel.Každý řádek obsahuje název účtu, příznak označující, zda jste již načetli jeho seznam přátel, a počítadlo, kolikrát se daný účet objevil mezi „přáteli“ ostatních. To vám umožňuje odhadnout popularitu v rámci vybrané sítě.

Hlavní smyčka vyzve uživatele k zadání úchytu Twitteru nebo příkazu pro ukončení.Pokud uživatel jednoduše stiskne Enter, skript se do databáze dotáže na další účet s hodnotou recovered = 0 a použije ho jako další cíl. Poté zavolá koncový bod Twitteru friends/list, analyzuje odpověď JSON, aktualizuje příznak recovered pro aktuální účet a buď vloží, nebo aktualizuje každého přítele v databázi, přičemž podle potřeby zvyšuje počet jeho čítačů přátel.

Protože je vše uloženo v SQLite, můžete prohledávač ukončit a později ho restartovat.Databáze slouží jako trvalé úložiště front a stavů. Samostatný pomocný skript může vypsat obsah tabulky Twitter, což vám umožní zkontrolovat, které účty jsou známé, které byly navštíveny a kolikrát se který z nich objevil jako přítel. Tento vzorec – uchovávání stavu procházení do relační databáze – se dobře zobecňuje na další úlohy procházení webu nebo API.

Základy modelování dat: primární klíče, cizí klíče a normalizace

Ukládání všech informací z Twitteru do jedné tabulky rychle naráží na problémy se škálovatelností a redundancí.Robustnějším přístupem je normalizace dat oddělením entit (osob) od vztahů (kdo koho sleduje) a jejich propojením pomocí klíčů.

Tabulka osob obvykle používá celočíselný primární klíč jako interní identifikátor.V SQLite můžete deklarovat id INTEGER PRIMARY KEY a engine automaticky vygeneruje jedinečné celé číslo pro každý vložený řádek. Také můžete zahrnout logický klíč, například handle Twitteru, označený jako UNIQUE, aby se zabránilo duplicitám. Logický klíč je to, co používá vnější svět, zatímco primární klíč je to, na co odkazuje váš kód a cizí klíče.

Samostatná tabulka follow pak zachycuje vztahy pomocí cizích klíčů.Každý řádek obsahuje dvojici uživatelských ID, obvykle pojmenovaných from_id a to_id (nebo podobně), která označují, že jedna osoba sleduje druhou. Na kombinaci těchto dvou sloupců můžete deklarovat omezení UNIQUE, které zajistí, že nemůžete omylem vložit stejný vztah dvakrát.

Normalizace – uložení každé informace jednou a její odkazování jinde pomocí klíčů – zabraňuje duplicitě, šetří místo a zlepšuje výkon.Místo ukládání stejného řetězce uživatelského jména do milionů řádků vztahů jej uložíte jednou do tabulky osob a poté na něj odkážete pomocí celočíselných ID. Celá čísla se rychleji porovnávají a indexují, což se ve velkém měřítku stává klíčovým.

V kódu Pythonu vede tento návrh k běžným vzorům pro vkládání nebo načítání uživatelů a vztahů.Před vložením vztahu se musíte ujistit, že oba účastníci existují v tabulce osob: použijete SELECT pomocí logického klíče a pokud není nalezen žádný řádek, použijete INSERT a zaznamenáte lastrowid jako ID nové osoby. Teprve poté použijete INSERT OR IGNORE pro řádek do tabulky follow, který tato ID propojuje. Omezení a OR IGNORE spolupracují, aby vaše data byla konzistentní bez nadměrných ručních kontrol.

Použití JOIN pro sloučení souvisejících tabulek v SQL

Jakmile jsou data rozložena do více normalizovaných tabulek, spoléháte se na SQL JOINy ​​k rekonstrukci potřebného kombinovaného zobrazení.Operace JOIN sloučí řádky ze dvou tabulek na základě shodných hodnot klíčů, čímž pro každou shodu efektivně vytvoří virtuální široký řádek.

V příkladu Twitteru vám propojení tabulek follow a people umožní zjistit, koho konkrétní uživatel sleduje nebo kdo sleduje jeho.Dotaz jako SELECT * FROM Follow JOIN People ON Follow.to_id = People.id WHERE Follow.from_id = 2 načte všechny osoby sledované uživatelem, jehož interní ID je 2. Klauzule JOIN říká databázi, aby pro každý řádek porovnávala Follow.to_id s People.id, a podmínka WHERE omezuje zdrojového uživatele.

Výsledná sada obsahuje sloupce z obou tabulek.Můžete vidět dvě celočíselná ID z tabulky sledování, za nimiž následuje celý řádek osoby (ID, přezdívka, obnovený příznak) z tabulky osob. Pokud uživatel sleduje více účtů, získáte jeden sloučený řádek na vztah, který duplikuje některé sloupce ze zdrojové osoby, ale zároveň vám poskytuje snadný přístup k atributům cílové osoby.

JOINy ​​se vyskytují v několika variantách – INNER, LEFT, RIGHT, FULL – ale normalizované návrhy obvykle používají INNER JOINy ​​pro základní vztahy.INNER JOIN zachovává pouze řádky, které mají shody na obou stranách, což je v souladu s myšlenkou, že řádek vztahu by měl vždy odkazovat na existující osoby. Při ladění nebo prozkoumávání můžete vybrat několik řádků z každé tabulky a z dotazu JOIN, abyste ověřili, zda se model chová podle očekávání.

Tento relační vzorec se objevuje všude: uživatelé a role, zákazníci a objednávky, produkty a kategorie, příspěvky a komentáře.Jakmile se pohodlně naučíte navrhovat tabulky s primárními a cizími klíči a psát dotazy JOIN, můžete modelovat a dotazovat se na složité domény a zároveň stále využívat Python pro logiku a analýzu na vyšší úrovni.

Když to všechno sečteme, zvládnutí SQL a Pythonu znamená nejen pochopení psaní čistých dotazů nebo skriptů, ale také interakce běhových prostředí, ovladačů, datových typů a limitů zdrojů napříč platformami.Od diagnostiky kryptických chyb služeb strojového učení v SQL Serveru a správy závislostí knihoven v sandboxových prostředích Pythonu až po návrh normalizovaných schémat SQLite a orchestraci komplexních analytických kanálů, čím plynulejší budete přecházet mezi databází a kódem, tím robustnější a škálovatelnější budou vaše datová řešení.