sqlite3 views, if not exists clause

[Josh]

Hi,
I'm running into a problem when trying to create a view in my sqlite
database in python. I think its a bug in the sqlite3 api that comes with
python 2.5.

This works as expected:
conn = sqlite3.connect(':memory:')
conn.execute("create table foo (a int,b int)")
conn.execute('create view bar as select * from foo')

This fails as expected:
conn = sqlite3.connect(':memory:')
conn.execute("create table foo (a int,b int)")
conn.execute('create view bar as select * from foo')
conn.execute('create view bar as select * from foo')
with exception "sqlite3.OperationalError: table bar already exists".
Weird that it fails with TABLE bar already exists, but this is the message
that sqlite actually returns so it is not python's fault.

THIS DOES NOT WORK, but it should!
conn = sqlite3.connect(':memory:')
conn.execute("create table foo (a int,b int)")
conn.execute('create view if not exists bar as select * from foo')
it fails with exception "sqlite3.OperationalError: near "not": syntax error"

Can anyone confirm, or pass this on to the appropriate person?
Thanks!
Josh

 

[Fredrik Lundh]

THIS DOES NOT WORK, but it should!

Python 2.5 was released on September 19th, 2006, and support for CREATE
VIEW IF NOT EXISTS was added to sqlite on October 9th, 2006:

http://www.sqlite.org/changes.html

</F>

 

[Roberto Bonvallet]

Python 2.5 was released on September 19th, 2006, and support for CREATE
VIEW IF NOT EXISTS was added to sqlite on October 9th, 2006:

So? "from __future__ import ..." should have supported this! <)

 

[Dennis Lee Bieber]

THIS DOES NOT WORK, but it should!
conn = sqlite3.connect(':memory:')
conn.execute("create table foo (a int,b int)")
conn.execute('create view if not exists bar as select * from foo')
it fails with exception "sqlite3.OperationalError: near "not": syntax error"

Can anyone confirm, or pass this on to the appropriate person?

The SQL reference in "The Definitive Guide to SQLite" does NOT show
"if not exists" as an optional clause for "create view". The template
shown is:

CREATE [ TEMP | TEMPORARY ] VIEW [database-name.]view-name AS
select-statement


{OTOH; trying to confirm using the command line utility did show me a
configuration problem -- somehow my "bin" directory (Windows) for
SQLite3 gave no "user" access to the actual files to run; had to go to
admin account and change security}
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[Dennis Lee Bieber]

engine. Rather, the syntax you're using is a relatively late addition
to the sqlite libs -- at least it fails on my 3.2.1 version of the
sqlite3 commmand-line tool, but succeeds on the latest download (3.3.8).

Failed on my 3.3.6 install too... (another package to update tonight
<sigh>)
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/