python how to form list to comma separated string for with correct quote for sql where in statement

Question

The below logic is used to convert so that list of strings such that each string is within quotes and comma separated

>>> record_hash
['AAA', 'BBB', 'CCC']
>>> t="'"+"','".join(record_hash)+"'"
>>> t
"'AAA','BBB','CCC'"
>>>

This logic is passed to below logic to form the SQL statement to form the where column in condition

 def selectAllPackageType(record_hash, severity):
        t="'"+"','".join(record_hash[])+"'"
        joined_string = ', '.join(['"{}"'.format(value) for value in record_hash])
        sql = 'select package_type from slack_jira where severity = ? and  record_hash in (?)'
        print(sql)
        conn = getSQLConnection();
        package_type = []
        conn.set_trace_callback(print)
        if conn is not None:
            cursor = conn.cursor()
            cursor.execute(sql,(severity,t))
            records = cursor.fetchall()
            for row in records:
                package_type.append(row['package_type'])
            cursor.close()
            conn.close()
        else:
            print("Error! cannot perform selectByPackageType.")
        return package_type

The conn.set_trace_callback(print) prints this value which is causing to fail the fetch the record as it contains one single record

select package_type from slack_jira where severity = 'MEDIUM' and  record_hash in ('''AAAAA'',''CCCC''');

what is needed is to form the correct SQL statement to be created with proper escape for where column in (?)

select package_type from slack_jira where severity = 'MEDIUM' and  record_hash in ('AAAAA','CCCC');

The empty square brackets in t="'"+"','".join(record_hash[])+"'" should be giving you a SyntaxError. You also never use joined_string? Beyond that, it might help if you printed t and record_hash just to confirm that they contain what you think they should. — Paul M.
– Paul M., Commented Nov 6, 2020 at 13:44
hi @TinNguyen it prints this select package_type from slack_jira where severity = 'MEDIUM' and record_hash in ('AAA,BBB') — anish
– anish, Commented Nov 6, 2020 at 13:50
okay then there is no way around that. You need to use the ? placeholder. You cannot insert a dynamic amount of variables in there. The ? is sanitized and only accepts "one string value". --- as in your sql statement needs to have multiple ? and then you just unpack *record_hash — Tin Nguyen
– Tin Nguyen, Commented Nov 6, 2020 at 13:52
Try to change sql to have ending ... record_hash in ? i.e. replace (?) with ? plus change t to be t = tuple(record_hash). — Arty
– Arty, Commented Nov 6, 2020 at 13:56

anish · Accepted Answer · 2020-11-06 15:34:03Z

1

I figured this out, this is working

result_set = conn.execute("SELECT * FROM slack_jira WHERE severity='MEDIUM' and id IN (%s)" % ','.join('?'*len(record_hash)), record_hash)

>>> import sqlite3
>>> conn = sqlite3.connect('/tmp/test.db')
>>> conn.set_trace_callback(print)
>>> record_hash=['A','B']
>>> result_set = conn.execute("SELECT * FROM slack_jira WHERE severity='MEDIUM' and id IN (%s)" % ','.join('?'*len(record_hash)), record_hash)
SELECT * FROM slack_jira WHERE severity='MEDIUM' and id IN ('A','B')

answered Nov 6, 2020 at 15:34

anish

7,50014 gold badges91 silver badges155 bronze badges

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

1 Comment

Arty Over a year ago

Nice, simple solution!

Collectives™ on Stack Overflow

python how to form list to comma separated string for with correct quote for sql where in statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related