choix du driver

Il existe plusieurs bibliothèques différentes pour exécuter du SQL à l’aide du langage Python (au travers d’un connecteur). On procède de la même manière en PHP, en Javascript, ou dans d’autres langages. La tendance est de passer par des ORMs, mais c’est plus compliqué de prime abord.

recherche en fonction du SGBDR

Le choix de la bibliothèque (driver) pour exécuter du SQL va dépendre du SGBDR

choix du driver (pilote, bibliothèque)

voici 2 liens : un comparatif des drivers, un petit article sur stackoverflow

Afin de connecter Python avec une base de données via un Driver (le connecteur), il existe des bibliothèques qui servent à interagir avec la base de données. Avec le SGBDR MySQL vous avez principalement le choix entre 3 drivers :

• MySQL/connector for Python doc sur mysql installation : bibliothèque propriétaire (ORACLE) qui se connecte à MySQL/ODBC
• MySQLdb : bibliothèque qui se connecte à MySQL à partir de Python, elle est écrite en langage C, elle est gratuite et le logiciel est “open source”.
• PyMySQL : bibliothèque qui se connecte à MySQL à partir de Python, c’est une bibliothèque écrite en Python. L’objectif de PyMySQL est de remplacer MySQLdb

Ces bibliothèques se ressemblent beaucoup :

• installation
• utilisation : nom des fonctions

PyMySql semble un très bon choix. MySQL/connector est propriétaire. Pour la suite des TP, on utilisera PyMySql mais on comparera MySQL/connector et PyMySql.

Installation de pymysql

pip  install PyMySQL

# sur debian
pip install pymysql --break-system-packages

PS : mariadb possède aussi une bibliothèque mais elle semble moins utilisée

Exemple avec PyMySql

• exemple de la documentation

exemple sur le site W3Schools : MySQL/connector for Python

copier/coller du site W3Schools

Avec l’éditeur VsCode, créer un fichier avec l’exemple ci-dessus , exemple de nom de fichier test1_pymysql.py
Cet exemple utilise les exemples de W3schools ; la connexion a juste été remplacée par celle de pymysql

#! /usr/bin/python
# -*- coding:utf-8 -*-

import pymysql.cursors

# mysql --user=votreLogin  --password=secret --host=serveurmysql --database=BDD_votreLogin

mydb = pymysql.connect(    #pymysql.connect remplace mysql.connector
  host="serveurmysql",   #localhost sur les machines perso.
  user="votreLogin",
  password="secret",
  database="BDD_votreLogin",
  charset='utf8mb4',                      # 2 attributs à ajouter
  cursorclass=pymysql.cursors.DictCursor  # 2 attributs à ajouter
)

mycursor = mydb.cursor()

# mycursor.execute("DROP TABLE IF EXISTS customers;")

mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")

# mycursor.execute("CREATE TABLE IF NOT EXISTS customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record inserted.")


sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
  ('Peter', 'Lowstreet 4'),
  ('Amy', 'Apple st 652'),
  ('Hannah', 'Mountain 21'),
  ('Michael', 'Valley 345'),
  ('Sandy', 'Ocean blvd 2'),
  ('Betty', 'Green Grass 1'),
  ('Richard', 'Sky st 331'),
  ('Susan', 'One way 98'),
  ('Vicky', 'Yellow Garden 2'),
  ('Ben', 'Park Lane 38'),
  ('William', 'Central st 954'),
  ('Chuck', 'Main Road 989'),
  ('Viola', 'Sideway 1633')
]
mycursor.executemany(sql, val)
mydb.commit()
print(mycursor.rowcount, "was inserted.")

mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

• Tester l’exemple ci-dessus, exemple python exercice1_pymysql.py

utilisation d’une connexion

Quatre étapes pour exécuter une requête

• Établir la connexion avec la base de données
• Créer et exécuter des requêtes SQL
• Récupérer le résultat
• Fermer la connexion

Fermer la connexion : opération très simple avec un simple script python, c’est un peu plus compliqué sur un serveur web, il faut fermer la connexion à chaque requête HTTP, sinon le serveur risque d’avoir des problèmes de fuite de mémoire.

Se connecter à la base de données

Il faut créer un objet objet-connexion à l’aide de la fonction-fabrique connect().

Cet objet assure l’interface entre votre programme et la base de données.

Après avoir importé une instance de l’objet bibliothèque (driver), il faut utiliser la méthode .connect() de la bibliothèque dans laquelle on passe plusieurs paramètres :

#! /usr/bin/python
# -*- coding:utf-8 -*-

import pymysql.cursors

mydb = pymysql.connect(    #pymysql.connect remplace mysql.connector
  host="serveurmysql"   #localhost sur les machines perso.
  , user="votreLogin"
  , password="secret"
  , database="BDD_votreLogin"
  #, port=3306         # sur mamp(mac) le port peut être 8889 ...
  , charset="utf8mb4"                       # 2 attributs à ajouter
  , cursorclass=pymysql.cursors.DictCursor  # 2 attributs à ajouter
)

• le nom de l’hôte (machine) sur lequel le serveur MySQL est installé (serveurmysql sur une machine de l’IUT, localhost ou 127.0.0.1 sur une machine personnelle avec MySQL ou MariaDB d’installé, sur une machine personnelle avec le VPN sur le serveur de l’IUT : serveurmysql.iut-bm.univ-fcomte.fr)
• le port TCP/IP utilisé par MySQL (par défaut 3306). Attention avec un autre SGBDR, le port est différent.
• le nom de la base de données MySQL (BDD_votreLogin sur la machine de l’IUT)
• le nom d’utilisateur (votreLogin sur la machine de l’IUT)
• le mot de passe (secret sur la machine de l’IUT)

Utilisation de “pymysql” dans le “framework” : “flask”

• fonction pour créer puis supprimer une connexion

#! /usr/bin/python
# -*- coding:utf-8 -*-
from flask import Flask, request, render_template, redirect, flash

app = Flask(__name__)
app.config["TEMPLATES_AUTO_RELOAD"] = True
app.secret_key = 'une cle(token) : grain de sel(any random string)'

                                    ## à ajouter
from flask import session, g
import pymysql.cursors

def get_db():
    if 'db' not in g:
        g.db =  pymysql.connect(
            host="localhost",                 # à modifier
            user="login",                     # à modifier
            password="secret",                # à modifier
            database="BDD_votrelogin",        # à modifier
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )
    return g.db

@app.teardown_appcontext
def teardown_db(exception):
    db = g.pop('db', None)
    if db is not None:
        db.close()

L’instruction @app.teardown_appcontext permet d’appeler la fonction close_connection lorsque que le serveur a fini de renvoyer une réponse.

Il faut créer et fermer la connexion à chaque requête HTTP, sinon il y a un risque de fuite de mémoire

• doc flask database 3.0 - doc flask database 2.0 - doc flask appcontext
• ancienne documentation flask -
• doc sqlachemny
  

CRUD “etudiant”

Exemple de code pour ajouter/modifier/supprimer et afficher le contenu de la table “etudiant”

• Pour afficher, ajouter, supprimer, modifier les enregistrements de la table “etudiant”, récupérer l’interface FLASK

Cloner le projet avec la commande :

git clone https://github.com/amillet-iut90/flask_demo1.git

chmod a+x flask_demo1/launcher.sh

# renommer le dossier si besoin
mv flask_demo1 flask_pymysql_demo1

# cd flask_pymysql_demo1/

# ouvrir le dossier (projet) avec pycharm

pycharm  flask_pymysql_demo1

Ouvrir le projet (dossier) avec PyCharm (ne pas créer un nouveau projet)

Vérifier que les 2 bibliothèques nécessaires sont installées

# avec un environnement virtuel 
pip install flask
pip install pymysql

# ou avec l'environnement de votre machine 

pip install flask --break-system-packages
pip install pymysql --break-system-packages

préparation du sql

Voici la liste des étudiants :

liste_etudiants = [
    {'id':1,'nom':'tom', 'groupe':'A1'},
    {'id':2,'nom':'enzo', 'groupe':'A1'},
    {'id':3,'nom':'laurence', 'groupe':'A2'},
    {'id':4,'nom':'theo', 'groupe':'A2'},
    {'id':5,'nom':'mehdi', 'groupe':'B1'}
]

si l’on dispose d’un code SQL comme celui ci-dessous, proposer l’instruction pour créer la table :

INSERT INTO etudiant (id_etudiant, nom_etudiant, groupe_etudiant) 
VALUES
(NULL, 'tom','A1'),
(NULL, 'enzo','A1'),
(NULL, 'laurence','A2'),
(NULL, 'theo','A2'),
(NULL, 'theo','B1')
;

Sans trop de problème, votre code SQL serait :

DROP TABLE IF EXISTS etudiant;
CREATE TABLE etudiant (
id_etudiant INT AUTO_INCREMENT
, nom_etudiant VARCHAR(255)
, groupe_etudiant VARCHAR(255)
, PRIMARY KEY(id_etudiant)
);

Proposer le code SQL pour :

• afficher les étudiants avec les bons noms de colonnes
• ajouter un étudiant
• supprimer un étudiant avec son identifiant id_etudiant
• modifier les attributs (colonnes) nom_etudiant, groupe_etudiant de l’étudiant avec son identifiant id_etudiant

Connectez vous à MySQL dans un terminal :

mysql --user=login --host=serveurmysql.iut-bm.univ-fcomte.fr --password=secret --database=BDD_login

Créer un fichier SQL sql_projet.sql

Modification du code :

SELECT id_etudiant AS id, nom_etudiant AS nom, groupe_etudiant AS groupe
FROM etudiant
ORDER BY nom;

INSERT INTO etudiant(id_etudiant, nom_etudiant, groupe_etudiant) VALUES (NULL, 'test1', 'test1');

DELETE FROM etudiant WHERE id_etudiant=2;

UPDATE etudiant SET nom_etudiant = 'test2', groupe_etudiant= 'test3' WHERE id_etudiant=3;

Tester ce fichier dans le terminal

préparation de la connexion

exécution des instructions sql dans chaque fonction pour chacune des routes quand c’est nécessaire

Pour chercher des informations (SELECT)

    mycursor = get_db().cursor()
    sql='''   SELECT      '''
    mycursor.execute(sql)

    liste_enregistrements = mycursor.fetchall()
    un_enregistrement = mycursor.fetchone()

Pour SELECT, on récupère :

• une liste avec à l’intérieur des dictionnaires. Ces listes peuvent s’apparenter à un tableau à 2 dimensions : un tableau d’enregistrements
• ou un enregistrement : un dictionnaire

Pour ajouter, modifier ou supprimer des informations (INSERT UPDATE DELETE)

    mycursor = get_db().cursor()
    sql=""
    mycursor.execute(sql)

    get_db().commit()

Résultat dans le curseur de l’instruction ci-dessus :

Pour INSERT, UPDATE, DELETE, on récupère le même résultat que sur un terminal : le nombre d’enregistrements affectés par la requête. La méthode rowcount d’un curseur permet de connaître le nombre d’enregistrements affectés (utilisation : mycursor.rowcount).

utilisation de paramètres

Pour faire des requêtes SQL avec des paramètres, il faut passer les paramètres dans un tuple ou un tableau à la fonction mycursor.execute et remplacer dans le code SQL les paramètres par des %s, il ne faut pas entourer les paramètres de simple ou double quotes ( guillemet" apostrophe ')

ATTENTION ne pas construire de code SQL en concaténant du code SQL et des valeurs issues de formulaires HTML => Injection SQL