How to create comma seperated string out of Python list?

Question

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.

Join your URLs list into one CSV string (no space after comma !) then use WHERE !FIND_IN_SET(URL, $found_media). Remember - this forbids index usage. — Akina
– Akina, Commented Mar 16, 2021 at 7:40
That sounds about right, I am struggling with creating this CSV string from the python list. — merlin
– merlin, Commented Mar 16, 2021 at 7:40

Nick · Accepted Answer · 2021-03-16 07:56:34Z

2

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

answered Mar 16, 2021 at 7:56

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user9706 Over a year ago

This is the right answer irregardless of what op asked for :-)

Nick Over a year ago

@AllanWind yeah, I've just taken it to its logical conclusion which is to do what is asked in the first sentence rather than the title.

merlin Over a year ago

@AllanWind That looks promissing. I am trying to follow your advise on prepared queries. How would that look like if I need to pass more values to the statement? E.g.: UPDATE tbl SET STATUS = 'unused', DATE_EDITED = curdate() WHERE merchant_id = 2 AND SKU = '09633907' AND STATUS != 'unused' AND URL NOT IN ({params})

Nick Over a year ago

@merlin if you want to pass the merchant_id and SKU values as well, just replace the fixed values in the query with %s, and use tuple([2, '09633907'] + found_media) for the parameters (obviously you can replace those values with variables if required).

merlin Over a year ago

Thank you @AllanWind. That also helped me to understand how to use prepared queries.

score 1 · Accepted Answer · 2021-03-16 07:54:31Z

1

Define item. Use map to extract each url, quote, then convert that array to a comma separated string with join:

item = {
    'media_urls': [
            {'type': 'image', 'url': 'p1.jpg'}, 
            {'type': 'image', 'url': 'p2.jpg'}
    ]
}
found_media = ','.join(map(lambda u: "'%s'" % u['url'], item['media_urls']))
print(found_media)

which will print:

'p1.jpg','p2.jpg'

edited Mar 16, 2021 at 7:54

answered Mar 16, 2021 at 7:48

user9706

1 Comment

Nick Over a year ago

This is clean, and correctly applies quotes around the strings which would be required for a query.

Krishna Chaurasia · Accepted Answer · 2021-03-16 08:14:29Z

1

Yes, the object needs to be initialized for first case as you observed and there will be extra , as well in the end:

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

You can use list comprehension with join() as:

found_media = ','.join([f"'{elt['url']}'" for elt in item['media_urls']])

edited Mar 16, 2021 at 8:14

answered Mar 16, 2021 at 7:40

Krishna Chaurasia

9,6646 gold badges27 silver badges44 bronze badges

4 Comments

user9706 Over a year ago

I am pretty sure you need to quote the strings to be useful in the query.

Krishna Chaurasia Over a year ago

the answer has clearly mentioned: there will be extra , as well in the end, I just wanted to point the flaw in the OPs script instead of just providing a different code. Regarding the quoting part, I think you are right but it is not clear from the question and the code presented so I didn't add anything that is not explicitly mentioned in the question.

user9706 Over a year ago

Yes, I realized that after I wrote the initial comment, and subsequently revised comment.

Nick Over a year ago

@AllanWind you are absolutely right, the strings would need to be in quotes to be used in the query as is.

Collectives™ on Stack Overflow

How to create comma seperated string out of Python list?

3 Answers 3

5 Comments

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related