What model should a SQLalchemy database column be to contain an array of data?

Question

So I am trying to set up a database, the rows of which will be modified frequently. Every hour, for instance, I want to add a number to a particular part of my database. So if self.checkmarks is entered into the database equal to 3, what is the best way to update this part of the database with an added number to make self.checkmarks now equal 3, 2? I tried establishing the column as db.Array but got an attribute error:

AttributeError: 'SQLAlchemy' object has no attribute 'Array'

I have found how to update a database, but I do not know the best way to update by adding to a list rather than replacing. My approach was as follows, but I don't think append will work because the column cannot be an array:

ven = data.query.filter_by(venid=ven['id']).first()
ven.totalcheckins = ven.totalcheckins.append(ven['stats']['checkinsCount'])
db.session.commit()

Many thanks in advance

Are you familiar with relationships on SQLAlchemy (or in relational databases in general)? — Pedro Romano
– Pedro Romano, Commented Nov 25, 2012 at 11:00
I read a bit about them in the docs but not really. Is this worth exploring to try and achieve this effect? — zch
– zch, Commented Nov 25, 2012 at 15:33
Yes. A one-to-many relationship is exactly what you want in this case. SQLAlchemy's documentation is excelent and I recommend you read that first. You'll see that the first case explained in the link I posted in my first comment is precisely what you want: you'll need an additional table for your checkmarks which will have a foreign key relationship to your parent table. When you define a relationship involving both tables, SQLAlchemy will map this automatically to a collection. — Pedro Romano
– Pedro Romano, Commented Nov 25, 2012 at 15:41
Thank you for the reply. Perfect! I will read up on that. Many thanks — zch
– zch, Commented Nov 25, 2012 at 16:05
if you do actually want to use "Array", it's available in the Postgresql dialect as sqlalchemy.dialects.postgresql.ARRAY. — zzzeek
– zzzeek, Commented Nov 25, 2012 at 19:31

Alexander Jung-Loddenkemper · Accepted Answer · 2012-11-27 13:36:36Z

7

If you really want to have a python list as a Column in SQLAlchemy you will want to have a look at the PickleType:

array = db.Column(db.PickleType(mutable=True))

Please note that you will have to use the mutable=True parameter to be able to edit the column. SQLAlchemy will detect changes automatically and they will be saved as soon as you commit them.

If you want the pickle to be human-readable you can combine it with json or other converters that suffice your purposes.

answered Nov 27, 2012 at 13:36

Alexander Jung-Loddenkemper

3252 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kolypto Over a year ago

With PostgreSQL, you can use the ARRAY column type

floer32 Over a year ago

Please see also the ScalarListType provided by SQLAlchemy-utils. It's pretty handy!

Collectives™ on Stack Overflow

What model should a SQLalchemy database column be to contain an array of data?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related