0

Following is my code sample.

SENTENCE = "Today is a wonderful day."
sent= (word_tokenize(SENTENCE))

cursor = conn.cursor()
cursor.execute('SELECT * FROM emotion_state WHERE key_word = %s', (sent))
results = cursor.fetchall()
for i in results:
    print(i)

Here I need to check whether the words in "sent" array available in the database or not. If there are some keyword available in the database that is in the "sent" array, I need to fetch all. I'm tried in followed way and it makes the error which is

raise errorclass(errorvalue)
_mysql_exceptions.ProgrammingError: not all arguments converted during
string formatting

How can I do this comparison easily ?

frlan
  • 6,950
  • 3
  • 31
  • 72
Chathurika
  • 419
  • 2
  • 6
  • 18
  • 1
    if `sent` need to be a tuple, you should add a comma inside of it like `(value,)` in case it's a single value. – ryugie Mar 02 '17 at 21:00
  • 3
    @Arount, no, using the `%` operator would make this vulnerable to SQL injection. Letting `execute` do the binding makes it possible to correctly escape the parameters. – Sjoerd Mar 02 '17 at 21:05
  • But it makes error – Chathurika Mar 02 '17 at 21:07
  • _mysql.connection.query(self, query) _mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '['It', 'is', 'wonderful', '.']' at line 1") – Chathurika Mar 02 '17 at 21:07
  • 1
    @Sjoerd oops, you are right, I deleted this bad advice from the internet :) – Arount Mar 02 '17 at 21:07
  • So how can I write the query instead of this way? – Chathurika Mar 02 '17 at 21:09
  • could you print `sent` ? what does `word_tokenize` do/return? – bouletta Mar 02 '17 at 21:27
  • @ bouletta Yes, I did. it returns ['It', 'is', 'wonderful','day', '.'] – Chathurika Mar 02 '17 at 21:30
  • Then using *both* answers provided should solve your problem. replace `=` by `IN` and `(sent)` by `(sent,)` – bouletta Mar 02 '17 at 21:32
  • @bouletta I did as you said. But it also makes an error – Chathurika Mar 02 '17 at 21:37
  • return "(%s)" % (','.join(escape_sequence(t, d))) TypeError: sequence item 0: expected str instance, bytes found – Chathurika Mar 02 '17 at 21:37
  • 1
    At least you're using placeholders from the get go. See the linked q/a for how to build the placeholders to match the list for IN operator. Or move to Postgresql and let psycopg2 handle lists for you. There's also [SQLAlchemy](http://www.sqlalchemy.org), which makes life with SQL in Python a lot easier. – Ilja Everilä Mar 02 '17 at 21:49
  • Yeah I got it. If you post this as an answer I can mark it as correct answer. Thank you so much. – Chathurika Mar 02 '17 at 21:53

2 Answers2

1

The problem is probably because the last argument in this call is a string, not a tuple:

cursor.execute('SELECT * FROM emotion_state WHERE key_word = %s', (sent))

As you know, a tuple is normally in this form:

(a, b, c)

However, if it contains only one element you should use an extra comma to indicate that it is a tuple:

(a,)

Alternatively, you could use a list:

[a]
Sjoerd
  • 74,049
  • 16
  • 131
  • 175
1

In SQL, the = operator is used to match a single thing. It looks like you want to match a list of things. Try WHERE key_word IN %s.

Andrew
  • 4,574
  • 26
  • 31