Re: testing for table existence

Colin Barnette wrote:
> I have a function I use which returns a list of the table names via
> the sqlite_master table, hope this helps!
> 
>     def list_tables(self):
>         """ List all tables (Permanent and Temporary). """
> 
>         self.cur.execute("SELECT name FROM (SELECT * FROM
> sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE
> type='table' ORDER BY name")
>         tlist = self.cur.fetchall()
> 
>         return tlist


certainly did.  I will admit fetchall returns weird data (a list of 
tuples of singletons)  here is some real data

[(u'friends_white_list',), (u'source_reputation',),
(u'trap_display_limits',), (u'trap_messages',)]

anyway, some code...

class DB_frame(object):
     def __init__ (self, default_DB):
         """ initialize the database connection """
         self.db = sqlite.connect(default_DB)
         self.cursor = self.db.cursor()


     def list_tables(self):
         """ List all tables (Permanent and Temporary). """

         self.cursor.execute("SELECT name FROM (SELECT * FROM 
sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE 
type='table' ORDER BY name")
         tlist = self.cursor.fetchall()

         return tlist

     def ensure(self, table_dictionary):
         """ make sure the tables listed exists and if they don't, 
create them"""
         known_tables = self.list_tables()
         for required_table in table_dictionary.keys():
             print (required_table,), known_tables
             if not (required_table,) in known_tables:
                 # table not found, creating
                 create_this = common_create %(required_table, 
table_dictionary[required_table])
                 print create_this
                 self.cursor.execute(create_this)

     def commit (self):
         """ """
         self.db.commit()

     def close(self):
         self.db.close()

a table dictionary looks like...

application_tables = { 'trap_messages': trap_messages,
                        'source_reputation': source_reputation,
                        'trap_display_limits': trap_display_limits,
                        'friends_white_list': friends_white_list,
                        }

a table definition looks like...

trap_display_limits = '''
tpblue_ID           TEXT PRIMARY KEY,
green_limit         INTEGER,
red_limit           INTEGER,
red_timestamp       INTEGER,
green_timestamp     INTEGER
'''

and the commit wrapper looks like...

common_create = 'CREATE TABLE %s ( %s );'

usage looks like...

db = DB_frame('/tmp/example')
db.ensure(application_tables)
c = db.cursor


now I fully expect that some of you will be ROTFLYAO.  That's okay as 
long as you tell me what you found funny.  :-)  I suspect I could derive 
DB_frame from something but I don't know the package well enough to say 
what.

---eric



-- 
Speech-recognition in use.  It makes mistakes, I correct some.