Обработка строк и Unicode

Microsoft SQL предоставляет несколько типов строк, которые драйвер mssql-python сопоставляет с объектами Pythonstr. Ключевой вопрос заключается в том, использовать ли varchar (не Unicode) или nvarchar (Unicode):

  • Используйте nvarchar, если ваши данные могут содержать символы, не входящие в набор ASCII, такие как имена, адреса или пользовательский контент на любом языке.
  • Используйте varchar, если данные содержат только символы ASCII (коды, идентификаторы, адреса электронной почты) и нужно сэкономить место в хранилище. varchar использует 1 байт на символ; nvarchar использует 2 байта на символ.
Тип SQL Unicode Максимальная длина Тип Python
char(n) нет 8,000 str
varchar(n) нет 8,000 str
varchar(max) нет 2 ГБ str
nchar(n) Yes 4,000 str
nvarchar(n) Yes 4,000 str
nvarchar(max) Yes 2 ГБ str
text нет 2 ГБ (устарело) str
ntext Yes 2 ГБ (устарело) str

Базовые операции со строками

Драйвер сопоставляет все строковые типы Microsoft SQL с объектами Python str.

Вставка и извлечение строк

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

import mssql_python

conn = mssql_python.connect(connection_string)
cursor = conn.cursor()

# Create temp table for demo
cursor.execute("""
    CREATE TABLE #StringDemo (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        Name NVARCHAR(100),
        Email NVARCHAR(200)
    )
""")

# Insert string data
cursor.execute(
    "INSERT INTO #StringDemo (Name, Email) VALUES (%(name)s, %(email)s)",
    {"name": "Alice Smith", "email": "alice@example.com"}
)
conn.commit()

# Retrieve string data
cursor.execute("SELECT Name, Email FROM #StringDemo WHERE ID = 1")
row = cursor.fetchone()
print(row.Name)   # 'Alice Smith'
print(row.Email)  # 'alice@example.com'

Струны со специальными символами

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

# Quotes and special characters handled automatically
cursor.execute("""
    CREATE TABLE #Notes (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        Title NVARCHAR(200),
        Content NVARCHAR(MAX)
    )
""")
cursor.execute(
    "INSERT INTO #Notes (Title, Content) VALUES (%(title)s, %(content)s)",
    {
        "title": "O'Brien's Report",
        "content": 'Contains "quotes" and special chars: <>&'
    }
)
conn.commit()

Поддержка Юникода

Используйте столбцы типа nvarchar и Python str для хранения и извлечения текста на любом языке.

Сохранить текст Unicode

Вставьте содержимое Unicode, передавая строки Python параметризованным запросам; драйвер кодирует их как UTF-16LE для столбцов nvarchar.

# International characters - use nvarchar columns
cursor.execute("""
    CREATE TABLE #Messages (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        Content NVARCHAR(MAX)
    )
""")
cursor.execute("""
    INSERT INTO #Messages (Content) VALUES (%(msg)s)
""", {"msg": "Hello 你好 مرحبا שלום 🎉"})

cursor.execute("SELECT Content FROM #Messages WHERE ID = 1")
row = cursor.fetchone()
print(row.Content)  # 'Hello 你好 مرحبا שלום 🎉'

Юникод на разных алфавитах

Поддерживайте несколько языков и скриптов в одной таблице, используя столбцы nvarchar и объёмные вставки.

messages = [
    {"lang": "English", "text": "Hello, World!"},
    {"lang": "Chinese", "text": "你好，世界！"},
    {"lang": "Japanese", "text": "こんにちは世界！"},
    {"lang": "Korean", "text": "안녕하세요, 세상!"},
    {"lang": "Arabic", "text": "مرحبا بالعالم!"},
    {"lang": "Hebrew", "text": "שלום עולם!"},
    {"lang": "Russian", "text": "Привет мир!"},
    {"lang": "Greek", "text": "Γειά σου Κόσμε!"},
    {"lang": "Emoji", "text": "👋🌍✨🎉"},
]

cursor.execute("""
    CREATE TABLE #Greetings (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        Language NVARCHAR(50),
        Message NVARCHAR(200)
    )
""")
cursor.executemany("""
    INSERT INTO #Greetings (Language, Message) VALUES (%(lang)s, %(text)s)
""", messages)
conn.commit()

Используйте столбцы типа nvarchar для Юникода

Всегда определяйте столбцы как nvarchar вместо varchar, если ваши данные могут содержать символы, не соответствующие ASCII.

-- For Unicode data, always use nvarchar, not varchar
CREATE TABLE #UnicodeDemo (
    ID INT IDENTITY PRIMARY KEY,
    Name NVARCHAR(100),        -- Supports Unicode
    Description NVARCHAR(MAX)  -- Supports large Unicode text
);

Рассмотрение длины строк

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

Фиксированная и переменная длина

В Microsoft SQL char(n) дополняет значения пробелами в конце до объявленной длины. Такое дополнение впустую расходует место в хранилище для данных переменной длины, но может повысить производительность при работе со столбцами фиксированной ширины, такими как коды стран. Используйте varchar(n) для большинства столбцов строк.

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

# char(6) pads to fixed length
cursor.execute(
    "SELECT StateProvinceCode FROM Person.StateProvince WHERE StateProvinceID = 1"
)  # nchar(6) column
row = cursor.fetchone()
print(repr(row.StateProvinceCode))  # 'AB    ' - right-padded with spaces

# nvarchar stores actual length
cursor.execute(
    "SELECT Name FROM Person.StateProvince WHERE StateProvinceID = 1"
)  # nvarchar column
row = cursor.fetchone()
print(repr(row.Name))  # 'Alberta' - no padding

Обрабатывайте промежуточные пространства

При извлечении данных из столбцов символов фиксированной длины используйте rstrip() для удаления заполняющих пробелов, добавленных Microsoft SQL Server.

# Strip trailing spaces from char columns
cursor.execute("SELECT ProductNumber FROM Production.Product")
for row in cursor:
    code = row.ProductNumber.rstrip()  # Remove trailing spaces
    print(f"Code: '{code}'")

Крупные струны (типы MAX)

Типы nvarchar(max) и varchar(max) поддерживают строки размером до 2 ГБ, что делает их идеальными для хранения больших текстовых документов, содержимого JSON или XML.

# Large text content
large_content = "x" * 100000  # 100K characters

cursor.execute("""
    CREATE TABLE #Documents (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        Content NVARCHAR(MAX)
    )
""")
cursor.execute("""
    INSERT INTO #Documents (Content) VALUES (%(content)s)
""", {"content": large_content})

cursor.execute("SELECT Content FROM #Documents WHERE ID = 1")
row = cursor.fetchone()
print(len(row.Content))  # 100000

Сравнение и сопоставление строк

Поведение Microsoft SQL при сравнении строк зависит от набора колляций в базе данных или столбце.

Учет регистра

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

# Case-insensitive collation (default for many databases)
cursor.execute("SELECT * FROM Person.Person WHERE LastName = %(name)s", {"name": "smith"})
# Might match 'Smith', 'SMITH', 'smith' depending on collation

# For case-sensitive comparison
cursor.execute("""
    SELECT * FROM Person.Person 
    WHERE LastName COLLATE Latin1_General_CS_AS = %(name)s
""", {"name": "Smith"})

Сопоставление с шаблоном LIKE

Используйте оператор LIKE с подстановочными знаками для поиска шаблонов строк; экранируйте специальные символы с помощью скобочной нотации для сопоставления с буквальными символами.

# Wildcard searches
search_term = "Road"
cursor.execute("""
    SELECT Name FROM Production.Product WHERE Name LIKE %(pattern)s
""", {"pattern": f"%{search_term}%"})

# Escape special characters in search
def escape_like(value: str) -> str:
    """Escape LIKE wildcards in search value."""
    return value.replace("[", "[[]").replace("%", "[%]").replace("_", "[_]")

search = "100%"
cursor.execute("""
    SELECT Name FROM Production.Product WHERE Name LIKE %(pattern)s
""", {"pattern": f"%{escape_like(search)}%"})

Вопросы кодирования

Поведение кодирования зависит от типа столбца в Microsoft SQL и исходной сортировки.

Предположения о кодировке и значения по умолчанию для Unicode

mssql-python Драйвер автоматически обрабатывает кодирование на основе типа столбца Microsoft SQL. По умолчанию строковые параметры передаются в кодировке UTF-16LE для столбцов nvarchar и в соответствии с правилами сортировки базы данных для столбцов varchar:

Тип столбца Проводное кодирование Результат Python
nvarchar, nchar, ntext UTF-16LE str (расшифровано водителем)
varchar, char, text Кодировка сортировки базы данных или столбца str (декодируется драйвером с использованием исходного кодирования)

Строки в Python всегда имеют внутреннее представление в Unicode. При передаче str параметра драйвер кодирует его для типа целевой колонки. По умолчанию драйвер отправляет параметры строк как nvarchar (Unicode), что гарантирует сохранение символов независимо от сбора в базе данных. Для varchar столбцов UTF-8 применяется только тогда, когда база данных или столбец использует колляцию с поддержкой UTF-8.

Если ваш столбец — это varchar и вам нужно отправить данные, не относящиеся к Unicode, чтобы точно совпадать с типом столбца (например, чтобы избежать неявных предупреждений о конвертации), используйте setinputsizes() для отмены значения по умолчанию:

import mssql_python

conn = mssql_python.connect(connection_string)
cursor = conn.cursor()

# Create temp table for demo
cursor.execute("CREATE TABLE #AsciiTable (Code VARCHAR(100))")

cursor.setinputsizes([(mssql_python.SQL_VARCHAR, 100, 0)])
cursor.execute(
    "INSERT INTO #AsciiTable (Code) VALUES (?)",
    ("ABC123",)
)
conn.commit()

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

Кодирование соединений

Драйвер mssql-python автоматически осуществляет кодирование соединения на основе версии и конфигурации Microsoft SQL Server. Поскольку строки Python — это Unicode, драйвер кодирует их соответствующим образом (UTF-8 или UTF-16) для целевой системы данных. Вам не нужно вручную настраивать кодирование соединений.

Столбцы VARCHAR с устаревшими правилами сортировки

Базы данных с правилами сортировки Windows-1252 (CP1252), такими как Latin1_General_CI_AS, хранят расширенные латинские символы (например, €, ™ и символы с диакритическими знаками) в столбцах varchar в кодировке CP1252. Водитель правильно расшифровывает этих персонажей на всех платформах.

Это различие важно для кроссплатформенных развертываний: те же varchar данные, которые правильно читаются на Windows, также читаются корректно и на Linux, без специальной настройки.

# Create a temp table with a varchar column and insert extended Latin characters
cursor.execute("CREATE TABLE #Products (Name VARCHAR(100))")
cursor.execute("INSERT INTO #Products (Name) VALUES (%(name)s)", {"name": "Café €100 ™"})
conn.commit()

# CP1252 characters in varchar columns are decoded correctly on all platforms
cursor.execute("SELECT Name FROM #Products WHERE Name LIKE '%€%'")
for row in cursor:
    print(row.Name)  # Correct on both Windows and Linux

Если ваша схема это позволяет, перенос столбцов из varchar в nvarchar полностью устраняет неоднозначность кодировки и обеспечивает поддержку всех символов Unicode.

Кодировка файлов

При чтении файлов для вставки в базу данных указывайте соответствующую кодировку для сохранения содержимого Юникода.

# Reading files with explicit encoding
def insert_file_content(cursor, conn, file_path: str, encoding: str = "utf-8"):
    with open(file_path, "r", encoding=encoding) as f:
        content = f.read()
    
    cursor.execute(
        "INSERT INTO #FileContent (Content) VALUES (%(content)s)",
        {"content": content}
    )
    conn.commit()

Общие строковые операции

Эти примеры охватывают распространённые шаблоны манипуляции строками как в Python, так и в SQL.

Конкатенация

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

# Concatenate in Python before insert
first_name = "Alice"
last_name = "Smith"
full_name = f"{first_name} {last_name}"

cursor.execute("""
    CREATE TABLE #ConcatDemo (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        FullName NVARCHAR(200)
    )
""")
cursor.execute(
    "INSERT INTO #ConcatDemo (FullName) VALUES (%(name)s)",
    {"name": full_name}
)

# Or concatenate in SQL
cursor.execute("""
    SELECT FirstName + ' ' + LastName AS FullName FROM Person.Person
""")

Форматирование строк

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

from decimal import Decimal

# Format for display
cursor.execute("SELECT Name, ListPrice FROM Production.Product WHERE ListPrice > 0")
for row in cursor.fetchall()[:5]:
    print(f"{row.Name}: ${row.ListPrice:.2f}")

# Pad strings
cursor.execute("SELECT ProductNumber FROM Production.Product")
for row in cursor.fetchall()[:5]:
    padded = row.ProductNumber.ljust(15)  # Left-justify, pad to 15 chars
    print(f"[{padded}]")

NULL против пустой строки

Microsoft SQL рассматривает NULL и пустую строку ('') как разные значения. NULL означает «неизвестное значение», а пустая строка — «заведомо пустая строка». Выберите один подход для своего приложения и придерживайтесь его последовательно. Большинство приложений используют NULL для отсутствующих необязательных полей.

Следующий пример демонстрирует, как различать NULL и пустую строку:

# NULL is different from empty string
cursor.execute("""
    CREATE TABLE #NullDemo (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        Name NVARCHAR(100),
        MiddleName NVARCHAR(100)
    )
""")
cursor.execute("""
    INSERT INTO #NullDemo (Name, MiddleName) 
    VALUES (%(name)s, %(middle)s)
""", {"name": "Alice", "middle": None})  # NULL

cursor.execute("""
    INSERT INTO #NullDemo (Name, MiddleName) 
    VALUES (%(name)s, %(middle)s)
""", {"name": "Bob", "middle": ""})  # Empty string

# Query differences
cursor.execute("SELECT * FROM #NullDemo WHERE MiddleName IS NULL")
cursor.execute("SELECT * FROM #NullDemo WHERE MiddleName = ''")

Операции обрезки

Используйте строковые методы Python, чтобы удалить начальные, конечные или все пробельные символы из значений, извлечённых из базы данных.

cursor.execute("SELECT Name FROM Production.Product")
for row in cursor:
    # Remove whitespace
    trimmed = row.Name.strip()  # Both ends
    left_trimmed = row.Name.lstrip()
    right_trimmed = row.Name.rstrip()

Строковые данные JSON

Храните JSON-документы в столбцах nvarchar(max) и отправляйте запросы с помощью JSON-функций Microsoft SQL.

Храните JSON как nvarchar

Сериализуйте словари Python в JSON-строки и вставляйте их в столбцы nvarchar; извлекайте их и десериализуйте, преобразуя обратно в объекты Python.

import json

data = {"name": "Alice", "scores": [95, 87, 91], "active": True}
json_string = json.dumps(data)

cursor.execute("""
    CREATE TABLE #Configs (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        ConfigData NVARCHAR(MAX)
    )
""")
cursor.execute("""
    INSERT INTO #Configs (ConfigData) VALUES (%(data)s)
""", {"data": json_string})

# Retrieve and parse
cursor.execute("SELECT ConfigData FROM #Configs WHERE ID = 1")
row = cursor.fetchone()
config = json.loads(row.ConfigData)
print(config["name"])  # 'Alice'

Использование функций Microsoft SQL JSON

Используйте функции JSON от Microsoft SQL для анализа и фильтрации данных JSON напрямую в запросах, а не в клиентском коде.

import json

data = {"name": "Alice", "scores": [95, 87, 91], "active": True}

cursor.execute("""
    CREATE TABLE #Configs (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        ConfigData NVARCHAR(MAX)
    )
""")
cursor.execute(
    "INSERT INTO #Configs (ConfigData) VALUES (%(data)s)",
    {"data": json.dumps(data)}
)
conn.commit()

cursor.execute("""
    SELECT JSON_VALUE(ConfigData, '$.name') AS Name
    FROM #Configs
    WHERE JSON_VALUE(ConfigData, '$.active') = 'true'
""")
for row in cursor:
    print(row.Name)  # 'Alice'

Полнотекстовый поиск

Используйте LIKE для сопоставления шаблонов или включите полнотекстовый индекс для более сложного поиска по тексту.

Полнотекстовые запросы

Оператор LIKE с шаблонами подстановки предоставляет простую альтернативу полнотекстовому поиску, когда полнотекстовый индекс недоступен.

# Using CONTAINS (requires full-text index on the table)
cursor.execute("""
    SELECT JobTitle FROM HumanResources.Employee
    WHERE JobTitle LIKE %(search)s
""", {"search": "%Engineer%"})

# Pattern-based search as an alternative to full-text
cursor.execute("""
    SELECT Name FROM Production.Product
    WHERE Name LIKE %(search)s
""", {"search": "%Mountain%"})

Лучшие практики

Применяйте эти рекомендации для корректной обработки строковых данных между языками и кодировками.

Используйте nvarchar для международных данных

Если вы не уверены, может ли столбец содержать Юникод, используйте nvarchar. Стоимость хранения скромна и предотвращает потерю данных при конвертации символов.

Следующий пример показывает разницу между определением столбцов для данных Unicode и только для ASCII:

-- Good: supports any language
CREATE TABLE #UserProfile (
    Name NVARCHAR(100),
    Bio NVARCHAR(MAX)
);

-- Limited: ASCII/Latin only
CREATE TABLE #UserProfileAscii (
    Name VARCHAR(100),
    Bio VARCHAR(MAX)
);

Валидация длины строки

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

def safe_insert(cursor, name: str, max_length: int = 100):
    """Insert with length validation."""
    if len(name) > max_length:
        raise ValueError(f"Name exceeds {max_length} characters")
    
    cursor.execute(
        "INSERT INTO #UserProfile (Name) VALUES (%(name)s)",
        {"name": name}
    )

Обрабатывайте двоичные строки отдельно

Различайте текстовые строки (Pythonstr, SQLnvarchar) и двоичные данные (Pythonbytes, SQLvarbinary), чтобы избежать проблем с кодированием.

binary_data = b'\x00\x01\x02'  # bytes - use varbinary
text_data = "Hello"            # str - use nvarchar

Связанные материалы

  • Last updated on