Microsoft SQL Server

Support for the Microsoft SQL Server database.

The following table summarizes current support levels for database release versions.

Поддерживаемые версии Microsoft SQL Server.

Support type

Versions

Fully tested in CI

2017

Normal support

2012+

Best effort

2005+

DBAPI Support

The following dialect/DBAPI options are available. Please refer to individual DBAPI sections for connect information.

Внешние диалекты

В дополнение к перечисленным выше уровням DBAPI с собственной поддержкой SQLAlchemy, существуют сторонние диалекты для других уровней DBAPI, совместимые с SQL Server. См. список «Внешние диалекты» на странице Диалекты.

Поведение автоинкремента / Колонки IDENTITY

SQL Server обеспечивает так называемое поведение «автоинкремента» с помощью конструкции IDENTITY, которая может быть помещена в любой отдельный целочисленный столбец в таблице. SQLAlchemy рассматривает IDENTITY в рамках стандартного поведения «автоинкремента» для целочисленного столбца первичного ключа, описанного в Column.autoincrement. Это означает, что по умолчанию первый целочисленный столбец первичного ключа в Table будет считаться столбцом идентификации - если он не связан с Sequence - и будет генерировать DDL как таковой:

from sqlalchemy import Table, MetaData, Column, Integer

m = MetaData()
t = Table('t', m,
        Column('id', Integer, primary_key=True),
        Column('x', Integer))
m.create_all(engine)

В приведенном выше примере DDL будет сгенерирован в виде:

CREATE TABLE t (
    id INTEGER NOT NULL IDENTITY,
    x INTEGER NULL,
    PRIMARY KEY (id)
)

Для случая, когда такое формирование по умолчанию IDENTITY нежелательно, укажите False для флага Column.autoincrement, для первого целочисленного столбца первичного ключа:

m = MetaData()
t = Table('t', m,
        Column('id', Integer, primary_key=True, autoincrement=False),
        Column('x', Integer))
m.create_all(engine)

Чтобы добавить ключевое слово IDENTITY к столбцу не первичного ключа, укажите True для флага Column.autoincrement на нужном объекте Column, и убедитесь, что Column.autoincrement установлено значение False на любом целочисленном столбце первичного ключа:

m = MetaData()
t = Table('t', m,
        Column('id', Integer, primary_key=True, autoincrement=False),
        Column('x', Integer, autoincrement=True))
m.create_all(engine)

Изменено в версии 1.4: Добавлена конструкция Identity в объекте Column для указания начального и инкрементного параметров IDENTITY. Она заменяет использование объекта Sequence для указания этих значений.

Не рекомендуется, начиная с версии 1.4: Параметры mssql_identity_start и mssql_identity_increment в Column являются устаревшими и должны быть заменены объектом Identity. Указание обоих способов настройки IDENTITY приведет к ошибке компиляции. Эти параметры также больше не возвращаются как часть ключа dialect_options в Inspector.get_columns(). Вместо этого используйте информацию в ключе identity.

Не рекомендуется, начиная с версии 1.3: Использование Sequence для указания характеристик IDENTITY устарело и будет удалено в одном из будущих выпусков. Пожалуйста, используйте параметры объекта Identity Identity.start и Identity.increment.

Изменено в версии 1.4: Удалена возможность использования объекта Sequence для изменения характеристик IDENTITY. Объекты Sequence теперь манипулируют только истинными типами T-SQL SEQUENCE.

Примечание

В таблице может быть только один столбец IDENTITY. При использовании autoincrement=True для включения ключевого слова IDENTITY, SQLAlchemy не защищает от одновременного указания опции для нескольких столбцов. Вместо этого база данных SQL Server отклонит оператор CREATE TABLE.

Примечание

Оператор INSERT, который пытается предоставить значение для столбца, помеченного IDENTITY, будет отклонен SQL Server. Для того чтобы значение было принято, необходимо включить опцию уровня сессии «SET IDENTITY_INSERT». Диалект SQLAlchemy SQL Server будет выполнять эту операцию автоматически при использовании конструкции ядра Insert; если при выполнении указывается значение для столбца IDENTITY, опция «IDENTITY_INSERT» будет включена на весь период вызова этого оператора. Однако этот сценарий не отличается высокой производительностью, и на него не следует полагаться при обычном использовании. Если таблица фактически не требует поведения IDENTITY в ее целочисленном столбце первичного ключа, ключевое слово следует отключить при создании таблицы, убедившись, что установлено значение autoincrement=False.

Управление «Старт» и «Инкремент»

Конкретный контроль над значениями «старт» и «инкремент» для генератора IDENTITY осуществляется с помощью параметров Identity.start и Identity.increment, передаваемых объекту Identity:

from sqlalchemy import Table, Integer, Column, Identity

test = Table(
    'test', metadata,
    Column(
        'id',
        Integer,
        primary_key=True,
        Identity(start=100, increment=10)
    ),
    Column('name', String(20))
)

CREATE TABLE для вышеуказанного объекта Table будет иметь вид:

CREATE TABLE test (
  id INTEGER NOT NULL IDENTITY(100,10) PRIMARY KEY,
  name VARCHAR(20) NULL,
  )

Примечание

Объект Identity поддерживает множество других параметров в дополнение к start и increment. Они не поддерживаются SQL Server и будут проигнорированы при генерации ddl CREATE TABLE.

Изменено в версии 1.3.19: Объект Identity теперь используется для воздействия на генератор IDENTITY для Column под SQL Server. Ранее использовался объект Sequence. Поскольку SQL Server теперь поддерживает реальные последовательности как отдельную конструкцию, Sequence будет функционировать обычным образом, начиная с версии SQLAlchemy 1.4.

Использование IDENTITY с нецелочисленными числовыми типами

SQL Server также позволяет использовать IDENTITY с колонками NUMERIC. Чтобы плавно реализовать этот паттерн в SQLAlchemy, первичный тип данных столбца должен оставаться Integer, однако базовый тип реализации, развернутый в базе данных SQL Server, может быть указан как Numeric с помощью TypeEngine.with_variant():

from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import Numeric
from sqlalchemy import String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class TestTable(Base):
    __tablename__ = "test"
    id = Column(
        Integer().with_variant(Numeric(10, 0), "mssql"),
        primary_key=True,
        autoincrement=True,
    )
    name = Column(String)

В приведенном выше примере Integer().with_variant() предоставляет четкую информацию об использовании, которая точно описывает намерение кода. Общее ограничение, что autoincrement применяется только к Integer, установлено на уровне метаданных, а не на уровне каждого диалекта.

При использовании приведенного выше шаблона идентификатор первичного ключа, возвращаемый при вставке строки, который также является значением, которое будет присвоено объекту ORM, такому как TestTable выше, будет экземпляром Decimal(), а не int при использовании SQL Server. Числовой тип возврата типа Numeric может быть изменен на возврат плавающих значений путем передачи False в Numeric.asdecimal. Чтобы нормализовать тип возврата вышеуказанного Numeric(10, 0) для возврата Python ints (который также поддерживает «длинные» целые значения в Python 3), используйте TypeDecorator следующим образом:

from sqlalchemy import TypeDecorator

class NumericAsInteger(TypeDecorator):
    '''normalize floating point return values into ints'''

    impl = Numeric(10, 0, asdecimal=False)
    cache_ok = True

    def process_result_value(self, value, dialect):
        if value is not None:
            value = int(value)
        return value

class TestTable(Base):
    __tablename__ = "test"
    id = Column(
        Integer().with_variant(NumericAsInteger, "mssql"),
        primary_key=True,
        autoincrement=True,
    )
    name = Column(String)

Поведение INSERT

Работа со столбцом IDENTITY во время INSERT включает две ключевые техники. Наиболее распространенной является возможность получения «последнего вставленного значения» для данного столбца IDENTITY, процесс, который SQLAlchemy выполняет неявно во многих случаях, в основном в рамках ORM.

Процесс получения этого значения имеет несколько вариантов:

  • В подавляющем большинстве случаев RETURNING используется в сочетании с операторами INSERT на SQL Server для того, чтобы получить вновь созданные значения первичного ключа:

    INSERT INTO t (x) OUTPUT inserted.id VALUES (?)

    Начиная с SQLAlchemy 2.0, функция Поведение «Вставка многих значений» для операторов INSERT также используется по умолчанию для оптимизации многорядных операторов INSERT; для SQL Server эта функция применяется как для операторов INSERT с возвратом, так и без возврата.

    Изменено в версии 2.0.10: Функция Поведение «Вставка многих значений» для операторов INSERT для SQL Server была временно отключена в SQLAlchemy версии 2.0.9 из-за проблем с упорядочиванием строк. Начиная с версии 2.0.10 эта функция снова включена, причем в специальном случае она обрабатывает требование единицы работы о необходимости упорядочивания RETURNING.

  • Если RETURNING недоступен или был отключен через implicit_returning=False, используется либо функция scope_identity(), либо переменная @@identity; поведение зависит от бэкенда:

    • при использовании PyODBC к концу оператора INSERT будет добавлена фраза ; select scope_identity(); для получения значения будет получен второй набор результатов. Дана таблица в виде:

      t = Table(
          't',
          metadata,
          Column('id', Integer, primary_key=True),
          Column('x', Integer),
          implicit_returning=False
      )

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

      INSERT INTO t (x) VALUES (?); select scope_identity()
    • Другие диалекты, такие как pymssql, будут обращаться к SELECT scope_identity() AS lastrowid после оператора INSERT. Если флаг use_scope_identity=False передан в create_engine(), то вместо него используется оператор SELECT @@identity AS lastrowid.

Таблица, содержащая столбец IDENTITY, запрещает оператор INSERT, который явно ссылается на столбец идентификации. Диалект SQLAlchemy определит, когда конструкция INSERT, созданная с использованием основной конструкции insert() (а не простой строки SQL), ссылается на столбец идентификации, и в этом случае будет выдавать SET IDENTITY_INSERT ON до начала выполнения оператора вставки и SET IDENTITY_INSERT OFF после выполнения. Приведенный пример:

m = MetaData()
t = Table('t', m, Column('id', Integer, primary_key=True),
                Column('x', Integer))
m.create_all(engine)

with engine.begin() as conn:
    conn.execute(t.insert(), {'id': 1, 'x':1}, {'id':2, 'x':2})

Вышеуказанный столбец будет создан с IDENTITY, однако в операторе INSERT мы указываем явные значения. В выводе echo мы можем увидеть, как SQLAlchemy обрабатывает это:

CREATE TABLE t (
    id INTEGER NOT NULL IDENTITY(1,1),
    x INTEGER NULL,
    PRIMARY KEY (id)
)

COMMIT
SET IDENTITY_INSERT t ON
INSERT INTO t (id, x) VALUES (?, ?)
((1, 1), (2, 2))
SET IDENTITY_INSERT t OFF
COMMIT

Это вспомогательный вариант использования, подходящий для тестирования и сценариев массовой вставки.

поддержка SEQUENCE

Объект Sequence создает «настоящие» последовательности, т.е. CREATE SEQUENCE:

>>> from sqlalchemy import Sequence
>>> from sqlalchemy.schema import CreateSequence
>>> from sqlalchemy.dialects import mssql
>>> print(CreateSequence(Sequence("my_seq", start=1)).compile(dialect=mssql.dialect()))
{printsql}CREATE SEQUENCE my_seq START WITH 1

Для генерации целочисленных первичных ключей, как правило, следует предпочесть конструкцию SQL Server IDENTITY по сравнению с последовательностью.

Совет

Начальным значением по умолчанию для T-SQL является -2**63, а не 1, как в большинстве других баз данных SQL. Пользователи должны явно установить Sequence.start на 1, если это ожидаемое значение по умолчанию:

seq = Sequence("my_sequence", start=1)

Добавлено в версии 1.4: добавлена поддержка SQL Server для Sequence

Изменено в версии 2.0: Диалект SQL Server больше не будет неявно отображать «START WITH 1» для CREATE SEQUENCE, что было впервые реализовано в версии 1.4.

MAX на VARCHAR / NVARCHAR

SQL Server поддерживает специальную строку «MAX» в типах данных VARCHAR и NVARCHAR для указания «максимально возможной длины». В настоящее время диалект обрабатывает это как длину «None» в базовом типе, а не предоставляет специфическую для диалекта версию этих типов, так что базовый тип, указанный как VARCHAR(None), может принимать поведение «unlengthed» на более чем одном бэкенде без использования специфических для диалекта типов.

Чтобы создать SQL Server VARCHAR или NVARCHAR с длиной MAX, используйте None:

my_table = Table(
    'my_table', metadata,
    Column('my_data', VARCHAR(None)),
    Column('my_n_data', NVARCHAR(None))
)

Поддержка коллаборации

Коллизии символов поддерживаются базовыми типами строк, задаваемыми строковым аргументом «collation»:

from sqlalchemy import VARCHAR
Column('login', VARCHAR(32, collation='Latin1_General_CI_AS'))

Когда такой столбец связан с Table, оператор CREATE TABLE для этого столбца выдаст:

login VARCHAR(32) COLLATE Latin1_General_CI_AS NULL

Поддержка лимитов/выбросов

В MSSQL добавлена поддержка LIMIT / OFFSET, начиная с SQL Server 2012, с помощью положений «OFFSET n ROWS» и «FETCH NEXT n ROWS». SQLAlchemy поддерживает эти синтаксисы автоматически, если обнаружен SQL Server 2012 или выше.

Изменено в версии 1.4: добавлена поддержка синтаксиса SQL Server «OFFSET n ROWS» и «FETCH NEXT n ROWS».

Для операторов, в которых указан только LIMIT и нет OFFSET, все версии SQL Server поддерживают ключевое слово TOP. Этот синтаксис используется во всех версиях SQL Server, когда отсутствует оговорка OFFSET. Утверждение типа:

select(some_table).limit(5)

будет выглядеть аналогично:

SELECT TOP 5 col1, col2.. FROM table

В версиях SQL Server до SQL Server 2012 оператор, использующий LIMIT и OFFSET или только OFFSET, будет отображаться с помощью оконной функции ROW_NUMBER(). Утверждение типа:

select(some_table).order_by(some_table.c.col3).limit(5).offset(10)

будет выглядеть аналогично:

SELECT anon_1.col1, anon_1.col2 FROM (SELECT col1, col2,
ROW_NUMBER() OVER (ORDER BY col3) AS
mssql_rn FROM table WHERE t.x = :x_1) AS
anon_1 WHERE mssql_rn > :param_1 AND mssql_rn <= :param_2 + :param_1

Обратите внимание, что при использовании LIMIT и/или OFFSET, как в старых, так и в новых синтаксисах SQL Server, в операторе также должен присутствовать ORDER BY, иначе возникает ошибка CompileError.

Поддержка комментариев DDL

Поддержка комментариев, включающая рендеринг DDL для таких атрибутов, как Table.comment и Column.comment, а также возможность отражения этих комментариев, поддерживается при условии, что используется поддерживаемая версия SQL Server. Если при первом подключении обнаруживается неподдерживаемая версия, например Azure Synapse (на основании наличия функции SQL fn_listextendedproperty), поддержка комментариев, включая рендеринг и отражение комментариев в таблицах, отключается, поскольку обе эти функции зависят от хранимых процедур и функций SQL Server, которые доступны не на всех типах бэкендов.

Чтобы принудительно включить или выключить поддержку комментариев, минуя автоопределение, установите параметр supports_comments в пределах create_engine():

e = create_engine("mssql+pyodbc://u:p@dsn", supports_comments=False)

Добавлено в версии 2.0: Добавлена поддержка комментариев к таблицам и столбцам для диалекта SQL Server, включая генерацию и отражение DDL.

Уровень изоляции транзакций

Все диалекты SQL Server поддерживают установку уровня изоляции транзакций как через специфический для диалекта параметр create_engine.isolation_level, принимаемый командой create_engine(), так и через аргумент Connection.execution_options.isolation_level, передаваемый команде Connection.execution_options(). Эта возможность работает путем выдачи команды SET TRANSACTION ISOLATION LEVEL <level> для каждого нового соединения.

Чтобы установить уровень изоляции с помощью create_engine():

engine = create_engine(
    "mssql+pyodbc://scott:tiger@ms_2008",
    isolation_level="REPEATABLE READ"
)

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

connection = engine.connect()
connection = connection.execution_options(
    isolation_level="READ COMMITTED"
)

Допустимые значения для isolation_level включают:

  • AUTOCOMMIT - pyodbc / pymssql-specific

  • READ COMMITTED

  • READ UNCOMMITTED

  • REPEATABLE READ

  • SERIALIZABLE

  • SNAPSHOT - специфично для SQL Server

Существует также больше вариантов конфигураций уровня изоляции, например, объекты «sub-engine», связанные с основным Engine, в каждом из которых применяются различные настройки уровня изоляции. См. обсуждение в Установка уровней изоляции транзакций, включая DBAPI Autocommit для справки.

Временная таблица / сброс ресурсов для пула соединений

Реализация пула соединений QueuePool, используемая объектом SQLAlchemy Engine, включает поведение reset on return, которое будет вызывать метод DBAPI .rollback(), когда соединения возвращаются в пул. Хотя этот откат очищает непосредственное состояние, использованное предыдущей транзакцией, он не охватывает более широкий диапазон состояния на уровне сеанса, включая временные таблицы, а также другие состояния сервера, такие как обработчики подготовленных операторов и кэши операторов. Недокументированная процедура SQL Server, известная как sp_reset_connection, является обходным решением этой проблемы, которое сбрасывает большую часть состояния сеанса, накапливающегося при подключении, включая временные таблицы.

Чтобы установить sp_reset_connection в качестве средства выполнения сброса при возврате, можно использовать крючок события PoolEvents.reset(), как показано в примере ниже. Параметр create_engine.pool_reset_on_return установлен в None, чтобы пользовательская схема могла полностью заменить поведение по умолчанию. Реализация пользовательского хука в любом случае вызывает .rollback(), поскольку обычно важно, чтобы собственное отслеживание DBAPI фиксации/отката оставалось согласованным с состоянием транзакции:

from sqlalchemy import create_engine
from sqlalchemy import event

mssql_engine = create_engine(
    "mssql+pyodbc://scott:tiger^5HHH@mssql2017:1433/test?driver=ODBC+Driver+17+for+SQL+Server",

    # disable default reset-on-return scheme
    pool_reset_on_return=None,
)


@event.listens_for(mssql_engine, "reset")
def _reset_mssql(dbapi_connection, connection_record, reset_state):
    if not reset_state.terminate_only:
        dbapi_connection.execute("{call sys.sp_reset_connection}")

    # so that the DBAPI itself knows that the connection has been
    # reset
    dbapi_connection.rollback()

Изменено в версии 2.0.0b3: Добавлены дополнительные аргументы состояния для события PoolEvents.reset() и дополнительно обеспечен вызов события для всех случаев «сброса», так что его можно использовать в качестве места для пользовательских обработчиков «сброса». Предыдущие схемы, использующие обработчик PoolEvents.checkin(), также остаются пригодными для использования.

Недействительность

MSSQL поддерживает три уровня нулевого значения столбцов. Нулевой уровень по умолчанию допускает нули и явно выражен в конструкции CREATE TABLE:

name VARCHAR(20) NULL

Если указано nullable=None, то спецификация не производится. Другими словами, используется конфигурация базы данных по умолчанию. В результате будет выведено:

name VARCHAR(20)

Если nullable равно True или False, то столбец будет NULL или NOT NULL соответственно.

Обработка даты/времени

Поддерживаются значения DATE и TIME. Параметры привязки преобразуются в объекты datetime.datetime(), как того требует большинство драйверов MSSQL, а результаты при необходимости обрабатываются из строк. Типы DATE и TIME недоступны для MSSQL 2005 и предыдущих версий - если обнаружена версия сервера ниже 2008, DDL для этих типов будет выдаваться как DATETIME.

Обесценивание больших текстовых/двоичных типов

Согласно SQL Server 2012/2014 Documentation, типы данных NTEXT, TEXT и IMAGE будут удалены из SQL Server в будущем выпуске. SQLAlchemy обычно соотносит эти типы с типами данных UnicodeText, TextClause и LargeBinary.

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

  • Когда этот флаг равен True, типы данных UnicodeText, TextClause и LargeBinary при использовании для вывода DDL будут выводить типы NVARCHAR(max), VARCHAR(max) и VARBINARY(max) соответственно. Это новое поведение с момента добавления данного флага.

  • Когда этот флаг равен False, типы данных UnicodeText, TextClause и LargeBinary при использовании для вывода DDL будут выводить типы NTEXT, TEXT и IMAGE соответственно. Таково давнее поведение этих типов.

  • Флаг начинается со значения None, до установления соединения с базой данных. Если диалект используется для вывода DDL без установки флага, он интерпретируется так же, как False.

  • При первом подключении диалект определяет, используется ли SQL Server версии 2012 или выше; если флаг все еще находится на None, он устанавливает его на True или False в зависимости от того, обнаружена ли версия 2012 или выше.

  • Флаг может быть установлен в значение True или False при создании диалекта, обычно через create_engine():

    eng = create_engine("mssql+pymssql://user:pass@host/db",
                    deprecate_large_types=True)
  • Полный контроль над тем, выводятся ли «старые» или «новые» типы, доступен во всех версиях SQLAlchemy, если вместо них использовать объекты типа UPPERCASE: NVARCHAR, VARCHAR, VARBINARY, TEXT, NTEXT, IMAGE всегда будут оставаться фиксированными и всегда выводить именно этот тип.

Имена многосторонних схем

Схемы SQL Server иногда требуют наличия нескольких частей в их классификаторе «schema», то есть включают имя базы данных и имя владельца как отдельные маркеры, например mydatabase.dbo.some_table. Эти многочастные имена могут быть заданы сразу с помощью аргумента Table.schema в Table:

Table(
    "some_table", metadata,
    Column("q", String(50)),
    schema="mydatabase.dbo"
)

При выполнении таких операций, как отражение таблиц или компонентов, аргумент схемы, содержащий точку, будет разделен на отдельные компоненты «база данных» и «владелец», чтобы правильно запросить таблицы информационной схемы SQL Server, поскольку эти два значения хранятся отдельно. Кроме того, при отображении имени схемы для DDL или SQL эти два компонента будут заключены в отдельные кавычки для имен, чувствительных к регистру, и других специальных символов. Приведенный ниже аргумент:

Table(
    "some_table", metadata,
    Column("q", String(50)),
    schema="MyDataBase.dbo"
)

Приведенная выше схема будет отображаться как [MyDataBase].dbo, а также при отражении будет отражена с использованием «dbo» в качестве владельца и «MyDataBase» в качестве имени базы данных.

Чтобы контролировать, как имя схемы разбивается на базу данных / владельца, укажите в имени скобки (которые в SQL Server являются символами кавычек). Ниже «владелец» будет рассматриваться как MyDataBase.dbo, а «база данных» будет None:

Table(
    "some_table", metadata,
    Column("q", String(50)),
    schema="[MyDataBase.dbo]"
)

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

Table(
    "some_table", metadata,
    Column("q", String(50)),
    schema="[MyDataBase.Period].[MyOwner.Dot]"
)

Изменено в версии 1.2: диалект SQL Server теперь рассматривает скобки как разделители идентификаторов, разделяя схему на отдельные маркеры базы данных и владельца, чтобы разрешить точки внутри любого имени.

Режим унаследованной схемы

В очень старых версиях диалекта MSSQL было реализовано поведение, при котором таблица с критериями схемы автоматически сглаживалась при использовании в операторе SELECT; давалась таблица:

account_table = Table(
    'account', metadata,
    Column('id', Integer, primary_key=True),
    Column('info', String(100)),
    schema="customer_schema"
)

этот унаследованный способ визуализации предполагает, что «customer_schema.account» не будет принят всеми частями SQL-оператора, как показано ниже:

>>> eng = create_engine("mssql+pymssql://mydsn", legacy_schema_aliasing=True)
>>> print(account_table.select().compile(eng))
{printsql}SELECT account_1.id, account_1.info
FROM customer_schema.account AS account_1

Этот режим поведения теперь отключен по умолчанию, так как он, похоже, не имеет смысла; однако в случае, если унаследованные приложения полагаются на него, он доступен с помощью аргумента legacy_schema_aliasing в create_engine(), как показано выше.

Не рекомендуется, начиная с версии 1.4: Флаг legacy_schema_aliasing теперь устарел и будет удален в одном из будущих выпусков.

Поддержка кластеризованных индексов

Диалект MSSQL поддерживает кластеризованные индексы (и первичные ключи) с помощью опции mssql_clustered. Эта опция доступна для Index, UniqueConstraint и PrimaryKeyConstraint.

Для создания кластеризованного индекса:

Index("my_index", table.c.x, mssql_clustered=True)

который отображает индекс как CREATE CLUSTERED INDEX my_index ON table (x).

Для создания кластеризованного первичного ключа используйте:

Table('my_table', metadata,
      Column('x', ...),
      Column('y', ...),
      PrimaryKeyConstraint("x", "y", mssql_clustered=True))

что приведет к отображению таблицы, например, в виде:

CREATE TABLE my_table (x INTEGER NOT NULL, y INTEGER NOT NULL,
                       PRIMARY KEY CLUSTERED (x, y))

Аналогично, мы можем сгенерировать кластеризованное уникальное ограничение, используя:

Table('my_table', metadata,
      Column('x', ...),
      Column('y', ...),
      PrimaryKeyConstraint("x"),
      UniqueConstraint("y", mssql_clustered=True),
      )

Чтобы явно запросить некластеризованный первичный ключ (например, когда требуется отдельный кластеризованный индекс), используйте:

Table('my_table', metadata,
      Column('x', ...),
      Column('y', ...),
      PrimaryKeyConstraint("x", "y", mssql_clustered=False))

что приведет к отображению таблицы, например, в виде:

CREATE TABLE my_table (x INTEGER NOT NULL, y INTEGER NOT NULL,
                       PRIMARY KEY NONCLUSTERED (x, y))

Параметры индексов, специфичные для MSSQL

Помимо кластеризации, диалект MSSQL поддерживает и другие специальные опции для Index.

ВКЛЮЧИТЬ

Опция mssql_include выводит INCLUDE(colname) для заданных строковых имен:

Index("my_index", table.c.x, mssql_include=['y'])

будет отображать индекс как CREATE INDEX my_index ON table (x) INCLUDE (y).

Фильтрованные индексы

Опция mssql_where выводит WHERE(условие) для заданных строковых имен:

Index("my_index", table.c.x, mssql_where=table.c.x > 10)

будет отображать индекс как CREATE INDEX my_index ON table (x) WHERE x > 10.

Добавлено в версии 1.3.4.

Упорядочение индексов

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

Index("my_index", table.c.x.desc())

будет отображать индекс как CREATE INDEX my_index ON table (x DESC).

Уровни совместимости

MSSQL поддерживает понятие установки уровней совместимости на уровне базы данных. Это позволяет, например, запускать базу данных, совместимую с SQL2000, при работе на сервере баз данных SQL2005. server_version_info всегда будет возвращать информацию о версии сервера базы данных (в данном случае SQL2005), а не информацию об уровне совместимости. Из-за этого при работе в режиме обратной совместимости SQLAlchemy может попытаться использовать операторы T-SQL, которые не могут быть разобраны сервером базы данных.

Триггеры

SQLAlchemy по умолчанию использует OUTPUT INSERTED для получения новых значений первичного ключа через столбцы IDENTITY или другие значения по умолчанию на стороне сервера. MS-SQL не позволяет использовать OUTPUT INSERTED в таблицах с триггерами. Чтобы отключить использование OUTPUT INSERTED для каждой таблицы, укажите implicit_returning=False для каждой Table, в которой есть триггеры:

Table('mytable', metadata,
    Column('id', Integer, primary_key=True),
    # ...,
    implicit_returning=False
)

Декларативная форма:

class MyClass(Base):
    # ...
    __table_args__ = {'implicit_returning':False}

Поддержка Rowcount / Версионирование ORM

Драйверы SQL Server могут иметь ограниченную возможность возвращать количество строк, обновленных в операторе UPDATE или DELETE.

На момент написания этой статьи драйвер PyODBC не может возвращать количество рядов при использовании OUTPUT INSERTED. Поэтому предыдущие версии SQLAlchemy имели ограничения для таких функций, как «ORM Versioning», которая полагается на точный подсчет рядов, чтобы сопоставить номера версий с совпадающими строками.

SQLAlchemy 2.0 теперь извлекает «rowcount» вручную для этих конкретных случаев использования, основываясь на подсчете строк, которые вернулись в RETURNING; поэтому, хотя драйвер все еще имеет это ограничение, функция ORM Versioning больше не влияет на него. Начиная с версии SQLAlchemy 2.0.5, функция ORM Versioning была полностью восстановлена для драйвера pyodbc.

Изменено в версии 2.0.5: Восстановлена поддержка версионности ORM для драйвера pyodbc. Ранее во время ORM flush выдавалось предупреждение о том, что версионность не поддерживается.

Включение изоляции моментальных снимков

В SQL Server по умолчанию используется режим изоляции транзакций, который блокирует целые таблицы, что приводит к длительному удержанию блокировок и частым тупикам даже в слабо параллельных приложениях. Для современных уровней поддержки параллелизма рекомендуется включить изоляцию моментальных снимков для базы данных в целом. Это достигается с помощью следующих команд ALTER DATABASE, выполняемых в подсказке SQL:

ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON

Справочную информацию об изоляции моментальных снимков SQL Server можно найти на сайте https://msdn.microsoft.com/en-us/library/ms175095.aspx.

Конструкции SQL Server SQL

Object Name Description

try_cast(expression, type_)

Выражение TRY_CAST для бэкендов, которые его поддерживают; это CAST, которое возвращает NULL для некастируемых преобразований.

function sqlalchemy.dialects.mssql.try_cast(expression: _ColumnExpressionOrLiteralArgument[Any], type_: _TypeEngineArgument[_T]) TryCast[_T]

Выражение TRY_CAST для бэкендов, которые его поддерживают; это CAST, которое возвращает NULL для некастируемых преобразований.

В SQLAlchemy эта конструкция поддерживается только диалектом SQL Server, и при ее использовании на других включенных бэкендах будет возникать ошибка CompileError. Однако сторонние бэкенды также могут поддерживать эту конструкцию.

Совет

Поскольку try_cast() происходит из диалекта SQL Server, его можно импортировать как из sqlalchemy., так и из sqlalchemy.dialects.mssql.

try_cast() возвращает экземпляр TryCast и в целом ведет себя аналогично конструкции Cast; на уровне SQL разница между CAST и TRY_CAST заключается в том, что TRY_CAST возвращает NULL для некастируемого выражения, например, при попытке привести строку "hi" к целочисленному значению.

Например:

from sqlalchemy import select, try_cast, Numeric

stmt = select(
    try_cast(product_table.c.unit_price, Numeric(10, 4))
)

На Microsoft SQL Server вышеприведенные данные будут выглядеть следующим образом:

SELECT TRY_CAST (product_table.unit_price AS NUMERIC(10, 4))
FROM product_table

Добавлено в версии 2.0.14: try_cast() был обобщен из диалекта SQL Server в конструкцию общего использования, которая может поддерживаться другими диалектами.

Типы данных SQL Server

Как и во всех диалектах SQLAlchemy, все типы UPPERCASE, которые, как известно, действительны для SQL сервера, импортируются из диалекта верхнего уровня, независимо от того, происходят ли они из sqlalchemy.types или из локального диалекта:

from sqlalchemy.dialects.mssql import (
    BIGINT,
    BINARY,
    BIT,
    CHAR,
    DATE,
    DATETIME,
    DATETIME2,
    DATETIMEOFFSET,
    DECIMAL,
    DOUBLE_PRECISION,
    FLOAT,
    IMAGE,
    INTEGER,
    JSON,
    MONEY,
    NCHAR,
    NTEXT,
    NUMERIC,
    NVARCHAR,
    REAL,
    SMALLDATETIME,
    SMALLINT,
    SMALLMONEY,
    SQL_VARIANT,
    TEXT,
    TIME,
    TIMESTAMP,
    TINYINT,
    UNIQUEIDENTIFIER,
    VARBINARY,
    VARCHAR,
)

Ниже перечислены типы, специфичные для SQL Server или имеющие специфичные для SQL Server аргументы построения:

Object Name Description

BIT

Тип MSSQL BIT.

DATETIME2

DATETIMEOFFSET

DOUBLE_PRECISION

тип данных SQL Server DOUBLE PRECISION.

IMAGE

JSON

Тип MSSQL JSON.

MONEY

NTEXT

Тип MSSQL NTEXT, для текста переменной длины в юникоде до 2^30 символов.

REAL

тип данных SQL Server REAL.

ROWVERSION

Внедрите тип SQL Server ROWVERSION.

SMALLDATETIME

SMALLMONEY

SQL_VARIANT

TIME

TIMESTAMP

Внедрите тип SQL Server TIMESTAMP.

TINYINT

UNIQUEIDENTIFIER

XML

MSSQL Тип XML.

class sqlalchemy.dialects.mssql.BIT

Тип MSSQL BIT.

И pyodbc, и pymssql возвращают значения из BIT-колонок как Python <class „bool“>, поэтому просто подкласс Boolean.

Members

__init__()

Классная подпись

класс sqlalchemy.dialects.mssql.BIT (sqlalchemy.types.Boolean)

method sqlalchemy.dialects.mssql.BIT.__init__(create_constraint: bool = False, name: Optional[str] = None, _create_events: bool = True, _adapted_from: Optional[SchemaType] = None)

наследуется от sqlalchemy.types.Boolean.__init__ метода Boolean

Сконструируйте булево значение.

Параметры:
  • create_constraint – по умолчанию имеет значение False. Если булево значение генерируется как int/smallint, также создайте ограничение CHECK на таблице, которое гарантирует 1 или 0 в качестве значения. … примечание:: настоятельно рекомендуется, чтобы ограничение CHECK имело явное имя для поддержки управления схемой. Это можно сделать либо путем установки параметра Boolean.name, либо путем создания соответствующего соглашения об именовании; см. справочную информацию в Настройка соглашений об именовании ограничений. … versionchanged:: 1.4 - этот флаг теперь имеет значение по умолчанию False, что означает, что для неродного перечислимого типа не генерируется ограничение CHECK.

  • name – если создается ограничение CHECK, укажите имя ограничения.

class sqlalchemy.dialects.mssql.CHAR

Тип SQL CHAR.

Классная подпись

класс sqlalchemy.dialects.mssql.CHAR (sqlalchemy.types.String)

method sqlalchemy.dialects.mssql.CHAR.__init__(length: Optional[int] = None, collation: Optional[str] = None)

наследуется от sqlalchemy.types.String.__init__ метода String

Создайте тип, удерживающий строку.

Параметры:
  • length – необязательный, длина столбца для использования в выражениях DDL и CAST. Может быть опущена, если не будет выдаваться CREATE TABLE. Некоторые базы данных могут требовать length для использования в DDL, и будут выдавать исключение при выдаче CREATE TABLE DDL, если включена VARCHAR без длины. Интерпретируется ли значение как байты или как символы, зависит от конкретной базы данных.

  • collation – Необязательно, collation на уровне столбцов для использования в выражениях DDL и CAST. Используется ключевое слово COLLATE, поддерживаемое SQLite, MySQL и PostgreSQL. Например: … sourcecode:: pycon+sql >>> from sqlalchemy import cast, select, String >>> print(select(cast(„some string“, String(collation=“utf8“)))) {printsql}SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1 … примечание:: В большинстве случаев для Unicode или UnicodeText следует использовать типы данных Column, которые предполагают хранение неасксичных данных. Эти типы данных гарантируют, что в базе данных используются правильные типы.

class sqlalchemy.dialects.mssql.DATETIME2

Классная подпись

класс sqlalchemy.dialects.mssql.DATETIME2 (sqlalchemy.dialects.mssql.base._DateTimeBase, sqlalchemy.types.DateTime)

class sqlalchemy.dialects.mssql.DATETIMEOFFSET

Классная подпись

класс sqlalchemy.dialects.mssql.DATETIMEOFFSET (sqlalchemy.dialects.mssql.base._DateTimeBase, sqlalchemy.types.DateTime)

class sqlalchemy.dialects.mssql.DOUBLE_PRECISION

тип данных SQL Server DOUBLE PRECISION.

Добавлено в версии 2.0.11.

class sqlalchemy.dialects.mssql.IMAGE

Members

__init__()

Классная подпись

класс sqlalchemy.dialects.mssql.IMAGE (sqlalchemy.types.LargeBinary)

method sqlalchemy.dialects.mssql.IMAGE.__init__(length: Optional[int] = None)

наследуется от sqlalchemy.types.LargeBinary.__init__ метода LargeBinary

Создайте тип LargeBinary.

Параметры:

length – опционально, длина столбца для использования в DDL-запросах, для тех бинарных типов, которые принимают длину, например, тип MySQL BLOB.

class sqlalchemy.dialects.mssql.JSON

Тип MSSQL JSON.

MSSQL поддерживает данные в формате JSON начиная с SQL Server 2016.

Тип данных JSON на уровне DDL будет представлять тип данных как NVARCHAR(max), но обеспечивает функции сравнения на уровне JSON, а также поведение принуждения Python.

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

См.также

JSON - основная документация по общему кроссплатформенному типу данных JSON.

Тип JSON поддерживает сохранение значений JSON, а также основные индексные операции, предоставляемые типом данных JSON, путем адаптации операций для отображения функций JSON_VALUE или JSON_QUERY на уровне базы данных.

Тип SQL Server JSON обязательно использует функции JSON_QUERY и JSON_VALUE при запросе элементов объекта JSON. Эти две функции имеют серьезное ограничение, заключающееся в том, что они взаимоисключающие в зависимости от типа возвращаемого объекта. Функция JSON_QUERY только возвращает словарь или список JSON, но не отдельный строковый, числовой или булевский элемент; функция JSON_VALUE только возвращает отдельный строковый, числовой или булевский элемент. Обе функции либо возвращают NULL, либо выдают ошибку, если они не используются против правильного ожидаемого значения.

Чтобы справиться с этим неудобным требованием, правила индексированного доступа выглядят следующим образом:

  1. При извлечении подэлемента из JSON, который сам является JSON-словарем или списком, следует использовать аксессор Comparator.as_json():

    stmt = select(
        data_table.c.data["some key"].as_json()
    ).where(
        data_table.c.data["some key"].as_json() == {"sub": "structure"}
    )
  2. При извлечении подэлемента из JSON, который является обычным булевым, строковым, целочисленным или плавающим, используйте соответствующий метод среди Comparator.as_boolean(), Comparator.as_string(), Comparator.as_integer(), Comparator.as_float():

    stmt = select(
        data_table.c.data["some key"].as_string()
    ).where(
        data_table.c.data["some key"].as_string() == "some string"
    )

Добавлено в версии 1.4.

Members

__init__()

Классная подпись

класс sqlalchemy.dialects.mssql.JSON (sqlalchemy.types.JSON)

method sqlalchemy.dialects.mssql.JSON.__init__(none_as_null: bool = False)

наследуется от sqlalchemy.types.JSON.__init__ метода JSON

Сконструируйте тип JSON.

Параметры:

none_as_null=False – если True, сохранять значение None как значение SQL NULL, а не JSON-кодировку null. Обратите внимание, что когда этот флаг равен False, конструкция null() все еще может быть использована для сохранения значения NULL, которое может быть передано непосредственно как значение параметра, которое специально интерпретируется типом JSON как SQL NULL:: from sqlalchemy import null conn.execute(table.insert(), {«data»: null()}) … note:: JSON.none_as_null не применяется к значениям, переданным в Column.default и Column.server_default; значение None, переданное для этих параметров, означает «по умолчанию отсутствует». Кроме того, при использовании в выражениях сравнения SQL, значение Python None продолжает ссылаться на SQL null, а не на JSON NULL. Флаг JSON.none_as_null явно указывает на постоянство значения в операторе INSERT или UPDATE. Значение JSON.NULL следует использовать для SQL-выражений, которые хотят сравнить с JSON null. … см. также:: JSON.NULL

class sqlalchemy.dialects.mssql.MONEY

Классная подпись

класс sqlalchemy.dialects.mssql.MONEY (sqlalchemy.types.TypeEngine)

class sqlalchemy.dialects.mssql.NCHAR

Тип SQL NCHAR.

Классная подпись

класс sqlalchemy.dialects.mssql.NCHAR (sqlalchemy.types.Unicode)

method sqlalchemy.dialects.mssql.NCHAR.__init__(length=None, **kwargs)

наследуется от sqlalchemy.types.Unicode.__init__ метода Unicode

Создайте объект Unicode.

Параметры те же, что и у String.

class sqlalchemy.dialects.mssql.NTEXT

Тип MSSQL NTEXT, для текста переменной длины в юникоде до 2^30 символов.

Members

__init__()

Классная подпись

класс sqlalchemy.dialects.mssql.NTEXT (sqlalchemy.types.UnicodeText)

method sqlalchemy.dialects.mssql.NTEXT.__init__(length=None, **kwargs)

наследуется от sqlalchemy.types.UnicodeText.__init__ метода UnicodeText

Создайте тип Текст с преобразованием в Юникод.

Параметры те же, что и у TextClause.

class sqlalchemy.dialects.mssql.NVARCHAR

Тип SQL NVARCHAR.

Классная подпись

класс sqlalchemy.dialects.mssql.NVARCHAR (sqlalchemy.types.Unicode)

method sqlalchemy.dialects.mssql.NVARCHAR.__init__(length=None, **kwargs)

наследуется от sqlalchemy.types.Unicode.__init__ метода Unicode

Создайте объект Unicode.

Параметры те же, что и у String.

class sqlalchemy.dialects.mssql.REAL

тип данных SQL Server REAL.

Классная подпись

класс sqlalchemy.dialects.mssql.REAL (sqlalchemy.types.REAL)

class sqlalchemy.dialects.mssql.ROWVERSION

Внедрите тип SQL Server ROWVERSION.

Тип данных ROWVERSION является синонимом SQL Server для типа данных TIMESTAMP, однако текущая документация SQL Server предлагает использовать ROWVERSION для новых типов данных в будущем.

Тип данных ROWVERSION не отражает (например, интроспекция) из базы данных как таковой; возвращаемый тип данных будет TIMESTAMP.

Это тип данных только для чтения, который не поддерживает INSERT значений.

Добавлено в версии 1.2.

См.также

TIMESTAMP

Members

__init__()

method sqlalchemy.dialects.mssql.ROWVERSION.__init__(convert_int=False)

наследуется от sqlalchemy.dialects.mssql.base.TIMESTAMP.__init__ метода TIMESTAMP

Создайте тип TIMESTAMP или ROWVERSION.

Параметры:

convert_int – если True, двоичные целочисленные значения будут преобразованы в целые числа при чтении.

Добавлено в версии 1.2.

class sqlalchemy.dialects.mssql.SMALLDATETIME

Members

__init__()

Классная подпись

класс sqlalchemy.dialects.mssql.SMALLDATETIME (sqlalchemy.dialects.mssql.base._DateTimeBase, sqlalchemy.types.DateTime)

method sqlalchemy.dialects.mssql.SMALLDATETIME.__init__(timezone: bool = False)

наследуется от sqlalchemy.types.DateTime.__init__ метода DateTime

Создайте новый DateTime.

Параметры:

timezone – булево. Указывает, что тип datetime должен включать поддержку временных зон, если она доступна только для базового типа удержания даты/времени. Рекомендуется использовать непосредственно тип данных TIMESTAMP при использовании этого флага, так как некоторые базы данных включают отдельные общие типы даты/времени, отличные от типа данных TIMESTAMP с поддержкой временных зон, например, Oracle.

class sqlalchemy.dialects.mssql.SMALLMONEY

Классная подпись

класс sqlalchemy.dialects.mssql.SMALLMONEY (sqlalchemy.types.TypeEngine)

class sqlalchemy.dialects.mssql.SQL_VARIANT

Классная подпись

класс sqlalchemy.dialects.mssql.SQL_VARIANT (sqlalchemy.types.TypeEngine)

class sqlalchemy.dialects.mssql.TEXT

Тип SQL TEXT.

Классная подпись

класс sqlalchemy.dialects.mssql.TEXT (sqlalchemy.types.Text)

method sqlalchemy.dialects.mssql.TEXT.__init__(length: Optional[int] = None, collation: Optional[str] = None)

наследуется от sqlalchemy.types.String.__init__ метода String

Создайте тип, удерживающий строку.

Параметры:
  • length – необязательный, длина столбца для использования в выражениях DDL и CAST. Может быть опущена, если не будет выдаваться CREATE TABLE. Некоторые базы данных могут требовать length для использования в DDL, и будут выдавать исключение при выдаче CREATE TABLE DDL, если включена VARCHAR без длины. Интерпретируется ли значение как байты или как символы, зависит от конкретной базы данных.

  • collation – Необязательно, collation на уровне столбцов для использования в выражениях DDL и CAST. Используется ключевое слово COLLATE, поддерживаемое SQLite, MySQL и PostgreSQL. Например: … sourcecode:: pycon+sql >>> from sqlalchemy import cast, select, String >>> print(select(cast(„some string“, String(collation=“utf8“)))) {printsql}SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1 … примечание:: В большинстве случаев для Unicode или UnicodeText следует использовать типы данных Column, которые предполагают хранение неасксичных данных. Эти типы данных гарантируют, что в базе данных используются правильные типы.

class sqlalchemy.dialects.mssql.TIME

Классная подпись

класс sqlalchemy.dialects.mssql.TIME (sqlalchemy.types.TIME)

class sqlalchemy.dialects.mssql.TIMESTAMP

Внедрите тип SQL Server TIMESTAMP.

Обратите внимание, что это полное отличие от типа TIMESTAMP стандарта SQL, который не поддерживается SQL Server. Это тип данных только для чтения, который не поддерживает INSERT значений.

Добавлено в версии 1.2.

См.также

ROWVERSION

Members

__init__()

Классная подпись

класс sqlalchemy.dialects.mssql.TIMESTAMP (sqlalchemy.types._Binary)

method sqlalchemy.dialects.mssql.TIMESTAMP.__init__(convert_int=False)

Создайте тип TIMESTAMP или ROWVERSION.

Параметры:

convert_int – если True, двоичные целочисленные значения будут преобразованы в целые числа при чтении.

Добавлено в версии 1.2.

class sqlalchemy.dialects.mssql.TINYINT

Классная подпись

класс sqlalchemy.dialects.mssql.TINYINT (sqlalchemy.types.Integer)

class sqlalchemy.dialects.mssql.UNIQUEIDENTIFIER

Members

__init__()

Классная подпись

класс sqlalchemy.dialects.mssql.UNIQUEIDENTIFIER (sqlalchemy.types.Uuid)

method sqlalchemy.dialects.mssql.UNIQUEIDENTIFIER.__init__(as_uuid: bool = True)

Сконструируйте тип UNIQUEIDENTIFIER.

Параметры:

as_uuid=True – если True, то значения будут интерпретироваться как объекты Python uuid, конвертируемые в/из строки через DBAPI. … versionchanged: 2.0 Добавлена прямая поддержка «uuid» для типа данных UNIQUEIDENTIFIER; интерпретация uuid по умолчанию True.

class sqlalchemy.dialects.mssql.VARBINARY

Тип MSSQL VARBINARY.

Этот тип добавляет дополнительные возможности к основному типу VARBINARY, включая режим «deprecate_large_types», в котором отображается либо VARBINARY(max), либо IMAGE, а также опцию SQL Server FILESTREAM.

Классная подпись

класс sqlalchemy.dialects.mssql.VARBINARY (sqlalchemy.types.VARBINARY, sqlalchemy.types.LargeBinary)

method sqlalchemy.dialects.mssql.VARBINARY.__init__(length=None, filestream=False)

Создайте тип VARBINARY.

Параметры:
  • length – опционально, длина столбца для использования в DDL-запросах, для тех бинарных типов, которые принимают длину, например, тип MySQL BLOB.

  • filestream=False – если True, отображает ключевое слово FILESTREAM в определении таблицы. В этом случае length должно быть None или 'max'. … versionadded:: 1.4.31

class sqlalchemy.dialects.mssql.VARCHAR

Тип SQL VARCHAR.

Классная подпись

класс sqlalchemy.dialects.mssql.VARCHAR (sqlalchemy.types.String)

method sqlalchemy.dialects.mssql.VARCHAR.__init__(length: Optional[int] = None, collation: Optional[str] = None)

наследуется от sqlalchemy.types.String.__init__ метода String

Создайте тип, удерживающий строку.

Параметры:
  • length – необязательный, длина столбца для использования в выражениях DDL и CAST. Может быть опущена, если не будет выдаваться CREATE TABLE. Некоторые базы данных могут требовать length для использования в DDL, и будут выдавать исключение при выдаче CREATE TABLE DDL, если включена VARCHAR без длины. Интерпретируется ли значение как байты или как символы, зависит от конкретной базы данных.

  • collation – Необязательно, collation на уровне столбцов для использования в выражениях DDL и CAST. Используется ключевое слово COLLATE, поддерживаемое SQLite, MySQL и PostgreSQL. Например: … sourcecode:: pycon+sql >>> from sqlalchemy import cast, select, String >>> print(select(cast(„some string“, String(collation=“utf8“)))) {printsql}SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1 … примечание:: В большинстве случаев для Unicode или UnicodeText следует использовать типы данных Column, которые предполагают хранение неасксичных данных. Эти типы данных гарантируют, что в базе данных используются правильные типы.

class sqlalchemy.dialects.mssql.XML

MSSQL Тип XML.

Это тип-заполнитель для целей отражения, который не включает в себя никакой поддержки типов данных со стороны Python. Он также не поддерживает дополнительные аргументы, такие как «CONTENT», «DOCUMENT», «xml_schema_collection».

Members

__init__()

Классная подпись

класс sqlalchemy.dialects.mssql.XML (sqlalchemy.types.Text)

method sqlalchemy.dialects.mssql.XML.__init__(length: Optional[int] = None, collation: Optional[str] = None)

наследуется от sqlalchemy.types.String.__init__ метода String

Создайте тип, удерживающий строку.

Параметры:
  • length – необязательный, длина столбца для использования в выражениях DDL и CAST. Может быть опущена, если не будет выдаваться CREATE TABLE. Некоторые базы данных могут требовать length для использования в DDL, и будут выдавать исключение при выдаче CREATE TABLE DDL, если включена VARCHAR без длины. Интерпретируется ли значение как байты или как символы, зависит от конкретной базы данных.

  • collation – Необязательно, collation на уровне столбцов для использования в выражениях DDL и CAST. Используется ключевое слово COLLATE, поддерживаемое SQLite, MySQL и PostgreSQL. Например: … sourcecode:: pycon+sql >>> from sqlalchemy import cast, select, String >>> print(select(cast(„some string“, String(collation=“utf8“)))) {printsql}SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1 … примечание:: В большинстве случаев для Unicode или UnicodeText следует использовать типы данных Column, которые предполагают хранение неасксичных данных. Эти типы данных гарантируют, что в базе данных используются правильные типы.

PyODBC

Support for the Microsoft SQL Server database via the PyODBC driver.

DBAPI

Documentation and download information (if applicable) for PyODBC is available at: https://pypi.org/project/pyodbc/

Connecting

Connect String:

mssql+pyodbc://<username>:<password>@<dsnname>

Подключение к PyODBC

URL здесь должен быть переведен в строки подключения PyODBC, как подробно описано в ConnectionStrings.

Подключения DSN

DSN-соединение в ODBC означает, что на клиентской машине настроен уже существующий источник данных ODBC. Затем приложение указывает имя этого источника данных, которое включает такие детали, как конкретный используемый драйвер ODBC, а также сетевой адрес базы данных. При условии, что источник данных настроен на клиенте, базовое соединение на основе DSN выглядит следующим образом:

engine = create_engine("mssql+pyodbc://scott:tiger@some_dsn")

Что выше, передаст следующую строку подключения в PyODBC:

DSN=some_dsn;UID=scott;PWD=tiger

Если имя пользователя и пароль опущены, форма DSN также добавит директиву Trusted_Connection=yes к строке ODBC.

Подключения по имени хоста

Соединения на основе имени хоста также поддерживаются pyodbc. Они часто проще в использовании, чем DSN, и имеют дополнительное преимущество: конкретное имя базы данных для подключения может быть указано локально в URL, а не фиксировано как часть конфигурации источника данных.

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

engine = create_engine("mssql+pyodbc://scott:tiger@myhost:port/databasename?driver=ODBC+Driver+17+for+SQL+Server")

Ключевое слово driver является значимым для диалекта pyodbc и должно быть указано в нижнем регистре.

Любые другие имена, переданные в строке запроса, передаются в строке подключения pyodbc, например authentication, TrustServerCertificate и т.д. Несколько ключевых аргументов должны быть разделены амперсандом (&); они будут переведены в точки с запятой, когда строка подключения pyodbc будет сгенерирована внутри:

e = create_engine(
    "mssql+pyodbc://scott:tiger@mssql2017:1433/test?"
    "driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes"
    "&authentication=ActiveDirectoryIntegrated"
)

Эквивалентный URL можно построить с помощью URL:

from sqlalchemy.engine import URL
connection_url = URL.create(
    "mssql+pyodbc",
    username="scott",
    password="tiger",
    host="mssql2017",
    port=1433,
    database="test",
    query={
        "driver": "ODBC Driver 18 for SQL Server",
        "TrustServerCertificate": "yes",
        "authentication": "ActiveDirectoryIntegrated",
    },
)

Передача точной строки Pyodbc

Строку подключения PyODBC можно также отправить в формате pyodbc напрямую, как указано в the PyODBC documentation, используя параметр odbc_connect. Объект URL может помочь сделать это проще:

from sqlalchemy.engine import URL
connection_string = "DRIVER={SQL Server Native Client 10.0};SERVER=dagger;DATABASE=test;UID=user;PWD=password"
connection_url = URL.create("mssql+pyodbc", query={"odbc_connect": connection_string})

engine = create_engine(connection_url)

Подключение к базам данных с помощью маркеров доступа

Некоторые серверы баз данных настроены так, чтобы принимать только маркеры доступа для входа в систему. Например, SQL Server позволяет использовать маркеры Azure Active Directory для подключения к базам данных. Для этого необходимо создать объект мандата с помощью библиотеки azure-identity. Более подробную информацию о шаге аутентификации можно найти в Microsoft’s documentation.

После получения движка учетные данные необходимо отправлять в pyodbc.connect каждый раз, когда запрашивается соединение. Один из способов сделать это - установить на движке слушатель событий, который добавляет мандатный маркер к вызову connect диалекта. Более подробно это обсуждается в Генерация динамических маркеров аутентификации. В частности, для SQL Server это передается как атрибут соединения ODBC со структурой данных described by Microsoft.

Следующий фрагмент кода создаст механизм, который подключается к базе данных Azure SQL, используя учетные данные Azure:

import struct
from sqlalchemy import create_engine, event
from sqlalchemy.engine.url import URL
from azure import identity

SQL_COPT_SS_ACCESS_TOKEN = 1256  # Connection option for access tokens, as defined in msodbcsql.h
TOKEN_URL = "https://database.windows.net/"  # The token URL for any Azure SQL database

connection_string = "mssql+pyodbc://@my-server.database.windows.net/myDb?driver=ODBC+Driver+17+for+SQL+Server"

engine = create_engine(connection_string)

azure_credentials = identity.DefaultAzureCredential()

@event.listens_for(engine, "do_connect")
def provide_token(dialect, conn_rec, cargs, cparams):
    # remove the "Trusted_Connection" parameter that SQLAlchemy adds
    cargs[0] = cargs[0].replace(";Trusted_Connection=Yes", "")

    # create token credential
    raw_token = azure_credentials.get_token(TOKEN_URL).token.encode("utf-16-le")
    token_struct = struct.pack(f"<I{len(raw_token)}s", len(raw_token), raw_token)

    # apply it to keyword arguments
    cparams["attrs_before"] = {SQL_COPT_SS_ACCESS_TOKEN: token_struct}

Совет

В настоящее время токен Trusted_Connection добавляется диалектом SQLAlchemy pyodbc при отсутствии имени пользователя или пароля. Его необходимо удалить в соответствии с рекомендацией Microsoft documentation for Azure access tokens, гласящей, что строка соединения при использовании маркера доступа не должна содержать параметров UID, PWD, Authentication или Trusted_Connection.

Включите автокоммит для соединений хранилища данных Azure SQL (DW)

Azure SQL Data Warehouse не поддерживает транзакции, и это может вызвать проблемы с «автозапуском» (и неявной фиксацией/откатом) SQLAlchemy. Мы можем избежать этих проблем, включив автокоммит на уровне pyodbc и движка:

connection_url = sa.engine.URL.create(
    "mssql+pyodbc",
    username="scott",
    password="tiger",
    host="dw.azure.example.com",
    database="mydb",
    query={
        "driver": "ODBC Driver 17 for SQL Server",
        "autocommit": "True",
    },
)

engine = create_engine(connection_url).execution_options(
    isolation_level="AUTOCOMMIT"
)

Избегайте отправки больших строковых параметров как TEXT/NTEXT

По умолчанию, по историческим причинам, ODBC-драйверы Microsoft для SQL Server отправляют длинные строковые параметры (более 4000 символов SBCS или 2000 символов Unicode) как значения TEXT/NTEXT. TEXT и NTEXT уже много лет как устарели и начинают вызывать проблемы совместимости с новыми версиями SQL_Server/Azure. Например, смотрите this issue.

Начиная с ODBC Driver 18 для SQL Server мы можем отменить унаследованное поведение и передавать длинные строки как varchar(max)/nvarchar(max) с помощью параметра строки подключения LongAsMax=Yes:

connection_url = sa.engine.URL.create(
    "mssql+pyodbc",
    username="scott",
    password="tiger",
    host="mssqlserver.example.com",
    database="mydb",
    query={
        "driver": "ODBC Driver 18 for SQL Server",
        "LongAsMax": "Yes",
    },
)

Pyodbc Pooling / поведение при закрытии соединения

PyODBC по умолчанию использует внутренние pooling, что означает, что соединения будут более долгоживущими, чем в самом SQLAlchemy. Поскольку SQLAlchemy имеет свое собственное поведение пула, часто предпочтительнее отключить это поведение. Это поведение может быть отключено только глобально на уровне модуля PyODBC, до того, как будут установлены соединения:

import pyodbc

pyodbc.pooling = False

# don't use the engine before pooling is set to False
engine = create_engine("mssql+pyodbc://user:pass@dsn")

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

См.также

pooling - в документации по PyODBC.

Драйвер / Поддержка юникода

PyODBC лучше всего работает с драйверами Microsoft ODBC, особенно в области поддержки Unicode как в Python 2, так и в Python 3.

Использование драйверов FreeTDS ODBC на Linux или OSX с PyODBC не рекомендуется; в этой области исторически было много проблем, связанных с Unicode, в том числе до того, как Microsoft предложила драйверы ODBC для Linux и OSX. Теперь, когда Microsoft предлагает драйверы для всех платформ, для поддержки PyODBC рекомендуется использовать именно их. FreeTDS остается актуальным для не-ODBC драйверов, таких как pymssql, где он работает очень хорошо.

Поддержка подсчета строк

Предыдущие ограничения в работе функции «версионированных строк» SQLAlchemy ORM с Pyodbc были устранены в SQLAlchemy 2.0.5. См. примечания по адресу Поддержка Rowcount / Версионирование ORM.

Режим быстрого исполнения

Драйвер PyODBC включает поддержку режима «быстрого исполнения executemany», который значительно сокращает количество обходов при вызове DBAPI executemany() при использовании драйверов Microsoft ODBC, для ограниченных по размеру пакетов, помещающихся в памяти. Эта возможность включается установкой атрибута .fast_executemany на курсоре DBAPI, когда должен использоваться вызов executemany. Диалект SQLAlchemy PyODBC SQL Server поддерживает этот параметр, передавая параметр fast_executemany в create_engine(), при использовании только драйвера Microsoft ODBC:

engine = create_engine(
    "mssql+pyodbc://scott:tiger@mssql2017:1433/test?driver=ODBC+Driver+17+for+SQL+Server",
    fast_executemany=True)

Изменено в версии 2.0.9: - the fast_executemany parameter now has its intended effect of this PyODBC feature taking effect for all INSERT statements that are executed with multiple parameter sets, which don’t include RETURNING. Previously, SQLAlchemy 2.0’s insertmanyvalues feature would cause fast_executemany to not be used in most cases even if specified.

Добавлено в версии 1.3.

См.также

fast executemany - на github

Setinputsizes Support

Начиная с версии 2.0, метод pyodbc cursor.setinputsizes() используется для выполнения всех операторов, за исключением вызовов cursor.executemany(), когда fast_executemany=True, где он не поддерживается (при условии, что insertmanyvalues остается включенным, «fastexecutemany» в любом случае не будет иметь места для операторов INSERT).

Использование cursor.setinputsizes() можно отключить, передав use_setinputsizes=False в create_engine().

Когда use_setinputsizes оставлен по умолчанию True, конкретные символы каждого типа, передаваемые в cursor.setinputsizes(), могут быть программно настроены с помощью хука DialectEvents.do_setinputsizes(). Примеры использования см. в этом методе.

Изменено в версии 2.0: Диалект mssql+pyodbc теперь по умолчанию использует use_setinputsizes=True для выполнения всех операторов, за исключением вызовов cursor.executemany(), когда fast_executemany=True. Это поведение можно отключить, передав use_setinputsizes=False в create_engine().

pymssql

Support for the Microsoft SQL Server database via the pymssql driver.

Connecting

Connect String:

mssql+pymssql://<username>:<password>@<freetds_name>/?charset=utf8

pymssql - это модуль Python, который предоставляет интерфейс Python DBAPI для работы с FreeTDS.

Изменено в версии 2.0.5: pymssql был восстановлен для тестирования непрерывной интеграции SQLAlchemy

Вернуться на верх