Python Language
Accès à la base de données

Remarques

Accéder à la base de données MySQL en utilisant MySQLdb

La première chose à faire est de créer une connexion à la base de données en utilisant la méthode connect. Après cela, vous aurez besoin d'un curseur qui fonctionnera avec cette connexion.

Utilisez la méthode execute du curseur pour interagir avec la base de données et, de temps en temps, validez les modifications à l'aide de la méthode de validation de l'objet de connexion.

Une fois que tout est fait, n'oubliez pas de fermer le curseur et la connexion.

Voici une classe Dbconnect avec tout ce dont vous aurez besoin.

import MySQLdb

class Dbconnect(object):

    def __init__(self):

        self.dbconection = MySQLdb.connect(host='host_example',
                                           port=int('port_example'),
                                           user='user_example',
                                           passwd='pass_example',
                                           db='schema_example')
        self.dbcursor = self.dbconection.cursor()

    def commit_db(self):
        self.dbconection.commit()

    def close_db(self):
        self.dbcursor.close()
        self.dbconection.close()

L'interaction avec la base de données est simple. Après avoir créé l'objet, utilisez simplement la méthode execute.

db = Dbconnect()
db.dbcursor.execute('SELECT * FROM %s' % 'table_example')

Si vous souhaitez appeler une procédure stockée, utilisez la syntaxe suivante. Notez que la liste des paramètres est facultative.

db = Dbconnect()
db.callproc('stored_procedure_name', [parameters] )

Une fois la requête terminée, vous pouvez accéder aux résultats de plusieurs manières. L'objet curseur est un générateur qui peut récupérer tous les résultats ou être en boucle.

results = db.dbcursor.fetchall()
for individual_row in results:
    first_field = individual_row[0]

Si vous voulez une boucle utilisant directement le générateur:

for individual_row in db.dbcursor:
    first_field = individual_row[0]

Si vous souhaitez valider les modifications apportées à la base de données:

db.commit_db()

Si vous voulez fermer le curseur et la connexion:

db.close_db()

SQLite

SQLite est une base de données légère basée sur disque. Comme il ne nécessite pas de serveur de base de données séparé, il est souvent utilisé pour le prototypage ou pour les petites applications souvent utilisées par un seul utilisateur ou par un utilisateur à un moment donné.

import sqlite3

conn = sqlite3.connect("users.db")
c = conn.cursor()

c.execute("CREATE TABLE user (name text, age integer)")

c.execute("INSERT INTO user VALUES ('User A', 42)")
c.execute("INSERT INTO user VALUES ('User B', 43)")

conn.commit()

c.execute("SELECT * FROM user")
print(c.fetchall())

conn.close()

Le code ci-dessus se connecte à la base de données stockée dans le fichier nommée users.db , en créant d'abord le fichier s'il n'existe pas déjà. Vous pouvez interagir avec la base de données via des instructions SQL.

Le résultat de cet exemple devrait être:

[(u'User A', 42), (u'User B', 43)]

La syntaxe SQLite: une analyse approfondie

Commencer

1. Importez le module sqlite en utilisant

   >>> import sqlite3
   

2. Pour utiliser le module, vous devez d'abord créer un objet Connection qui représente la base de données. Ici, les données seront stockées dans le fichier example.db:

   >>> conn = sqlite3.connect('users.db')
   

   Alternativement, vous pouvez également fournir le nom spécial :memory: pour créer une base de données temporaire en RAM, comme suit:

   >>> conn = sqlite3.connect(':memory:')
   

3. Une fois que vous avez une Connection , vous pouvez créer un objet Cursor et appeler sa méthode execute() pour exécuter les commandes SQL:

   c = conn.cursor()
   
   # Create table
   c.execute('''CREATE TABLE stocks
               (date text, trans text, symbol text, qty real, price real)''')
   
   # Insert a row of data
   c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
   
   # Save (commit) the changes
   conn.commit()
   
   # We can also close the connection if we are done with it.
   # Just be sure any changes have been committed or they will be lost.
   conn.close()
   

Attributs importants et fonctions de Connection

1. isolation_level

   C'est un attribut utilisé pour obtenir ou définir le niveau d'isolation actuel. Aucun pour le mode autocommit ou l'un des modes DEFERRED , IMMEDIATE ou EXCLUSIVE .

2. cursor

   L'objet curseur est utilisé pour exécuter des commandes et des requêtes SQL.

3. commit()

   Valide la transaction en cours.

4. rollback()

   Annule les modifications apportées depuis l'appel précédent à commit()

5. close()

   Ferme la connexion à la base de données. Il n'appelle pas commit() automatiquement. Si close() est appelée sans d'abord appeler commit() (en supposant que vous n'êtes pas en mode autocommit), toutes les modifications apportées seront perdues.

6. total_changes

   Un attribut qui enregistre le nombre total de lignes modifiées, supprimées ou insérées depuis l'ouverture de la base de données.

7. execute , executemany et executescript

   Ces fonctions fonctionnent de la même manière que celles de l'objet curseur. Ceci est un raccourci car l'appel de ces fonctions via l'objet de connexion entraîne la création d'un objet curseur intermédiaire et appelle la méthode correspondante de l'objet curseur

8. row_factory

   Vous pouvez remplacer cet attribut par un appelable qui accepte le curseur et la ligne d'origine sous la forme d'un tuple et renverra la ligne de résultat réel.

   def dict_factory(cursor, row):
       d = {}
       for i, col in enumerate(cursor.description):
           d[col[0]] = row[i]
       return d
   
   conn = sqlite3.connect(":memory:")
   conn.row_factory = dict_factory
   

Fonctions importantes du Cursor

1. execute(sql[, parameters])

   Exécute une seule instruction SQL. L'instruction SQL peut être paramétrée (c.-à-d. Des espaces réservés au lieu de littéraux SQL). Le module sqlite3 prend en charge deux types d'espaces réservés: les points d'interrogation ? («Style qmark») et les espaces réservés nommés :name («style nommé»).

   import sqlite3
   conn = sqlite3.connect(":memory:")
   cur = conn.cursor()
   cur.execute("create table people (name, age)")
   
   who = "Sophia"
   age = 37
   # This is the qmark style:
   cur.execute("insert into people values (?, ?)",
               (who, age))
   
   # And this is the named style:
   cur.execute("select * from people where name=:who and age=:age",
               {"who": who, "age": age})  # the keys correspond to the placeholders in SQL
   
   print(cur.fetchone())
   

Attention: n'utilisez pas %s pour insérer des chaînes dans les commandes SQL, car cela peut rendre votre programme vulnérable à une attaque par injection SQL (voir Injection SQL ).

2. executemany(sql, seq_of_parameters)

   Exécute une commande SQL contre toutes les séquences de paramètres ou mappages trouvés dans la séquence sql. Le module sqlite3 permet également d'utiliser un itérateur générant des paramètres au lieu d'une séquence.

   L = [(1, 'abcd', 'dfj', 300),    # A list of tuples to be inserted into the database
        (2, 'cfgd', 'dyfj', 400),
        (3, 'sdd', 'dfjh', 300.50)]                           
   
   conn = sqlite3.connect("test1.db")
   conn.execute("create table if not exists book (id int, name text, author text, price real)")
   conn.executemany("insert into book values (?, ?, ?, ?)", L)
   
   for row in conn.execute("select * from book"):
       print(row)
   

   Vous pouvez également transmettre des objets d'itérateur en tant que paramètre à executemany, et la fonction effectuera une itération sur chaque tuple de valeurs renvoyé par l'itérateur. L'itérateur doit retourner un tuple de valeurs.

   import sqlite3
   
   class IterChars:
       def __init__(self):
           self.count = ord('a')
   
       def __iter__(self):
           return self
   
       def __next__(self):            # (use next(self) for Python 2)
           if self.count > ord('z'):
               raise StopIteration
           self.count += 1
           return (chr(self.count - 1),) 
   
   conn = sqlite3.connect("abc.db")
   cur = conn.cursor()
   cur.execute("create table characters(c)")
   
   theIter = IterChars()
   cur.executemany("insert into characters(c) values (?)", theIter)
   
   rows = cur.execute("select c from characters")
   for row in rows:
       print(row[0]),
   

3. executescript(sql_script)

   Ceci est une méthode de commodité non standard pour exécuter plusieurs instructions SQL à la fois. Il émet d'abord une instruction COMMIT , puis exécute le script SQL obtenu en tant que paramètre.

   sql_script peut être une instance de str ou bytes .

   import sqlite3
   conn = sqlite3.connect(":memory:")
   cur = conn.cursor()
   cur.executescript("""
        create table person(
            firstname,
            lastname,
            age
        );
   
        create table book(
            title,
            author,
            published
        );
   
        insert into book(title, author, published)
        values (
            'Dirk Gently''s Holistic Detective Agency',
            'Douglas Adams',
            1987
        );
        """)
   

   Le prochain ensemble de fonctions est utilisé conjointement avec les SELECT en SQL. Pour récupérer des données après avoir exécuté une SELECT , vous pouvez traiter le curseur comme un itérateur, appeler la méthode fetchone() du curseur pour extraire une seule ligne correspondante ou appeler fetchall() pour obtenir la liste des lignes correspondantes.

   Exemple de forme d'itérateur:

   import sqlite3
   stocks = [('2006-01-05', 'BUY', 'RHAT', 100, 35.14),
             ('2006-03-28', 'BUY', 'IBM', 1000, 45.0),
             ('2006-04-06', 'SELL', 'IBM', 500, 53.0),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)]
   conn = sqlite3.connect(":memory:")
   conn.execute("create table stocks (date text, buysell text, symb text, amount int, price real)")
   conn.executemany("insert into stocks values (?, ?, ?, ?, ?)", stocks)    
   cur = conn.cursor()
   
   for row in cur.execute('SELECT * FROM stocks ORDER BY price'):
       print(row)
   
   # Output:
   # ('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
   # ('2006-03-28', 'BUY', 'IBM', 1000, 45.0)
   # ('2006-04-06', 'SELL', 'IBM', 500, 53.0)
   # ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)
   

4. fetchone()

   Récupère la ligne suivante d'un ensemble de résultats de requête, en retournant une seule séquence ou Aucun lorsque aucune autre donnée n'est disponible.

   cur.execute('SELECT * FROM stocks ORDER BY price')
   i = cur.fetchone()
   while(i): 
       print(i)
       i = cur.fetchone()
   
   # Output:
   # ('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
   # ('2006-03-28', 'BUY', 'IBM', 1000, 45.0)
   # ('2006-04-06', 'SELL', 'IBM', 500, 53.0)
   # ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)
   

5. fetchmany(size=cursor.arraysize)

   Récupère le prochain ensemble de lignes d'un résultat de requête (spécifié par taille), renvoyant une liste. Si la taille est omise, fetchmany renvoie une seule ligne. Une liste vide est renvoyée lorsqu'aucune ligne supplémentaire n'est disponible.

   cur.execute('SELECT * FROM stocks ORDER BY price')
   print(cur.fetchmany(2))
   
   # Output:    
   # [('2006-01-05', 'BUY', 'RHAT', 100, 35.14), ('2006-03-28', 'BUY', 'IBM', 1000, 45.0)]
   

6. fetchall()

   Récupère toutes les lignes (restantes) d'un résultat de requête, renvoyant une liste.

   cur.execute('SELECT * FROM stocks ORDER BY price')
   print(cur.fetchall())
   
   # Output:
   # [('2006-01-05', 'BUY', 'RHAT', 100, 35.14), ('2006-03-28', 'BUY', 'IBM', 1000, 45.0), ('2006-04-06', 'SELL', 'IBM', 500, 53.0), ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)]
   

Types de données SQLite et Python

SQLite supporte nativement les types suivants: NULL, INTEGER, REAL, TEXT, BLOB.

Voici comment les types de données sont convertis lorsque vous passez de SQL à Python ou vice versa.

                None     <->     NULL
                int      <->     INTEGER/INT
                float    <->     REAL/FLOAT
                str      <->     TEXT/VARCHAR(n)
                bytes    <->     BLOB

Accès à la base de données PostgreSQL avec psycopg2

psycopg2 est l'adaptateur de base de données PostgreSQL le plus populaire, à la fois léger et efficace. C'est l'implémentation actuelle de l'adaptateur PostgreSQL.

Ses principales caractéristiques sont l'implémentation complète de la spécification Python DB API 2.0 et la sécurité des threads (plusieurs threads peuvent partager la même connexion)

Établir une connexion à la base de données et créer une table

import psycopg2

# Establish a connection to the database.
# Replace parameter values with database credentials.
conn = psycopg2.connect(database="testpython", 
                        user="postgres",
                        host="localhost",
                        password="abc123",
                        port="5432") 

# Create a cursor. The cursor allows you to execute database queries. 
cur = conn.cursor()

# Create a table. Initialise the table name, the column names and data type. 
cur.execute("""CREATE TABLE FRUITS (
                    id          INT ,
                    fruit_name  TEXT,
                    color       TEXT,
                    price       REAL
            )""")
conn.commit()
conn.close()

Insérer des données dans la table:

# After creating the table as shown above, insert values into it.
cur.execute("""INSERT INTO FRUITS (id, fruit_name, color, price)
               VALUES (1, 'Apples', 'green', 1.00)""")

cur.execute("""INSERT INTO FRUITS (id, fruit_name, color, price)
               VALUES (1, 'Bananas', 'yellow', 0.80)""")

Récupération des données de la table:

# Set up a query and execute it 
cur.execute("""SELECT id, fruit_name, color, price 
             FROM fruits""")

# Fetch the data 
rows = cur.fetchall()

# Do stuff with the data
for row in rows:
    print "ID = {} ".format(row[0])
    print "FRUIT NAME = {}".format(row[1])
    print("COLOR = {}".format(row[2]))
    print("PRICE = {}".format(row[3]))

Le résultat de ce qui précède serait:

ID = 1 
NAME = Apples
COLOR = green
PRICE = 1.0

ID = 2 
NAME = Bananas
COLOR = yellow
PRICE = 0.8

Et voilà, vous savez maintenant la moitié de tout ce que vous devez savoir sur psycopg2 ! :)

Base de données Oracle

Conditions préalables:

• Package cx_Oracle - Voir ici pour toutes les versions
• Client instantané Oracle - Pour Windows x64 , Linux x64

Installer:

• Installez le package cx_Oracle en tant que:

  sudo rpm -i <YOUR_PACKAGE_FILENAME>

• Extrayez le client instantané Oracle et définissez les variables d'environnement comme suit:

ORACLE_HOME=<PATH_TO_INSTANTCLIENT>
PATH=$ORACLE_HOME:$PATH
LD_LIBRARY_PATH=<PATH_TO_INSTANTCLIENT>:$LD_LIBRARY_PATH

Créer une connexion:

import cx_Oracle

class OraExec(object):
    _db_connection = None
    _db_cur = None

    def __init__(self):
        self._db_connection = 
            cx_Oracle.connect('<USERNAME>/<PASSWORD>@<HOSTNAME>:<PORT>/<SERVICE_NAME>')
        self._db_cur = self._db_connection.cursor()

Obtenir la version de la base de données:

ver = con.version.split(".")
print ver

Sortie d'échantillon: ['12', '1', '0', '2', '0']

Exécuter la requête: SELECT

_db_cur.execute("select * from employees order by emp_id")
for result in _db_cur:
    print result

La sortie sera en tuples Python:

(10, 'SYSADMIN', 'IT-INFRA', 7)

(23, 'HR ASSOCIATE', 'HUMAN RESOURCES', 6)

Exécuter la requête: INSERT

_db_cur.execute("insert into employees(emp_id, title, dept, grade) 
                values (31, 'MTS', 'ENGINEERING', 7)
_db_connection.commit()

Lorsque vous effectuez des opérations d'insertion / mise à jour / suppression dans une base de données Oracle, les modifications sont uniquement disponibles dans votre session jusqu'à ce que la commit soit émise. Lorsque les données mises à jour sont validées dans la base de données, elles sont alors disponibles pour les autres utilisateurs et sessions.

Execute query: INSERT en utilisant les variables Bind

Référence

Les variables de liaison vous permettent de réexécuter des instructions avec de nouvelles valeurs, sans avoir à ré-analyser l'analyse. Les variables de liaison améliorent la réutilisation du code et peuvent réduire le risque d'attaques par injection SQL.

rows = [ (1, "First" ),
     (2, "Second" ),
     (3, "Third" ) ]
_db_cur.bindarraysize = 3
_db_cur.setinputsizes(int, 10)
_db_cur.executemany("insert into mytab(id, data) values (:1, :2)", rows)
_db_connection.commit()

Fermer la connexion:

_db_connection.close()

La méthode close () ferme la connexion. Toute connexion non explicitement fermée sera automatiquement libérée à la fin du script.

Connexion

Créer une connexion

Selon PEP 249, la connexion à une base de données doit être établie à l'aide d'un constructeur connect() , qui renvoie un objet Connection . Les arguments de ce constructeur dépendent de la base de données. Reportez-vous aux rubriques spécifiques à la base de données pour connaître les arguments pertinents.

import MyDBAPI

con = MyDBAPI.connect(*database_dependent_args)

Cet objet de connexion a quatre méthodes:

1: fermer

con.close()

Ferme la connexion instantanément. Notez que la connexion est automatiquement fermée si la méthode Connection.__del___ est appelée. Toute transaction en attente sera implicitement annulée.

2: commettre

con.commit()

Valide toute transaction en attente dans la base de données.

3: rollback

con.rollback()

Revient au début de toute transaction en attente. En d'autres termes, cela annule toute transaction non engagée dans la base de données.

4: curseur

cur = con.cursor()

Renvoie un objet Cursor . Ceci est utilisé pour effectuer des transactions sur la base de données.

Utiliser sqlalchemy

Pour utiliser sqlalchemy pour la base de données:

from sqlalchemy import create_engine
from sqlalchemy.engine.url import URL


url = URL(drivername='mysql',
          username='user',
          password='passwd',
          host='host',
          database='db')

engine = create_engine(url)  # sqlalchemy engine

Maintenant, ce moteur peut être utilisé: par exemple avec des pandas pour récupérer des dataframes directement depuis mysql

import pandas as pd

con = engine.connect()
dataframe = pd.read_sql(sql=query, con=con)