Adding a set to a database table, using format string – how to include null values

I have a set that depending on the situation can contain anywhere between 1 and 5 elements. I am saving this set to a database table using a format string. My table contains 5 tables, but when creating it, I did include that it was okay to have null values in the columns.

my set below can be:

small = set(1, 2, 3,4,5)

but it can also be:

small = set(1,2,3)

This is my insert statement within my database table.

cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
query2 = "INSERT INTO StreamingService (movie_id, service1, service2, service3, service4, service5) VALUES ((select id from movies), % s, % s, % s, % s, % s)"
cursor.execute(query2, small)
mysql.connection.commit()

I know that I need to find a way to make my table accept the set, even without 5 values in the set and include null values in the empty spaces.

Any help, advice would be great!

Answer

You need convert your set into a list and then add None to the list for each missing value. You could append None for each missing value or create a list of None and extend your “small” list.

small = set(1,2,3)

fixed = list(small)
# Make a list of None, one per missing value
extras = [None] * (5 - len(small))
# Add the None list to the list of values to make up the count.
fixed.extend(extras) 

cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
query2 = "INSERT INTO StreamingService (movie_id, service1, service2, service3, service4, service5) VALUES ((select id from movies), %s, %s, %s, %s, %s)"
cursor.execute(query2, fixed)
mysql.connection.commit()