Python format array list into string

Question

I'm looking to take an array list and attach it to a string.

Python 2.7.10, Windows 10

The list is loaded from a mySQL table and the output is this:

skuArray = [('000381001238',) ('000381001238',) ('000381001238',) ('FA200513652',) ('000614400967',)]

I'm wanting to take this list and attach it to a separate query

the problem:

query = "SELECT ItemLookupCode,Description, Quantity, Price, LastReceived "
query = query+"FROM Item "
query = query+"WHERE ItemLookupCode IN ("+skuArray+") " 
query = query+"ORDER BY LastReceived ASC;"

I get the error:

TypeError: cannot concatenate 'str' and 'tuple' objects

My guess here is that I need to format the string as:

'000381001238', '000381001238', '000381001238', 'FA200513652','000614400967'

Ultimately the string needs to read:

query = query+"WHERE ItemLookupCode IN ('000381001238', '000381001238', '000381001238', 'FA200513652','000614400967') "

I have tried the following:

skuArray = ''.join(skuArray.split('(', 1))
skuArray = ''.join(skuArray.split(')', 1))

Second Try:

skus = [sku[0] for sku in skuArray]
stubs = ','.join(["'?'"]*len(skuArray))


msconn = pymssql.connect(host=r'*', user=r'*', password=r'*', database=r'*')
cur = msconn.cursor()
query ='''
SELECT ItemLookupCode,Description, Quantity, Price, LastReceived
FROM Item 
WHERE ItemLookupCode IN { sku_params }
ORDER BY LastReceived ASC;'''.format(sku_params = stubs)
cur.execute(query, params=skus)
row = cur.fetchone()
print row[3]
cur.close()
msconn.close()

Thanks in advance for your help!

don't format variables into SQL-Statements. Use placeholders. — Daniel
– Daniel, Commented Jan 12, 2017 at 16:02
Yeah, I do realize the SQL injection aspect.. I'll give that shot. Thanks! — ahinkle
– ahinkle, Commented Jan 12, 2017 at 16:03
"and the output is this" Are you sure? That does not look like the usual Python representation for either a tuple or a list. Looks more like a tuple of one-elemented tuples, but not exactly... Please show the exact output, with all the brackets and commas, and possibly also tell us what type(skuArray) shown. — tobias_k
– tobias_k, Commented Jan 12, 2017 at 16:03
"""select ItemLookupCode,Description, Quantity, Price, LastReceived from item where ItemLookupCode in {} order by LastReceived ASC;""".format(tuple(x for x in skuArray)). — Abdou
– Abdou, Commented Jan 12, 2017 at 16:07

Kelvin · Accepted Answer · 2017-01-12 17:14:07Z

4

If you want to do the straight inline SQL you could use a list comprehension:

', '.join(["'{}'}.format(sku[0]) for sku in skuArray])

Note: You need to add commas between tuples (based on example)

That said, if you want to do some sql, I would encourage you to parameterize your request with ?

Here is an example of how you would do something like that:

skuArray = [('000381001238',), ('000381001238',), ('000381001238',), ('FA200513652',), ('000614400967',)]
skus = [sku[0] for sku in skuArray]
stubs = ','.join(["'?'"]*len(skuArray))

qry = '''
SELECT ItemLookupCode,Description, Quantity, Price, LastReceived 
FROM Item 
WHERE ItemLookupCode IN ({ sku_params })
ORDER BY LastReceived ASC;'''.format(sku_params = stubs)

#assuming pyodbc connection syntax may be off
conn.execute(qry, params=skus)

Why?

Non-parameterized queries are a bad idea because it leaves you vulnerable to sql injection and is easy to avoid.

edited Jan 12, 2017 at 17:14

answered Jan 12, 2017 at 16:05

Kelvin

1,3672 gold badges13 silver badges23 bronze badges

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

10 Comments

ahinkle Over a year ago

Thank you for your help.

ahinkle Over a year ago

Thanks again - However I get an error because there is no second comma: ('000381001238',), any ideas here?

ahinkle Over a year ago

Edit: I have updated my question with this code (cleaner)

Kelvin Over a year ago

Do you need to have it as a tuple or can you just straight value search? What error are you getting?

ahinkle Over a year ago

Traceback.. ", line 60, in <module> ORDER BY LastReceived ASC;'''.format(sku_params = stubs) KeyError: ' sku_params '

|

wildwilhelm · Accepted Answer · 2017-01-12 16:05:25Z

2

Assuming that skuArray is a list, like this:

>>> skuArray = [('000381001238',), ('000381001238',), ('000381001238',), ('FA200513652',), ('000614400967',)]

You can format your string like this:

>>> ', '.join(["'{}'".format(x[0]) for x in skuArray])
"'000381001238', '000381001238', '000381001238', 'FA200513652', '000614400967'"

edited Jan 12, 2017 at 16:05

answered Jan 12, 2017 at 16:04

wildwilhelm

5,0391 gold badge21 silver badges24 bronze badges

2 Comments

Nir Alfasi Over a year ago

You are making the assumption that skuArray is a list.

ahinkle Over a year ago

Yes, this is a list. Thank you.

Collectives™ on Stack Overflow

Python format array list into string

2 Answers 2

10 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related