python sqlite3 code works in global space but thows error when I place it in a function
Question: I'm trying to run the follwoing 'sqlite3_custom_type.py' example from the book
"Python Standard Library by Example". The following code works 'straight out
of the box':

    
    
    import os
    import sqlite3
    
    
    db_filename = 'todo.db'
    
    db_is_new = not os.path.exists(db_filename)
    
    conn = sqlite3.connect(db_filename)
    
    if db_is_new:
        print('need to create schema')
    else:
        print('database exists, assume schema does to')
    
    conn.close()
    
    #import sqlite3
    try:
        import cPickle as pickle
    except:
        import pickle
    
    db_filename = 'todo.db'
    
    def adapter_func(obj):
        """Convert from in-memory to storage representation.
        """
        print 'adapter_func(%s)\n' % obj
        return pickle.dumps(obj)
    
    def converter_func(data):
        """Convert from storage to in-memory representation.
        """
        print 'converter_func(%r)\n' % data
        return pickle.loads(data)
    
    
    class MyObj(object):
        def __init__(self, arg):
            self.arg = arg
        def __str__(self):
            return 'MyObj(%r)' % self.arg
    
    # Register the functions for manipulating the type.
    sqlite3.register_adapter(MyObj, adapter_func)
    sqlite3.register_converter("MyObj", converter_func)
    
    # Create some objects to save.  Use a list of tuples so
    # the sequence can be passed directly to executemany().
    to_save = [ (MyObj('this is a value to save'),),
                (MyObj(42),),
                ]
    
    with sqlite3.connect(db_filename,
                         detect_types=sqlite3.PARSE_DECLTYPES) as conn:
        # Create a table with column of type "MyObj"
        conn.execute("""
        create table if not exists obj (
            id    integer primary key autoincrement not null,
            data  MyObj
        )
        """)
        cursor = conn.cursor()
    
        # Insert the objects into the database
        cursor.executemany("insert into obj (data) values (?)", to_save)
    
        # Query the database for the objects just saved
        cursor.execute("select id, data from obj")
        for obj_id, obj in cursor.fetchall():
            print 'Retrieved', obj_id, obj, type(obj)
            print   
    

But if I put the all the code in a function such as

    
    
    def stuff():
        ~same code as above but indented
    if __name__=="__main__":
        stuff()
    

then I get an error code:

    
    
    cursor.executemany("insert into obj (data) values (?)", to_save)
    sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.
    

Why doesn't the code work when it is in a function and how can I make it work?


Answer: As per other answers, it's good style to place classes in module scope. The
real reason for failure in this particular case though, is because of the
`pickle.dumps(obj)` call which tries to pickle a non-module level class.

Try the following code in your `adapter_func`:

    
    
    def adapter_func(obj):
        """Convert from in-memory to storage representation.
        """
        try:
            return pickle.dumps(obj)
        except Exception, arg:
            print 'Failed to pickle object [%s]' % arg
    

You will see an error such as the following when `MyObj` is declared inside
`stuff`:

    
    
    Failed to pickle object [Can't pickle <class '__main__.MyObj'>: it's not found as __main__.MyObj]
    

It is a requirement of `pickle` that classes to be pickled are declared at the
module level as [described in the pickle
documentation](http://docs.python.org/2/library/pickle.html#what-can-be-
pickled-and-unpickled). The sqlite3 module appears to be squashing exceptions
raised in the adapter functions, rather than propagating them through
resulting in a silent failure.

You can declare and register your adapter and converter functions inside
`stuff`. Style issues aside, you could also declare your `MyObj` inside your
function and have it work, as long as you find some other way to
serialise/deserialise your object.

It's the attempt to pickle a class which isn't at the top level which is the
root of this problem.

