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 première abord.
Le choix de la bibliothèque (driver) pour exécuter du SQL va dépendre du SGBDR
id_produit | nom |
---|---|
mysql | PyMySQL,MySQL/connector, MySQLdb |
postgreSQL | psycopg2 |
Oracle | cx_Oracle |
SQL server (microsoft) | pyodbc |
sqlite | sqlite3 |
mongodb | pymongo |
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 :
Ces bibliothèques se ressemblent beaucoup :
PyMySql
semble un très bon choix. MySQL/connector
est propriétaire. Pour la suite des TPs, on utilisera PyMySql
mais on comparera avec 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 sur le site W3Schools : MySQL/connector for Python
remarque : il faut ajouter python -m pip install mysql-connector-python
copier/coller du site W3Schools
Créer un fichier avec l’exemple ci-dessus , exemple de nom de fichier test1_pymysql.py
Remplacer la connexion avec celle de pymysql
#! /usr/bin/python
# -*- coding:utf-8 -*-
import pymysql.cursors
# mysql --user=votreLogin --password=votreMotDePasse --host=serveurmysql --database=BDD_votreLogin
= pymysql.connect( #pymysql.connect remplace mysql.connector
mydb ="serveurmysql", #localhost sur les machines perso.
host="votreLogin",
user="votreMotDePasse",
password="BDD_votreLogin",
database='utf8mb4', # 2 attributs à ajouter
charset=pymysql.cursors.DictCursor # 2 attributs à ajouter
cursorclass
)
= mydb.cursor()
mycursor
# mycursor.execute("DROP TABLE IF EXISTS customers;")
"CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")
mycursor.execute(
# mycursor.execute("CREATE TABLE IF NOT EXISTS customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")
= "INSERT INTO customers (name, address) VALUES (%s, %s)"
sql = ("John", "Highway 21")
val
mycursor.execute(sql, val)
mydb.commit()print(mycursor.rowcount, "record inserted.")
= "INSERT INTO customers (name, address) VALUES (%s, %s)"
sql = [
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.")
"SELECT * FROM customers")
mycursor.execute(= mycursor.fetchall()
myresult for x in myresult:
print(x)
python exercice1_pymysql.py
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.
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
= pymysql.connect( #pymysql.connect remplace mysql.connector
mydb ="serveurmysql" #localhost sur les machines perso.
host="votreLogin"
, user="votreMotDePasse"
, password="BDD_votreLogin"
, database#, port=3306 # sur mamp(mac) le port peut être 8889 ...
="utf8mb4" # 2 attributs à ajouter
, charset=pymysql.cursors.DictCursor # 2 attributs à ajouter
, cursorclass )
#! /usr/bin/python
# -*- coding:utf-8 -*-
from flask import Flask, request, render_template, redirect, flash
= Flask(__name__)
app "TEMPLATES_AUTO_RELOAD"] = True
app.config[= 'une cle(token) : grain de sel(any random string)'
app.secret_key
## à ajouter
from flask import session, g
import pymysql.cursors
def get_db():
if 'db' not in g:
= pymysql.connect(
g.db ="localhost", # à modifier
host="login", # à modifier
user="secret", # à modifier
password="BDD_votrelogin", # à modifier
database='utf8mb4',
charset=pymysql.cursors.DictCursor
cursorclass
)return g.db
@app.teardown_appcontext
def teardown_db(exception):
= g.pop('db', None)
db 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 recréer et fermer la connexion à chaque requête HTTP, sinon il y a un risque de fuite de mémoire
Exemple de code pour ajouter/modifier/supprimer et afficher le contenu de la table “etudiant”
Cloner le projet avec la commande :
git clone https://github.com/amillet-iut90/flask_demo1.git
# ouvrir le dossier avec pycharm
# dans un terminal
chmod u+x launcher.sh
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 (
INT AUTO_INCREMENT
id_etudiant VARCHAR(255)
, nom_etudiant VARCHAR(255)
, groupe_etudiant PRIMARY KEY(id_etudiant)
, );
Proposer le code SQL pour :
id_etudiant
nom_etudiant, groupe_etudiant
de l’étudiant avec son identifiant id_etudiant
Modification du code :
SELECT id_etudiant AS id, nom_etudiant AS nom, groupe_etudiant AS groupe
FROM etudiant
ORDER BY nom_etudiant;
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;
mycursor = get_db().cursor()
sql=''' SELECT '''
mycursor.execute(sql)
liste_enregistrement = mycursor.fetchall()
un_enregistrement = mycursor.fetchone()
Pour SELECT, on récupère :
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
).
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 '
)
Lors du passage de paramètres, if faut faire attention aux injections
⚠️ => Ne jamais former une chaîne de texte avec la concaténation de texte (instruction SQL) et de paramètres de la requête HTTP (INPUT du formulaire par exemple)
Le driver réalise une requête préparée - doc mariadb - exemple de requête préparée
ATTENTION ne pas construire de code SQL en concaténant du code SQL et des valeurs issues de formulaires HTML => Injection SQL
mycursor = mydb.cursor()
. Sur FLASK, remplacer mydb
par la fonction get_db()
.%s
%s
result=mycursor.execute(sql, tuple)
[]
peut remplacer un tuple python ()
commit
avec la connexion, exemple de commande : mydb.commit()
(ATTENTION mydb
n’est pas un curseur mais la connexion. Sur FLASK, remplacer mydb
par la fonction get_db()
.)Travail à réaliser :
Modifier le projet ci dessus de façon à obtenir une interface minimale pour afficher/ajouter/modifier/supprimer des enregistrements dans la table “etudiant”.