Re: [pysqlite] PySqlite DELETE problem.....

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Peter Schmidt wrote:
> statement = "DELETE from 'User' WHERE 'Id' = 'di'"

Your where clause is nonsense!  Single quotes in SQL delimit a string
while double quotes delimit an identifier (eg column name).  Double
quotes are only needed around an identifier if it contains characters
such as spaces or punctuation.  Sometimes you can use either quote but
that is a historical accident which would break backwards compatibility
to fix.

So technically the above query isn't valid SQL.  SQLite overlooks 'User'
and treats it as a table name.  But the string 'Id' is never equal to
the string 'di' so no row will be deleted.  If you had said WHERE 'Id' =
'Id' then every row would have been deleted!

The query should be:

  DELETE from user where id = 'di'

If you want to guard against identifiers that may have spaces you can
also use square brackets:

  DELETE from [user names] where [system id] = 'di'

And of course you should be using bindings otherwise this will be about
your program http://xkcd.com/327/

  cursor.execute("DELETE from [user names] where [system id] = ?",
                  ('di',))

> con.commit

As Adrian pointed out, you also need to call the function :-)

Here are some SQLite tickets where you can see people being bitten by
the quoting rules:

http://www.sqlite.org/cvstrac/tktview?tn=3432
http://www.sqlite.org/cvstrac/tktview?tn=1890
http://www.sqlite.org/cvstrac/tktview?tn=3379
http://www.sqlite.org/cvstrac/tktview?tn=874
http://www.sqlite.org/cvstrac/tktview?tn=3006
http://www.sqlite.org/cvstrac/tktview?tn=3190

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAklad6UACgkQmOOfHg372QTQKQCeKtxT4GVALMxwa8gpO+uR+Y5T
gF8AoKnaZtH8zndDTmAfczVOukNy7H9z
=fsCB
-----END PGP SIGNATURE-----