custom sql with sqlite causes backtrace

[msoulier]

My production code is using Python 2.3 and Django 0.96. Yes, I know.
Next release picks up Django 1.0.2 and Python 2.4. Yay.

In the meantime, I have a custom model manager with a method that
executes custom sql. Works fine with PostgreSQL, but when I'm
developing with SQLite I get a traceback.

Traceback (most recent call last):
File "<console>", line 1, in ?
File "/home/msoulier/work/mitel-msl-tug/root/etc/e-smith/web/django/
teleworker/clients/models.py", line 43, in disconnectAll
cursor.execute("""
File "/home/msoulier/work/bin/msl8/lib/python2.3/site-packages/
django/db/backends/util.py", line 12, in execute
return self.cursor.execute(sql, params)
File "/home/msoulier/work/bin/msl8/lib/python2.3/site-packages/
django/db/backends/sqlite3/base.py", line 93, in execute
return Database.Cursor.execute(self, query, params)
Warning: You can only execute one statement at a time.

This is the method in question.

def disconnectAll(self, instanceid, nonlocal=False):
instanceid = int(instanceid)
cursor = connection.cursor()
log.debug("ClientManager.disconnectAll: disconnecting non-
local clients")
instance = TugInstance.objects.get(id=instanceid)
local_tugid = instance.tugid
log.debug("local tugid is %s" % local_tugid)
cursor.execute("""
BEGIN;
UPDATE clients
SET connected = 'false'
WHERE connected = 'true'
AND tugid <> %s;
COMMIT;""", [local_tugid])

Am I doing something wrong here?

Thanks,
Mike

[msoulier]

On Apr 18, 1:42 pm, msoulier <msoul...@digitaltorque.ca> wrote:
>         cursor.execute("""
>             BEGIN;
>             UPDATE clients
>             SET connected = 'false'
>             WHERE connected = 'true'
>             AND tugid <> %s;
>             COMMIT;""", [local_tugid])

And, of course, just after posting this I found the issue.

I put a BEGIN; COMMIT; around the snippet because in postgres the
commands didn't seem to do anything. I foolishly didn't find out why
it worked when I used a sub-transaction. So now my question is why
this didn't seem to work without using one.

The quest for truth continues...

Mike

[Tim Chase]

> Warning: You can only execute one statement at a time.

...

> cursor.execute("""
> BEGIN;
> UPDATE clients
> SET connected = 'false'
> WHERE connected = 'true'
> AND tugid <> %s;
> COMMIT;""", [local_tugid])
>
> Am I doing something wrong here?

I suspect sqlite doesn't like to have multiple commands in the
same execution. You have 3 commands: BEGIN, UPDATE, and COMMIT.
Django should be handling the transactions for you, so you
don't need the BEGIN or COMMIT. Just issue

cursor.exectue("""

UPDATE clients
SET connected = 'false'
WHERE connected = 'true'

AND tugid <> %s""", [local_tugid])

-tim