Re: difference between statements

Eric S. Johansson wrote:

Could you post some code and say what version of Python/pysqlite you're 
using?

> does not work in pysqlite
> del_cmd  = "DELETE FROM external_internal WHERE (internal_ID='esj');"
> no action

How are you running this?

> del_cmd  = "DELETE FROM external_internal WHERE (internal_ID=esj);"
> pysqlite2.dbapi2.OperationalError: no such column: esj

That's expected - you didn't quote 'esj' so it's trying to match a 
column that doesn't exist.

> so, what is the difference between these three?  also ? param and :foo 
> substitutes also failed.

I don't use :foo but I do use ? params, and they do work.

 >>> import sqlite3
 >>> C=sqlite3.Connection("c:/zz")
 >>> c=C.cursor()
 >>> c.execute("create table t(id TEXT)")
<sqlite3.Cursor object at 0x00D8B050>
 >>>
 >>> for val in ("abc", "def", "esj"):
... 	c.execute("insert into t values(?)", (val,))
... 	
<sqlite3.Cursor object at 0x00D8B050>
<sqlite3.Cursor object at 0x00D8B050>
<sqlite3.Cursor object at 0x00D8B050>
 >>>
 >>> for val in ("abc", "def", "esj"):
... 	c.execute("insert into t values(?)", (val,))
... 	
<sqlite3.Cursor object at 0x00D8B050>
<sqlite3.Cursor object at 0x00D8B050>
<sqlite3.Cursor object at 0x00D8B050>
 >>>
 >>> c.execute("select * from t").fetchall()
[(u'abc',), (u'def',), (u'esj',), (u'abc',), (u'def',), (u'esj',)]
 >>>
 >>> c.execute("delete from t where id=?", ("esj",))
<sqlite3.Cursor object at 0x00D8B050>
 >>>
 >>> c.execute("select * from t").fetchall()
[(u'abc',), (u'def',), (u'abc',), (u'def',)]
 >>>
 >>> c.execute("delete from t where (id=?)", ("esj",))
<sqlite3.Cursor object at 0x00D8B050>
 >>>
 >>> c.execute("select * from t").fetchall()
[(u'abc',), (u'def',), (u'abc',), (u'def',)]
 >>>
 >>> c.execute("delete from t where (id=?)", ("def",))
<sqlite3.Cursor object at 0x00D8B050>
 >>>
 >>> c.execute("select * from t").fetchall()
[(u'abc',), (u'abc',)]
 >>>

Martin