Reading character Array instead of String Array from flask SQLAlchemy

Question

I created a following table in postgresql.

class Test(db.Model):
    __tablename__ = 'Test'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    genres = db.Column(db.ARRAY(db.String(120)))

To save genres into the Test Table, I read values from the UI and store it in the DB through the following code,

genres = request.form.getlist('genres')
test = Test(name=name, genres=genres)
    db.session.add(test)
    db.session.commit()

and while reading it from the DB, I do,

test = Test.query.get(test_id)
data = test.serialize()
print(f'Data => {data}')

The problem I am running into is When I read genres from Test table it is returned as an array of characters,

'genres': ['{', 'A', 'l', 't', 'e', 'r', 'n', 'a', 't', 'i', 'v', 'e', ',', 'B', 'l', 'u', 'e', 's', ',', 'C', 'l', 'a', 's', 's', 'i', 'c', 'a', 'l', '}']

rather than the way they were created/stored & saved into the table, which is an array of Strings,

genres = ['Alternative', 'Blues', 'Classical']

Edit: Even if I write the result before Serialization happens I still see this issue. Here is resulting genres upon running the `test.query.get('id'),

genres: ['{', 'A', 'l', 't', 'e', 'r', 'n', 'a', 't', 'i', 'v', 'e', ',', 'B', 'l', 'u', 'e', 's', ',', 'C', 'l', 'a', 's', 's', 'i', 'c', 'a', 'l', '}']

What am I doing wrong?

Answer 1

I recently ran into this issue and unable to find the root cause. The strange thing is that the behavior is not consistent. One of the tables will convert to JSON correctly while some others do not.

My solution was to write a function that fixes the broken format.

def fix_json_array(obj, attr):
    arr = getattr(obj, attr)
    if isinstance(arr, list) and len(arr) > 1 and arr[0] == '{':
        arr = arr[1:-1]
        arr = ''.join(arr).split(",")
        setattr(obj,attr, arr)

Then I simply put this after I query the table.

record = myTable.query.get(row_id)
fix_json_array(record, "genres")