Manipuler les bases de données SQL dans Pepper

sql1

Pepper dispose de SQLite3 en natif installé sur le système. Il est donc possible d’interagir avec des bases de données de type SQL.

Python permet de contrôler sans problème les fichiers SQLite3. Voici un bon tutoriel en Français pour vous expliquer. Un autre tuto rapide en anglais pour avoir toutes les bases

Un bon outil pour créer des bases SQLite3 est un plug in firefox . Pour installer cet utilitaire SQLite Manager, un petit tutoriel est ici.  Il convient de créer une base de données SQLite3 avec l’utilitaire utilitaire, dans un premier temps

Editeur de base SQLite dans Firefox

Editeur de base SQLite dans Firefox

Création d’une table dans une base SQL Lite avec Python

La création d’une base de données SQL avec Python est assez simple. Voici le code de création.

class MyClass(GeneratedClass):
    def __init__(self):
        GeneratedClass.__init__(self)

    def onLoad(self):
        #put initialization code here
        pass

    def onUnload(self):
        #put clean-up code here
        pass

    def onInput_onStart(self, p):
        import sqlite3  # Chargement de la librarie SQLite 3

        nomtable=self.getParameter("nomtable")
        conn = sqlite3.connect(p) # or use :memory: to put it in RAM
        cursor = conn.cursor()

        # create a table
        cursor.execute("""CREATE TABLE """+nomtable+""" (title text, artist text, release_date text, publisher text, media_type text) """)

        self.onStopped() #activate the output of the box
        pass

    def onInput_onStop(self):
        self.onUnload() #it is recommended to reuse the clean-up as the box is stopped
        self.onStopped() #activate the output of the box

    def CheckTableExists(conn):
        cur = conn.cursor();
        sql = """SELECT count(*) FROM sqlite_master WHERE type='table' AND name='Stories'""";
        cur.execute(sql);
        data = cur.fetchone();
        if (data[0] == 0):
            return False;
        else:
            return True;
        pass

Insérer des données dans la base de données

class MyClass(GeneratedClass):
    def __init__(self):
        GeneratedClass.__init__(self)

    def onLoad(self):
        #put initialization code here
        pass

    def onUnload(self):
        #put clean-up code here
        pass

    def onInput_onStart(self,p):
        import sqlite3  # Chargement de la librarie SQLite 3

        nombase=self.getParameter("nombase")
        conn = sqlite3.connect(nombase) # or use :memory: to put it in RAM
        cursor = conn.cursor()
        # insert data

        cursor.execute(p)
        # save data to database
        conn.commit()

        self.onStopped(p) #activate the output of the box
        pass

    def onInput_onStop(self):
        self.onUnload() #it is recommended to reuse the clean-up as the box is stopped
        self.onStopped() #activate the output of the box

 

Modifier des données de la table QSL

import sqlite3
 
conn = sqlite3.connect("mydatabase.db")
cursor = conn.cursor()
 
sql = """
UPDATE albums 
SET artist = 'John Doe' 
WHERE artist = 'Andy Hunter'
"""
cursor.execute(sql)
conn.commit()

 

Effacer des données de la table

import sqlite3
 
conn = sqlite3.connect("mydatabase.db")
cursor = conn.cursor()
 
sql = """
DELETE FROM albums
WHERE artist = 'John Doe'
"""
cursor.execute(sql)
conn.commit()

 

Faire une requête SQL

import sqlite3

import threading
import time
import datetime


class SQLiteThread(threading.Thread):


    def __init__(self, dbfile, box):
        super(SQLiteThread, self).__init__()
        self.mDbfile = dbfile
        self.mUnloading = False
        self.mQuery = ""
        self.mIsRunning = False
        self.mSem1 = threading.Semaphore(0)
        self.mSem2 = threading.Semaphore(0)
        self.mBox = box

    def run(self):
        self.mBox.logger.info("=== Entering sqlite thread ===")

        self.con = sqlite3.connect(self.mDbfile)
        self.con.text_factory = str
        self.mIsRunning = True
        while not self.mUnloading:
            self.mSem1.acquire()
            self.mResult = []
            if len(self.mQuery) > 0:
                self.mResult = []
                r = self.con.execute(self.mQuery)
                self.mResult = r.fetchall()
            self.mSem2.release()

        self.mBox.logger.info(" === closing sqlite connection === ")
        self.con.close()

        self.mBox.logger.info(" === ending sqlite thread === ")


    def onExit(self):
        self.mUnloading = True
        self.mQuery = ""
        self.mSem1.release()

    def onExecuteQuery(self, query):
        self.mQuery = query
        self.mSem1.release()
        self.mSem2.acquire()
        return self.mResult


class MyClass(GeneratedClass):
    def __init__(self):
        GeneratedClass.__init__(self)

    def onLoad(self):
        global TKSQliteThreads

        self.sqliteGlobalLock = None

        if "TKSQliteThreads" in globals():
            self.sqliteGlobalLock = TKSQliteThreads[0]["lock"]
        else:
            self.sqliteGlobalLock = threading.Lock()
            TKSQliteThreads = [{"file":"global", "thread":None, "lock":self.sqliteGlobalLock, "count":0}]

        with self.sqliteGlobalLock:
            self.framemanager = ALProxy("ALFrameManager")

            self.dbfile = self.getParameter("sqlite db file")
            self.dbfilepath = self.framemanager.getBehaviorPath(self.behaviorId) + self.dbfile

            self.sqlThread = None
            for t in TKSQliteThreads:
                if t["file"] == self.dbfilepath:
                    self.sqlThread = t["thread"]
                    self.dbLock = t["lock"]
                    count = t["count"] + 1
                    t["count"] = count
                    self.logger.info("SQLite query  FILE:%s REF=%d" % (self.dbfile,count))
                    break

            if self.sqlThread == None:
                self.logger.info("SQLite query  FILE:%s REF=1" % self.dbfile)
                self.sqlThread = SQLiteThread(self.dbfilepath,self)
                self.dbLock = threading.Lock()

                TKSQliteThreads.append({"file":self.dbfilepath, "thread":self.sqlThread, "lock":self.dbLock, "count":1})
                self.sqlThread.start()

    def onUnload(self):
        if self.sqlThread:
            with self.sqliteGlobalLock:
                for t in TKSQliteThreads:
                    if t["file"] == self.dbfilepath:
                        t["count"] = t["count"] - 1
                        self.logger.info("SQLite query  FILE:%s REF=%d" % (self.dbfile,t["count"]))
                        if t["count"] <= 0:
                            TKSQliteThreads.remove(t)
                            self.sqlThread.onExit()
                        break

    def onInput_onStart(self, p):
        if self.sqlThread:
            with self.dbLock:
                return self.onResult(self.sqlThread.onExecuteQuery(p))

        else:
            return self.onResult([])

    def onInput_onStop(self):
        self.onUnload()
        self.onStopped()

 

Protection des bases de données avec mot de passe

Il est possible de limiter l’accès à la base de donnés SQLite par mot de passe

Pour protéger avec mot de passe, il est nécessaire, avant toute opération, de définir le mot de passe de la base de la facon suivante

SQLiteConnection conn = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
conn.SetPassword("password");
conn.open();

Par la suite, la connexion à la base de données protégée se fait avec la commande suivante

conn = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;Password=password;");
conn.Open();

Pour changer le mode de passe dans le futur, utiliser la commande

 conn.ChangePassword("new_password");

Pour faire un reset du mot de passe ou pour le retirer:

conn.ChangePassword(String.Empty);

 

Les codes sources des boxes et script exemples téléchargeables (pensez à retirer albums.sqlite de l’arborescence du behavior une fois que vous avez crée la table. Car à chaque UPLOAD et test des autres scripts (INSERT & SELECT) elle serait écrasée, effaçant alors les changements souhaités)

sql

Sources: Les bases de données en Python, Les Bases de données avec Python, Cours Bases de données Python