python+sqlite3 in memory database

Denis Gomes denisg640 at gmail.com
Mon Aug 30 19:07:34 CEST 2010


Hi Everyone,

    I am using sqlite3 with python2.5 and the pysqlite wrapper. I am trying
to copy tables from one database (in memory) to another database (file)
using ATTACH. I looked on the internet and found a couple of sites that show
how to do this but the table schema is not copied.

def SaveToFile(self,filename):
        # Attach external db file - give it handle filename
        # Attaching automatically creates a database by default
        self.__curs.execute("ATTACH %s AS %s" % (filename,filename))
        table_names=self.__curs.execute("SELECT name FROM main.sqlite_master
WHERE type='table'").fetchall()
        for table_name, in table_names:
            #copy in mem db to persistent db
            self.__curs.execute("CREATE TABLE %s.%s AS SELECT * FROM
main.%s" % (filename,table_name,table_name))
        self.__curs.execute("DETACH %s" % filename)  # Detach external db"""


Is there a way to copy the schema from the sqlite_master table.  I know we
can used iter.dump from within python to dump to text based SQL but I would
prefer not to do this.  Eventually my goal is to dynamically load and unload
sections of a file based database (could be tables or rows) in and out of
memory for effeciency purposes.  Any help is appreciated. Thanks in advance.

Denis
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20100830/cf5a1a33/attachment.html>


More information about the Python-list mailing list