Trying to combine VALUES and SELECT-FROM in one SQLite query-CodePudding

Here's little piece of pythonic sqlite query i wrote as self-teaching newbie:

cursor.execute(f"""INSERT INTO {mover_allocator[mover_targeter]}
              (ID, TITLE, SEASONS_WATCHED, PERSONAL_RATING, GENRE)
              VALUES
              (NULL, (SELECT TITLE FROM {selected_table} WHERE TITLE = '{title_to_move}'), ?, ?,
                     (SELECT GENRE FROM {selected_table} WHERE TITLE = '{title_to_move}'))""",
              (seasons_watched, series_rating))
series_database.commit()

And it works. But i definitely dislike double SELECT-FROM in brackets, so i tried to rewrite it:

cursor.execute(f"""INSERT INTO {mover_allocator[mover_targeter]}
              (ID, TITLE, SEASONS_WATCHED, PERSONAL_RATING, GENRE)
              VALUES
              (NULL, TITLE, ?, ?, GENRE)
              SELECT TITLE, GENRE FROM {selected_table} WHERE TITLE = '{title_to_move}'""",
              (seasons_watched, series_rating))
series_database.commit()

And now it successfully doesn't. Feeling a little tired already of documentation intricacies, i make wonder: is such approach possible at all? I mean, to combine VALUES and SELECT-FROM in one query sequentially?

update: in my case {selected_table} has only two columns instead of five in {mover_allocator[mover_targeter]}, columns mismatch in other words.

CodePudding user response：

You can make this work by combining your VALUES into the SELECT, rather than the other way around:

cursor.execute(f"""INSERT INTO {mover_allocator[mover_targeter]}
              (ID, TITLE, SEASONS_WATCHED, PERSONAL_RATING, GENRE)
              SELECT NULL, TITLE, ?, ?, GENRE FROM {selected_table} WHERE TITLE = ?""",
              (seasons_watched, series_rating, title_to_move))

Note that title_to_move should be a parameter as well.