VMM/…: Provide parameters as tuple to cursor.execute(). v0.6.x
authorPascal Volk <neverseen@users.sourceforge.net>
Sat, 07 Aug 2010 20:01:19 +0000
branchv0.6.x
changeset 352 22d115376e4d
parent 351 4bba5fb90b78
child 353 2ae40cd0d213
VMM/…: Provide parameters as tuple to cursor.execute().
VirtualMailManager/account.py
VirtualMailManager/alias.py
VirtualMailManager/aliasdomain.py
VirtualMailManager/domain.py
VirtualMailManager/maillocation.py
VirtualMailManager/relocated.py
VirtualMailManager/transport.py
--- a/VirtualMailManager/account.py	Sat Aug 07 05:16:26 2010 +0000
+++ b/VirtualMailManager/account.py	Sat Aug 07 20:01:19 2010 +0000
@@ -73,7 +73,7 @@
         `False` - if the user could be found. """
         dbc = self._dbh.cursor()
         dbc.execute('SELECT uid, mid, tid FROM users WHERE gid = %s AND '
-                    'local_part = %s', self._domain.gid, self._addr.localpart)
+                    'local_part=%s', (self._domain.gid, self._addr.localpart))
         result = dbc.fetchone()
         dbc.close()
         if result:
@@ -146,9 +146,8 @@
         """Count all alias addresses where the destination address is the
         address of the Account."""
         dbc = self._dbh.cursor()
-        sql = "SELECT COUNT(destination) FROM alias WHERE destination = '%s'"\
-                % self._addr
-        dbc.execute(sql)
+        dbc.execute('SELECT COUNT(destination) FROM alias WHERE destination '
+                    '= %s', (self._addr,))
         a_count = dbc.fetchone()[0]
         dbc.close()
         return a_count
@@ -259,15 +258,15 @@
             sieve_col = 'managesieve'
         self._prepare(MailLocation(self._dbh, mbfmt=cfg_dget('mailbox.format'),
                                    directory=cfg_dget('mailbox.root')))
-        sql = "INSERT INTO users (local_part, passwd, uid, gid, mid, tid,\
- smtp, pop3, imap, %s) VALUES ('%s', '%s', %d, %d, %d, %d, %s, %s, %s, %s)" % (
-            sieve_col, self._addr.localpart, pwhash(self._passwd,
-                                                    user=self._addr),
-            self._uid, self._domain.gid, self._mail.mid, self._transport.tid,
-            cfg_dget('account.smtp'), cfg_dget('account.pop3'),
-            cfg_dget('account.imap'), cfg_dget('account.sieve'))
         dbc = self._dbh.cursor()
-        dbc.execute(sql)
+        dbc.execute('INSERT INTO users (local_part, passwd, uid, gid, mid, '
+                    'tid, smtp, pop3, imap, %s) VALUES' % (sieve_col,) + \
+                    '(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
+                    (self._addr.localpart,
+                     pwhash(self._passwd, user=self._addr), self._uid,
+                     self._domain.gid, self._mail.mid, self._transport.tid,
+                     cfg_dget('account.smtp'), cfg_dget('account.pop3'),
+                     cfg_dget('account.imap'), cfg_dget('account.sieve')))
         self._dbh.commit()
         dbc.close()
         self._new = False
@@ -291,15 +290,15 @@
         dbc = self._dbh.cursor()
         if field == 'password':
             dbc.execute('UPDATE users SET passwd = %s WHERE uid = %s',
-                        pwhash(value, user=self._addr), self._uid)
+                        (pwhash(value, user=self._addr), self._uid))
         elif field == 'transport':
             if value != self._transport.transport:
                 self._transport = Transport(self._dbh, transport=value)
                 dbc.execute('UPDATE users SET tid = %s WHERE uid = %s',
-                            self._transport.tid, self._uid)
+                            (self._transport.tid, self._uid))
         else:
             dbc.execute('UPDATE users SET name = %s WHERE uid = %s',
-                        value, self._uid)
+                        (value, self._uid))
         if dbc.rowcount > 0:
             self._dbh.commit()
         dbc.close()
@@ -351,7 +350,7 @@
         dbc.execute("SELECT address ||'@'|| domainname FROM alias, "
                     "domain_name WHERE destination = %s AND domain_name.gid = "
                     "alias.gid AND domain_name.is_primary ORDER BY address",
-                    str(self._addr))
+                    (str(self._addr),))
         addresses = dbc.fetchall()
         dbc.close()
         aliases = []
@@ -374,11 +373,11 @@
         self._chk_state()
         dbc = self._dbh.cursor()
         if force:
-            dbc.execute('DELETE FROM users WHERE uid = %s', self._uid)
+            dbc.execute('DELETE FROM users WHERE uid = %s', (self._uid),)
             # delete also all aliases where the destination address is the same
             # as for this account.
             dbc.execute("DELETE FROM alias WHERE destination = %s",
-                        str(self._addr))
+                        (str(self._addr),))
             self._dbh.commit()
         else:  # check first for aliases
             a_count = self._count_aliases()
@@ -388,7 +387,7 @@
                              u"destination address '%(address)s'.") %
                            {'count': a_count, 'address': self._addr},
                            ALIAS_PRESENT)
-            dbc.execute('DELETE FROM users WHERE uid = %s', self._uid)
+            dbc.execute('DELETE FROM users WHERE uid = %s', (self._uid,))
             self._dbh.commit()
         dbc.close()
         self._new = True
@@ -420,7 +419,7 @@
     dbc.execute("SELECT local_part||'@'|| domain_name.domainname AS address, "
                 "uid, users.gid FROM users LEFT JOIN domain_name ON "
                 "(domain_name.gid = users.gid AND is_primary) WHERE uid = %s",
-                uid)
+                (uid,))
     info = dbc.fetchone()
     dbc.close()
     if not info:
--- a/VirtualMailManager/alias.py	Sat Aug 07 05:16:26 2010 +0000
+++ b/VirtualMailManager/alias.py	Sat Aug 07 20:01:19 2010 +0000
@@ -41,7 +41,7 @@
         """Loads all known destination addresses into the _dests list."""
         dbc = self._dbh.cursor()
         dbc.execute('SELECT destination FROM alias WHERE gid = %s AND '
-                    'address = %s', self._gid, self._addr.localpart)
+                    'address = %s', (self._gid, self._addr.localpart))
         dests = dbc.fetchall()
         if dbc.rowcount > 0:
             self._dests.extend(EmailAddress(dest[0]) for dest in dests)
@@ -81,11 +81,11 @@
         dbc = self._dbh.cursor()
         if not destination:
             dbc.execute('DELETE FROM alias WHERE gid = %s AND address = %s',
-                        self._gid, self._addr.localpart)
+                        (self._gid, self._addr.localpart))
         else:
             dbc.execute('DELETE FROM alias WHERE gid = %s AND address = %s '
-                        'AND destination = %s', self._gid,
-                        self._addr.localpart, str(destination))
+                        'AND destination = %s',
+                        (self._gid, self._addr.localpart, str(destination)))
         if dbc.rowcount > 0:
             self._dbh.commit()
         dbc.close()
--- a/VirtualMailManager/aliasdomain.py	Sat Aug 07 05:16:26 2010 +0000
+++ b/VirtualMailManager/aliasdomain.py	Sat Aug 07 20:01:19 2010 +0000
@@ -42,7 +42,7 @@
         domain name is marked as primary."""
         dbc = self._dbh.cursor()
         dbc.execute('SELECT gid, is_primary FROM domain_name WHERE '
-                    'domainname = %s', self._name)
+                    'domainname = %s', (self._name,))
         result = dbc.fetchone()
         dbc.close()
         if result:
@@ -76,7 +76,7 @@
                         self._domain.name, NO_SUCH_DOMAIN)
         dbc = self._dbh.cursor()
         dbc.execute('INSERT INTO domain_name VALUES (%s, %s, FALSE)',
-                    self._name, self._domain.gid)
+                    (self._name, self._domain.gid))
         self._dbh.commit()
         dbc.close()
         self._gid = self._domain.gid
@@ -89,7 +89,7 @@
                         self._name, NO_SUCH_ALIASDOMAIN)
         dbc = self._dbh.cursor()
         dbc.execute('SELECT domainname FROM domain_name WHERE gid = %s AND '
-                    'is_primary', self._gid)
+                    'is_primary', (self._gid,))
         domain = dbc.fetchone()
         dbc.close()
         if domain:
@@ -118,8 +118,8 @@
                         ALIASDOMAIN_EXISTS)
         dbc = self._dbh.cursor()
         dbc.execute('UPDATE domain_name SET gid = %s WHERE gid = %s AND '
-                    'domainname = %s AND NOT is_primary', self._domain.gid,
-                    self._gid, self._name)
+                    'domainname = %s AND NOT is_primary', (self._domain.gid,
+                    self._gid, self._name))
         self._dbh.commit()
         dbc.close()
         self._gid = self._domain.gid
@@ -134,7 +134,7 @@
                         self._name, NO_SUCH_ALIASDOMAIN)
         dbc = self._dbh.cursor()
         dbc.execute('DELETE FROM domain_name WHERE domainname = %s AND NOT '
-                    'is_primary', self._name)
+                    'is_primary', (self._name,))
         if dbc.rowcount > 0:
             self._dbh.commit()
             self._gid = 0
--- a/VirtualMailManager/domain.py	Sat Aug 07 05:16:26 2010 +0000
+++ b/VirtualMailManager/domain.py	Sat Aug 07 20:01:19 2010 +0000
@@ -64,7 +64,7 @@
         dbc = self._dbh.cursor()
         dbc.execute('SELECT dd.gid, tid, domaindir, is_primary FROM '
                     'domain_data dd, domain_name dn WHERE domainname = %s AND '
-                    'dn.gid = dd.gid', self._name)
+                    'dn.gid = dd.gid', (self._name,))
         result = dbc.fetchone()
         dbc.close()
         if result:
@@ -160,10 +160,10 @@
                          DOMAIN_EXISTS)
         assert self._directory is not None and self._transport is not None
         dbc = self._dbh.cursor()
-        dbc.execute("INSERT INTO domain_data VALUES (%s, %s, %s)", self._gid,
-                    self._transport.tid, self._directory)
-        dbc.execute("INSERT INTO domain_name VALUES (%s, %s, %s)", self._name,
-                    self._gid, True)
+        dbc.execute("INSERT INTO domain_data VALUES (%s, %s, %s)", (self._gid,
+                    self._transport.tid, self._directory))
+        dbc.execute("INSERT INTO domain_name VALUES (%s, %s, %s)", (self._name,
+                    self._gid, True))
         self._dbh.commit()
         dbc.close()
         self._new = False
@@ -214,12 +214,12 @@
             return
         dbc = self._dbh.cursor()
         dbc.execute("UPDATE domain_data SET tid = %s WHERE gid = %s",
-                    transport.tid, self._gid)
+                    (transport.tid, self._gid))
         if dbc.rowcount > 0:
             self._dbh.commit()
         if force:
             dbc.execute("UPDATE users SET tid = %s WHERE gid = %s",
-                        transport.tid, self._gid)
+                        (transport.tid, self._gid))
             if dbc.rowcount > 0:
                 self._dbh.commit()
         dbc.close()
@@ -231,7 +231,7 @@
         dbc = self._dbh.cursor()
         dbc.execute('SELECT gid, domainname, transport, domaindir, '
                     'aliasdomains, accounts, aliases, relocated FROM '
-                    'vmm_domain_info WHERE gid = %s', self._gid)
+                    'vmm_domain_info WHERE gid = %s', (self._gid,))
         info = dbc.fetchone()
         dbc.close()
         keys = ('gid', 'domainname', 'transport', 'domaindir', 'aliasdomains',
@@ -243,7 +243,7 @@
         self._chk_state()
         dbc = self._dbh.cursor()
         dbc.execute('SELECT local_part from users where gid = %s ORDER BY '
-                    'local_part', self._gid)
+                    'local_part', (self._gid,))
         users = dbc.fetchall()
         dbc.close()
         accounts = []
@@ -258,7 +258,7 @@
         self._chk_state()
         dbc = self._dbh.cursor()
         dbc.execute('SELECT DISTINCT address FROM alias WHERE gid = %s ORDER '
-                    'BY address', self._gid)
+                    'BY address', (self._gid,))
         addresses = dbc.fetchall()
         dbc.close()
         aliases = []
@@ -273,7 +273,7 @@
         self._chk_state()
         dbc = self._dbh.cursor()
         dbc.execute('SELECT address FROM relocated WHERE gid = %s ORDER BY '
-                    'address', self._gid)
+                    'address', (self._gid,))
         addresses = dbc.fetchall()
         dbc.close()
         relocated = []
@@ -288,7 +288,7 @@
         self._chk_state()
         dbc = self._dbh.cursor()
         dbc.execute('SELECT domainname FROM domain_name WHERE gid = %s AND '
-                    'NOT is_primary ORDER BY domainname', self._gid)
+                    'NOT is_primary ORDER BY domainname', (self._gid,))
         anames = dbc.fetchall()
         dbc.close()
         aliasdomains = []
@@ -321,7 +321,8 @@
     """
     domainname = check_domainname(domainname)
     dbc = dbh.cursor()
-    dbc.execute('SELECT gid FROM domain_name WHERE domainname=%s', domainname)
+    dbc.execute('SELECT gid FROM domain_name WHERE domainname = %s',
+                (domainname,))
     gid = dbc.fetchone()
     dbc.close()
     if gid:
--- a/VirtualMailManager/maillocation.py	Sat Aug 07 05:16:26 2010 +0000
+++ b/VirtualMailManager/maillocation.py	Sat Aug 07 20:01:19 2010 +0000
@@ -130,8 +130,8 @@
         *directory* name. If it fails goto _save()."""
         dbc = self._dbh.cursor()
         dbc.execute("SELECT mid FROM maillocation WHERE fid = (SELECT fid "
-                    "FROM mailboxformat WHERE format = '%s') AND directory = "
-                    "'%s'" % (mbfmt, directory))
+                    "FROM mailboxformat WHERE format = %s) AND directory = %s",
+                    (mbfmt, directory))
         result = dbc.fetchone()
         dbc.close()
         if not result:
@@ -147,8 +147,8 @@
         dbc.execute("SELECT nextval('maillocation_id')")
         mid = dbc.fetchone()[0]
         dbc.execute("INSERT INTO maillocation (fid, mid, directory) VALUES ("
-                    "(SELECT fid FROM mailboxformat WHERE format = '%s'), %u, "
-                    "'%s')" % (mbfmt, mid, directory))
+                    "(SELECT fid FROM mailboxformat WHERE format = %s), %s, "
+                    "%s)",  (mbfmt, mid, directory))
         self._dbh.commit()
         dbc.close()
         self._mid = mid
--- a/VirtualMailManager/relocated.py	Sat Aug 07 05:16:26 2010 +0000
+++ b/VirtualMailManager/relocated.py	Sat Aug 07 20:01:19 2010 +0000
@@ -52,7 +52,7 @@
         """
         dbc = self._dbh.cursor()
         dbc.execute('SELECT destination FROM relocated WHERE gid = %s AND '
-                    'address = %s', self._gid, self._addr.localpart)
+                    'address = %s', (self._gid, self._addr.localpart))
         destination = dbc.fetchone()
         dbc.close()
         if destination:
@@ -83,11 +83,11 @@
         dbc = self._dbh.cursor()
         if not update:
             dbc.execute('INSERT INTO relocated VALUES (%s, %s, %s)',
-                        self._gid, self._addr.localpart, str(self._dest))
+                        (self._gid, self._addr.localpart, str(self._dest)))
         else:
             dbc.execute('UPDATE relocated SET destination = %s WHERE gid = %s '
-                        'AND address = %s', str(self._dest), self._gid,
-                        self._addr.localpart)
+                        'AND address = %s',
+                        (str(self._dest), self._gid, self._addr.localpart))
         self._dbh.commit()
         dbc.close()
 
@@ -105,7 +105,7 @@
                        self._addr, NO_SUCH_RELOCATED)
         dbc = self._dbh.cursor()
         dbc.execute('DELETE FROM relocated WHERE gid = %s AND address = %s',
-                    self._gid, self._addr.localpart)
+                    (self._gid, self._addr.localpart))
         if dbc.rowcount > 0:
             self._dbh.commit()
         dbc.close()
--- a/VirtualMailManager/transport.py	Sat Aug 07 05:16:26 2010 +0000
+++ b/VirtualMailManager/transport.py	Sat Aug 07 20:01:19 2010 +0000
@@ -69,7 +69,8 @@
     def _load_by_id(self):
         """load a transport by its id from the database"""
         dbc = self._dbh.cursor()
-        dbc.execute('SELECT transport FROM transport WHERE tid=%s', self._tid)
+        dbc.execute('SELECT transport FROM transport WHERE tid = %s',
+                    (self._tid,))
         result = dbc.fetchone()
         dbc.close()
         if result:
@@ -82,7 +83,7 @@
         """Load a transport by its transport name from the database."""
         dbc = self._dbh.cursor()
         dbc.execute('SELECT tid FROM transport WHERE transport = %s',
-                    self._transport)
+                    (self._transport,))
         result = dbc.fetchone()
         dbc.close()
         if result:
@@ -95,8 +96,8 @@
         dbc = self._dbh.cursor()
         dbc.execute("SELECT nextval('transport_id')")
         self._tid = dbc.fetchone()[0]
-        dbc.execute('INSERT INTO transport VALUES (%s, %s)', self._tid,
-                    self._transport)
+        dbc.execute('INSERT INTO transport VALUES (%s, %s)', (self._tid,
+                    self._transport))
         self._dbh.commit()
         dbc.close()