pysqlite - Checking the existance of a table

Discussion in 'Python' started by rh0dium, Jun 17, 2005.

  1. rh0dium

    rh0dium Guest

    Hi all,

    I am starting to play with pysqlite, and would like to know if there is
    a function to determine if a table exists or not.


    Thanks
     
    rh0dium, Jun 17, 2005
    #1
    1. Advertising

  2. Simply use the internal table SQLite_Master:

    select name from SQLite_Master

    will return all existing tables.

    Regards,
    Matthias
     
    Matthias Kluwe, Jun 17, 2005
    #2
    1. Advertising

  3. | I am starting to play with pysqlite,
    | and would like to know if there is a function
    | to determine if a table exists or not.

    rh0dium ....

    One way to get at a list of table names
    in an SQLite data base is to query
    the sqlite_master table ....


    import sys
    import sqlite

    this_db = sys.argv[ 1 ]

    list_sql = [ "select tbl_name" ,
    "from sqlite_master" ]

    str_sql = '\n'.join( list_sql )

    dbc = sqlite.connect( db = "%s" % this_db )

    curs = dbc.cursor()

    curs.execute( str_sql )

    list_tables = curs.fetchall()

    print '\n Table Names in SQLite DB .... %s \n' % ( this_db )

    for table_name in list_tables :

    print " %s " % ( table_name )

    print

    dbc.close()


    --
    Stanley C. Kitching
    Human Being
    Phoenix, Arizona

    ----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
    http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
    ----= East and West-Coast Server Farms - Total Privacy via Encryption =----
     
    Cousin Stanley, Jun 18, 2005
    #3
  4. rh0dium wrote:
    > Hi all,
    >
    > I am starting to play with pysqlite, and would like to know if there is
    > a function to determine if a table exists or not.


    You can try to access the table in a try-catch block, something like:

    cur.execute("select * from tablename where 1=2")

    and check if it fails.

    Or you can query the sqlite_master table (don't know any specification
    off-hand, but it contains the schema information).

    Instead of doing a select on sqlite_master, you can use "pragma
    table_info", which returns information for each column in the table,
    and, apparently, an empty list if the table does not exist:

    >>> cur.execute("pragma table_info(foo)")
    >>> print cur.fetchall()

    [(0, u'bar', u'integer', 0, None, 0)]

    >>> cur.execute("pragma table_info(foo_does_not_exist)")
    >>> print cur.fetchall()

    []

    HTH,

    -- Gerhard
     
    =?ISO-8859-1?Q?Gerhard_H=E4ring?=, Jun 18, 2005
    #4
  5. rh0dium

    Peter Hansen Guest

    Gerhard Häring wrote:
    > Or you can query the sqlite_master table (don't know any specification
    > off-hand, but it contains the schema information).


    Item #9 in the FAQ (http://www.sqlite.org/faq.html#q9) shows it as:

    CREATE TABLE sqlite_master (
    type TEXT,
    name TEXT,
    tbl_name TEXT,
    rootpage INTEGER,
    sql TEXT
    );


    -Peter
     
    Peter Hansen, Jun 18, 2005
    #5
  6. rh0dium

    Dave Cook Guest

    On 2005-06-17, rh0dium <> wrote:

    > I am starting to play with pysqlite, and would like to know if there is
    > a function to determine if a table exists or not.


    sqlite_master has already been mentioned, so I'll point out some useful
    pragmas (under "Pragmas to query the database schema"):

    http://sqlite.org/pragma.html

    Dave Cook
     
    Dave Cook, Jun 19, 2005
    #6
    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. Lambuz
    Replies:
    2
    Views:
    397
    Jim Michaels
    Mar 3, 2006
  2. F. GEIGER
    Replies:
    2
    Views:
    562
    F. GEIGER
    May 21, 2005
  3. Gerhard Häring
    Replies:
    3
    Views:
    394
    Gerhard Häring
    Mar 13, 2008
  4. Banibrata Dutta
    Replies:
    0
    Views:
    221
    Banibrata Dutta
    Apr 28, 2008
  5. Andy B
    Replies:
    1
    Views:
    3,999
    Munna
    May 27, 2008
Loading...

Share This Page