Search code examples
pythonmonetdb

How to persist an inmemory monetdbe db to local disk


I am using an embedded monetdb database in python using Monetdbe. I can see how to create a new connection with the :memory: setting

But i cant see a way to persist the created database and tables for use later. Once an in memory session ends, all data is lost.
So i have two questions:

  1. Is there a way to persist an in memory db to local disk and
  2. Once an in memory db has been saved to local disk, is it possible to load the db to memory at a later point to allow fast data analytics. At the moment it looks like if i create a connection from a file location, then my queries are reading from local disk rather memory.

Solution

  • It is a little bit hidden away admittedly, but you can check out the following code snipet from the movies.py example in the monetdbe-examples repository:

    import monetdbe
    
    database = '/tmp/movies.mdbe'    
    
    with monetdbe.connect(database) as conn:
        conn.set_autocommit(True)
        conn.execute(
            """CREATE TABLE Movies
            (id SERIAL, title TEXT NOT NULL, "year" INTEGER NOT NULL)""")
    

    So in this example the single argument to connect is just the desired path to your database directory. This is how you can (re)start a database that stores its data in a persistent way on a file system.

    Notice that I have intentionally removed the python lines from the example in the actual repo that start with the comment # Removes the database if it already exists. Just to make the example in the answer persistent.

    I haven't run the code but I expect that if you run this code twice consecutively the second run wil return a database error on the execute statement as the movies table should already be there.

    And just to be sure, don't use the /tmp directory if you want your data to persist between restarts of your computer.