Bug in the DELETE statement in sqlite3 module
Respected Developer(s), while writing a database module for one of my applications in python I encountered something interesting. I had a username and password field in my table and only one entry which was "Admin" and "password". While debugging I purposefully deleted that record. Then I ran the same statement again. To my surprise, it got execute. Then I ran the statement to delete the user "admin" (lowercase 'a') which does not exist in the table. Surprisingly again is got executed even though the table was empty. What I expected was an error popping up. But nothing happened. I hope this error gets fixed soon. The code snippet is given below. self.cursor.execute(''' DELETE FROM Users WHERE username = ?''',(self.username,))
This is not a bug, this is correct behavior of any sql database. 2016-06-15 8:40 GMT+02:00 ninostephen mathew <ninosm12@gmail.com>:
Respected Developer(s), while writing a database module for one of my applications in python I encountered something interesting. I had a username and password field in my table and only one entry which was "Admin" and "password". While debugging I purposefully deleted that record. Then I ran the same statement again. To my surprise, it got execute. Then I ran the statement to delete the user "admin" (lowercase 'a') which does not exist in the table. Surprisingly again is got executed even though the table was empty. What I expected was an error popping up. But nothing happened. I hope this error gets fixed soon. The code snippet is given below.
self.cursor.execute(''' DELETE FROM Users WHERE username = ?''',(self.username,))
_______________________________________________ Python-Dev mailing list Python-Dev@python.org https://mail.python.org/mailman/listinfo/python-dev Unsubscribe: https://mail.python.org/mailman/options/python-dev/duda.piotr%40gmail.com
-- 闇に隠れた黒い力 弱い心を操る
On 15 June 2016 at 07:40, ninostephen mathew <ninosm12@gmail.com> wrote:
Respected Developer(s), while writing a database module for one of my applications in python I encountered something interesting. I had a username and password field in my table and only one entry which was "Admin" and "password". While debugging I purposefully deleted that record. Then I ran the same statement again. To my surprise, it got execute. Then I ran the statement to delete the user "admin" (lowercase 'a') which does not exist in the table. Surprisingly again is got executed even though the table was empty. What I expected was an error popping up. But nothing happened. I hope this error gets fixed soon. The code snippet is given below.
self.cursor.execute(''' DELETE FROM Users WHERE username = ?''',(self.username,))
First of all, this list is for the discussions about the development of Python itself, not for developing applications with Python. You should probably be posting to python-list instead. Having said that, this is how SQL works - a DELETE statement selects all records matching the WHERE clause and deletes them. If the WHERE clause doesn't match anything, nothing gets deleted. So your code is working exactly as I would expect. Paul
A point of order: it's not necessary to post three separate "this is the wrong list" replies. In fact the optimal number is probably close to zero -- I understand we all want to be helpful, and we don't want to send duplicate replies, but someone who posts an inappropriate question is likely to try another venue when they receive no replies, and three replies to the list implies that some folks are a little too eager to appear helpful (while reading the list with considerable delay). When the OP pings the thread maybe one person, preferably someone who reads the list directly via email from the list server, could post a standard "wrong list" response. On Wed, Jun 15, 2016 at 8:29 AM, Paul Moore <p.f.moore@gmail.com> wrote:
On 15 June 2016 at 07:40, ninostephen mathew <ninosm12@gmail.com> wrote:
Respected Developer(s), while writing a database module for one of my applications in python I encountered something interesting. I had a username and password field in my table and only one entry which was "Admin" and "password". While debugging I purposefully deleted that record. Then I ran the same statement again. To my surprise, it got execute. Then I ran the statement to delete the user "admin" (lowercase 'a') which does not exist in the table. Surprisingly again is got executed even though the table was empty. What I expected was an error popping up. But nothing happened. I hope this error gets fixed soon. The code snippet is given below.
self.cursor.execute(''' DELETE FROM Users WHERE username = ?''',(self.username,))
First of all, this list is for the discussions about the development of Python itself, not for developing applications with Python. You should probably be posting to python-list instead.
Having said that, this is how SQL works - a DELETE statement selects all records matching the WHERE clause and deletes them. If the WHERE clause doesn't match anything, nothing gets deleted. So your code is working exactly as I would expect.
Paul _______________________________________________ Python-Dev mailing list Python-Dev@python.org https://mail.python.org/mailman/listinfo/python-dev Unsubscribe: https://mail.python.org/mailman/options/python-dev/guido%40python.org
-- --Guido van Rossum (python.org/~guido)
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 06/15/2016 12:33 PM, Guido van Rossum wrote:
A point of order: it's not necessary to post three separate "this is the wrong list" replies. In fact the optimal number is probably close to zero -- I understand we all want to be helpful, and we don't want to send duplicate replies, but someone who posts an inappropriate question is likely to try another venue when they receive no replies, and three replies to the list implies that some folks are a little too eager to appear helpful (while reading the list with considerable delay). When the OP pings the thread maybe one person, preferably someone who reads the list directly via email from the list server, could post a standard "wrong list" response.
In addition, please don't undermine the "this is the wrong list" message by responding substantively to the OP's query. Tres. - -- =================================================================== Tres Seaver +1 540-429-0999 tseaver@palladion.com Palladion Software "Excellence by Design" http://palladion.com -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBAgAGBQJXYYc/AAoJEPKpaDSJE9HYlSgP/1v+FpEvildmH4fEpZXG+j18 jCt3Q48ffSW22oPhx4lyfZv1Sh3EOsEuHHd3oU7jG9kUtTPyluQQYJiygfCBpSev CP8LonjJxxkFsVwK5SRGcp7JdjiFbLyqUXbtkFM6s2OE7mpXwtbn4suCRJx7MYaO CUkN2h0vAandftV4xu+lp/r7n0l8HLTTOsrUFuPZRbT4dVzKwRcM+ER1W4tCnkgZ bFRXM8YjrUcX/Um2blSi4yZT75TvHjyi44ujbQPsR3OHCPN8GAfAzIVSkbiECP2K xAqT2/h0E6VkGdEymELCMRHvhCI2wFrAoA6nWYCdyR2Ekg7VB/tnr6AGi+SNvP06 BETMf0BRxpd4sXOvS4+ydhBQQpydW4hiw61RHs8xFiy0W7pqp5Zh4ZHHcZBR2KRT TXfoxrwQIBIWKlyBdgv9d0maOWg3uq3I3MqO2vnGj/XRPsjs/BWCX9BYZqpnEATB MasQItCMPoOfmVxlS+cS7rIXXVFdwulm2s5GRZR9PwEuMS8Vmi9A5UyEpshlDYZM ZMPT3CScFOyczVgC3N+LyO7rYaJMlcNQD/HxxQDvpXoYinxQAFo4eVE2+490XN8j Od8n3UIo72+rFyyFJ8A7iBORYF9UD44VrFHQRHROTEvv7dV1OTYSVZcdqBb4Ik6S 8Wl+qMIEm8VcuFKI4b/T =4IaO -----END PGP SIGNATURE-----
On 2016-06-15, at 08:40 , ninostephen mathew <ninosm12@gmail.com> wrote:
Respected Developer(s), while writing a database module for one of my applications in python I encountered something interesting. I had a username and password field in my table and only one entry which was "Admin" and "password". While debugging I purposefully deleted that record. Then I ran the same statement again. To my surprise, it got execute. Then I ran the statement to delete the user "admin" (lowercase 'a') which does not exist in the table. Surprisingly again is got executed even though the table was empty. What I expected was an error popping up. But nothing happened. I hope this error gets fixed soon. The code snippet is given below.
self.cursor.execute(''' DELETE FROM Users WHERE username = ?''',(self.username,))
Despite Python bundling sqlite, the Python mailing list is not responsible for developing SQLite (only for the SQLite bindings themselves) so this is the wrong mailing list. That being said, the DELETE statement deletes whichever records in the table match the provided predicate. If no record matches the predicate, it will simply delete no record, that is not an error, it is the exact expected and documented behaviour for the statement in SQL in general and SQLite in particular. See https://www.sqlite.org/lang_delete.html for the documentation of the DELETE statement in SQLite. While you should feel free to report your expectations to the SQLite project or to the JTC1/SC32 technical committee (which is responsible for SQL itself) I fear that's what you will get told there, and that you are about 30 years too late to try influence such a core statement of the language. Not that it would have worked I'd think, I'm reasonably sure the behaviour of the DELETE statement is a natural consequence of SQL's set- theoretic foundations: DELETE applies to a set of records, regardless of the set's cardinality.
participants (6)
-
Guido van Rossum -
ninostephen mathew -
Paul Moore -
Piotr Duda -
Tres Seaver -
Xavier Morel