0

I'm trying to write a dynamic query that takes my dict and writes it into the database.

A dict can be of any size, with any number of keys, and can look look like this:

dict = {
    'name': 'foo',
    'date': '2008',
    'genre': ['bar', 'baz']
}

The problem is that the values in the dictionary can be a either a string or a list of strings. Using the function I already have I can generate queries of dynamic length, but only for a value that is a string, not a list of strings.

def insert_data(metadata):
    conn = sql.connect(utils.get_db_path())

    _insert_data(conn,
                 'albums',
                 name=metadata['album'],
                 date=metadata['date'],
                 tracktotal=metadata['tracktotal'],
                 disctotal=metadata['disctotal']
                 )

def _insert_data(conn, table_name, **metadata):
    c = conn.cursor()
    query = f'INSERT INTO {table}({", ".join(metadata.keys())}) VALUES({",".join(["?"] * len(metadata))})'
    c.execute(query, tuple(value for value in metadata.values())

An query generated by this code would look like this:

INSERT INTO albums(name, date, tracktotal, disctotal) VALUES(?,?,?,?)

However, if there values are a list of strings, I need to generate several queries, which is even more complicated when several values are a list, not only one (for example, both date and genre are a list, in this case I need 2^2 = 4 queries)

What would be the way to do this, or is there a different approach that doesn't require that many queries?

EDIT1: The table in the database for the aforementioned dict would look like this:

TABLE albums
id   name    date
1    foo     2008

TABLE genres
id   name 
1    bar
2    baz

TABLE albumgenres
id    album_id   genre_id
1     1          1
2     1          2

Writing into albums is easy because there is no duplicates there, but the code to call the function for genres would look like this:

 _insert_data(conn, 
             'genres', 
             name='['bar', 'baz']
             )

and not work properly anymore.

n0stalghia
  • 31
  • 1
  • 7
  • How would you represent that list in the database? Sqlite doesn't support array types for columns. – AKX Jun 17 '19 at 16:49
  • Edited my question to respond to this comment, forgot to mention this in the original post. – n0stalghia Jun 17 '19 at 16:52
  • Alright. You might be best off looking at a lightweight ORM such as Peewee to help you out with this - generating the queries by hand will not be worth it in my opinion (unless for learning purposes). – AKX Jun 17 '19 at 16:59
  • ORM makes for an even better learning experience since I've never touched it, I'll look into either Peewee or SQLAlchemy since I have decent experience with SQLAlchemy Core. Thanks – n0stalghia Jun 17 '19 at 17:04

1 Answers1

0

Upon further research I believe that this can be achieved by making a list of dicts based on the provided dict using a Cartesian product.

See Combine Python Dictionary Permutations into List of Dictionaries

n0stalghia
  • 31
  • 1
  • 7