Is there a way to aggregate values in a column in sqlite in one-to-many relationship into array?

user_6693304 0
Viewed 267

Is there a way to aggregate values in a column in sqlite in one-to-many relationship into array? For example, I have 2 tables like this:

Artists:
ArtistId    name
1            AC/DC
2            Accept

Albums:
AlbumId ArtistId Title
1         1        For Those About To Rock We Salute You
2         1        Let There Be Rock
3         2        Balls to the Wall
4         2        Restless and Wild

When I just do a query with a join:

SELECT
   Name,
   Title
FROM
   artists
JOIN albums USING(ArtistId)
WHERE artists.ArtistId = 1;

I get: enter image description here

I found out that I can do group_concat:

SELECT
   Name,
   GROUP_CONCAT(Title)
FROM
   artists
JOIN albums USING(ArtistId)
WHERE artists.ArtistId = 1;

To concatenate all values together: enter image description here

But I still have to parse the coma-separated string with titles: For Those About To Rock We Salute You,Let There Be Rock in the code to get the array of titles for each artist.

I use Python and I'd prefer to get something like a tuple for each row: (name, titlesArray)

A much easier way in this case for me would be to use json.loads and json.dumps functions to save all the "many" array members into the same row in the same table, instead of using the recommended way for databases to save values in different tables and then use joins to retrieve them: the "many" values is an array on the object, and it's just much easier to save and get them using just 2 functions: json.loads and json.dumps, compared to manually saving the "many" values into a separate table, create binding to the "one" value, then use group_concat to concat them into a string, and then parse it more to actually get my array back.

Is it possible to get an array of values, or do I have to do group_concat and parse the string?

python-3.x sqlite
1 Answers

You might not be able to receive an array from sqlite straight away, but you can achieve the result with a very little edit on your query and a single line in python.

group_concat supports a custom delimiter that you can use later to split the entries.

Let's assume you have something like this:

from typing import Typle
import sqlite3

def connect(file: str = None) -> sqlite3.Connection:
    connection = None

    try:
        connection = sqlite3.connect(file)
    except sqlite3.Error:
        raise

    return connection


def select(connection: sqlite3.Connection) -> Tuple(str, str)):
    entry = None

    try:
        sql = """
        SELECT
            Name,
            GROUP_CONCAT(Title)
        FROM artists
        JOIN albums USING(ArtistId)
        WHERE artists.ArtistId = 1;
        """
        cursor.execute(sql, parameters)

        reply = cursor.fetchone()
        if reply is not None:
            entry = reply

    except sqlite3.Error:
        raise 
    finally:
        cursor.close()  

    return entry

that you can use to connect to the database and select from it like so:

connection = connect(r"/path/to/file.sqlite3")
if connection is not None:
    entry = select(connection)
    connection.close()

It is not important if your query is inside a function or not, the important concept is that you are using python to do this query, and you can add some code to manipulate the values.

As you can see here group_concat accepts a separator that you can use to arbitrarily separate values.

Your new select function could be something like:

def select(connection: sqlite3.Connection) -> Tuple(str, Tuple(str, ...)):
    entry = None
    separator = r"|"

    try:
        sql = f"""
        SELECT
            Name,
            GROUP_CONCAT(Title, {separator})
        FROM artists
        JOIN albums USING(ArtistId)
        WHERE artists.ArtistId = 1;
        """
        cursor.execute(sql, parameters)

        reply = cursor.fetchone()
        if reply is not None:
            reply[1] = reply[1].split(separator)
            entry = reply

    except sqlite3.Error:
        raise 
    finally:
        cursor.close()  

    return entry

Without changing how you use this function, you would now have a tuple with all the titles.

Another idea you'd like to consider is to do a more specific select query, like:

select albums.Title
from albums
where albums.ArtistId = 1;

In this case, you can have a list of titles using: cursor.fetchall(). Of course the band name should be asked separately in this case.

user_19999240
Related