Why my SQLite request gives good result in SQLite client but it doesn't give the correct result when executing with Python?

Question

Environment:

Python 3.7

SQLITE3

PROBLEM:

I am trying to build an SQL request where I need to get a list of IDs that are not in a "blacklist" of IDs.

When I execute by hand the SQL request in my SQLITE3 client it gives the good result:

Request:

SELECT MAX(actions.id) as id, actions.id_contact, contacts.username,actions.id_message, actions.date_created FROM actions INNER JOIN contacts ON actions.id_contact=contacts.id WHERE actions.id_task_user=4807 AND actions.id_social_account='Brigitte Vandecastle' AND actions.replied_invitation=1 AND actions.id_contact AND actions.id_contact NOT IN (2948, 2951, 2985, 2991, 2993, 3009) GROUP BY actions.id_contact

Result :

3132    2950    Benjamin FRANKLIN message_txt_invitation_1A   2022-03-01 10:41:54
3140    2994    Adil DUPONT message_txt_invitation_1A   2022-03-01 22:00:27
3152    3006    Thiện TOM   message_txt_invitation_1A   2022-03-01 22:12:23

This is the correct result and satisfies me. But when I execute it in my python code, it gives the wrong result (one id which is in my black list).

CODE:

# I build my black list
list_actions_sent_message_tuple = sqlite_cursor.execute(
    # "SELECT actions.id_contact, contacts.username,actions.id_message, actions.date_created,actions.replied FROM actions INNER JOIN contacts ON actions.id_contact=contacts.id WHERE actions.id_task_user=? AND actions.id_social_account=? AND (actions.replied<>? OR actions.replied IS ?) AND type_action LIKE ? GROUP BY actions.id_contact ORDER BY actions.date_created DESC",
    "SELECT actions.id_contact FROM actions INNER JOIN contacts ON actions.id_contact=contacts.id WHERE actions.id_task_user=? AND actions.id_social_account=? AND actions.id_message NOT LIKE ? GROUP BY actions.id_contact",
    (int(p_taskuser_id), p_username, '%invitation%')).fetchall()
print(f"list_actions_sent_message_tuple:{list_actions_sent_message_tuple}")
# Convert this list of tuples in a simple list
list_actions_sent_message_list=[]
for element in list_actions_sent_message_tuple:
    list_actions_sent_message_list.append(element[0])
#list_actions_sent_message_list_str
list_actions_sent_message_list_str=str(list_actions_sent_message_list).replace('[','').replace(']','')
print(f"list_actions_sent_message_list_str:{list_actions_sent_message_list_str}")
# list_actions_sent_message_list_str is my black list


try:
    list_actions_did_reply_invitation_but_no_message_sent = sqlite_cursor.execute(
        "SELECT MAX(actions.id) as id, actions.id_contact, contacts.username,actions.id_message, actions.date_created FROM actions INNER JOIN contacts ON actions.id_contact=contacts.id WHERE actions.id_task_user=? AND actions.id_social_account=? AND actions.replied_invitation=? AND actions.id_contact NOT IN (?) GROUP BY actions.id_contact",
        (int(p_taskuser_id), p_username, 1, list_actions_sent_message_list_str)).fetchall()
except Exception as ex:
    print(f"It didn't work with list_actions_sent_message_list_str : {ex}")


print(f"list_actions_did_reply_invitation_but_no_message_sent : {list_actions_did_reply_invitation_but_no_message_sent}")

OUTPUT:

list_actions_sent_message_tuple:[(2948,), (2951,), (2985,), (2991,), (2993,), (3009,)]
list_actions_sent_message_list_str:2948, 2951, 2985, 2991, 2993, 3009
list_actions_did_reply_invitation_but_no_message_sent : [(3129, 2948, 'Marie-Laure TROADEC', 'message_txt_invitation_1A', '2022-03-01 10:10:02'), (3132, 2950, 'Benjamin FRANKLIN ', 'message_txt_invitation_1A', '2022-03-01 10:41:54'), (3140, 2994, 'Adil DUPONT', 'message_txt_invitation_1A', '2022-03-01 22:00:27'), (3152, 3006, 'Thiện TOM', 'message_txt_invitation_1A', '2022-03-01 22:12:23')]

As you can see, the Python code gives me a list of rows with id_contact = 2948 which is in my black list. It doesn't suppose to be in the result as I asked for "NOT IN (2948, 2951, 2985, 2991, 2993, 3009)"?

Anyone can help please?

You're trying to abuse parameter substitution to make your `NOT IN` list. You're creating the string `'2948, 2951, 2985, 2991, 2993, 3009'` and then substituting that in as a single parameter. This means that your SQL is `NOT IN ('2948, 2951, 2985, 2991, 2993, 3009')` Pay close attention to the quotes `'`. That's a list with ***one*** string as its ***only*** value. Your previous question was closed and now links to an answer that shows how this ***should*** be done. Essentially, dynamically build the correct number of `?` in your query; `NOT IN (?, ?, ..., ?)` as opposed to `NOT IN (?)` — MatBailie, Mar 02 '22 at 12:29
Thank you som uch for your help. But this solution is super complicate for me. 1rst, I don't have only one ARGUMENT in my WHERE condition, but several. 2nd, I am used to work with =? or LIKE ? in the building of my SQLITE3 request. This %$ is completely new. Is there any other examples with several ARGUMENTS in the SQLITE REQUEST? — Gauthier Buttez, Mar 02 '22 at 13:41
First, you need as many `?` as are in your list. `NOT IN(?)` won't work if your list is three items. You'll need 3 `?`... `NOT IN (?, ? ?)` This code makes that list of question marks: `'SELECT * FROM fubar WHERE x = ? AND y <> ? AND z NOT IN (%s)' % ','.join('?' * len(list_actions_sent_message_list))` — MatBailie, Mar 02 '22 at 15:04
Second you need to expand your list to individual items to include in your tuple. That's `(int(p_taskuser_id), p_username, 1, *list_actions_sent_message_list)` *(The `*` does the expansion.)* — MatBailie, Mar 02 '22 at 15:06

Why my SQLite request gives good result in SQLite client but it doesn't give the correct result when executing with Python?

0 Answers0