Re: testing for table existence

Looks good to me! Excellent function there.  I don't know if this is
the right place for this sort of thing, but I'm interested in starting
a project to create a SQLite database editor written in Python with
some GUI like wxPython or PyGTK+. Is there any interest in the
community for this sort of thing?

If there is, feel free to e-mail me, or respond here if that's okay.

On 2/26/07, Eric S. Johansson <[email protected]> wrote:
> 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.
> _______________________________________________
> pysqlite mailing list
> pysqlite-IAPFreCvJWPBWskQ1e/[email protected]
> http://lists.initd.org/mailman/listinfo/pysqlite
>