Често срещани проблеми със SQL и Python и как да се справите с тях

Начало » Питон » Често срещани проблеми със SQL и Python и как да се справите с тях

Работата със SQL и Python заедно е една от най-мощните комбинации в разработката на данни и backend., но също така отваря вратата към дълъг списък от фини грешки, капани за конфигурация и изненади в производителността. Ако някога сте се взирали в загадъчна обратна връзка, докато връзката ви с базата данни „просто би трябвало да работи“, или сте се чудили защо един и същ аналитичен скрипт работи светкавично бързо на вашия лаптоп, но пълзи в SQL Server, не сте сами.

Това ръководство обединява реални проблеми с SQL-Python, проблеми с ниско ниво на услугите за машинно обучение на SQL Server и практически модели за използване на двата езика в анализите.Вместо неясни съвети, ще намерите конкретни примери, типични съобщения за грешки и подробни идеи за диагностициране и отстраняване на проблеми, както и пълно ръководство за това как да проектирате, заявявате и манипулирате бази данни в Python, използвайки SQLite и други двигатели.

Често срещани проблеми с връзката между SQL и Python

Една от първите трудности при смесването на SQL и Python е просто получаването на стабилна връзка.Дори когато идентификационните данни и DSN изглеждат правилни, малки несъответствия в драйверите, пътищата или средите могат да предизвикат объркващи грешки по време на изпълнение в момента, в който стартирате app.py или изпълните скрипт от командния ред.

Във виртуализирани среди това става по-крехкоНапример, може да стартирате SQLite или SQL Server във виртуална машина, докато разработвате на хост операционната система и тествате връзката с GUI инструмент като SQL Developer или SQL Server Management Studio. GUI се свързва добре, но Python скриптът се проваля, защото използва различен драйвер, липсваща библиотека или изцяло друг мрежов път.

Типичните проблеми с връзката включват липсващи ODBC/DB API драйвери, неправилна DSN конфигурация, блокирани портове и несъответстващи режими на удостоверяване.Много често се наблюдава, че Python генерира общи изключения, като например „не можа да се свърже“, докато основният проблем е, че системата не може да зареди споделена библиотека (например libc++ или libc++abi в Linux) или не намира очаквания ODBC драйвер за SQLite, PostgreSQL, MySQL или SQL Server.

Когато се свързвате от Python, обикновено използвате библиотеки като sqlite3, psycopg2, pyodbc, mysql-connector-python, PyMySQL или ORM слой като SQLAlchemy.Всеки от тях има свой собствен формат на низ за свързване, типове грешки и зависимости. GUI клиент може да използва различен стек от драйвери, който скрива тези проблеми, така че винаги проверявайте кой точно драйвер и параметри за свързване използва вашият Python код.

Защо комбинирането на SQL и Python е стратегически мощно

Освен техническите главоболия, има стратегическа причина, поради която разработчиците и анализаторите продължават да настояват за комбиниране на Python със SQL.: всеки език обхваща различна част от жизнения цикъл на данните и заедно те ви осигуряват цялостен работен процес, който е трудно да се постигне само с един инструмент.

SQL все още е стандартът за управление на релационни данниТой се отличава с добре структурирани данни, релационна цялост, индексиране и транзакционни натоварвания. Със SQL получавате бързо филтриране, обединяване и агрегиране на големи набори от данни, унифициран достъп за много инструменти и предвидима производителност, подкрепена от десетилетия изследвания на бази данни.

Python блести, след като данните напуснат контекста на базата данниС библиотеки като pandas, NumPy, matplotlib и seaborn можете да почиствате, преоформяте и анализирате данни по произволно сложни начини, да изпълнявате статистики или машинно обучение и да изграждате визуализации или отчети програмно, включително анализ на данни в реално времеМного трансформации, които са неудобни или многословни в SQL, се превръщат в прости изрази на Python.

На практика това означава ясно разделение на трудаВкарайте колкото е възможно повече филтриране, агрегиране и основна трансформация в SQL, след което върнете подреден набор от данни в Python за тежки анализи, моделиране или визуализация. Анализаторите и инженерите, които владеят свободно и двата езика, могат бързо да преминат от бизнес въпрос към възпроизводим конвейер от данни.

Свързване на Python към SQL бази данни: библиотеки и шаблони

За да могат SQL и Python да работят надеждно заедно, са ви необходими правилните конектори и известна дисциплина относно начина, по който отваряте, използвате и затваряте сесии на базата данни.Точният стек зависи от двигателя на базата данни, но концепциите са сходни.

За леки, вградени работни процеси SQLite често е най-лесният избор.Python се доставя с модула sqlite3 в стандартната библиотека, така че можете да създавате файл с база данни, да дефинирате таблици и да изпълнявате заявки, без да инсталирате допълнителен софтуер. Това е идеално за прототипи, малки аналитични проекти или преподаване на релационни концепции.

За бази данни от сървърен клас обикновено използвате специфични за двигателя драйвери или ORM.PostgreSQL се използва широко с psycopg2, SQL Server често преминава през pyodbc или ODBC драйвера на Microsoft, а MySQL/MariaDB разчитат на mysql-connector-python или PyMySQL. В допълнение към това, SQLAlchemy предоставя слой на абстракция на високо ниво, който ви позволява да пишете преносими SQL изрази и да управлявате пулове за връзки.

Надеждният модел на свързване включва четене на идентификационни данни от променливи на средата или мениджър на секрети, използване на параметризирани заявки за избягване на инжектиране и прилагане на правилна обработка на грешки.След всяка единица работа, трябва изрично да извършите или отмените транзакциите и да освободите връзката обратно към пула или да я затворите, вместо да държите много неактивни сесии отворени.

С SQLAlchemy и pandas работният процес става особено гладък: конструирате URL адрес за връзка, създавате енджина и след това използвате pandas.read_sql_query, за да извлечете резултатите от заявката директно в DataFrame. Оттам нататък разполагате с пълната мощ на екосистемата на Python за почистване, анализ и експортиране на данни.

Услуги за машинно обучение в SQL Server: Проблеми с интеграцията на R и Python

Microsoft SQL Server включва функция, наречена Machine Learning Services, която вгражда R и Python runtime-ове в двигателя на базата данни., което ви позволява да извиквате външни скриптове чрез sp_execute_external_script. Това е мощна функция за анализ в базата данни, но е свързано с дълъг списък от специфични за версията грешки и ограничения, които трябва да разберете.

Проблемите с инсталирането и надстройката са особено чести в SQL Server 2016, 2017, 2019 и 2022.Проблемите варират от липсващи R компоненти на специфични образи на виртуални машини на Azure, до непълни инсталатори на Python в ранни компилации на SQL Server 2017, до CU (кумулативни актуализации) пакети, които не успяват да поканят за офлайн R актуализации. В някои случаи трябва да подадете допълнителни параметри, като например MRCACHEDIRECTORY, в командния ред, за да насочите настройката към кеширани CAB файлове.

Съществуват и проблеми със зависимостите, специфични за платформатаВ Linux компилации на SQL Server 2019 и по-нови версии, R и Python runtime-ите може да не успеят да стартират, защото споделени библиотеки като libc++.so.1 или libc++abi.so.1 не са налични в пътя на библиотеката за разширяемост. Получените грешки често се появяват като общи съобщения „Невъзможно е да се комуникира с runtime-а“ в SQL Server, докато регистрационните файлове на стартовия панел показват липсващия .so файл. Корекциите обикновено включват копиране на необходимите споделени библиотеки в /opt/mssql-extensibility/lib или излагане на директории чрез mssql.conf.

На Windows сървъри, конфигурирани с настройки за криптография FIPS, има друг клас неуспех при инсталиране.Опитът за активиране на услуги за машинно обучение или езикови разширения може да доведе до грешки, свързани с несъвместимостта на създаването на AppContainer с валидираните от FIPS алгоритми на платформата Windows. Решението е временно да деактивирате FIPS, да завършите инсталацията или надстройката и след това да активирате отново FIPS, след като SQL Server бъде напълно конфигуриран.

Някои кумулативни актуализации въвеждат временни регресии, които влияят на изпълнението на скриптовеНапример, SQL Server 2017 CUs 5-7 включваха грешка в rlauncher.config, когато пътят към временната директория съдържаше интервали, което водеше до неуспех на R скриптовете с грешка „cannot create R_TempDir“. По-късни CU поправиха това, но дотогава администраторите трябваше да пререгистрират външната среда за скриптове, използвайки RegisterRExt.exe с флагове за деинсталиране и инсталиране.

Несъответствия във версиите между клиентската и сървърната среда за изпълнение

Друг повтарящ се източник на объркване е съвместимостта на версиите между клиентските инструменти (Microsoft R Client или Python пакети) и сървърните среди за изпълнение (R Server или SQL Server Machine Learning Services).Когато изпълнявате отдалечени скриптове от клиент на по-стар екземпляр на SQL Server, несъответствие може да предизвика явни грешки или фини проблеми със сериализацията.

В SQL Server 2016 R Services, версиите на R библиотеките на клиента и сървъра трябва да съвпадат точно.Стартирането на Microsoft R Client 9.x срещу сървър с R Server 8.0.3 води до съобщения, че клиентът ви е несъвместим и ви предлага да инсталирате съответстваща версия. По-късните версии облекчиха това изискване, но ако видите тези грешки, трябва да проверите и двете страни и или да надстроите сървъра, или да инсталирате съвместим клиент.

Сериализацията и десериализацията на обучените модели са особено чувствителни към разликите във версиите.С RevoScaleR в R и revoscalepy в Python, модел, сериализиран с по-нов API, може да не успее да се десериализира на сървър, използващ по-стара инфраструктура за сериализация, което води до вътрешни грешки, като например неуспехи на memDecompress в R или NameError в Python, когато rx_unserialize_model не е дефиниран. Надграждането на екземпляра на SQL Server до поне CU3 за SQL Server 2017 обикновено разрешава тези несъответствия.

Предварително обучените модели, инсталирани на SQL Server 2017, също могат да достигнат ограничения за дължината на пътяРанните компилации съхраняваха двоичните файлове на моделите в дълбоки структури на директории под пътя на екземпляра по подразбиране и Python не можеше да отвори файловете, защото пълният път надвишаваше ограниченията на операционната система. Предложените поправки включваха инсталиране на модели в персонализиран по-кратък път, инсталиране на SQL Server в по-кратка главна директория или дори създаване на NTFS твърди връзки с fsutil, за да се разкрие по-кратък псевдоним на същия файл.

Когато проектирате решение, използвайки услугите за машинно обучение на SQL Server, винаги заключвайте версиите и нивата на CU като част от плана за внедряване.Разпространяването на скриптове между множество сървъри с различни CU нива, без да се проследяват тези детайли, е рецепта за трудно отстраняема сериализация и проблеми по време на изпълнение по-късно.

Управление на ресурсите, производителност и поведение при студен старт

Дори когато услугите за машинно обучение на SQL Server са правилно инсталирани и версията им е съобразена, може да достигнете ограничения за производителност поради управлението на ресурсите и обединяването на процеси.Разбирането на това как се държат процесите на стартовата площадка и спътниците е ключово за осигуряване на постоянна латентност.

SQL Server създава пулове процеси за външни скриптове за всеки потребител, за всяка база данни и за всеки език.Първото извикване на sp_execute_external_script след период на неактивност кара launchpad да стартира нови сателитни процеси за R или Python. Този студен старт може да бъде забележимо бавен на силно натоварени сървъри или виртуални машини с ограничения. По-късните извиквания използват повторно затопления пул, така че второто и третото изпълнение са много по-бързи.

Ако латентността при първото повикване е проблем – например в сценарии за оценяване в реално време – можете да поддържате пуловете топли, като периодично изпълнявате леки скриптове.Много екипи планират прост „no-op“ R или Python скрипт чрез SQL Agent, който да се задейства на всеки няколко минути, предотвратявайки задачата за почистване при неактивност да изключи сателитните процеси.

В SQL Server 2016 Enterprise Edition, ранните компилации ограничават външната памет за скриптове до около 20% от общата RAM памет.За 32 GB сървър това означаваше, че изпълнимите R файлове може да бъдат ограничени до около 6.4 GB на заявка. За по-големи модели или широки набори от данни това бързо се превръща в ограничение, което води до грешки при разпределение на паметта или значително пейджинг. Администраторите трябва да прегледат текущите настройки по подразбиране и да коригират настройките на регулатора на ресурсите, когато се очакват сложни ML натоварвания.

Паралелизмът е друго фино ограничениеКогато извиквате библиотеки на Microsoft ML или RevoScaleR извън SQL Server (напр. RGui), дори ако базовото издание е Enterprise, тези библиотеки често работят в еднонишков режим. По подобен начин имаше известни грешки в SQL Server 2019, при които R скриптове, използващи контексти на RxLocalPar или базовия паралелен пакет, можеха да доведат до замръзване на SQL Server поради проблеми със записването на нулевото устройство в средата за изпълнение с пясъчна кутия.

Ограничения на типа данни, кодирането и схемата при извикване на външни скриптове

Типовете данни и кодировките са чест източник на неочаквано поведение при прехвърляне на SQL данни към R или Python чрез sp_execute_external_scriptНе всички SQL типове се поддържат, а някои се поддържат само частично или се конвертират безшумно, което може да доведе до загуба на точност или повредени низове, особено при сложни структури като... масиви в SQL.

По-ранните CU на SQL Server 2017 имаха силни ограничения за числови, десетични и парични типове за изходни схеми на Python.Когато се комбинират с WITH RESULT SETS и Python, неподдържаните типове водят до грешки и съобщения в SqlSatelliteCall, показващи, че са разрешени само bit, smallint, int, datetime, smallmoney, real и float (плюс частично char/varchar). По-късните CU поправиха това, но все още трябва да сте наясно кои типове данни излагате на външни среди за изпълнение.

За R скриптовете, money, numeric, decimal и bigint се преобразуват в числовия тип на R.Вследствие на това, стойностите с голяма величина или тези с много десетични знаци могат да загубят точност; паричните типове могат да задействат предупреждения за центови стойности, които не могат да бъдат точно представени, а bigint надвишава 53-битовото ограничение за цяло число в R, което води до закръгляване на най-малко значимите битове.

Кодирането на низове също е важноПредаването на Unicode данни, съхранявани във varchar колони, може да повреди символи, различни от ASCII, тъй като сравненията в SQL Server може да не съответстват на UTF-8 кодирането, очаквано от R или Python. Препоръчителните подходи са да се използват UTF-8 сравнения, налични в SQL Server 2019+, или да се съхранява Unicode текст в nvarchar и да се обработват преобразуванията изрично във вашия скрипт.

Някои SQL функции са изцяло забранени за външни скриптове.Заявки, препращащи към колони с Always Encrypted или маскирани колони, не могат да бъдат директно подавани към R скриптове при определени контексти; може да се наложи да копирате защитени данни във временни таблици без криптиране или маскиране за анализ. Освен това, в контекст на изчисления на SQL Server, аргументи като colClasses в R не могат да отменят типовете колони; трябва да използвате CAST или CONVERT в T-SQL, преди да предадете данни на R.

Бинарните полезни товари също имат специални правилаКогато се връща суров тип на R, стойността трябва да бъде включена в изходния кадър с данни, а не обвързана с изходен параметър. Ефективно се поддържа само един набор от сурови изходи; ако имате нужда от множество двоични изходи, може да се наложи да извикате съхранената процедура няколко пъти или да върнете данните обратно в SQL чрез ODBC от скрипта.

Практически проблеми при инсталиране и разширяване на Python в SQL Server

Инсталирането и разширяването на Python средата, включена в пакета с SQL Server Machine Learning Services, е по-ограничено от самостоятелната Anaconda или системния Python.Много потребители получават грешки, когато се опитват да добавят пакети с pip или sqlmlutils, особено в Windows със SQL Server 2019.

В Windows, чест проблем след инсталиране на SQL Server 2019 е, че pip съобщава за проблеми с конфигурацията на TLS/SSL.Оплаква се, че ssl модулът не е наличен, въпреки че очевидно можете да стартирате Python. Причината обикновено е липсата на OpenSSL DLL файлове (libssl-1_1-x64.dll и libcrypto-1_1-x64.dll) в поддиректорията DLLs на PYTHON_SERVICES. Копирането на тези файлове от папката Library\bin в DLL файлове и след това стартирането на нов команден ред обикновено възстановява способността на pip да прави HTTPS заявки.

Някои популярни ML пакети, като tensorflow, имат несъвместими изисквания за зависимости.Колелото tensorflow може да изисква по-нова версия на NumPy от тази, предварително инсталирана в Python средата на SQL Server. Тъй като NumPy се третира като системен пакет, не можете да го надстроите чрез sqlmlutils, така че опитите за инсталиране на tensorflow по този маршрут се провалят. Вместо това трябва да извикате изпълнимия файл PYTHON_SERVICES директно с -m pip и да надстроите или инсталирате пакети в тази среда, понякога след ръчно актуализиране на разпространяеми runtime-и като Microsoft Visual C++.

В Linux, включената в пакета входна точка за pip може да бъде разглобена веднага щом е настроена.За SQL Server 2019, изпълнението на pip от /opt/mssql/mlservices/runtime/python/bin може да доведе до срив с грешка в интерпретатора, сочеща към несъществуващо местоположение в legacy ML Server. Решението е да изтеглите get-pip.py от PyPA и да го стартирате с правилния двоичен файл на Python под /opt/mssql/mlservices/bin/python/python, като по този начин ефективно рестартирате pip за тази среда за изпълнение.

Съществуват и фини поведения около изходните параметри varbinary и varchar в Python скриптовете.Ако извикването ви на sp_execute_external_script изведе OUTPUT параметър от тип varbinary(max) или large varchar и не успеете да присвоите стойност в Python скрипта, компонентът BxlServer може да генерира грешки и да спре да работи. Безопасният модел е изрично да инициализирате тези параметри в Python кода си, дори ако просто ги зададете на празен низ или 0x0.

Класически SQL + Python работен процес със SQLite

Отдалечавайки се от спецификите на SQL Server, един много продуктивен начин за изучаване и създаване на прототип на SQL-Python интеграция е използването на SQLite с модула sqlite3 на Python.SQLite съхранява данни в един файл, не изисква отделен сървърен процес и се държи като малка релационна база данни с поддръжка на SQL.

В SQLite, базата данни е просто организиран файл, който съхранява структурирани данни на диска.Подобно на речник на Python, той съпоставя ключовете със стойности, но добавя индексиране, ефективно съхранение на големи набори от данни и възможности за заявки. Структурите се въртят около таблици (подобно на електронните таблици), редове (записи) и колони (полета). В по-формална релационна терминология това са релации, кортежи и атрибути.

За да започнете, се свързвате с файл с база данни с sqlite3.connectАко файлът не съществува, SQLite го създава. От връзката създавате обект курсор, който действа като манипулатор за изпълнение на SQL команди и итериране върху резултатите. Работният процес е аналогичен на отваряне на файл и четене ред по ред, с изключение на това, че изпълнявате SQL изрази, вместо да четете обикновен текст.

Създаването на таблица изисква задаване на имена на колони и типове данниВъпреки че SQLite е доста гъвкав по отношение на въвеждането на текст, дефинирането на типове помага на енджина да избере ефективни формати за съхранение и стратегии за индексиране. Например, една проста таблица за песни може да дефинира текстово заглавие и цяло число за брой възпроизвеждания. След като таблицата е създадена с CREATE TABLE, можете да вмъквате редове, използвайки INSERT и заместители на параметри (въпросителни знаци), за да обвържете безопасно Python стойности.

Използване на SQL от Python: INSERT, SELECT, UPDATE, DELETE

SQL предоставя четири основни операции – INSERT, SELECT, UPDATE и DELETE – които се съпоставят добре с Python код, работещ със sqlite3.Всяка операция манипулира редове в таблица, а клаузата WHERE ви позволява да насочвате към конкретни записи.

INSERT добавя нови записи към таблицаВ Python извиквате cursor.execute с оператор като INSERT INTO Songs (title, plays) VALUES (?, ?), предавайки набор от параметри. Използването на заместители вместо конкатенация на низове избягва SQL инжектирането и обработва правилно цитирането. След вмъкване извиквате conn.commit, за да съхраните промените от транзакцията във файла на базата данни.

SELECT чете данни обратно от базата данни, като по избор филтрира и подрежда резултатитеЕдно просто заглавие SELECT, което възпроизвежда FROM Songs, превръща курсора в итерируем обект върху редове. За големи набори от резултати SQLite не зарежда всички редове в паметта наведнъж; вместо това ги връща, когато for цикълът итерира. Можете да изберете всички колони с * или да укажете подмножество, а също така можете да използвате WHERE, ORDER BY и LIMIT, за да ограничите и сортирате записите.

DELETE премахва редове за постоянно въз основа на условиеИзраз като DELETE FROM Songs WHERE plays < 100 изтрива всички песни с нисък брой възпроизвеждания. Няма опция за отмяна, така че в уроците е обичайно да се изтриват редове в края на скрипт, за да се направят повторно изпълняваните примери идемпотентни. Трябва да направите commit след изтриванията, ако искате промените да се запазят.

UPDATE променя колони в съществуващи редовеВие указвате таблицата, клауза SET с новите стойности и опционална логика WHERE. Например, UPDATE Songs SET plays = 16 WHERE title = 'My Way' засяга всеки ред, чието заглавие съвпада с този низ. Ако пропуснете WHERE, ще актуализирате всеки ред в таблицата, което често е източник на случайни групови промени.

Създаване на Twitter crawler с SQLite и Python

Практическа демонстрация на смесването на SQL и Python е малък Twitter crawler, който съхранява състоянието в SQLite база данни.Въпреки че API-тата и политиките на Twitter се променят с времето, архитектурната идея остава поучителна: искате да преглеждате взаимоотношенията с приятели, да избягвате повторно посещение на акаунти и да улавяте показатели за популярност, като същевременно можете да спирате и възобновявате, без да губите напредък.

Роботът поддържа таблица с акаунти в Twitter и проследява дали всеки от тях е бил посетен и колко пъти се показва като приятел.Всеки ред съдържа името на акаунта, флаг, показващ дали вече сте извлекли списъка му с приятели, и брояч, показващ колко пъти този акаунт се е показвал сред „приятелите“ на други. Това ви позволява да оцените популярността си в рамките на избраната мрежа.

Основният цикъл подканва потребителя да въведе дескриптор на Twitter или команда за изход.Ако потребителят просто натисне Enter, скриптът отправя запитване към базата данни за следващия акаунт с recovered = 0 и го използва като следваща цел. След това извиква крайната точка friends/list на Twitter, анализира JSON отговора, актуализира recovered флага за текущия акаунт и или вмъква, или актуализира всеки приятел в базата данни, като увеличава броячите на приятелите му, ако е необходимо.

Тъй като всичко се съхранява в SQLite, можете да прекратите сканера и да го рестартирате по-късно.Базата данни служи като издръжливо хранилище за опашки и състояния. Отделен помощен скрипт може да извлече съдържанието на таблицата в Twitter, което ви позволява да проверите кои акаунти са известни, кои са били посетени и колко пъти всеки се е появявал като приятел. Този модел – запазване на състоянието на обхождане в релационна база данни – се обобщава добре за други задачи за обхождане на мрежата или API.

Основи на моделирането на данни: първични ключове, външни ключове и нормализация

Съхраняването на цялата информация за Twitter в една таблица бързо се сблъсква с проблеми с мащабируемостта и излишествата.По-стабилен подход е да се нормализират данните чрез отделяне на обектите (хора) от връзките (кой кого следва) и свързването им чрез ключове.

Таблицата с хора обикновено използва целочислен първичен ключ като вътрешен идентификатор.В SQLite можете да декларирате id INTEGER PRIMARY KEY и енджина автоматично генерира уникално цяло число за всеки вмъкнат ред. Можете също така да включите логически ключ, като например дескриптора на Twitter, маркиран като UNIQUE, за да предотвратите дублиране. Логическият ключ е това, което външният свят използва, докато първичният ключ е това, към което вашият код и външните ключове се позовават.

Отделна таблица за проследяване след това улавя връзките, използвайки външни ключовеВсеки ред съдържа чифт потребителски идентификатори, обикновено наречени from_id и to_id (или подобни), показващи, че един човек следва друг. Можете да декларирате UNIQUE ограничение за комбинацията от тези две колони, което гарантира, че не можете случайно да вмъкнете една и съща връзка два пъти.

Нормализацията – съхраняването на всяка информация веднъж и препращането ѝ другаде с ключове – избягва дублирането, спестява място и подобрява производителността.Вместо да запазвате един и същ низ с потребителско име в милиони редове за взаимоотношения, го запазвате веднъж в таблицата с хора и след това сочите към него чрез целочислени идентификатори. Целите числа са по-бързи за сравняване и индексиране, което става от решаващо значение при голям мащаб.

В Python кода този дизайн води до общи модели за вмъкване или извличане на потребители и взаимоотношения.Преди да вмъкнете връзка, трябва да се уверите, че и двамата участници съществуват в таблицата с хора: използвате SELECT по логически ключ и ако не е намерен ред, използвате INSERT и записвате lastrowid като ID на новото лице. Едва след това вмъквате или игнорирате ред в таблицата follow, свързваща тези ID. Ограниченията и OR IGNORE работят заедно, за да поддържат данните ви съгласувани без прекомерни ръчни проверки.

Използване на JOIN за комбиниране на свързани таблици в SQL

След като данните са разпределени в множество нормализирани таблици, вие разчитате на SQL JOIN-ове, за да реконструирате комбинирания изглед, от който се нуждаете.JOIN обединява редове от две таблици въз основа на съвпадащи ключови стойности, като ефективно създава виртуален широк ред за всяко съвпадение.

В примера с Twitter, обединяването на таблиците „следвания“ и „хора“ ви позволява да видите кого следва даден потребител или кой го следва.Заявка като SELECT * FROM Follow JOIN People ON Follow.to_id = People.id WHERE Follow.from_id = 2 извлича всички хора, следвани от потребителя, чийто вътрешен ID е 2. Клаузата JOIN указва на базата данни да съпостави Follow.to_id с People.id за всеки ред, а условието WHERE ограничава потребителя източник.

Резултатният набор съдържа колони от двете таблициМоже да видите двата целочислени идентификатора от таблицата „следване“, последвани от пълния ред за лицето (идентификатор, потребителско име, възстановен флаг) от таблицата „хора“. Когато потребител следи много акаунти, получавате един комбиниран ред за всяка връзка, дублирайки някои колони от лицето източник, но ви давайки лесен достъп до атрибутите на целевото лице.

JOIN-овете се предлагат в няколко вида – INNER, LEFT, RIGHT, FULL – но нормализираните дизайни обикновено използват INNER JOIN-ове за основни релации.INNER JOIN запазва само редовете, които имат съвпадения от двете страни, което е в съответствие с идеята, че редът за връзка винаги трябва да препраща към съществуващи хора. При дебъгване или проучване можете да SELECT няколко реда от всяка таблица и от заявка за JOIN, за да проверите дали моделът се държи според очакванията.

Този релационен модел се появява навсякъде: потребители и роли, клиенти и поръчки, продукти и категории, публикации и коментариСлед като се усвоите удобно с проектирането на таблици с първични и външни ключове и писането на JOIN заявки, можете да моделирате и да заявявате сложни домейни, като същевременно се възползвате от Python за логика и анализ на по-високо ниво.

Като цяло, овладяването на SQL и Python означава разбиране не само на това как да се пишат чисти заявки или скриптове, но и как взаимодействат средата за изпълнение, драйверите, типовете данни и ограниченията на ресурсите в различните платформи.От диагностициране на загадъчни грешки в услугите за машинно обучение в SQL Server и управление на зависимостите на библиотеките в изолирани Python среди, до проектиране на нормализирани SQLite схеми и оркестриране на цялостни аналитични канали, колкото по-плавно се движите между базата данни и кода, толкова по-стабилни и мащабируеми ще станат вашите решения за данни.