sqlite3 views, if not exists clause

Discussion in 'Python' started by Josh, Nov 14, 2006.

  1. Josh

    Josh Guest

    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
     
    Josh, Nov 14, 2006
    #1
    1. Advertising

  2. "Josh" wrote:

    > 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>
     
    Fredrik Lundh, Nov 14, 2006
    #2
    1. Advertising

  3. Fredrik Lundh wrote:
    > "Josh" wrote:
    >
    >> 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:


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

    --
    Roberto Bonvallet
     
    Roberto Bonvallet, Nov 14, 2006
    #3
  4. On Tue, 14 Nov 2006 08:32:43 -0500, "Josh" <> declaimed
    the following in comp.lang.python:


    > 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

    HTTP://wlfraed.home.netcom.com/
    (Bestiaria Support Staff: )
    HTTP://www.bestiaria.com/
     
    Dennis Lee Bieber, Nov 14, 2006
    #4
  5. On Tue, 14 Nov 2006 13:52:18 -0000, "Tim Golden"
    <> declaimed the following in
    comp.lang.python:


    > 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

    HTTP://wlfraed.home.netcom.com/
    (Bestiaria Support Staff: )
    HTTP://www.bestiaria.com/
     
    Dennis Lee Bieber, Nov 14, 2006
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Totan
    Replies:
    0
    Views:
    1,030
    Totan
    Apr 17, 2006
  2. Tim Golden
    Replies:
    1
    Views:
    721
  3. Ulf Meinhardt
    Replies:
    8
    Views:
    6,211
  4. Jeffrey 'jf' Lim
    Replies:
    5
    Views:
    507
    Jeffrey 'jf' Lim
    Apr 9, 2007
  5. SunSw0rd
    Replies:
    4
    Views:
    277
    SunSw0rd
    Jul 2, 2009
Loading...

Share This Page