Intéressé par des cours d'informatique en ligne ?
Visitez mon nouveau site
https://www.yesik.it !
cx_Oracle est un module Python qui permet de se connecter à une base de données Oracle à partir de Python. cx_Python est conforme à la PEP-249 Python Database API Specification v2.0 (DBAPI). C'est à dire que si vous avez déjà travaillé avec une autre interface compatible, vous devriez sans trop de problèmes retrouver vos points de repère. Dans le cas contraire, pas de panique: vous constaterez dans les lignes suivantes qu'accéder à une base de données avec Python est un jeu d'enfant.
Remarque:
La PEP-249 Python Database API Specification v2.0 (DBAPI) est une spécifications de bas niveau. Elle peut être utilisée directement comme nous allons l'illustrer ici. Ou par l'intermédiaire d'API de plus haut niveau comme SQLAlchemy. Mais c'est là une autre histoire...
Installer les outils
En introduction, je parlais de jeu d'enfant. Mais avant de pouvoir s'amuser, il faut passer par la corvée de l'installation. En fait, j'exagère un peu: ici encore rien de vraiment rébarbatif. Comme à mon habitude, je vais donner ici la procédure pour Linux.
Python
Tout d'abord, je suppose que vous avec une version de Python récente déjà installée sur votre machine. La dernière version de cx_Oracle au moment où j'écris est la 5.1.3 — compatible avec Python 2.7, 3.3 et 3.4. En ce qui me concerne, j'utiliserai ici Python 3.4.1:
sh$ python3 --version Python 3.4.1
Oracle InstantClient
Ensuite, en plus de python, vous aurez besoin des bibliothèques et du SDK Oracle InstantClient. Pour les obtenir, il faut vous rendre sur la page de téléchargement du site d'Oracle. Là, vous devrez choisir votre architecture et accepter la licence d'utilisation avant de pouvoir télécharger les fichiers nécessaires. Dans mon cas, il s'agit des fichiers instantclient-basic-linux.x64-12.1.0.2.0.zip et instantclient-sdk-linux.x64-12.1.0.2.0.zip.
Une fois les archives sur votre machine, il faut les "dézipper". L'emplacement n'a pas vraiment d'importance. Mais pour ma part, je les ai installées dans /opt/oracle afin que tous les utilisateurs du système puissent y accéder:
sh$ mkdir -p /opt/oracle sh$ cd /opt/oracle sh$ unzip /path/to/instantclient-basic-linux.x64-12.1.0.2.0.zip sh$ unzip /path/to/instantclient-sdk-linux.x64-12.1.0.2.0.zip
Quand ce sera fait, vous trouverez un nouveau sous répertoire appelé instantclient_*_* (le nom exact dépendra de la version installé). Dans mon cas, il s'agit de instantclient_12_1. Il faut aller dans ce dossier pour rajouter un lien manquant vers une bibliothèque:
sh$ cd instantclient_*_*/ sh$ ln -s libclntsh.so.*.* libclntsh.so
cx_Oracle
Voilà, c'est presque fini. Il vous reste à définir la variable d'environnement ORACLE_HOME puis à rajouter le chemin vers les bibliothèque à LD_LIBRARY_PATH et vous pourrez installer cx_Oracle:
sh$ export ORACLE_HOME="/opt/oracle/instantclient_12_1"
sh$ export LD_LIBRARY_PATH="${ORACLE_HOME}:${LD_LIBRARY_PATH}"
sh$ python3 setup.py build sh$ sudo python3 setup.py install
Tester l'installation
Si tout se passe comme prévu, après quelques secondes vous serez en mesure de tester votre installation:
sh$ python3 Python 3.4.1 (default, Oct 29 2014, 15:58:14) [GCC 4.9.1] on linux >>> import cx_Oracle as cx >>> cx.version '5.1.3'
>>> cx.clientversion() (12, 1, 0, 2, 0)
>>> con = cx.connect('sylvain/password@grolem.hoenn.pkmn')
>>> con.version
'11.2.0.2.0'
Comme vous le voyez, sur ma machine j'ai donc installé cx_Oracle version 5.1.3, qui utilise la bibliothèque InstantClient 12.1.0.2.0 pour se connecter à un serveur Oracle version 11.2.0.2.0.
Utilisation
Première requête
Passons maintenant au vif du sujet en tentant une première requête. Quelque chose de simple, mais qui utilise quand même une construction spécifique à Oracle, juste pour se convaincre que j'utilise bien un driver dédié:
import cx_Oracle as cx # 1) établir une connexion avec le serveur con = cx.connect('sylvain/password@grolem.hoenn.pkmn') # 2) obtenir un "curseur" # c'est l'objet qui sert à effectuer une requête et à # recupérer le résultat cursor = con.cursor() # 3) effectuer la requête cursor.execute("select level n from dual connect by level < 10") # ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ # générer les entiers de 1 à 10 à l'aide d'une # requête hiérarchique (spécifique à Oracle) # 4) collecter et afficher le résultat for row in cursor: print(row) # 5) fermer le curseur cursor.close() # 6) fermer la connexion au serveur con.close()
Globalement, le fonctionnement de ce fragment de code devrait être assez facilement compréhensible — même sans expérience préalable de DBAPI. Quand au résultat produit, il correspond à ce que l'on est en droit d'attendre:
sh$ python3 cx_1.py (1,) (2,) (3,) (4,) (5,) (6,) (7,) (8,) (9,)
Remarquez au passage que chaque ligne du résultat est obtenue sous la forme d'un tuple. Vous devrez donc accéder au champs individuels par leur index, et non pas par le nom de la colonne correspondante. L'exemple suivant utilise la base de données de démonstration HR livrée avec Oracle pour illustrer ce point:
import cx_Oracle as cx con = cx.connect('hr/password@grolem.hoenn.pkmn') cursor = con.cursor() cursor.execute("select first_name, last_name from employees where rownum < 10") # ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ # récupère le prénom et le nom de 10 employés arbitrairement choisis for row in cursor: print("Prénom: {:20s} Nom: {:20s}".format(row[0], row[1])) # ^^^ ^^^ # accès aux champs individuels par # leur index cursor.close() con.close()
De manière alternative, on peut aussi déstructurer le tuple pour rendre les choses un peu plus lisibles:
for row in cursor: nom, prenom = row print("Prénom: {:20s} Nom: {:20s}".format(nom, prenom))
Dans les deux cas, le résultat produit est le suivant:
Prénom: Ellen Nom: Abel Prénom: Sundar Nom: Ande Prénom: Mozhe Nom: Atkinson Prénom: David Nom: Austin Prénom: Hermann Nom: Baer Prénom: Shelli Nom: Baida Prénom: Amit Nom: Banda Prénom: Elizabeth Nom: Bates Prénom: Sarah Nom: Bell
Récupérer les lignes comme un dictionnaire
Remarque:
Cette fonctionnalité est spécifique à cx_Oracle et ne fait pas partie de l'API DB2 de Python
Si récupérer un tuple n'est pas satisfaisant, cx_Oracle vous offre la possibilité de définir votre propre format pour les lignes lues. Cela passe par la définition d'une fonction chargée de convertir le tuple original à votre guise:
import os import cx_Oracle as cx os.environ['NLS_LANG']='FRENCH_FRANCE.UTF8' # Définit la variable d'environnement NLS_LANG # pour utiliser l'encodage UTF-8 côté client con = cx.connect('hr/password@grolem.hoenn.pkmn') cursor = con.cursor() cursor.execute('select first_name "Prénom", last_name "Nom" ' ' from employees where rownum < 10') # J'utilise dans la requête des alias pour le "Prénom" (avec un accent!) # et le nom def set_rowfactory(cursor): """Associe ma "factory" à un curseur.""" def my_factory(*t): # grâce à `curseur.description` # je peux accéder au nom de chaque colonne. # Et une compréhension de dictionnaire me permet # de construire un tableau associatif avec les données return { cursor.description[i][0] : value for i, value in enumerate(t) } # Associe la "factory" avec le curseur cursor.rowfactory = my_factory set_rowfactory(cursor) # Le reste du code est identique à ce que nous avions en tout premier... for row in cursor: print(row) cursor.close() con.close()
Observez maintenant le résultat:
{'Prénom': 'Ellen', 'Nom': 'Abel'}
{'Prénom': 'Sundar', 'Nom': 'Ande'}
{'Prénom': 'Mozhe', 'Nom': 'Atkinson'}
{'Prénom': 'David', 'Nom': 'Austin'}
{'Prénom': 'Hermann', 'Nom': 'Baer'}
{'Prénom': 'Shelli', 'Nom': 'Baida'}
{'Prénom': 'Amit', 'Nom': 'Banda'}
{'Prénom': 'Elizabeth', 'Nom': 'Bates'}
{'Prénom': 'Sarah', 'Nom': 'Bell'}
Selon vos besoins, vous pourrez trouver cela plus pratique que d'utiliser un tuple. Pour ma part, j'aime assez cette technique car elle réduit les problèmes de maintenance lorsqu'un champ est ajouté à une requête SELECT tout en restant simple. Évidemment, il y a un léger surcoût à l'exécution. Comme toujours, c'est une question de compromis...
Transmettre des données non-ASCII
Je n'ai pas trop insisté dessus jusqu'à présent, mais il est (heureusement) possible de transmettre et recevoir des données non-ASCII avec cx_Oracle. Comme vous l'avez peut-être remarqué dans l'exemple précédent j'ai défini la variable d'environnement NLS_LANG à une valeur adaptée avant d'ouvrir la connexion à la base de données. Cela a été indispensable afin d'utiliser l'alias prénom (avec un accent) pour l'une des colonnes récupérées:
cursor.execute('select first_name "Prénom", last_name "Nom" '
...
Sur mon système Linux, j'utilise utf8 -- et j'ai donc choisi ici en encodage correspondant. Si vous utilisez plutôt ISO-8859-15 ou l'un des nombreux autres encodages de caractères supportés par Oracle, vous ajusterez en conséquence. Par exemple:
| Encodage | Nom de code Oracle | Exemple |
|---|---|---|
| ISO-8859-1(Latin1) | WE8ISO8859P1 | os.environ['NLS_LANG']='FRENCH_FRANCE.WE8ISO8859P1' |
| ISO-8859-15 (Latin0/9) € symbol | WE8ISO8859P15 | os.environ['NLS_LANG']='FRENCH_FRANCE.WE8ISO8859P15' |
| UTF-8 | UTF8 (Unicode 3.0) | os.environ['NLS_LANG']='FRENCH_FRANCE.UTF8' |
| AL32UTF8 (Unicode 4.0) | os.environ['NLS_LANG']='FRENCH_FRANCE.AL32UTF8' | |
| Windows CP-1252 | WE8MSWIN1252 | os.environ['NLS_LANG']='FRENCH_FRANCE.WE8MSWIN1252' |
Commandes de définitions de données (DDL statements)
Pour continuer notre exploration de cx_Oracle, voyons maintenant comment envoyer des commandes de définition de données (CREATE, DROP, ...). Par exemple pour créer une table:
import os import cx_Oracle as cx os.environ['NLS_LANG']='FRENCH_FRANCE.UTF8' con = cx.connect('sylvain/password@grolem.hoenn.pkmn') cursor = con.cursor() cursor.execute('create table t("id" number(5) not null, ' ' "nom" varchar2(80), "prénom" varchar2(80))') cursor.close() con.close()
Comme vous le voyez, DML ou DDL, pas de vraie différence. On teste?
sh$ python3 cx_4.py && echo ok ok
Bien. Mais que ce passe-t-il si on relance ce programme une seconde fois?
sh$ python3 cx_4.py && echo ok
Traceback (most recent call last):
File "cx_4.py", line 8, in <module>
cursor.execute('create table t("id" number(5) not null, '
cx_Oracle.DatabaseError: ORA-00955: name is already used by an existing object
Ah, ah! Une exception. Que l'on peut traiter comme d'habitude avec Python:
import os import sys import cx_Oracle as cx os.environ['NLS_LANG']='FRENCH_FRANCE.UTF8' con = cx.connect('sylvain/password@grolem.hoenn.pkmn') cursor = con.cursor() try: cursor.execute('create table t("id" number(5) not null, ' ' "nom" varchar2(80), "prénom" varchar2(80))') except cx.DatabaseError as exc: error, = exc.args print("Code: ", error.code, file=sys.stderr) # print("Offset ", error.offset, file=sys.stderr) # ^^^^^^ # position de l'erreur dans la requête. # sans signification ici print("Message ", error.message.strip(), file=sys.stderr) # ^^^^^^^ # élimine la "fin de ligne" (EOL) print("Context ", error.context, file=sys.stderr) cx.DatabaseError finally: cursor.close() con.close()
sh$ python3 cx_4b.py Code: 955 Message ORA-00955: name is already used by an existing object Context Cursor_InternalExecute()
Insérer des données
En se basant sur la table créée dans la section précédente, voyons comment transmettre une requête pour insérer des données. En soi même, cela n'a rien de spécial. Mais c'est une très bonne occasion pour évoquer quelques petites choses... Mais, tout d'abord, le code:
import os import cx_Oracle as cx os.environ['NLS_LANG']='FRENCH_FRANCE.UTF8' con = cx.connect('sylvain/password@grolem.hoenn.pkmn') data = ({"nom":"Starr", "prénom":"Ringo"}, {"nom":"Harrison", "prénom":"George"}) cursor = con.cursor() for idx, rec in enumerate(data): cursor.execute('insert into t("id", "nom", "prénom") ' ' values(:id, :nom, :prénom)', id=idx, **rec) cursor.close() con.close()
Comme vous le voyez, on peut passer des paramètre nommés (:nom, ...) dans une requête. Les arguments étant passés en fin de commande execute.
Il est tentant de se dire en lisant ce fragment de code, qu'il n'est guère efficient puisqu'on envoie encore et toujours la même requête au serveur. Il serait plus intéressant de créer puis de ré-utiliser une requête préparée. Mais ... c'est déjà ce qui se passe de manière transparente!
En effet, lors de la première itération, execute prépare la requête et la garde en cache. Quand je ré-invoque cette méthode avec la même requête à l'itération suivante, cx_Oracle la retrouve dans son cache et n'impose donc pas au SGBD de la recompiler à chaque fois. Je vous renvoie sur la documentation de Cursor.execute pour les subtilités de ce mécanisme.
Quand à l’exécution, si vous testez ce programme, vous verrez qu'il s'exécute sans exception. Vérifions cependant que les données sont bien insérées. Par exemple, à partir de SQL*Plus:
sh$ sqlplus sylvain@grolem.hoenn.pkmn
Enter password:
SQL> SELECT * FROM T;
no rows selected
Hein? Comment ça, aucune ligne ?!?
Si vous avez un petit peu d'expérience avec les bases de données, vous vous doutez de ce que j'ai oublié:
for idx, rec in enumerate(data):
cursor.execute('insert into t("id", "nom", "prénom") '
' values(:id, :nom, :prénom)',
id=idx, **rec)
cursor.close()
con.commit()
con.close()
Si vous ne confirmez pas la transaction par un appel explicite à con.commit, les modifications sont annulées (rollback). Justement, pour vous éviter d'oublier cela, cx_Oracle autorise l'utilisation de l'objet connexion comme un gestionnaire de contexte. Ce que nous allons utiliser maintenant.
Utiliser la connexion comme gestionnaire de contexte
Un gestionnaire de contexte (PEP-343 The "with" Statement) est une construction introduite avec Python 2.5 et qui permet de garantir que certaines taches seront exécutées automatiquement à la sortie d'un bloc. C'est très utile par exemple pour s'assurer qu'un fichier sera fermé quoi qu'il arrive. Ou, dans notre cas, qu'une transaction sera confirmé en cas de sortie normale du bloc. Avec le corollaire que les modifications sont annulées si l'on sort du bloc à cause d'une exception. En pratique cela s'écrit ainsi:
import os import cx_Oracle as cx os.environ['NLS_LANG']='FRENCH_FRANCE.UTF8' with cx.connect('sylvain/password@grolem.hoenn.pkmn') as con: data = ({"nom":"Starr", "prénom":"Ringo"}, {"nom":"Harrison", "prénom":"George"}) cursor = con.cursor() for idx, rec in enumerate(data): cursor.execute('insert into t("id", "nom", "prénom") ' ' values(:id, :nom, :prénom)', id=idx, **rec) cursor.close()
Remarquez: plus de con.commit() ni de con.close(). La durée de vie de la connexion est le bloc with. Et celle-ci est automatiquement confirmée (commit) en cas de sortir normale.
Remarque:
Si pour une raison ou une autre vous ne souhaitez pas utiliser l'objet connexion en gestionnaire de contexte, l’idiome équivalent est:
con = cx.connect('sylvain/password@grolem.hoenn.pkmn') cursor = con.cursor() try: cursor.execute(...) except cx.DatabaseError: con.rollback() raise else: con.commit() finally: cursor.close() con.close()
Supprimer des enregistrements
Passons maintenant à la suppression d'un enregistrement. Mais tout d'abord, je vous rassure: nous n'allons pas faire l'inventaire de toutes les commandes SQL. Vous l'avez compris, le principe restera toujours le même. Néanmoins, je vais utiliser le prétexte d'une requête DELETE pour aborder un dernier point de détail:
import os import cx_Oracle as cx os.environ['NLS_LANG']='FRENCH_FRANCE.UTF8' with cx.connect('sylvain/password@grolem.hoenn.pkmn') as con: cursor = con.cursor() cursor.execute('delete from t') print("Enregistrements supprimés:", cursor.rowcount) # ^^^^^^^^^^^^^^^ # nombre de lignes affectés par la requête cursor.close()
Comme vous le voyez dans le code, le curseur a un attribut rowcount qui permet de connaître le nombre de lignes affectés par une requête (DELETE,UPDATE ou INSERT) ou les nombre de ligne sélectionnées (SELECT).
À l'exécution, cela produit ce résultat:
sh$ python3 cx_7.py Enregistrements supprimés: 2
Conclusion
Voilà, ce rapide tour d'horizon de cx_Oracle est terminé. Je n'ai fait qu’effleurer les possibilités de ce module. Mais cela devrait déjà être suffisant pour vous permettre de stocker et récupérer des données, ou encore de modifier le schéma de votre base. Une fois que vous serez un peu plus familiarisé avec ces fonctionnalités élémentaires, n'hésitez pas à vous plonger dans la documentation officielle: celle-ci est un peu aride, mais avec de la persévérances, vous découvrirez que cx_Oracle est suffisamment souple pour s'adapter à de nombreux besoins tout en étant adapté aux spécificités d'Oracle.
Ressources
- Documentation officielle de cx_Oracle
- Using Python With Oracle Database 11g – sur the Oracle Technology Network
