Environment:
Python 3.7
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.
Error:
near "?": syntax error
What I tried:
I simply tried to pass any type of values: tuple, string, and list without any success. You can see here below my sample code and the error output messages.
The error message is " near "?": syntax error". It could be any of the SQL parameters. So in order to be sure the issue is from this 'NOT IN' parameter in my SQL request, I execute the same request without "NOT IN" (called "list_actions_sent_message_did_reply_invitation_WITHOUT_NOT_IN"). In this way, I am sure the issue is from "NOT IN" parameter. The SQL request is a bit long, but as we are sure it works without "NOT IN", you can simply ignore the beginning of SQL request and focus on the last part of SQL request => The "NOT IN ..."
# Ok they replied invitation, but maybe we already sent the First message message_txt_1A or message_txt_1B???
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()
# 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_tuple
print(f"list_actions_sent_message_tuple:{list_actions_sent_message_tuple}")
# list_actions_sent_message_list_str
print(f"list_actions_sent_message_list:{list_actions_sent_message_list}")
#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_tuple
list_actions_sent_message_list_tuple=tuple(list_actions_sent_message_list)
print(f"list_actions_sent_message_list_tuple:{list_actions_sent_message_list_tuple}")
# WITHOUT NOT IN TO BE SURE ISSUE IS FROM NOT IN
try:
list_actions_sent_message_did_reply_invitation_WITHOUT_NOT_IN = 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=? GROUP BY actions.id_contact",
(int(p_taskuser_id), p_username, 1)).fetchall()
print(f"list_actions_sent_message_did_reply_invitation_WITHOUT_NOT_IN:{list_actions_sent_message_did_reply_invitation_WITHOUT_NOT_IN}")
except Exception as ex:
print(f"It didn't work with list_actions_sent_message_tuple : {ex}")
#list_actions_sent_message_tuple
try:
list_actions_sent_message_did_reply_invitation = 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_tuple)).fetchall()
except Exception as ex:
print(f"It didn't work with list_actions_sent_message_tuple : {ex}")
# list_actions_sent_message_list
try:
list_actions_sent_message_did_reply_invitation = 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)).fetchall()
except Exception as ex:
print(f"It didn't work with list_actions_sent_message_list : {ex}")
# list_actions_sent_message_list_str
try:
list_actions_sent_message_did_reply_invitation = 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}")
# list_actions_sent_message_list_tuple
try:
list_actions_sent_message_did_reply_invitation = 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 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_tuple)).fetchall()
except Exception as ex:
print(f"It didn't work with list_actions_sent_message_list_tuple : {ex}")
print(f"list_actions_sent_message_did_reply_invitation : {list_actions_sent_message_did_reply_invitation}")
OUTPUT:
list_actions_sent_message_tuple:[(2948,), (2951,), (2985,), (2991,), (2993,), (3009,)]
list_actions_sent_message_list:[2948, 2951, 2985, 2991, 2993, 3009]
list_actions_sent_message_list_str:(2948, 2951, 2985, 2991, 2993, 3009)
list_actions_sent_message_list_tuple:(2948, 2951, 2985, 2991, 2993, 3009)
list_actions_sent_message_did_reply_invitation_WITHOUT_NOT_IN:[(3129, 2948, 'Marie-Gilberte François', '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 PAO', 'message_txt_invitation_1A', '2022-03-01 22:12:23')]
It didn't work with list_actions_sent_message_tuple : near "?": syntax error
It didn't work with list_actions_sent_message_list : near "?": syntax error
It didn't work with list_actions_sent_message_list_str : near "?": syntax error
It didn't work with list_actions_sent_message_list_tuple : near "?": syntax error
Traceback (most recent call last):
File "H:\Mon Drive\project\mymodulesteam.py", line 11038, in LINKEDIN_PurgeMessagingQueueaForInvitation
print(f"list_actions_sent_message_did_reply_invitation : {list_actions_sent_message_did_reply_invitation}")
UnboundLocalError: local variable 'list_actions_sent_message_did_reply_invitation' referenced before assignment
Can anyone help me please?
