def send(self, message): conn = SQLTransaction() cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) cursor.execute("SELECT * FROM pim.users WHERE user_id = %s", [self.session["user_id"]]) user = cursor.fetchone() conn.close() smtpConn = smtplib.SMTP("localhost") msg = MIMEMultipart() msg.set_charset("UTF-8") msg["From"] = self.sender msg["To"] = self.admin msg["Subject"] = u"Návrh na zlepšení PIM" htmlPart = MIMEText(message.encode("utf8"), "html") msg.attach(htmlPart) smtpConn.sendmail("PIM", self.admin, msg.as_string()) smtpConn.quit() return {"status": "OK"}
def send(self, message): conn = SQLTransaction() cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) cursor.execute("SELECT * FROM pim.users WHERE user_id = %s", [self.session['user_id']]) user = cursor.fetchone() conn.close() smtpConn = smtplib.SMTP("localhost") msg = MIMEMultipart() msg.set_charset("UTF-8") msg['From'] = self.sender msg['To'] = self.admin msg['Subject'] = u"Návrh na zlepšení PIM" htmlPart = MIMEText(message.encode("utf8"), 'html') msg.attach(htmlPart) smtpConn.sendmail("PIM", self.admin, msg.as_string()) smtpConn.quit() return {"status": "OK"}
def __init__(self, userId, accountId): conn = SQLTransaction() cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) cursor.execute(""" SELECT * FROM pim.imap_accounts WHERE imap_account_id = %s AND user_id = %s""", (accountId, userId)) settings = cursor.fetchone() conn.close() if settings['ssl'] == True: self.imapHandle = imaplib.IMAP4_SSL(settings['host'], settings['port']) else: self.imapHandle = imaplib.IMAP4(settings['host'], settings['port']) if settings['login']: self.login(settings['login'], settings['password']) self.attachments = {}
def sendMail(self, accountId, subject, recipients, message): conn = SQLTransaction() cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) cursor.execute(""" SELECT * FROM pim.smtp_accounts WHERE smtp_account_id = %s AND user_id = %s""", (accountId, self.session['user_id'])) settings = cursor.fetchone() conn.close() smtpConn = smtplib.SMTP(settings['host']) if settings['login']: smtpConn.login(settings['login'], settings['password']) to = ", ".join(recipients) msg = MIMEMultipart() msg.set_charset("UTF-8") msg['From'] = settings['name'] msg['To'] = to msg['Subject'] = subject htmlPart = MIMEText(message.encode("utf8"), 'html') msg.attach(htmlPart) smtpConn.sendmail(settings['name'], to, msg.as_string()) smtpConn.quit() return { 'status': "OK" }
class SQLDelete(ColumnParser): table = None query = "DELETE FROM %(table)s %(where)s" sqlConn = None useTransaction = False def __init__(self, table, connection = None): self.table = table if connection: self.sqlConn = connection self.useTransaction = True else: self.sqlConn = SQLTransaction() #def setFilter(self, args, join = "AND"): #filters = [] #for item in args: #if item in self.searchAliases: #filters.append(self.searchAliases[item] % args[item]) #else: #filters.append("%s = '%s'" % (item, args[item])) #self.where = join.join(filters) def delete(self): qry = self.__parseQuery() cursor = self.sqlConn.cursor() cursor.execute(qry) if not self.useTransaction: self.sqlConn.commit() self.sqlConn.close() return True def __parseQuery(self): query = self.query % { 'table': self.table, 'where': "WHERE " + " OR ".join(self.filter) } return query def __del__(self): if self.useTransaction == False: self.sqlConn.close() def setFilter(self, query, join = "AND"): if not query or type(query) not in [dict, list]: return if type(query) == dict: query = [query] self.filter = [] join = " %s " % join cursor = self.sqlConn.cursor() for args in query: outer = [] args = self.decodeValue(args) for item in args: col = self.columns[self.columnsIndex[item]] if col.searchAlias: outer.append(cursor.mogrify(col.searchAlias, [args[item]])) else: if type(args[item]) in [str, unicode] and args[item][-1:] == "*": #USE ILIKE OPERATOR outer.append("%s ILIKE '%s%%'" % (item, args[item][0:-1])) else: outer.append("%s = '%s'" % (item, args[item])) self.filter.append(join.join(outer))
class SQLInsert(ColumnParser): #table = None query = "INSERT INTO %(table)s (%(columns)s) VALUES (%(values)s)" #sqlConn = None #useTransaction = False def __init__(self, table, connection = None): self.table = table self.addData = {} if connection: self.sqlConn = connection self.useTransaction = True else: self.sqlConn = SQLTransaction() self.useTransaction = False def insert(self): qry = self.__parseQuery() self.data.update(self.addData) cursor = self.sqlConn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) cursor.execute(qry, self.data) returns = [] for row in cursor.fetchall(): returns.append(row) if not self.useTransaction: self.sqlConn.commit() #self.sqlConn.close() return returns[0] def __parseQuery(self): data = [] keys = [] returning = "" if len(self.primaryKeys) > 0: returning = " RETURNING " + ",".join(self.primaryKeys) for key in self.data: if key not in self.columnsInsertable: continue data.append("%%(%s)s" % key) keys.append(key) for key in self.addData: data.append("%%(%s)s" % key) keys.append(key) #keys = set(keys) #data = set(data) query = self.query % { 'table': self.table, 'columns': ", ".join(keys), 'values': ", ".join(data) } print "QRY", query + returning return query + returning def __del__(self): if self.useTransaction == False: self.sqlConn.close()
class SQLUpdate(ColumnParser): table = None key = None query = "UPDATE %(table)s SET %(setter)s %(where)s" sqlConn = None useTransaction = False def __init__(self, table, connection = None): self.table = table if connection: self.sqlConn = connection self.useTransaction = True else: self.sqlConn = SQLTransaction() def __del__(self): if self.useTransaction == False: self.sqlConn.close() def setKey(self, key): self.key = key def update(self): qry = self.__parseQuery() cursor = self.sqlConn.cursor() cursor.execute(qry) if not self.useTransaction: self.sqlConn.commit() self.sqlConn.close() return True def __parseQuery(self): cursor = self.sqlConn.cursor() setterArray = [] for key in self.data: #if type(self.data[key]) in [str, unicode]: #self.data[key] = self.data[key].encode("utf8") setterArray.append(cursor.mogrify("%s = %s" % (key.encode("utf8"), "%s"), [self.data[key]])) setter = ", ".join(setterArray) where = "" if self.key: if type(self.key) == dict: keyArray = [] for i in self.key: keyArray.append(cursor.mogrify("%s = %s" % (i.encode("utf8"), "%s"), [self.key[i]])) key = " AND ".join(keyArray) #elif type(key) == list: #TODO!!! where = " WHERE %s" % key query = self.query % { 'table': self.table, 'setter': setter, 'where': where } print "PARSEED QRY", query return query