How can I insert arrays into columns on a database table for my pyramid app?

I am trying to create an sql (sqlite) database where users upload an stl file and the data (all the points, triangles, ect.) is stored in a database so that it is permanently available. Is it possible to do this with a database with only two columns (three with the key): name (title for the url), and data (the array data)?

The data array is in the format: [[[x1,y1,z1],....],[[v1,v2,v3],...]]. All points are given first and then the triangles are defined through ordering of the points given. Can this array be stored in the database, and if so, what data type would it be (integer, string, ect.)?

Upon reading into this issue more, It seems that pickling would be a good way to go: http://docs.python.org/2/library/pickle.html

I am having trouble figuring out how to implement this. Should I just add pickle(data)?

Edit: upon further review, it seems like pickling introduces some security holes that do not exist if using JSON. Is it possible to simply call jsondatastring=JSON.stringify(data) and then save that to the database? If so, what would be the appropriate column type?

Solution

If your intention is only to store the array in DB and work with it in your webapp code, SQLAlchemy's PickleType is a good choice. Pickling and unpickling will be done transparently for you:

from sqlalchemy.types import PickleType

class Foo(Base):
    __tablename__ = 'foo'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    array = Column(PickleType)

foo = Foo(name=u'bar', array=[1, 2, 3])
session.add(foo)
session.commit()

foo = session.query(Foo).filter_by(name=u'bar').one()
print foo.array