'''
Created on 21/nov/2010
@author: placava
'''
from piergiuseppe82.guestbook.model.Dedica import Dedica
import MySQLdb
import logging
class DedicaDao(object):
'''
classdocs
'''
def __init__(self):
self.__host__="localhost"
self.__user__="root"
self.__passwd__="smlab82"
self.__db__="test_py"
def findById(self,id):
sql = """select id,nickname,email,messaggio,dataInserimento
from dedica where id=%d""" % (id)
conn = MySQLdb.connect(host=self.__host__, user=self.__user__, passwd=self.__passwd__, db=self.__db__)
cursor = conn.cursor()
row = None
try:
cursor.execute(sql)
row = cursor.fetchone()
cursor.close()
conn.commit()
except Exception as e:
conn.rollback()
logging.error("Unexpected error:"+ e)
logging.debug("rollback")
conn.close()
return self.__rowToDedica__(row)
def insert(self,dedica):
logging.debug("chiamato Inserte")
sql = """insert into dedica
(nickname, email, messaggio)
values('%s','%s','%s')""" % (dedica.getNickname(),dedica.getEmail(),dedica.getMessaggio())
conn = MySQLdb.connect(host=self.__host__, user=self.__user__, passwd=self.__passwd__, db=self.__db__)
cursor = conn.cursor()
logging.debug("aperto il cursore")
try:
cursor.execute(sql)
cursor.close()
logging.debug("eseguito close cursor")
conn.commit()
logging.debug("eseguto commit")
except Exception as e:
conn.rollback()
logging.error("Unexpected error:"+ e)
logging.debug("rollback")
conn.close()
logging.debug("connsione chiusa")
def update(self,dedica):
logging.debug("chiamato Update")
sql = """update dedica
set nickname='%s', email='%s', messaggio='%s' where id=%d
""" % (dedica.getNickname(),dedica.getEmail(),dedica.getMessaggio(),dedica.getId())
logging.debug(sql)
conn = MySQLdb.connect(host=self.__host__, user=self.__user__, passwd=self.__passwd__, db=self.__db__)
cursor = conn.cursor()
try:
cursor.execute(sql)
cursor.close()
conn.commit()
except Exception as e:
conn.rollback()
logging.error("Unexpected error:"+ e)
logging.debug("rollback")
conn.close()
def delete(self,id):
sql = """delete from dedica where id = %d""" % (id)
conn = MySQLdb.connect(host=self.__host__, user=self.__user__, passwd=self.__passwd__, db=self.__db__)
cursor = conn.cursor()
try:
cursor.execute(sql)
cursor.close()
conn.commit()
except Exception as e:
conn.rollback()
logging.error("Unexpected error:"+ e)
logging.debug("rollback")
conn.close()
def findAll(self):
logging.debug("Chiamato find all")
sql = """select id,nickname,email,messaggio,dataInserimento
from dedica order by dataInserimento asc"""
conn = MySQLdb.connect(host=self.__host__, user=self.__user__, passwd=self.__passwd__, db=self.__db__)
rows=None
cursor = conn.cursor()
try:
cursor.execute(sql)
rows = cursor.fetchall()
cursor.close()
conn.commit()
except Exception as e:
conn.rollback()
logging.error("Unexpected error:"+ e)
logging.debug("rollback")
conn.close()
return self.__rowsToDediche__(rows)
def __rowToDedica__(self,row):
logging.debug("Chiamato rowToDedica")
logging.debug(row)
if not(row):
return None
return Dedica(row[0],row[1],row[2],row[3],row[4])
def __rowsToDediche__(self,rows):
logging.debug("Chiamato rowsToDediche")
if not(rows):
return None
listDediche = []
for row in rows:
dedica = self.__rowToDedica__(row)
listDediche.append(dedica)
return listDediche