Gestire i database con Python

Python mette a disposizione la libreria pyodbc per gestire l'accesso a vari tipi di database tra cui SQL Server.La libreria va installata tramite il comando pip

DOS / Batch fileCopia

pip install pyodbc

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

PythonCopia

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

SQLCopia

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

PythonCopia

# 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

PythonCopia

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()

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ì

PythonCopia

# 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

PythonCopia

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

PythonCopia

# 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)

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

PythonCopia

# 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])

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

SQLCopia

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

oppure

SQLCopia

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

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

T-SQLCopia

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

PythonCopia

# 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

T-SQLCopia

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

PythonCopia

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

PythonCopia

row.nomeCampo

ad esempio

PythonCopia

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)

Per maggiori informazioni vedi la documentazione ufficiale.

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