2

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.

Daniel Segal
  • 87
  • 3
  • 9
  • Does this answer your question? [Storing an Array of Strings in a database](https://stackoverflow.com/questions/21584612/storing-an-array-of-strings-in-a-database) – ggorlen Apr 22 '20 at 16:33
  • [Also good](https://stackoverflow.com/questions/29562036/how-to-store-a-array-in-a-database). – ggorlen Apr 22 '20 at 16:34
  • https://stackoverflow.com/questions/7300230/using-list-as-a-data-type-in-a-column-sqlalchemy helped me best – darthn Apr 14 '23 at 18:34

2 Answers2

5

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.

jmkjaer
  • 1,019
  • 2
  • 12
  • 29
  • On both links I see the serialized option, which I would need to learn because I'm not familiar with. Does it have any disadvantages? – Daniel Segal Apr 22 '20 at 16:43
  • @DanielSegal Are you familiar with [database normalization](https://en.wikipedia.org/wiki/Database_normalization)? Atomicity is a requirement for [1NF](https://en.wikipedia.org/wiki/First_normal_form). But serialization is simply to convert data to a format that can be stored (e.g. a string). – jmkjaer Apr 22 '20 at 16:46
  • No, Im not familiar with it. my question is is this method different from doing str(list) and when I want to go back to list just write a fancy function which does it? – Daniel Segal Apr 22 '20 at 16:50
  • 2
    Both are methods of stringifying an object, but `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. – jmkjaer Apr 22 '20 at 16:57
  • Also, SQLite supports [adaptation of objects](https://docs.python.org/3.8/library/sqlite3.html#using-adapters-to-store-additional-python-types-in-sqlite-databases). – jmkjaer Apr 22 '20 at 16:58
  • 1
    SQLite also supports JSON, and SQLAlchemy supports SQLite JSON implementation: https://docs.sqlalchemy.org/en/13/dialects/sqlite.html#sqlalchemy.dialects.sqlite.JSON – Ilja Everilä Apr 22 '20 at 17:48
5

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.

Queens Coder
  • 331
  • 4
  • 9