How to create comma seperated string out of Python list?

I want to mark unused datasets in MySQL from a list in python. How can a list be created in python that will serve the MySQL query?

Here is my approach:

The following python list contains urls

item['media_urls'] = [
    {'type': 'image', 'url': 'p1.jpg'}, 
    {'type': 'image', 'url': 'p2.jpg'}
]

Create array of available urls. This code does not work as I believe the var needs to be initialised in the beginning and then the comma should not be after the last value:

for media_url in item['media_urls']:
    found_media += media_url['url'].', '

I also tried:

found_media = ','.join(media_url['url'])
# results in: TypeError: list indices must be integers or slices, not str

Update table and set all urls that are not in found_media to “disabled”:

# pseudo code, I just need the query
$sql = "
    UPDATE tbl
    SET status = 'disabled'
    WHERE URL NOT IN ($found_media)
"

Any help on this is greatly appreciated.

Answer

You should really use a prepared query with parameters for the URL values to protect against SQL injection. Use a list comprehension to find all the found_media values, then use join to generate a parameter list for the query and then finally execute the query:

item = {'media_urls': [
    {'type': 'image', 'url': 'p1.jpg'}, 
    {'type': 'image', 'url': 'p2.jpg'}
]}

found_media = [m['url'] for m in item['media_urls']]
params = ','.join(['%s'] * len(found_media))

sql = f'''
UPDATE tbl
SET status = 'disabled'
WHERE URL NOT IN ({params})
'''

cursor.execute(sql, tuple(found_media))