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

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!

CodePudding user response：

update your table to accept nulls, and while inserting the data in your code initialize the variables as null before getting the values from the user.

CodePudding user response：

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()