CODEWITHSUNDEEP
pythondatabasesqlalchemy
Janie
Janie

Reputation: 66

Is it possible to have a list or array in a database using SQLAlchemy?

I'm trying to build a system that allows users to make 'projects'. These projects have a fairly simple syntax, they just need an ID, a name, optionally a description and the participants.

Because I want users to be able to add or remove other users from the project without having to input the entire list of users again, I want to make use of a string or array or some such method instead of a string.

However, I'm stuck with trying to input it. I initially tried a regular list, but SQLalchemy didn't accept that. After a google search, it appears that's not possible? Unless I simply haven't come upon it.

I am now trying to use an Array instead. My current code looks like this: 

class DBProject(db.Model):
__tablename__ = 'project'
    project_id = db.Column(db.Integer, primary_key=True)
    project_name = db.Column(db.String(128))
    project_description = db.Column(db.String(255), nullable=True)
    project_participants = db.Column(db.ARRAY(db.Integer))

But this gives the error: in _compiler_dispatch raise exc.UnsupportedCompilationError(visitor, cls) sqlalchemy.exc.CompileError: (in table 'project', column 'project_participants'): Compiler can't render element of type

Before, I tried leaving (db.Integer) out or replacing it with just Integer (because I had seen this with other people with similar problems) like this: 

project_participants = db.Column(db.ARRAY(Integer))

Which gives the error that 'Integer' is not defined or, in case of leaving it out altogether, this error:

TypeError: init() missing 1 required positional argument: 'item_type'

I'm hoping to add the array to the database so that I can use it to append new users or delete users without having to make the system's user input all allowed users all over again when he just wants to add or delete one.

Upvotes: 3

Views: 6624

Answers (1)

Florian H
Florian H

Reputation: 3082

First i recommend you strongly to save your participants data in an additional table. You can add a m:n relation between your DBProject-Table and your Participants-Table. Anything else would be against any best practice use of databases. Saving your participants as an Array in your table makes it impossible or at least very uncomfortable to filter by participants in a SQL-query.

But if you have a good reason to ignore that recommendation you can use pickle to make SQLAlchemy transform your array into a string while writing into your database.

class DBProject(db.Model):
    __tablename__ = 'project'
    project_id = db.Column(db.Integer, primary_key=True)
    project_name = db.Column(db.String(128))
    project_description = db.Column(db.String(255), nullable=True)
    project_participants = db.Column(db.PickleType, nullable=True)

Using that construct you can basicalliy put any object (if not exceeding a database specific maximum size) into a database field.

Save data:

dbproject_object = DBProject()
dbproject_object.name = "a_name"
dbproject_object.participants = ["you","him","another one"]
session.add(dbproject_object)
session.commit()

Read Data:

participants_array = db.session.query(DBProject).filter(name == "a_name").one().participants 

Result:
participants_array : ["you","him","another one"]

Upvotes: 6

Related Questions