Zarządzanie kursorami i zestawami wyników

Sterownik mssql-python zapewnia obiekty kursora do wykonywania zapytań, obsługi wielu zestawów wyników oraz efektywnego zarządzania pamięcią.

Podstawy kursora

Tworzenie i używanie kursorów

Wywołaj wywołanie conn.cursor() , aby utworzyć kursor, a następnie użyć execute() i pobrać metody do uruchamiania zapytań i pobierania wyników:

import mssql_python

conn = mssql_python.connect(
    "Server=<server>.database.windows.net;"
    "Database=<database>;"
    "Authentication=ActiveDirectoryDefault;"
    "Encrypt=yes"
)

# Create cursor
cursor = conn.cursor()

# Execute query
cursor.execute("SELECT TOP 10 Name, ListPrice FROM Production.Product")

# Process results
for row in cursor:
    print(row.Name)

# Close cursor when done
cursor.close()

Wzorzec menedżera kontekstu

Użyj with instrukcji do implementacji menedżera kontekstu do automatycznego czyszczenia:

with mssql_python.connect(connection_string) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT TOP 10 Name, ListPrice FROM Production.Product")
        products = cursor.fetchall()
    # Cursor automatically closed on exit
# Connection automatically closed on exit

Wiele kursorów

Important

Sterownik mssql-python nie obsługuje Multiple Active Result Sets (MARS). Możesz tworzyć wiele kursorów na jednym połączeniu, ale tylko jeden kursor może mieć aktywne zapytanie w danym momencie. Zawsze pobieraj wszystkie wyniki z kursora przed wykonaniem na innym kursorze na tym samym połączeniu.

conn = mssql_python.connect(connection_string)

# Multiple cursors on same connection
cursor1 = conn.cursor()
cursor2 = conn.cursor()

# Fetch results completely from cursor1 before using cursor2
cursor1.execute("SELECT TOP 5 ProductID, Name FROM Production.Product")
products = cursor1.fetchall()

cursor2.execute("SELECT TOP 5 ProductCategoryID, Name FROM Production.ProductCategory")
categories = cursor2.fetchall()

cursor1.close()
cursor2.close()

Jeśli musisz uruchamiać zapytania równocześnie, użyj osobnych połączeń:

conn1 = mssql_python.connect(connection_string)
conn2 = mssql_python.connect(connection_string)

cursor1 = conn1.cursor()
cursor2 = conn2.cursor()

cursor1.execute("SELECT TOP 5 ProductID, Name FROM Production.Product")
cursor2.execute("SELECT TOP 5 ProductCategoryID, Name FROM Production.ProductCategory")

products = cursor1.fetchall()
categories = cursor2.fetchall()

cursor1.close()
cursor2.close()
conn1.close()
conn2.close()

Strategie pobierania

Pobieranie wszystkiego vs iteracyjne pobieranie

Użyj fetchall(), aby załadować cały zestaw wyników do pamięci jednocześnie, lub iteruj po kursorze, aby przetwarzać wiersze pojedynczo bez buforowania.

# Fetch all at once - loads entire result into memory
cursor.execute("SELECT * FROM Production.Product")
all_products = cursor.fetchall()
print(f"Loaded {len(all_products)} products")

# Iterative fetch - memory efficient
cursor.execute("SELECT * FROM Production.Product")
count = 0
for row in cursor:
    count += 1
print(f"Processed {count} products")

Pobieranie partiami

Używaj fetchmany() z rozmiarem partii do przetwarzania dużych zbiorów wyników w partiach bez ładowania wszystkiego do pamięci.

def process_batch(rows):
    # Example: print each row. Replace with your own logic.
    for row in rows:
        print(row)

def fetch_in_batches(cursor, batch_size: int = 1000):
    """Fetch results in batches to manage memory."""
    while True:
        batch = cursor.fetchmany(batch_size)
        if not batch:
            break
        yield batch

cursor.execute("SELECT * FROM LargeTable")
for batch in fetch_in_batches(cursor, batch_size=5000):
    process_batch(batch)
    print(f"Processed batch of {len(batch)} rows")

Użyj fetchval dla pojedynczych wartości

Zastosowanie fetchval() do zapytań skalarnych, które zwracają pojedynczą wartość. Zwraca pierwszą kolumnę pierwszego wiersza.

# Efficient for scalar queries
cursor.execute("SELECT COUNT(*) FROM Production.Product")
count = cursor.fetchval()  # Returns single value directly

cursor.execute("SELECT MAX(ListPrice) FROM Production.Product")
max_price = cursor.fetchval()

Wiele zestawów wyników

Przetwarzaj wiele zbiorów wyników

Użyj nextset() do przejścia poza aktualny zestaw wyników do następnego po pobraniu wszystkich wierszy z poprzedniego zbioru.

# Query returns multiple results
cursor.execute("""
    SELECT TOP 3 CustomerID, AccountNumber FROM Sales.Customer;
    SELECT TOP 3 SalesOrderID, OrderDate FROM Sales.SalesOrderHeader;
    SELECT TOP 3 ProductID, Name FROM Production.Product;
""")

# First result set
print("Customers:")
customers = cursor.fetchall()
for c in customers:
    print(f"  {c.AccountNumber}")

# Move to second result set
if cursor.nextset():
    print("Orders:")
    orders = cursor.fetchall()
    for o in orders:
        print(f"  Order #{o.SalesOrderID}")

# Move to third result set
if cursor.nextset():
    print("Products:")
    products = cursor.fetchall()
    for p in products:
        print(f"  {p.Name}")

Iteruj wszystkie zbiory wyników

Powtarzaj, aż nextset() zwróci False, aby przetworzyć wszystkie zbiory wyników z pojedynczego wywołania execute:

def process_all_result_sets(cursor):
    """Process all result sets from a query."""
    result_sets = []
    
    while True:
        # Fetch current result set
        rows = cursor.fetchall()
        result_sets.append(rows)
        
        # Try to move to next result set
        if not cursor.nextset():
            break
    
    return result_sets

cursor.execute("""
    SELECT TOP 3 ProductID, Name FROM Production.Product ORDER BY ProductID;
    SELECT TOP 3 SalesOrderID, TotalDue FROM Sales.SalesOrderHeader ORDER BY SalesOrderID;
""")
all_results = process_all_result_sets(cursor)
print(f"Retrieved {len(all_results)} result sets")

Sprawdź, czy istnieje więcej zestawów wyników

Sprawdzaj wartość zwrotną elementu nextset() w pętli, aby przetworzyć wszystkie zestawy wyników bez wcześniejszej wiedzy, ile ich jest:

cursor.execute("""
    SELECT COUNT(*) AS ProductCount FROM Production.Product;
    SELECT COUNT(*) AS PersonCount FROM Person.Person;
""")

result_num = 1
while True:
    count = cursor.fetchval()
    print(f"Result set {result_num}: {count}")
    
    result_num += 1
    if not cursor.nextset():
        break

Opis kursora

Uzyskaj metadane kolumn

Po wykonaniu zapytania cursor.description zawiera sekwencję 7-elementowych krotek — po jednej dla każdej kolumny — z nazwą, kodem typu, rozmiarem wyświetlacza, rozmiarem wewnętrznym, precyzją, skalą i dopuszczalnością wartości NULL:

cursor.execute("SELECT ProductID, Name, ListPrice FROM Production.Product WHERE ProductID < 10")

# Get column information
for col in cursor.description:
    print(f"Column: {col[0]}, Type: {col[1]}")

# description structure: (name, type_code, display_size, internal_size, 
#                        precision, scale, null_ok)

Buduj dynamiczne obsługiwacze wyników

Zbuduj obsługi wyników, które obsługują dowolne zapytanie, konwertując każdy wiersz za pomocą row._mapping:

def query_to_dicts(cursor) -> list[dict]:
    """Convert query results to list of dictionaries."""
    return [dict(row._mapping) for row in cursor.fetchall()]

cursor.execute("SELECT ProductID, Name, ListPrice FROM Production.Product WHERE ProductID < 10")
products = query_to_dicts(cursor)
for p in products:
    print(p["Name"])

Obsługa zapytań bez rezultatów

cursor.description jest po None instrukcjach nie-SELECT, takich jak INSERT, UPDATE, oraz DELETE. Sprawdź to przed wywołaniem metod pobierania:

cursor.execute("CREATE TABLE #UpdDemo (Name NVARCHAR(50), Price DECIMAL(10,2), CategoryID INT)")
cursor.execute("INSERT INTO #UpdDemo VALUES ('Widget', 10.0, 5), ('Gadget', 20.0, 5)")
cursor.execute("UPDATE #UpdDemo SET Price = Price * 1.1 WHERE CategoryID = 5")

# description is None for non-SELECT statements
if cursor.description is None:
    print(f"Updated {cursor.rowcount} rows")
else:
    results = cursor.fetchall()

Liczba wierszy

Obszary dotknięte torem

Po INSERT, UPDATE, lub DELETE, cursor.rowcount zwraca liczbę wierszy dotkniętych zdaniem:

cursor.execute("CREATE TABLE #RowDemo (Name NVARCHAR(50), Stock INT)")
cursor.execute("INSERT INTO #RowDemo VALUES ('A', 0), ('B', 5), ('C', 0)")
cursor.execute("UPDATE #RowDemo SET Stock = -1 WHERE Stock = 0")
print(f"Rows affected: {cursor.rowcount}")

cursor.execute("DELETE FROM #RowDemo WHERE Stock = -1")
print(f"Deleted {cursor.rowcount} rows")

Obsłuż nieznaną liczbę wierszy

cursor.rowcount zwraca -1, gdy liczba wierszy nie jest dostępna. Sprawdź, czy jest -1, przed zgłoszeniem liczby zmienionych wierszy.

# Some operations might not return row count
cursor.execute("EXEC dbo.uspGetEmployeeManagers @BusinessEntityID = 5")

if cursor.rowcount == -1:
    print("Row count not available")
else:
    print(f"Affected {cursor.rowcount} rows")

Śledź każdy wynik w partii

nextset() odświeża rowcount dla wyniku, do którego przechodzisz. Dla wyników INSERT, UPDATE i DELETE raportuje liczbę wierszy, których dotyczy ta instrukcja. W przypadku wyniku generującego wiersze jest resetowany przed pobraniem -1. Sprawdź description przed wywołaniem metody pobierania, ponieważ wynik modyfikacji danych nie ma wierszy do pobrania.

cursor.execute("CREATE TABLE #BatchCounts (ID int)")
cursor.execute("""
    SET NOCOUNT OFF;
    INSERT INTO #BatchCounts VALUES (1);
    INSERT INTO #BatchCounts VALUES (2), (3);
    SELECT ID FROM #BatchCounts ORDER BY ID;
    UPDATE #BatchCounts SET ID = ID + 10;
    DELETE FROM #BatchCounts WHERE ID = 99;
""")

while True:
    if cursor.description is None:
        print(f"Affected: {cursor.rowcount}")
    else:
        print(f"Before fetch: {cursor.rowcount}")
        print([row.ID for row in cursor.fetchall()])
    if not cursor.nextset():
        break

Output:

Affected: 1
Affected: 2
Before fetch: -1
[1, 2, 3]
Affected: 3
Affected: 0

Dotyczy to również wyników generowanych przez procedury przechowywane i pętle Transact-SQL (T-SQL). SET NOCOUNT ON powoduje pominięcie liczby zmodyfikowanych wierszy, więc nie włączaj tego, gdy Twoja aplikacja potrzebuje tych liczb. Aby uzyskać więcej informacji, zobacz Procedury składowane.

Pomiń wiersze

Użyj skip jako alternatywy dla paginacji

cursor.skip() Przesuwa pozycję kursora bez pobierania wierszy. W przypadku dużych zbiorów danych preferuj OFFSET-FETCH paginację na poziomie SQL dla lepszej wydajności:

def get_page_using_skip(cursor, page: int, page_size: int):
    """Get a page of results using skip."""
    cursor.execute("SELECT * FROM Production.Product ORDER BY ProductID")
    
    # Skip rows from previous pages
    cursor.skip((page - 1) * page_size)
    
    # Fetch this page
    return cursor.fetchmany(page_size)

# Get page 3
page_3 = get_page_using_skip(cursor, page=3, page_size=20)

Note

Dla dużych zbiorów danych używaj paginacji na poziomie SQL (OFFSET-FETCH) zamiast pomijania po stronie klienta, ponieważ jest to bardziej efektywne.

Komunikaty diagnostyczne

Dostęp do cursor.messages

Atrybut ten messages przechowuje komunikaty informacyjne generowane podczas wykonywania instrukcji SQL, jak opisano w PEP 249. Te wiadomości zawierają dane wyjściowe instrukcji PRINT oraz elementu RAISERROR o poziomach ważności poniżej 11.

Atrybut to lista krotk, gdzie każda krotka zawiera kod typu wiadomości oraz tekst wiadomości:

conn = mssql_python.connect(connection_string, autocommit=True)
cursor = conn.cursor()
cursor.execute("PRINT 'Hello world!'")
print(cursor.messages)

Output:

[('[01000] (0)', '[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Hello world!')]

Tekst wiadomości zawiera informacje o prefiksie sterownika, ponieważ sterownik pobiera wiadomości jako rekordy diagnostyczne przez SQLGetDiagRec.

Przechwytywanie wiadomości z procedur przechowywanych

Odczytaj cursor.messages po wykonaniu instrukcji, aby pobrać wszelkie komunikaty wyjściowe PRINT lub informacyjne komunikaty serwera z poprzedniej instrukcji:

cursor.execute("EXECUTE dbo.uspGetEmployeeManagers @BusinessEntityID = 5")
results = cursor.fetchall()

# Check for any informational messages
if cursor.messages:
    for msg_type, msg_text in cursor.messages:
        print(f"Server message: {msg_text}")

Zarządzanie pamięcią

Efektywne przetwarzanie dużych rezultatów

Pobieraj partiami za pomocą fetchmany(), aby przetwarzać tabele zbyt duże, by wczytać je do pamięci jednocześnie:

def process_large_table(cursor, batch_size: int = 10000):
    """Process large result set without loading all into memory."""
    cursor.execute("SELECT * FROM VeryLargeTable")
    
    total_processed = 0
    while True:
        rows = cursor.fetchmany(batch_size)
        if not rows:
            break
        
        for row in rows:
            process_row(row)
        
        total_processed += len(rows)
        print(f"Progress: {total_processed} rows processed")
    
    return total_processed

Przetwarzanie oparte na generatorach

Opakuj pobieranie partiami w generator, aby przetwarzać po jednym wierszu naraz, przy stałym zużyciu pamięci niezależnie od rozmiaru zbioru wyników:

def row_generator(cursor, batch_size: int = 1000):
    """Generate rows from cursor without loading all."""
    while True:
        rows = cursor.fetchmany(batch_size)
        if not rows:
            break
        for row in rows:
            yield row

cursor.execute("SELECT * FROM LargeTable")
for row in row_generator(cursor, batch_size=5000):
    # Process one row at a time
    print(row)  # Replace with your own row-handling logic

Zamykaj kursory natychmiast

Zawsze zamykaj kursory w bloku finally , aby zwolnić zasoby po stronie serwera, nawet jeśli wystąpi wyjątek:

def get_product(conn, product_id: int):
    """Get product and properly close cursor."""
    cursor = conn.cursor()
    try:
        cursor.execute(
            "SELECT * FROM Production.Product WHERE ProductID = %(id)s",
            {"id": product_id}
        )
        return cursor.fetchone()
    finally:
        cursor.close()

Zarządzanie stanem kursora

Sprawdź, czy kursor zawiera dane

Sprawdź, czy zapytanie zwraca jakieś wiersze, sprawdzając, czy fetchone() zwraca None:

cursor.execute("SELECT ProductID, Name FROM Production.Product WHERE ProductID = 999")
row = cursor.fetchone()

if row is None:
    print("Product not found")
else:
    print(f"Found: {row.Name}")

Ponowne wykorzystanie kursorów

Pojedynczy kursor może wykonać wiele zapytań kolejno. Każde execute() wywołanie zastępuje poprzedni zestaw wyników:

cursor = conn.cursor()

# Execute multiple queries with same cursor
cursor.execute("SELECT TOP 5 * FROM Sales.Customer")
customers = cursor.fetchall()

cursor.execute("SELECT TOP 5 * FROM Production.Product")
products = cursor.fetchall()

cursor.execute("SELECT TOP 5 * FROM Sales.SalesOrderHeader")
orders = cursor.fetchall()

cursor.close()

Najlepsze rozwiązania

Wzór: Klasa pomocnicza kursora

Hermetyzuj zarządzanie cyklem życia kursora w klasie pomocniczej, aby ograniczyć powtarzalny kod w całej aplikacji:

class CursorManager:
    """Helper for managing cursor lifecycle."""
    
    def __init__(self, connection):
        self.conn = connection
    
    def execute_and_fetch(self, query: str, params: dict = None) -> list:
        """Execute query and return all results."""
        cursor = self.conn.cursor()
        try:
            cursor.execute(query, params or {})
            return cursor.fetchall()
        finally:
            cursor.close()
    
    def execute_scalar(self, query: str, params: dict = None):
        """Execute query and return single value."""
        cursor = self.conn.cursor()
        try:
            cursor.execute(query, params or {})
            return cursor.fetchval()
        finally:
            cursor.close()
    
    def execute_non_query(self, query: str, params: dict = None) -> int:
        """Execute non-SELECT and return row count."""
        cursor = self.conn.cursor()
        try:
            cursor.execute(query, params or {})
            return cursor.rowcount
        finally:
            cursor.close()

# Usage
db = CursorManager(conn)
products = db.execute_and_fetch("SELECT TOP 5 Name FROM Production.Product")
count = db.execute_scalar("SELECT COUNT(*) FROM Production.Product")

db.execute_non_query("CREATE TABLE #Logs (LogID INT, Age INT)")
db.execute_non_query("INSERT INTO #Logs VALUES (1, 45), (2, 20), (3, 60)")
affected = db.execute_non_query("DELETE FROM #Logs WHERE Age > 30")

Nie zostawiaj kursorów otwartych

Kursor, który nie jest wyraźnie zamknięty, przechowuje zasoby po stronie serwera do czasu zamknięcia połączenia. Użyj try/finally, aby zagwarantować czyszczenie:

# Bad: cursor left open
def get_data_bad(conn):
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM Data")
    return cursor.fetchall()
    # Cursor never closed!

# Good: always close cursor
def get_data_good(conn):
    cursor = conn.cursor()
    try:
        cursor.execute("SELECT * FROM Data")
        return cursor.fetchall()
    finally:
        cursor.close()

Dopasowywanie żywotności kursora do działania

Twórz krótkotrwałe kursory dla pojedynczych operacji. Używaj tego samego kursora wyłącznie do serii powiązanych operacji:

# Short-lived cursor for simple query
def get_user_count(conn) -> int:
    cursor = conn.cursor()
    try:
        cursor.execute("SELECT COUNT(*) FROM Person.Person")
        return cursor.fetchval()
    finally:
        cursor.close()

# Reuse cursor for related operations
def update_inventory(conn, items: list):
    cursor = conn.cursor()
    try:
        for item in items:
            cursor.execute(
                "UPDATE Inventory SET Quantity = %(qty)s WHERE ProductID = %(id)s",
                item
            )
        conn.commit()
    finally:
        cursor.close()