Interaction Python & SQL Server

SQL Server est le système de gestion de bases de données relationnelles de Microsoft. Nous verrons connecter se connecter à une base de donnée SQL Server et effectuer les opérations CRUD.

Je vous propose de manipuler les bases de données avec le packages SQLite3. Il est natif dans Python et il est facile à utiliser.

Créer une table

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('datase.db')
print("Opened database successfully")

conn.execute('''CREATE TABLE COMPANY
         (id INT PRIMARY KEY     NOT NULL,
         name           TEXT    NOT NULL,
         age            INT     NOT NULL,
         address        CHAR(50),
         salary         REAL);''')
print("Table created successfully")

conn.close()

Voici les quatre principales opérations sur les tables d'une base de données (C.R.U.D).

1. Create
2. Read
3. Update
4. Delete

1. Insérer des données

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('datase.db')
print("Opened database successfully")

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Paul', 32, 'California', 20000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");

conn.commit()
print("Records created successfully")
conn.close()

2. Lire les données

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('datase.db')
print("Opened database successfully")

cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
   print("ID = ", row[0])
   print("NAME = ", row[1])
   print("ADDRESS = ", row[2])
   print("SALARY = ", row[3], "\n")

print ("Operation done successfully")
conn.close()

3. Mettre à jour les données

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('datase.db')
print("Opened database successfully");

conn.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
conn.commit()
print("Total number of rows updated :", conn.total_changes)

cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
   print("ID = ", row[0])
   print("NAME = ", row[1])
   print("ADDRESS = ", row[2])
   print("SALARY = ", row[3], "\n")

print("Operation done successfully")
conn.close()

4. Supprimer des données

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('datase.db')
print("Opened database successfully")

conn.execute("DELETE from COMPANY where ID = 2;")
conn.commit()
print("Total number of rows deleted :", conn.total_changes)

cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
   print("ID = ", row[0])
   print("NAME = ", row[1])
   print("ADDRESS = ", row[2])
   print("SALARY = ", row[3], "\n")

print("Operation done successfully")
conn.close()

---

Si vous souhiatez aller plus loin, rendez-vous sur la documentation de SQLite ici.