def giveAllLanguages():
#return ["anglais"]
conn = connDB()
cursor = conn.execute("SELECT name from LANGUAGES")
result = [row[0] for row in cursor]
conn.close()
return result
def getCardsWithAttribute(language, attributeName, attribute):
conn = connDB()
cursor = conn.execute("SELECT id from {} where {} = '{}'".format(
language.upper(), attributeName, attribute))
result = [row[0] for row in cursor]
conn.close()
return result
def removeLanguage(language):
conn = connDB()
conn.execute("DELETE from LANGUAGES where name = '{}';".format(
language.upper()))
conn.execute('drop table if exists {}'.format(language.upper()))
conn.commit()
conn.close()
def getAllCards(language):
result = []
conn = connDB()
cursor = conn.execute("SELECT * FROM {}".format(language.upper()))
for row in cursor:
result.append(flashcard.FlashCards(*row))
conn.close()
return result
def getCompleteCardsWithAttribute(language, attributeName, attribute):
result = []
conn = connDB()
cursor = conn.execute("SELECT * from {} where {} = '{}'".format(
language.upper(), attributeName, attribute))
for row in cursor:
result.append(flashcard.FlashCards(*row))
conn.close()
return result
def modifyCard(tableName, Id, trad, ex, theme, diff, level, image, sound,
nature):
conn = connDB()
cursor = conn.execute(
"UPDATE '{}' SET TRADUCTION = ?, EXEMPLE = ?, THEME = ? ,DIFFICULTE = ? , MAITRISE = ? , ILLUSTRATIONPATH = ? , SOUNDPATH = ? , NATURE = ? WHERE ID = ? "
.format(tableName.upper()),
(trad, ex, theme, diff, level, image, sound, nature, Id))
conn.commit()
conn.close()
def getCardById(language, name):
conn = connDB()
cursor = conn.execute("SELECT * from {} where id = {}".format(
language.upper(), name))
for row in cursor: #il ne devrait y avoir qu'une seule row dans cursor
return flashcard.FlashCards(
*row
) #mais je ne sais pas comment le manipuler autrement qu'en le parcourant
conn.close()
def getCardsToLearn(language, givenleveldown, givenlevelup):
result = []
conn = connDB()
cursor = conn.execute(
"SELECT * from {} where MAITRISE <= {} and MAITRISE >= {}".format(
language.upper(), givenlevelup, givenleveldown))
for row in cursor:
result.append(flashcard.FlashCards(*row))
conn.close()
return result
def extract_picture(p_id):
sql = "SELECT picture,type,file_name FROM PICTURES where p_id = :id;"
p_id = {'id': p_id}
conn = connDB()
pic = conn.execute(sql, p_id)
ablob, ext, afile = pic.fetchone()
filename = './PICTURES/' + afile + ext
with open(filename, 'wb') as output_file:
output_file.write(ablob)
return filename
def extract_audio(a_id):
sql = "SELECT audio,type,file_name FROM AUDIOS where a_id = :id;"
a_id = {'id': a_id}
conn = connDB()
aud = conn.execute(sql, a_id)
ablob, ext, afile = aud.fetchone()
filename = './AUDIOS/' + afile + ext
with open(filename, 'wb') as output_file:
output_file.write(ablob)
return filename
def insert_audio(audio_file):
with open(audio_file, 'rb') as input_file:
ablob = input_file.read()
base = os.path.basename(audio_file)
afile, ext = os.path.splitext(base)
sql = "INSERT INTO AUDIOS(audio, type, file_name) VALUES(?, ?, ?);"
conn = connDB()
conn.execute(sql, [sqlite3.Binary(ablob), ext, afile])
conn.commit()
conn.close()
def addCard(mot, trad, ex, theme, diff, level, image, sound, nature, langue):
conn = connDB()
nextId = getNextId(langue)
conn.execute(
"INSERT INTO {} (ID, MOT, TRADUCTION, EXEMPLE, THEME, DIFFICULTE, MAITRISE, ILLUSTRATIONPATH, SOUNDPATH, NATURE, LANGUE) \
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ".format(langue.upper()),
(nextId, mot, trad, ex, theme, diff, level, image, sound, nature,
langue.upper()))
conn.commit()
conn.close()
def existsLanguage(language):
conn = connDB()
cursor = conn.execute(
"select count(*) from LANGUAGES where name = '{}'".format(
language.upper()))
result = cursor.fetchone()[0]
conn.close()
if result:
return True
else:
return False
def getNextId(tableName):
conn = connDB()
a = [
x for x in conn.execute("SELECT max(ID) FROM {}".format(
tableName.upper()))
][0][0]
if (a):
result = 1 + a
else:
result = 1
conn.close()
return result
def existeSameCard(language, mot, traduction):
conn = connDB()
#sql = "select count(*) from {} where mot = '{}' and traduction = '{}';".format(language.upper(),mot,traduction)
cursor = conn.execute(
"select count(*) from '{}' where mot = ? and traduction = ? ".format(
language.upper()), (mot, traduction))
result = cursor.fetchone()[0]
conn.close()
if result:
return True #existe une carte meme
else:
return False #existe pas une carte meme
def addLanguage(language):
conn = connDB()
conn.execute("INSERT INTO LANGUAGES (NAME) \
VALUES ('{}')".format(language.upper()))
conn.execute('''CREATE TABLE {}
(ID INT PRIMARY KEY NOT NULL,
MOT TEXT NOT NULL,
TRADUCTION TEXT NOT NULL,
EXEMPLE TEXT,
THEME TEXT,
DIFFICULTE INT,
MAITRISE INT,
ILLUSTRATIONPATH TEXT,
SOUNDPATH TEXT,
NATURE TEXT,
LANGUE TEXT NOT NULL);'''.format(language.upper()))
conn.commit()
conn.close()
def removeCard(language, name):
conn = connDB()
conn.execute("DELETE from {} where id = {};".format(
language.upper(), name))
conn.commit()
conn.close()
def getALLtables():
conn = connDB()
cursor = conn.execute("SELECT name FROM sqlite_master WHERE type='table'")
result = [row[0] for row in cursor]
conn.close()
return result
def create_audiotable():
conn = connDB()
sql = "create table IF NOT EXISTS AUDIOS(a_id INTEGER PRIMARY KEY AUTOINCREMENT,audio BLOB,type TEXT,file_name TEXT);"
conn.execute(sql)
conn.close()
def create_picturetable():
conn = connDB()
sql = "create table IF NOT EXISTS PICTURES(p_id INTEGER PRIMARY KEY AUTOINCREMENT,picture BLOB,type TEXT,file_name TEXT);"
conn.execute(sql)
conn.close()