I'm currently building a project in which for every user I need to save a list of Strings (urls of articles he read).
I'm using python + flask + SQLalchemy with sqlite.
I understand that sqlite doesn't support arrays, so I was thinking about switching over to a different database instead of sqlite, one which supports arrays.
I would like to know what would you do? which database supports arrays or if there's better way solving it.
You can serialize it in some way. An option is to simply calling str with the array, and then handle it in some way when you need to convert it back.
Another option is to use the json module:
import json
lst = [1,2,3]
serialized = json.dumps(lst)
print(serialized) # '[1, 2, 3]'
print(type(serialized)) # <class 'str'>
deserialized = json.loads(serialized)
print(deserialized) # [1, 2, 3]
print(type(deserialized)) # <class 'list'>
But as ggorlen's link suggests, you should try to come up with a better option than storing the array directly.
str is valid Python, json.dumps is valid JSON and Python. But yes, you can definitely write a fancy function that converts back. I just think it's easier to use json.loads instead.
MySQL and SQLlite both have support for JSON columns. You can store an array in a JSON column. In terms of best practice and performance, putting arrays in a database is debatable of course.
ORM
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
list_of_items = Column(JSON, nullabled=False)
Adding an Array to your DB.
parent_one = Parent(list_of_items=['item1', 'item2'])
session.add(parent_one)
session.commit()
Now when you fetch the record of parent_one, you will see its 'list_of_items' column is an array in python.
This can also be done with the SQL alchemy core language.
