Intéressé par des cours d'informatique en ligne ?
Visitez mon nouveau site https://www.yesik.it !

Python-logo.png

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