raw sql что это

Snaql. Raw SQL в Python-проектах

В последний год у меня появилось новое правило — каждые 3 месяца изучать новый язык программирования и его экосистему. На это есть несколько причин: новые парадигмы, концепции, инструменты, да и просто интересно что там, по ту сторону набившего с годами оскомину Python. Это простое правило позволило изучить за текущий год современные хипстерские Go, Clojure и Rust, проникнуться их идеями и best practices, что, кстати, очень положительно влияет на стиль и качество кода, когда я пишу на своём основном языке.

Рассматривая стек Luminus, я наткнулся на простую и в то же время шикарную, на мой вкус, библиотеку Yesql для организации SQL-запросов в проекте на Clojure и я не увидел чего-то похожего для Python (может плохо искал). Идея этой библиотеки простая — не морочьте себе голову, используйте обычные SQL-запросы, у вас есть возможность именования этих запросов и мапинга на соответствующие динамические функции. Всё это выглядит как набор микро-шаблонов с SQL и их рендер по какому-то контексту. Просто, эффективно, хочу такое у себя в проекте на Python.

Вообще в последнее время мне импонирует мысль, что ORM не нужны. Они переусложняют, на самом деле, работу с реляционными БД, скрывают «адский» SQL за ширмой сложных конструкций собственных объектов, а зачастую выдают и крайне неэффективный результат. Наверняка кто-то поспорит с этим выводом, но моя практика показала, что Django ORM ужасающе простой чуть более чем всегда (и доступен только если вы используете Django, конечно), SQLAlchemy ужасающе сложный, Peewee — ни разу не встречал в дикой природе, к тому же ещё немного и он станет как Alchemy по своему порогу вхождения. SQL — сам по себе мощный и выразительный DSL, вам не нужен ещё один уровень абстракции над ним, серьёзно. Под другим углом я задумался о целесообразности ORM во время очередного проекта на Tornado. Алхимия чудесным алхимическим образом убивает всю асинхронность выполнения обработчика блокирующими вызовами в базу. И вариантов кроме как использовать тот же Momoko с сырыми запросами я не увидел.

Всё, что нам нужно для полного счастья — это разведение SQL-строк и Python-кода по разным углам и некоторая гибкость в построении конструкций по условиям или контексту. Ну и перестать бояться писать SQL, конечно. Изучить SQL до необходимого уровня реально проще чем все нюансы Алхимии для того же результата.

Попробовав и немного переосмыслив Yesql у меня родилась крохотная библиотека Snaql, которая решает описанную выше проблему, хоть и немного по-своему. Я решил вообще не завязываться на клиенты к базам и использовать Jinja2 в качестве движка для парсинга и рендеринга шаблонов с SQL-блоками (со всеми вытекающими возможностями использовать её шаблонную логику). Вот как это выглядит.

2. Создаём в своём проекте папку, куда будем складывать файлы с SQL-блоками. Или несколько таких папок.

3. В users.sql у нас, например, все запросы, связанные с сущностью пользователя.

Как можно догадаться, SQL помещается внутри блока <%sql%><%endsql%>, «users_by_country» это название функции, на которую навешивается данный SQL (создаётся динамически), а «note» — это docstring к этой функции, он опционален.

Таких блоков в одном файле может быть сколь угодно много. Главное, чтобы их имена были уникальны.

4. Теперь нам нужна фабрика, которая распарсит такие файлы и создаст набор одноимённых функций.

Извлечь в коде необходимый SQL теперь можно просто вызвав

На самом деле уже этого может быть достаточно. Но не в случае с генерируемыми условиями запроса. В этом случае можно добавить в шаблон всю то логику, которую предоставляет Jinja. Например:

Если вызвать функцию без контекста:

И если с контекстом:

Получив сформированный SQL, остальное — дело техники. Вроде неплохо, да? В любом случае пишите свои «за» и «против» в комментариях, мне интересно мнение сообщества, насколько это может быть удобным кому-то кроме меня.

UPD: Спасибо за конструктивные комментарии. Теперь у меня есть с чего формировать roadmap на 0.2. Не стесняйтесь присылать issues и requests на GitHub.

UPD2: Благодаря вашим конструктивным замечаниям, я обновил Snaql до версии 0.2, там теперь есть guards и conditions blocks, расширена поддержка версий интерпретатора до 2.6, 2.7, 3.3, 3.4, 3.5.

Источник

Необработанные SQL-запросы

Entity Framework Core позволяет вам переходить на уровень необработанных SQL-запросов при работе с реляционной базой данных. Необработанные SQL-запросы полезны, если запрос невозможно выразить с помощью LINQ. Они также полезны, если использование запроса LINQ приводит к отправке неэффективного SQL-запроса. Необработанные SQL-запросы могут возвращать обычные типы сущностей или типы сущностей без ключей, которые являются частью модели.

Для этой статьи вы можете скачать пример из репозитория GitHub.

Основные необработанные SQL-запросы

Необработанные SQL-запросы могут использоваться для выполнения хранимой процедуры.

Передача параметров

Всегда используйте параметризацию для необработанных SQL-запросов.

При добавлении предоставленных пользователем значений в необработанный SQL-запрос необходимо соблюдать осторожность, чтобы не исключить атаки путем внедрения кода SQL. Помимо проверки отсутствия в этих значениях недопустимых символов, всегда следует использовать параметризацию, которая отправляет значения отдельно от текста SQL.

Метод FromSqlRaw позволяет использовать именованные параметры в строке SQL-запроса, что полезно, если у хранимой процедуры есть необязательные параметры.

Создание с помощью LINQ

Метод compose можно использовать поверх исходного необработанного SQL-запроса с помощью операторов LINQ. EF Core будет обрабатывать его как вложенный запрос и производить составление на его основе в базе данных. В приведенном ниже примере используется необработанный SQL-запрос, который выбирает одну из функций с табличным значением. Затем запрос выполняет составление на ее основе с использованием LINQ для фильтрации и сортировки.

Приведенный выше запрос создает следующий код SQL:

Включение связанных данных

Метод Include можно использовать для включения связанных данных, как с любым другим запросом LINQ:

Отслеживание изменений

В следующем примере используется необработанный SQL-запрос, который выбирает одну из функций с табличным значением, а затем отключает отслеживание изменений с помощью вызова AsNoTracking :

Ограничения

Есть несколько ограничений, которые следует учитывать при использовании необработанных SQL-запросов:

Источник

Raw sql что это

ВЫБОР ТИПА ДАННЫ Х

Типы данных ORACLE

Следующие секции описывают типы данных ORACLE, которые могут использоваться в определениях столбцов.

Символьные типы данных

Типы данных CHAR и VARCHAR2 хранят алфавитно-цифровые данные; в столбце одного из этих типов данных можно хранить любые символы. Символьные данные хранятся как строки символов, где байтовые значения соответствуют схеме кодирования символов (обычно называемой набором символов или кодовой страницей); набор символов базы данных устанавливается при создании базы данных и никогда не изменяется. Примерами наборов символов служат 7-битовый ASCII (американский стандартный код для обмена информацией), кодовая страница 500 набора символов EBCDIC (расширенный двоично-кодированный десятичный код обмена) или Japan Extended UNIX. ORACLE поддерживает как однобайтовые, так и мультибайтовые схемы кодирования. Обратитесь к приложению E для дополнительной информации о средствах поддержки национальных языков (NLS) в ORACLE и о том, как поддерживаются различные схемы кодирования символов.

Так как ORACLE дополняет пробелами значения в столбцах CHAR и не дополняет пробелами значения в столбцах VARCHAR2, столбцы VARCHAR2 более экономны в смысле затрат памяти на хранение. По этой причине, при полном просмотре большой таблицы, содержащей столбцы VARCHAR2, необходимо прочитать меньше блоков, чем для аналогичной таблицы со столбцами CHAR. Если ваше приложение часто выполняет полные просмотры больших таблиц, содержащих символьные данные, вы можете улучшить производительность, выбрав для таких данных тип данных VARCHAR2 вместо CHAR.

ORACLE сравнивает значения CHAR, используя ДОПОЛНЯЮЩУЮ СЕМАНТИКУ сравнения. Если сравниваемые значения имеют разную длину, то ORACLE дополняет более короткое значение пробелами до равной длины. Если два значения отличаются лишь числом хвостовых пробелов, то они считаются равными.

Тип данных VARCHAR2

Тип данных VARCHAR2 хранит символьные строки ПЕРЕМЕННОЙ длины. При создании таблицы со столбцом VARCHAR2 для этого столбца задается максимальная длина (в байтах, а не в символах) от 1 до 2000. Для каждой строки, значение столбца VARCHAR2 записывается как поле переменной длины (если входное значение превышает максимальную длину столбца, ORACLE возвращает ошибку). Например, предположим, что столбец объявлен с типом VARCHAR2 и максимальной длиной 50 символов. Если входное значение для этого столбца имеет длину 10 символов, то (в однобайтовом наборе символов) значение столбца в строке будет иметь длину 10 символов (10 байт), а не 50.

ORACLE сравнивает значения VARCHAR2, используя НЕДОПОЛНЯЮЩУЮ СЕМАНТИКУ сравнения. Два значения считаются равными лишь тогда, когда они состоят из одних и тех же символов и имеют одинаковую длину.

Тип данных VARCHAR

Тип данных VARCHAR в настоящее время является синонимом типа данных VARCHAR2. Однако в будущей версии ORACLE тип данных VARCHAR будет хранить строки символов переменной длины с иной семантикой сравнения. Поэтому используйте тип данных VARCHAR2 для символьных строк переменной длины.

Длины столбцов для символьных типов данных

Тип данных NUMBER используется для хранения нуля и положительных или отрицательных чисел с фиксированной и плавающей точкой. Для этого типа данных гарантируется переносимость между любыми операционными системами, которые поддерживает ORACLE, с точностью до 38 цифр. Вы можете хранить положительные и отрицательные числа в интервале от 1 x 10**-130 до 9.99..9 x 10**125 (с точностью до 38 значащих цифр), а также ноль.

Для числовых столбцов можно просто указать NUMBER, например:

или можно указать ТОЧНОСТЬ (общее число цифр) и МАСШТАБ (число цифр справа от десятичной точки):

Хотя это и не обязательно, при задании числовых полей рекомендуется явно указывать точность и масштаб; это обеспечивает возможность дополнительной проверки данных на входе. На табл.3-1 приведены примеры хранения данных при использовании различных показателей масштаба.

Влияние показателя масштаба на хранение числовых данных

Тип данных DATE хранит значения в виде точек времени (т.е. дату и время). Тип данных DATE запоминает год (включая век), месяц, день, часы, минуты и секунды. ORACLE может хранить даты в диапазоне от 1 января 4712 года до н.э. до 31 декабря 4712 года нашей эры. Если в маске формата не указано BC (до н.э.), предполагается по умолчанию наша эра (AD).

ORACLE использует для хранения дат собственный внутренний формат. Данные дат хранятся в фиксированных полях длиной семь байт, соответствующих веку, году, месяцу, дню, часу, минуте и секунде.

Стандартный формат даты ORACLE для ввода и вывода имеет вид DD-MON-YY, например:

Этот умалчиваемый формат даты можно изменить для инстанции с помощью параметра NLS_DATE_FORMAT. Его можно также изменить на время сессии пользователя с помощью предложения ALTER SESSION. Для ввода дат в формате, отличном от текущего умалчиваемого формата даты, используйте функцию TO_DATE с маской формата, например:

Если используется стандартный формат DD-MON-YY, то YY указывает год в 20-м веке (например, 31-DEC-92 означает 31 декабря 1992 г.). Если вы хотите задавать годы в другом веке, используйте другую маску формата, как показано выше.

Время хранится в 24-часовом формате HH:MM:SS. Если не введено значение времени, по умолчанию предполагается полночь (12:00:00 A.M.). Если вводится только порция, содержащая время, то за дату принимается первый день текущего месяца. Для того, чтобы ввести в дату время, необходимо это указать в маске формата функции TO_DATE, например:

Для сравнения дат, содержащих время, используйте функцию SQL TRUNC, если вы хотите проигнорировать компоненту времени. Используйте функцию SQL SYSDATE, чтобы получить текущие системные дату и время. С помощью параметра FIXED_DATE можно установить SYSDATE как константу; это может быть полезно при отладке.

Использование юлианских дат

Юлианские даты позволяют датировать события от общей точки. (Эта точка принимается за 01-01-4712 г. до н.э., так что сегодняшние даты лежат где-то в пределах 2.4 миллиона дней.) Юлианская дата по определению нецелая, ее дробная часть составляет часть дня. ORACLE использует упрощенный подход, в котором используются целые числа. Юлианские даты могут вычисляться и интерпретироваться по-разному; метод, используемый в ORACLE, представляет дату в виде семизначного числа (для наиболее часто используемых дат); например, 8 апреля 1991 года будет представлено в виде 2448355.

Для преобразования дат в даты юлианского календаря в функциях преобразования даты (TO_DATE, TO_CHAR) может быть использована маска формата «J». Например следующий запрос возвращает все даты в юлианском формате:

Чтобы использовать юлианские даты в вычислениях, необходимо использовать также функцию TO_NUMBER. Для ввода юлианских дат можно использовать функцию TO_DATE:

Арифметика дат ORACLE учитывает аномалии исторически применяемых календарей. Например, при переходе с юлианского календаря на грегорианский календарь, 15 октября 1582 года, были потеряны предыдущие 10 дней (с 05 по 14 октября). Кроме того, год 0 не существует.

Пропущенные даты могут быть введены в базу данных, но они игнорируются в арифметике дат и рассматриваются как следующая «реальная» дата. Например, следующим днем за 04 октября 1582 будет 15 октября 1582, а следующим днем за 05 октября 1582 будет 16 октября 1582.

Замечание: Это обсуждение арифметики дат применимо не ко всем национальным стандартам дат (например, некоторым в Азии).

Столбец, описанный как LONG, может содержать символьную строку переменной длины до двух гигабайт. Столбцы типа LONG имеют многие характеристики столбцов VARCHAR2. Длина значений LONG может лимитироваться основной памятью, доступной на вашем компьютере.

Использование данных LONG

Тип данных LONG используется в словаре данных для хранения текста определений обзоров. Вы можете использовать столбцы, определенные как LONG, в списках SELECT, фразах SET предложений UPDATE и фразах VALUES предложений INSERT.

Ограничения на данные типа LONG и LONG RAW

Проектируя таблицы, содержащие данные LONG или LONG RAW, помещайте каждый столбец LONG или LONG RAW в особую таблицу, отдельно от любых других связанным с ними данных, вместо того чтобы хранить столбец LONG или LONG RAW в общей таблице с другими данными. После этого вы можете связать обе таблицы ограничением ссылочной целостности. Такой проект позволит предложениям SQL, использующим лишь другие ассоциированные данные, избежать сканирования по данным LONG или LONG RAW.

Чтобы сохранить информацию о журнальных статьях, включая текст каждой статьи, создайте две таблицы:

Таблица ARTICLE_TEXT хранит только текст каждой статьи. Таблица ARTICLE_HEADER хранит всю остальную информацию о статье, включая ее заголовок, первого автора, название журнала и дату публикации. Обе таблицы связаны ограничением ссылочной целостности по столбцу ID в каждой таблице.

Этот проект позволяет предложениям SQL опрашивать данные, отличные от текстов статей, не читая самих текстов. Например, если вы хотите выбрать всех первых авторов, публиковавшихся в журнале «Nature» в течение июля месяца 1991 года, вы можете выдать следующий запрос по таблице ARTICLE_HEADER:

Если бы текст каждой статьи хранился в одной таблице с ее автором, названием журнала и датой публикации, ORACLE пришлось бы просматривать все эти тексты при выполнении данного запроса.

Типы данных RAW и LONG RAW

Типы данных RAW и LONG RAW используются для данных, которые не должны ни интерпретироваться ORACLE, ни преобразовываться при передаче данных между различными системами. Эти типы данных предназначены для двоичных данных или байтовых строк. Например, LONG RAW можно использовать для хранения графики, звука, документов или массивов двоичных данных; их интерпретация зависит от их использования.

RAW эквивалентен VARCHAR2, а LONG RAW эквивалентен LONG, с тем исключением, что SQL*Net (который соединяет пользовательские сессии с инстанцией) и утилиты экспорта и импорта не выполняют преобразований при передаче данных RAW или LONG RAW. Напротив, SQL*Net и импорт/экспорт автоматически конвертируют данные CHAR, VARCHAR2 и LONG между набором символов базы данных и набором символов сессии пользователя (установленным параметром NLS_LANGUAGE или командой ALTER SESSION), если эти наборы символов различны.

Когда ORACLE автоматически преобразует данные RAW или LONG RAW в тип данных CHAR или из него (как в случае, когда данные RAW вводятся как литерал в предложении INSERT), эти данные рассматриваются как шестнадцатеричные цифры, каждая из которых представляет полубайт (четыре бита). Например, один байт данных RAW с битовым представлением 11001011 вводится и отображается как ‘CB’.

Данные LONG RAW не могут индексироваться, однако данные RAW можно индексировать.

ROWID’ы и тип данных ROWID

Каждой строке некластеризованной таблицы в базе данных ORACLE назначается уникальный ROWID, соответствующий физическому адресу данной строки (начального куска строки, если строка хранится как

несколько кусков, связанных в цепочку). В случае кластеризованных таблиц, строки разных таблиц, если они хранятся в одном и том же блоке данных, могут иметь одинаковый ROWID.

ROWID’ы используют двоичное представление физического адреса для каждой выбираемой строки. При запросах из SQL*Plus или SQL*DBA это двоичное представление преобразуется в шестнадцатеричное представление VARCHAR2, и запрос, показанный выше, мог бы возвратить следующую информацию строк:

ROWID, назначенный строке, остается неизменным до тех пор, пока строка не будет экспортирована и вновь импортирована (с помощью утилит IMPORT и EXPORT). Когда строка удаляется (и соответствующая транзакция подтверждена), ROWID, ассоциированный с удаленной строкой, может быть назначен строке, вставляемой в последующей транзакции.

Нельзя устанавливать значение псевдостолбца ROWID в предложениях INSERT или UPDATE. Значения ROWID в псевдостолбце ROWID внутренне используются ORACLE в разнообразных операциях (см. следующую секцию). Хотя к значениям псевдостолбца ROWID можно обращаться как к другим столбцам таблицы (в списках SELECT и фразах WHERE), эти значения не хранятся в базе данных и не являются данными базы данных.

ROWID’ы и базы данных не-ORACLE

Приложения базы данных ORACLE можно выполнять на серверах баз данных, отличных от ORACLE, используя SQL*Connect или Oracle Open Gateway. В таких случаях двоичный формат значений ROWID изменяется в соответствии с характеристиками системы не-ORACLE. Более того, стандартная трансляция значений ROWID в формат VARCHAR2/шестнадцатеричный недоступна. Программы могут по-прежнему использовать тип данных ROWID; однако они должны применять нестандартную трансляцию в шестнадцатеричный формат, используя до 256 байт. Обратитесь к соответствующему

руководству по OCI или прекомпилятору за дополнительными подробностями об использовании значений ROWID в сочетании с системами, отличными от ORACLE.

Как используются ROWID’ы

ROWID’ы внутренне используются ORACLE в конструкциях индексов. Каждый ключ в индексе ассоциируется с ROWID’ом, указывающим на адрес соответствующей строки, для быстрого доступа.

Прежде чем использовать ROWID’ы в предложениях DML, они должны быть проверены и гарантированы от изменений; иными словами, необходимые строки должны быть заблокированы, чтобы их нельзя было удалить. Попытка обращения к данным с некорректным значением ROWID приведет либо к тому, что строка не будет возвращена, либо к ошибке 1410 (неверный ROWID).

Вы можете также создавать таблицы со столбцами, определенными с типом данных ROWID; например, вы определяете таблицу исключений со столбцом типа данных ROWID, чтобы запоминать ROWID’ы тех строк базы данных, которые нарушают ограничения целостности. Столбцы, определенные с типом данных ROWID, ведут себя как обычные столбцы; их значения можно обновлять, и т.п. Все значения в столбце типа данных ROWID занимают шесть байт.

Примеры использования значений ROWID

Используя ROWID с некоторыми групповыми функциями, вы можете увидеть, как данные внутренне хранятся в базе данных ORACLE.

Функцию SUBSTR можно использовать, чтобы разбить значение ROWID на три его компоненты (файл, блок и строку). Например, запрос

мог бы возвратить следующие данные:

ROWID’ы могут быть полезны для получения информации о физическом хранении данных таблицы. Например, если вы хотите узнать о физическом размещении строк таблицы (скажем, принимая решение о разрезании таблицы), то следующий запрос сообщит вам, сколько файлов данных содержат строки заданной таблицы:

результаты этого запроса могут быть следующими:

Дополнительную информацию Вы можете получить в компании Interface Ltd.

Источник

документация Django 3.0

Explore the ORM before using raw SQL!

The Django ORM provides many tools to express queries without writing raw SQL. For example:

Выполнение чистых запросов¶

Метод raw() менеджера может использоваться для выполнения чистых SQL и возврата объектов модели:

This method takes a raw SQL query, executes it, and returns a django.db.models.query.RawQuerySet instance. This RawQuerySet instance can be iterated over like a normal QuerySet to provide object instances.

Лучше посмотрим на пример. Предположим у нас есть такая модель:

Вы можете выполнить следующий SQL:

Названия таблиц для моделей

Откуда берется название таблицы для модели Person в этом примере?

Соответствие полей ответа и полей модели¶

raw() автоматически сопоставляет поля ответа и поля модели.

Порядок полей в запросе не важен. Другими словами, следующие два запроса идентичны:

Пока названия полей совпадают, экземпляр модели будет успешно создан.

Индексирование¶

raw() поддерживает индексирование. Если вам нужна только первая запись результата, вы можете сделать следующее:

Отложенная загрузка полей¶

Часть полей при запросе может быть опущена:

На первый взгляд, кажется что запрос вернул имя и фамилию. Однако, на самом деле этот пример выполнил три Запроса. Только имя было получено при выполнении raw() – фамилия была получена при обращении к полю для двух объектов.

Добавление аннотации¶

Вы можете выполнять запросы, которые возвращают поля, отсутствующие в модели. Например, мы можем использовать функцию PostgreSQL age(), чтобы получить список людей и их возраст вычисленный базой данных:

Передача параметров в raw() ¶

Если вам необходимо выполнить запрос с параметрами, используйте аргумент params :

Параметры из словаря не поддерживаются SQLite, необходимо использовать список.

Не используйте форматирование строк в SQL запросе!

Очень заманчиво написать запрос следующим образом:

Или таким (со скобками вокруг %s ):

Не делайте этих ошибок.

Непосредственное выполнение SQL запроса¶

В таких случаях вы можете обратиться к базе данных напрямую в обход уровня моделей.

Чтобы обезопаситься против SQL инъекций, не следует добавлять скобки вокруг %s в SQL строке.

Если необходимо вставить символ процента, необходимо его продублировать:

По умолчанию Python DB API вернет результат без названий полей, то есть вы получите список значений, а не словарь. За счет небольшой потери производительности вы можете получить словарь следующим образом:

Еще один вариант – использовать collections.namedtuple() из стандартных библиотек Python. namedtuple – это объект с интерфейсом кортежа, который предоставляет доступ к полям через атрибут. Он также поддерживает индексы и итерацию. Результат нельзя поменять и можно получить через название поля или индекс:

Пример, показывающий разницу между тремя методами:

Подключения и указатель¶

Использование курсора как контекстный менеджер:

Вызов сохраненных процедуры¶

Например, у нас есть процедура в базе данных Oracle:

Источник