Gestire i database con Python / Sgart.it

Python mette a disposizione la libreria pyodbc per gestire l'accesso a vari tipi di database tra cui SQL Server.

E' possibile scaricare una versione gratuita denominata Express dal sito ufficiale di Microsoft SQL Server

La libreria va installata tramite il comando pip

pip install pyodbc

Il passo successivo è quello di importarla e aprire la connessione con una specifica connection string

import pyodbc

# creo la connection string a Microsoft Sql Server
connection_string = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=.;DATABASE=python_db;UID=nodejs;PWD=nodE$Js_2015!x"

# crea la connessione
cnn = pyodbc.connect(connection_string)

Nella connection string vanno specificati:

DRIVER: il driver da utilizzare per l'accesso al server, nel caso di SQL Server il valore è {ODBC Driver 17 for SQL Server} per le versioni da 2008 a 2017
SERVER: il nome della macchina su cui è intallato SQL Server, il punto si riferisce al server locale (nel caso di SQL Express, solitamente installato come istanza nominale, il nome diventa .\SQLEXPRESS)
DATABASE: il nome del database a cui accedere
UID: l'utente sql da usare per l'accesso al database
PWD: la password da usare per l'accesso

Supponendo di avere una tabella SQL di nome categories con i campi: id, category e modified

CREATE TABLE [dbo].[categories](
	[ID] [int] NOT NULL,
	[Category] [nvarchar](50) NOT NULL,
	[Modified] [datetime] NOT NULL,
 CONSTRAINT [PK_categories] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[categories] ([ID], [Category], [Modified]) VALUES (0, N'Undefined', getdate())
GO
INSERT [dbo].[categories] ([ID], [Category], [Modified]) VALUES (1, N'Red', getdate())
GO
INSERT [dbo].[categories] ([ID], [Category], [Modified]) VALUES (2, N'Green', getdate())
GO
INSERT [dbo].[categories] ([ID], [Category], [Modified]) VALUES (3, N'Blue', getdate())
GO
INSERT [dbo].[categories] ([ID], [Category], [Modified]) VALUES (4, N'Yellow', getdate())
GO
INSERT [dbo].[categories] ([ID], [Category], [Modified]) VALUES (5, N'Purple', getdate())
GO
INSERT [dbo].[categories] ([ID], [Category], [Modified]) VALUES (6, N'Orange', getdate())
GO

La prima cosa che possiamo fare è leggere i dati. Per far questo dobbiamo fare una serie di operazioni in sequenza: creare una query, creare un cursore, eseguire la query e, in ultimo, visualizzare il risultato

# preparo la query senza l'ultimo parametro
query = "SELECT [ID],[Category],[modified] FROM [dbo].[categories] WHERE [ID] > "

# creo un cursore per ciclare sul risultato
cursor = cnn.cursor()

# eseguo la query concatenando il parametro mancante alla query
cursor.execute(query + "2")

# uso il cursore per ciclare sui risultati 
# per accedere ai parametri uso un indice posizionale
for row in cursor:
    print("id:", row[0])
    print("category:", row[1])
    print("modified:", row[2])

# molto importante, rilascio le risorse nell'ordine corretto
cursor.close()
cnn.close()

Un modo migliore per gestire il rilascio delle risorse è quello di usare le keywords with ... as ... in modo che le risorse vengano rilasciate correttamente ed automaticamente alla fine del blocco di istruzioni

import pyodbc

connection_string = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=.;DATABASE=python_db;UID=nodejs;PWD=nodE$Js_2015!x"
query = "SELECT [ID],[Category],[modified] FROM [dbo].[categories] WHERE [ID] > "

with pyodbc.connect(connection_string) as cnn:
    with cnn.cursor() as cursor:
        cursor.execute(query + "2")
        # in questo caso uso "fetchone" per posizionarmi sulla prima riga
        row = cursor.fetchone()
        # finché ci sono i dati continuo il ciclo
        while row:
            print("id:", row[0])
            print("category:", row[1])
            print("modified:", row[2])
            # mi sposto sulla riga successiva e continuo 
            row = cursor.fetchone()

In questo esempio ho usato la combinazione cursor.fetchone() e il ciclo while per visualizzare le righe, in alternativa al for precedente.

Negli esempi precedenti il parametro con valore "2", viene concatenato alla stringa contenente la query.
Questo modo di costruire le query, concatenando le stringhe, può aprire la porta a scenari di tipo SQL injection, soprattutto nel caso in cui il parametro arriva da un input dell'utente. Assolutamente da evitare.

La soluzione ottimale è quella di costruire delle query parametriche usando il punto di domanda come placeholder per i parametri.
La query precedente può essere riscritta così

# notare il punto di domanda finale usato come placeholder del valore
query = "SELECT [ID],[Category],[modified] FROM [dbo].[categories] WHERE [ID] > ?"

usando il metodo cursor.execute(query, params) per passare una collection di valori a params che verranno sostituiti, in modo sicuro, all'interno della query in corrispondenza dei punti di domanda

with pyodbc.connect(connection_string) as cnn:
    with cnn.cursor() as cursor:
        # assegno al parametro (?) il valore "numerico" 2
        params = [2]
        # eseguo la query con i parametri
        cursor.execute(query, params)
        for row in cursor:
            print("id:", row[0])
            print("category:", row[1])
            print("modified:", row[2])

In modo analogo possiamo scrivere su SQL (INSERT, UPDATE e DELETE), questo è un esempio di insert

# query di INSERT di un nuovo record con parametri (?)
query = """
    INSERT INTO [dbo].[categories] ([ID], [Category], [Modified])
    VALUES(?, ?, GETDATE());
"""
with pyodbc.connect(connection_string) as cnn:
    with cnn.cursor() as cursor:
        # i parametri sono posizionali, verranno sostituiti
        # in base all'ordine in cui compaiono nella query
        params = [7, "light yellow"]
        cursor.execute(query, params)

In questo caso uso una stringa delimitata da tre virgolette doppie per avere una formattazione su più righe

posso modificare la query aggiungendo la keyword OUTPUT, di T-SQL, per farmi ritornare i valori inseriti tra cui il campo Modified di tipo DateTime valorizzato lato SQL Server

# notare la keyword "OUTPUT" e l'uso del prefisso "INSERTED"
query = """
    INSERT INTO [dbo].[categories] ([ID], [Category], [Modified])
    OUTPUT [INSERTED].[ID], [INSERTED].[Category], [INSERTED].[Modified]  
    VALUES(?, ?, GETDATE());
"""

with pyodbc.connect(connection_string) as cnn:
    with cnn.cursor() as cursor:
        params = [8, "dark gray"]
        cursor.execute(query, params)
        # rileggo il risultato dell'insert, keyword OUTPUT nella query
        row = cursor.fetchone()
        print("id:", row[0])
        print("category:", row[1])
        print("modified:", row[2])

Questo metodo di utilizzo della keyword OUTPUT torna utile anche per recuperare il valore di un eventuale campo IDENTITY.

Una soluzione migliore all'uso delle query parametriche, è l'uso delle store procedure. Il vantaggio delle store procedure è quello di essere parsate una sola volta e compilate lato server, quindi risultano molto più efficienti dell'uso delle query stringa.

La sintassi della query per recuperare dati da una store procedure è questa

EXEC nomeStorePocedure @nomeParametro1 = ?, @nomeParametro2 = ?, ...

Con altri database, diversi da Microsoft Sql Server, la convenzione per i parametri potrebbe essere diversa dall'uso del prefisso chiocciola (@)

oppure

{CALL nomeStorePocedure (?, ?, ...)}

Ad esempio per recuperare una singola riga, posso usare la store SpuCategoriesGet

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE SpuCategoriesGet
	@ID int
AS
BEGIN
	SET NOCOUNT ON;

	SELECT [ID],[Category],[Modified]
	FROM [dbo].[categories]
	WHERE [ID]=@ID
END
GO

ed utilizzarla con il seguente codice

# uso le graffe e la keyword CALL per richiamare la store procedure
# passando un parametro (?)
query = "{CALL SpuCategoriesGet (?)}"
# query = "EXEC SpuCategoriesGet @ID=?"

with pyodbc.connect(connection_string) as cnn:
    with cnn.cursor() as cursor:
        params = [2]    # l'id della riga da recuperare
        cursor.execute(query, params)
        row = cursor.fetchone()
        print(f"{row[0]} | {row[1]} | {row[2]}")

Un altro metodo disponibile sul cursore è fetchval() che restituisce il singolo valore ritornato da una query o store.
Ad esempio questa query SpuCategoriesGetCategory, che passando un valore corrispondente all'Id della riga, ritorna la descrizione presente nel campo Category

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE SpuCategoriesGetCategory
	@ID int 
AS
BEGIN
	SET NOCOUNT ON;

	SELECT TOP 1 [Category]
	FROM [dbo].[categories]
	WHERE [ID] = @ID;
END
GO

il codice Python per usare la store è questo

query = "{CALL SpuCategoriesGetCategory (?)}"
# query = "SELECT count(*) FROM categories" # ritorna il conteggio delle righe

with pyodbc.connect(connection_string) as cnn:
    with cnn.cursor() as cursor:
        params = [2]  
        cursor.execute(query, params)
        # recupero il primo campo del primo record con fetchval()
        return_value = cursor.fetchval()
        print(f"Category: {return_value}")

In alternativa al riferimento posizionale nei dati di ritorno, possiamo usare l'accesso ai campi per nome usando la sinstassi

row.nomeCampo

ad esempio

query = "SELECT [ID] AS [id],[Category],[modified] FROM [dbo].[categories] ORDER BY [Category];"

with pyodbc.connect(connection_string) as cnn:
    with cnn.cursor() as cursor:
        cursor.execute(query)
        for row in cursor:
            # accedo per nome case sensitive, 
            # "row.id" => ok, "row.ID" => errore perchè è stato rinominato con "AS"
            print("id:", row.id)
            print("category:", row.Category)
            print("modified:", row.modified)

Il nome dei campi dopo il punto è case sensitive e dipende da come sono scritti nella query, non importa come sono scritti nel database.

Per maggiori informazioni vedi la documentazione ufficiale.

La stessa libreria permette di accedere anche a Oracle, My SQL, SQLite, PostgreSQL, ecc ...