Microsoft SQL Server¶
Support for the Microsoft SQL Server database.
The following table summarizes current support levels for database release versions.
Support type |
Versions |
---|---|
2017 |
|
2012+ |
|
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-specificREAD 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_) |
Выражение |
- 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 |
---|---|
Тип MSSQL BIT. |
|
тип данных SQL Server DOUBLE PRECISION. |
|
Тип MSSQL JSON. |
|
Тип MSSQL NTEXT, для текста переменной длины в юникоде до 2^30 символов. |
|
тип данных SQL Server REAL. |
|
Внедрите тип SQL Server ROWVERSION. |
|
Внедрите тип SQL Server TIMESTAMP. |
|
MSSQL Тип XML. |
- class sqlalchemy.dialects.mssql.BIT¶
Тип MSSQL BIT.
И pyodbc, и pymssql возвращают значения из BIT-колонок как Python <class „bool“>, поэтому просто подкласс Boolean.
Members
Классная подпись
класс
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, укажите имя ограничения.
-
method
- 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
, которые предполагают хранение неасксичных данных. Эти типы данных гарантируют, что в базе данных используются правильные типы.
-
method
- 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.
Классная подпись
класс
sqlalchemy.dialects.mssql.DOUBLE_PRECISION
(sqlalchemy.types.DOUBLE_PRECISION
)
- class sqlalchemy.dialects.mssql.IMAGE¶
Members
Классная подпись
класс
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.
-
method
- 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, либо выдают ошибку, если они не используются против правильного ожидаемого значения.Чтобы справиться с этим неудобным требованием, правила индексированного доступа выглядят следующим образом:
При извлечении подэлемента из 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"} )
При извлечении подэлемента из 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
Классная подпись
класс
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, значение PythonNone
продолжает ссылаться на 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
.
-
method
- class sqlalchemy.dialects.mssql.NTEXT¶
Тип MSSQL NTEXT, для текста переменной длины в юникоде до 2^30 символов.
Members
Классная подпись
класс
sqlalchemy.dialects.mssql.NTEXT
(sqlalchemy.types.UnicodeText
)-
method
sqlalchemy.dialects.mssql.NTEXT.
__init__(length=None, **kwargs)¶ наследуется от
sqlalchemy.types.UnicodeText.__init__
методаUnicodeText
Создайте тип Текст с преобразованием в Юникод.
Параметры те же, что и у
TextClause
.
-
method
- 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
.
-
method
- 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.
См.также
Members
Классная подпись
класс
sqlalchemy.dialects.mssql.ROWVERSION
(sqlalchemy.dialects.mssql.base.TIMESTAMP
)-
method
sqlalchemy.dialects.mssql.ROWVERSION.
__init__(convert_int=False)¶ наследуется от
sqlalchemy.dialects.mssql.base.TIMESTAMP.__init__
методаTIMESTAMP
Создайте тип TIMESTAMP или ROWVERSION.
- Параметры:
convert_int – если True, двоичные целочисленные значения будут преобразованы в целые числа при чтении.
Добавлено в версии 1.2.
-
method
- class sqlalchemy.dialects.mssql.SMALLDATETIME¶
Members
Классная подпись
класс
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.
-
method
- 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
, которые предполагают хранение неасксичных данных. Эти типы данных гарантируют, что в базе данных используются правильные типы.
-
method
- 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.
См.также
Members
Классная подпись
класс
sqlalchemy.dialects.mssql.TIMESTAMP
(sqlalchemy.types._Binary
)-
method
sqlalchemy.dialects.mssql.TIMESTAMP.
__init__(convert_int=False)¶ Создайте тип TIMESTAMP или ROWVERSION.
- Параметры:
convert_int – если True, двоичные целочисленные значения будут преобразованы в целые числа при чтении.
Добавлено в версии 1.2.
-
method
- class sqlalchemy.dialects.mssql.TINYINT¶
Классная подпись
класс
sqlalchemy.dialects.mssql.TINYINT
(sqlalchemy.types.Integer
)
- class sqlalchemy.dialects.mssql.UNIQUEIDENTIFIER¶
Members
Классная подпись
класс
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
.
-
method
- class sqlalchemy.dialects.mssql.VARBINARY
Тип MSSQL VARBINARY.
Этот тип добавляет дополнительные возможности к основному типу
VARBINARY
, включая режим «deprecate_large_types», в котором отображается либоVARBINARY(max)
, либо IMAGE, а также опцию SQL ServerFILESTREAM
.Классная подпись
класс
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
-
method
- 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
, которые предполагают хранение неасксичных данных. Эти типы данных гарантируют, что в базе данных используются правильные типы.
-
method
- class sqlalchemy.dialects.mssql.XML¶
MSSQL Тип XML.
Это тип-заполнитель для целей отражения, который не включает в себя никакой поддержки типов данных со стороны Python. Он также не поддерживает дополнительные аргументы, такие как «CONTENT», «DOCUMENT», «xml_schema_collection».
Members
Классная подпись
-
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
, которые предполагают хранение неасксичных данных. Эти типы данных гарантируют, что в базе данных используются правильные типы.
-
method
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