Comment gérer des tables SQL Server avec Python ? pyodbc

Comment se connecter à une base de données et gérer des tables SQL Server avec des scripts Python et la bibliothèque pyodbc ?

Bienvenue dans ce tutoriel pour apprendre à se connecter à SQL Server et gérer les tables avec du code écrit en langage Python ! Et ainsi gérer les tables dans les bases de données MS SQL avec le module pyodbc et le language T-SQL! En effet, vous apprendrez à utiliser Python pour interagir avec une base de données SQL Server. Que vous soyez débutant ou utilisateur avancé, ce cours vous fournira les connaissances et les compétences nécessaires pour travailler avec SQL Server en Python. Dans le tutoriel Python précédent, nous avons vu comment gérer les fichiers texte en utilisant Python et des scripts simples.

Dans cette section, nous aborderons les bases de la connexion à une base de données SQL Server en utilisant Python. Nous utiliserons la bibliothèque pyodbc. Pyodbc est une bibliothèque populaire et il est beaucoup plus facile de travailler avec des bases de données SQL Server en utilisant Python.

Table of Contents

1. Installer le module Python pyodbc

Tout d’abord, vous devrez installer la bibliothèque pyodbc. Vous pouvez le faire en exécutant la commande suivante dans votre invite de commande ou terminal.

pip install pyodbc

2. Se connecter à une base de données SQL Server avec Python

Une fois la bibliothèque installée, importez-la dans le script Python à l’aide de la fonction d’import, et utilisez-la pour créer une connexion à la base de données SQL Server. Le script ci-dessous effectue ces deux opérations.

Importer la bibliothèque pyodbc
Utiliser la méthode connect() pour créer une connexion à la base de données SQL Server, et passer les informations nécessaires :
- Le pilote SQL : {SQL Server}
- Le nom du serveur : localhost
- Nom de la base de données : Expert-Only
- Et si la connexion doit être considérée comme fiable ou non : oui.

import pyodbc

connection = pyodbc.connect('Driver={SQL Server};'
                            'Server=server_name;'
                            'Database=database_name;'
                            'Trusted_Connection=yes;')

Dans le script ci-dessus, nous importons la bibliothèque pyodbc et utilisons la méthode connect() pour créer une connexion à la base de données SQL Server. Nous passons les informations nécessaires telles que le nom du serveur, le nom de la base de données et si la connexion doit être fiable ou non.

Une fois que vous avez une connexion à la base de données SQL Server, vous pouvez l’utiliser pour exécuter des requêtes. Dans les prochaines sections, nous couvrirons comment exécuter différents types de requêtes en utilisant Python.

Pour travailler avec SQL Server en utilisant Python, les opérations de base de données les plus utilisées sont SELECT, UPDATE et DELETE. Pour les utiliser, préparez d’abord la table et insérez quelques lignes d’exemple.

3. Créer une table SQL Server en Python

Tout d’abord, importez la bibliothèque pyodbc et créez la connexion avec le serveur, puis créez un objet curseur pour exécuter la requête SQL qui crée la table clients, puis validez la transaction, et enfin fermez le curseur et la connexion au serveur. Ci-dessous est un exemple pratique d’une chaîne de connexion Python pour se connecter à une base de données SQL Server.

import pyodbc

connection = pyodbc.connect('Driver={SQL Server};'
                            'Server=localhost;'
                            'Database=Expert-Only;'
                            'Trusted_Connection=yes;')

# Créer un object de type curseur en Python
cursor = connection.cursor()

# Create the customers table
create_table_query = """
CREATE TABLE [dbo].[Customers](
   [CustomerID] [int] NOT NULL,
   [FirstName] [nvarchar](20) NULL,
   [LastName] [nvarchar](20) NULL,
   [City] [nvarchar](20) NULL,
   [Country] [nvarchar](50) NULL,
   CONSTRAINT [CustomersPrimaryKeyCustomerID] 
      PRIMARY KEY CLUSTERED ([CustomerID] ASC)
);
"""
cursor.execute(create_table_query)

# Valider la transaction dans la base SQL avec commit
connection.commit()

# Close the cursor and the connection
cursor.close()
connection.close()

4. Insérer des données dans une table SQL en langage Python

Une autre requête courante est la requête INSERT, qui est utilisée pour insérer des données dans une table. Pour exécuter une requête INSERT, vous pouvez utiliser la méthode cursor() pour créer un objet curseur, puis utiliser la méthode execute() pour exécuter la requête.

Dans le script suivant, nous utilisons une instruction INSERT INTO pour insérer 10 lignes de données dans la table Clients. Nous passons les valeurs pour les colonnes suivantes :

CustomerID
FirstName
LastName
City
et Country.

Les valeurs sont au format (1, ‘Ali’, ‘Ahmed’, ‘Le Caire’, ‘Egypte’). Assurez-vous que les noms de colonnes et les types de données correspondent à votre schéma de table.

# Insert data into the customers table
insert_query = """
INSERT INTO Customers (CustomerID, FirstName, LastName, City, Country)
VALUES (1, 'Ali', 'Ahmed', 'Cairo', 'Egypt'),
       (2, 'Johnny', 'John', 'Toronto', 'Canada'),
       (3, 'John', 'Doe', 'Mexico City', 'Mexico'),
       (4, 'Shu', 'Abbas', 'Paris', 'France'),
       (5, 'Jeane', 'Raffin', 'Liushutun', 'China'),
       (6, 'Legra', 'Leate', 'Baszki', 'Poland'),
       (7, 'Sullivan', 'Goadby', 'Xiaoguwei', 'China'),
       (8, 'Tom', 'Ellams', 'Lop Buri', 'Thailand'),
       (9, 'Trace', 'Fowell', 'Sriwing', 'Indonesia'),
       (10, 'Christoffer', 'Rochford', 'Mburukullu', 'Indonesia')
"""
cursor.execute(insert_query)
print(cursor.rowcount, " rows inserted")

# Valider les changements dans la base avec commit
connection.commit()

Le script affiche également le nombre de lignes insérées dans la table en utilisant la méthode rowcount du curseur.

Comment se connecter à SQL Server en Python ?

5. Exécuter une requête SQL Server SELECT depuis Python

La requête SELECT est le type de requête le plus couramment utilisé pour récupérer des données d’une table. Pour exécuter une requête SELECT, vous pouvez utiliser la méthode cursor() pour créer un objet curseur, puis la méthode execute() pour exécuter la requête. Une fois la requête exécutée, la méthode fetchall() peut être utilisée pour récupérer toutes les lignes renvoyées par la requête.

Dans ce script, un objet curseur est créé et une requête SELECT est exécutée sur la table des clients, nommée Customers. La méthode fetchall() est utilisée pour récupérer toutes les lignes de la requête et les stocker dans la variable rows, puis le script itère à travers les lignes et affiche les données. Enfin, le curseur et la connexion sont fermés.

# Créer un object de type curseur
cursor = connection.cursor()

# Execute the SELECT query to retrieve data from the Customers table
cursor.execute("SELECT * FROM Customers")

# Fetch all rows from the query
rows = cursor.fetchall()

# Iterate through the rows and print the data
for row in rows:
    print(row)

# Close the cursor and the connection
cursor.close()
connection.close()

Il faut garder à l’esprit que pour sélectionner uniquement des colonnes spécifiques, il est nécessaire modifier l’instruction SELECT *, qui sélectionne toute les colonnes de la table, en SELECT CustomerID, lastname, firstname FROM Customers, par exemple.

Script Python pour exécuter une requête SQL Server Select

6. Requête SQL Server UPDATE en Python

Un autre type courant de requête est la requête UPDATE, utilisée pour mettre à jour des données dans une table SQL. Pour exécuter une requête de mise à jour, une option est d’utiliser la méthode cursor() pour créer un objet de type curseur, puis utiliser la méthode execute() pour exécuter la requête SQL Server.

Comme pour toutes les requêtes qui effectuent une modification des données, il est important de systématiquement valider la transaction après l’exécution d’une requête UPDATE. Cette validation permet d’enregistrer les modifications de manière effective dans la table, et d’écrire les données physiquement dans la base de données MS SQL.

# Créer un object de type curseur
cursor = connection.cursor()

# Execute the UPDATE query to update data in the Customers table
cursor.execute("UPDATE Customers SET City = 'New York' WHERE CustomerID = 1")

# Valider les changements dans la base avec commit
connection.commit()

# Close the cursor and the connection
cursor.close()
connection.close()

Ce script crée un objet curseur et exécute une requête UPDATE sur la table Customers en utilisant la méthode cursor.execute(). La requête met à jour la valeur de la colonne City à ‘New York’ lorsque CustomerID est égal à 1. La méthode connection.commit() est utilisée pour valider les modifications dans la base de données. Enfin, le curseur et la connexion sont fermés.

Bien sûr, pour mettre à jour plusieurs colonnes en même temps, il suffit d’adapter la liste des champs et de modifier la clause WHERE pour spécifier les lignes à mettre à jour en ajoutant des conditions. D’autres scripts utiles pour gérer les tables sont les types de données listes en Python.

7. Requêtes DELETE avec Python et SQL Server

Un autre type courant de requête est la requête DELETE, qui est utilisée pour supprimer des données d’une table. Pour exécuter une requête DELETE, vous pouvez utiliser la méthode cursor() pour créer un objet curseur, puis utiliser la méthode execute() pour exécuter la requête.

Comme pour les requêtes SQL INSERT et UPDATE, il est important de valider la transaction après l’exécution d’une requête DELETE pour enregistrer les modifications dans la table.

# Créer un object de type curseur
cursor = connection.cursor()

# Execute the DELETE query to delete data from the Customers table
cursor.execute("DELETE FROM Customers WHERE CustomerID = 1")

# Valider les changements dans la base avec commit
connection.commit()

# Close the cursor and the connection
cursor.close()
connection.close()

8. Conclusion sur les objets MS SQL et Python

Dans ce cours, nous avons découvert les scripts de base pour gérer des tables SQL Server avec des scripts Python. Pour ce faire, utiliser la bibliothèque Python pyodbc et ses différentes méthodes. Par exemple, comment se connecter à une base de données SQL Server? Ou encore comment exécuter différents types de requêtes et comment effectuer des transactions. C’est juste le début de ce que vous pouvez faire avec Python et une base de données SQL Server.

En effet, avec ces exemples pratiques, il est possible de construire des scripts et des applications beaucoup plus complexes. Le but étant d’interagir avec des bases de données SQL Server. J’espère que vous avez apprécié ce tutoriel et je vous encourage à continuer à apprendre et à expérimenter avec Python.

Pour aller plus loin il est possible de modulariser et réutiliser votre code. Pour cela utiliser les fonctions définies par l’utilisateur en Python pour créer du code personnalisé.