Часті проблеми з SQL та Python та як їх вирішувати

Home » Python » Часті проблеми з SQL та Python та як їх вирішувати

Спільна робота з SQL та Python — одне з найпотужніших поєднань у розробці даних та бекенду., але це також відкриває двері до довгого списку ледь помітних помилок, пасток конфігурації та несподіванок щодо продуктивності. Якщо ви коли-небудь дивилися на загадкове трасування, тоді як ваше з’єднання з базою даних «має просто працювати», або задавалися питанням, чому той самий аналітичний скрипт блискавично швидко працює на вашому ноутбуці, але повзає всередині SQL Server, ви не самотні.

Цей посібник об'єднує реальні проблеми SQL-Python, проблеми низькорівневих служб машинного навчання SQL Server та практичні моделі використання обох мов в аналітиці.Замість розпливчастих порад ви знайдете конкретні приклади, типові повідомлення про помилки та покрокові ідеї для діагностики та виправлення проблем, а також повний огляд того, як проектувати, запитувати та маніпулювати базами даних на Python за допомогою SQLite та інших рушіїв.

Поширені проблеми з'єднання між SQL та Python

Одна з перших проблем при поєднанні SQL та Python — це просто отримання стабільного з'єднання.Навіть коли облікові дані та DSN виглядають правильними, невеликі невідповідності в драйверах, шляхах або середовищах можуть спричинити незрозумілі помилки виконання в момент запуску app.py або запуску скрипта з командного рядка.

У віртуалізованих середовищах це стає більш крихкимНаприклад, ви можете запускати SQLite або SQL Server у віртуальній машині під час розробки на хост-ОС та тестування з'єднання за допомогою інструмента графічного інтерфейсу, такого як SQL Developer або SQL Server Management Studio. Графічний інтерфейс підключається нормально, але скрипт 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 у механізм бази даних., що дозволяє викликати зовнішні скрипти через 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 можуть не запускатися, оскільки спільні бібліотеки, такі як libc++.so.1 або libc++abi.so.1, недоступні в шляху бібліотеки розширюваності. Отримані помилки часто відображаються як загальні повідомлення «Неможливо зв’язатися з середовищем виконання» в SQL Server, тоді як журнали панелі запуску показують відсутній файл .so. Виправлення зазвичай включають копіювання необхідних спільних бібліотек до /opt/mssql-extensibility/lib або відкриття каталогів через mssql.conf.

На серверах Windows, налаштованих з параметрами криптографії FIPS, існує інший клас збоїв інсталяції.Спроба ввімкнути служби машинного навчання або розширення мови може призвести до помилок про те, що створення AppContainer несумісне з алгоритмами, перевіреними FIPS платформи Windows. Тимчасове рішення полягає в тимчасовому вимкненні FIPS, завершенні інсталяції або оновлення, а потім повторному ввімкненні FIPS після повного налаштування SQL Server.

Деякі сукупні оновлення вводять тимчасові регресії, які впливають на виконання скриптів.Наприклад, SQL Server 2017 CU версій 5-7 містили помилку в rlauncher.config, коли шлях до тимчасового каталогу містив пробіли, що призводило до збою R-скриптів із повідомленням «не вдалося створити R_TempDir». Пізніші CU виправили це, але до того часу адміністраторам доводилося перереєструвати зовнішнє середовище сценаріїв за допомогою RegisterRExt.exe з прапорцями видалення та встановлення.

Невідповідності версій між клієнтським та серверним середовищами виконання

Ще одним постійним джерелом плутанини є сумісність версій між клієнтськими інструментами (клієнт Microsoft R або пакети Python) та серверними середовищами виконання (сервер R або служби машинного навчання SQL Server).Під час запуску віддалених скриптів з клієнта на старішому екземплярі SQL Server невідповідність може спричинити явні помилки або незначні проблеми серіалізації.

У службах SQL Server 2016 R версії бібліотек 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% від загальної оперативної пам'яті.Для сервера на 32 ГБ це означало, що розмір виконуваних файлів R може бути обмежений приблизно 6.4 ГБ на запит. Для більших моделей або широких наборів даних це швидко стає обмеженням, що призводить до помилок розподілу пам'яті або значного підкачувань. Адміністратори повинні переглядати поточні значення за замовчуванням та коригувати параметри регулятора ресурсів, коли очікуються складні робочі навантаження машинного навчання.

Паралелізм – ще одне тонке обмеженняКоли ви викликаєте бібліотеки Microsoft ML або RevoScaleR ззовні SQL Server (наприклад, RGui), навіть якщо базовою версією є Enterprise, ці бібліотеки часто працюють в однопотоковому режимі. Аналогічно, у SQL Server 2019 були відомі помилки, через які сценарії R, що використовують контексти RxLocalPar або базовий паралельний пакет, могли призвести до зависання SQL Server через проблеми із записом на нульовий пристрій у ізольованому середовищі виконання.

Обмеження типу даних, кодування та схеми під час виклику зовнішніх скриптів

Типи даних та кодування є частим джерелом неочікуваної поведінки під час передачі SQL-даних у R або Python через sp_execute_external_script.Не всі типи SQL підтримуються, а деякі підтримуються лише частково або перетворюються непомітно, що може призвести до втрати точності або пошкодження рядків, особливо зі складними структурами, такими як масиви в SQL.

Попередні версії SQL Server 2017 CU мали суворі обмеження на числові, десяткові та грошові типи для вихідних схем 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 є повідомлення про проблеми конфігурації TLS/SSL у pip.Він скаржиться, що модуль ssl недоступний, хоча ви явно можете запустити Python. Причиною зазвичай є відсутність DLL-файлів OpenSSL (libssl-1_1-x64.dll та libcrypto-1_1-x64.dll) у підкаталозі DLLs PYTHON_SERVICES. Копіювання цих файлів з папки Library\bin у DLL-файли, а потім запуск нового командного рядка зазвичай відновлює здатність pip робити HTTPS-запити.

Деякі популярні пакети машинного навчання, такі як tensorflow, мають несумісні вимоги до залежностейДля роботи колеса tensorflow може знадобитися новіша версія NumPy, ніж та, що попередньо встановлена ​​в середовищі Python SQL Server. Оскільки NumPy розглядається як системний пакет, ви не можете оновити його через sqlmlutils, тому спроби встановити tensorflow цим маршрутом завершуються невдачею. Натомість вам потрібно викликати виконуваний файл PYTHON_SERVICES безпосередньо за допомогою -m pip та оновити або встановити пакети в цьому середовищі, іноді після ручного оновлення розповсюджуваних середовищ виконання, таких як Microsoft Visual C++.

У Linux точку входу pip, що входить до комплекту, можна виділити одразу після встановлення.Для SQL Server 2019 запуск pip з /opt/mssql/mlservices/runtime/python/bin може призвести до аварійного завершення роботи з помилкою інтерпретатора, яка вказує на неіснуюче розташування застарілого 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 видаляє всі пісні з низькою кількістю відтворень. Скасування дій немає, тому в навчальних посібниках часто видаляють рядки в кінці скрипта, щоб зробити повторно запущені приклади ідемпотентними. Ви повинні зафіксувати зміни після видалення, якщо хочете зберегти зміни.

UPDATE змінює стовпці в існуючих рядкахВи вказуєте таблицю, речення SET з новими значеннями та необов'язкову логіку WHERE. Наприклад, UPDATE Songs SET plays = 16 WHERE title = 'My Way' впливає на кожен рядок, заголовок якого відповідає цьому рядку. Якщо пропустити WHERE, буде оновлено кожен рядок у таблиці, що часто є джерелом випадкових масових змін.

Створення пошукового робота Twitter за допомогою SQLite та Python

Практична демонстрація поєднання SQL та Python – це невеликий пошуковий сканер Twitter, який зберігає стан у базі даних SQLite.Хоча API та політики Twitter з часом змінюються, архітектурна ідея залишається повчальною: ви хочете переглядати стосунки з друзями, уникати повторного відвідування облікових записів та фіксувати показники популярності, і все це з можливістю зупиняти та відновлювати без втрати прогресу.

Сканер веде таблицю облікових записів Twitter та відстежує, чи було отримано кожен із них і скільки разів він відображається як друг.Кожен рядок містить назву облікового запису, прапорець, який вказує, чи ви вже отримали його список друзів, і лічильник того, скільки разів цей обліковий запис відображався серед «друзів» інших. Це дозволяє оцінити популярність у вибірковій мережі.

Основний цикл запитує у користувача дескриптор Twitter або команду виходу.Якщо користувач просто натискає Enter, скрипт запитує базу даних щодо наступного облікового запису зі значенням recovered = 0 та використовує його як наступну ціль. Потім він викликає кінцеву точку Twitter friends/list, аналізує відповідь JSON, оновлює прапорець recovered для поточного облікового запису та або вставляє, або оновлює кожного друга в базі даних, збільшуючи його лічильники друзів за потреби.

Оскільки все зберігається в SQLite, ви можете завершити роботу сканера та перезапустити його пізніше.База даних слугує надійним сховищем черги та станів. Окремий допоміжний скрипт може виводити вміст таблиці Twitter, дозволяючи перевіряти, які облікові записи відомі, які відвідувалися та скільки разів кожен з них відображався як друг. Цей шаблон — збереження стану сканування в реляційній базі даних — добре узагальнюється для інших завдань веб-сканування або API-сканування.

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

Зберігання всієї інформації Twitter в одній таблиці швидко стикається з проблемами масштабованості та надмірності.Більш надійний підхід полягає в нормалізації даних шляхом відділення сутностей (людей) від зв'язків (хто за ким стежить) та пов'язування їх за допомогою ключів.

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

Окрема таблиця follow потім фіксує зв'язки за допомогою зовнішніх ключівКожен рядок містить пару ідентифікаторів користувачів, зазвичай з назвами from_id та to_id (або подібними), що вказують на те, що одна людина слідує за іншою. Ви можете оголосити обмеження UNIQUE для комбінації цих двох стовпців, що гарантує, що ви не зможете випадково вставити той самий зв'язок двічі.

Нормалізація — одноразове зберігання кожного фрагмента інформації та посилання на нього в іншому місці за допомогою ключів — дозволяє уникнути дублювання, заощадити місце та покращити продуктивність.Замість того, щоб зберігати один і той самий рядок імені користувача в мільйонах рядків зв'язків, ви зберігаєте його один раз у таблиці people, а потім вказуєте на нього за допомогою цілочисельних ідентифікаторів. Цілі числа швидше порівнювати та індексувати, що стає критично важливим при великих масштабах.

У коді Python така конструкція призводить до поширених шаблонів для вставки або отримання користувачів та зв'язків.Перш ніж вставляти зв'язок, ви повинні переконатися, що обидва учасники існують у таблиці people: ви виконуєте SELECT за логічним ключем, а якщо рядок не знайдено, ви виконуєте INSERT та фіксуєте lastrowid як ідентифікатор нової особи. Тільки після цього ви виконуєте INSERT OR IGNORE рядок у наступній таблиці, що пов'язує ці ідентифікатори. Обмеження та OR IGNORE працюють разом, щоб забезпечити узгодженість даних без надмірних ручних перевірок.

Використання JOIN для об'єднання пов'язаних таблиць у SQL

Після розподілу даних по кількох нормалізованих таблицях ви покладаєтеся на SQL JOIN для реконструкції необхідного об'єднаного представлення.Операція JOIN об'єднує рядки з двох таблиць на основі збігу ключових значень, фактично створюючи віртуальний широкий рядок для кожного збігу.

У прикладі Twitter об’єднання таблиць follow та people дозволяє побачити, на кого підписаний певний користувач або хто підписаний на нього.Запит типу SELECT * FROM Follow JOIN People ON Follow.to_id = People.id WHERE Follow.from_id = 2 отримує всіх людей, на яких підписався користувач, внутрішній ідентифікатор якого дорівнює 2. Речення JOIN повідомляє базі даних про необхідність зіставлення Follow.to_id з People.id для кожного рядка, а умова WHERE обмежує користувача-джерела.

Результуючий набір містить стовпці з обох таблицьВи можете побачити два цілочисельні ідентифікатори з таблиці "Підписки", а потім повний рядок особи (ідентифікатор, ім'я користувача, відновлений прапорець) з таблиці "Підписки". Коли користувач підписаний на багато облікових записів, ви отримуєте один об'єднаний рядок для кожного зв'язку, що дублює деякі стовпці з вихідної особи, але надає вам легкий доступ до атрибутів цільової особи.

JOIN-и бувають різних видів — INNER, LEFT, RIGHT, FULL — але нормалізовані проекти зазвичай використовують INNER JOIN-и для основних зв'язків.. INNER JOIN зберігає лише рядки, які мають збіги з обох сторін, що узгоджується з ідеєю, що рядок зв'язку завжди повинен посилатися на існуючих людей. Під час налагодження або дослідження можна вибрати кілька рядків з кожної таблиці та з запиту JOIN, щоб перевірити, чи модель поводиться належним чином.

Цей реляційний патерн проявляється скрізь: користувачі та ролі, клієнти та замовлення, продукти та категорії, публікації та коментаріЩойно ви освоїте проектування таблиць з первинними та зовнішніми ключами, а також написання запитів JOIN, ви зможете моделювати та запитувати складні домени, водночас використовуючи переваги Python для логіки та аналізу вищого рівня.

З огляду на все це, оволодіння SQL та Python означає розуміння не лише того, як писати чисті запити чи скрипти, але й того, як середовища виконання, драйвери, типи даних та обмеження ресурсів взаємодіють на різних платформах.Від діагностики загадкових помилок служб машинного навчання в SQL Server та керування залежностями бібліотек в ізольованих середовищах Python до проектування нормалізованих схем SQLite та оркестрації конвеєрів наскрізної аналітики, чим плавніше ви переходите між базою даних і кодом, тим надійнішими та масштабованішими стають ваші рішення для обробки даних.