[ab2013]

Hi,

I am having problems with string matching in a column of a table.

For example, let's say that the schema (a simplified version of the actual table, with modified attribute names) is:

Cow(cID INTEGER PRIMARY KEY, cName TEXT)

The python statement I have to count for the number for a string (or cow name in this case is):

cName = 'Betty';

self.cursor.execute('SELECT cID FROM Cow WHERE cName=?', (cName,))
Result = self.cursor.fetchone()

The result is that Result = NULL.

However, without modifying the database, if I fire up sqlite3 on the database and enter the command:

sqlite> SELECT cID FROM Cow WHERE cName = 'Betty'; 

I get a valid ID number ... not an empty result set.

What am I doing wrong in Python?

Thanks!

This post has been edited by ab2013: 13 April 2012 - 09:48 PM

[Ionut]

I haven't worked with databases yet in Python, but it sounds, after your description, that the parameter is not correctly passed to the statement, ie instead of cName = 'Betty', the where clause looks like cName=Betty. Try adding some single quotes to your string.

[ab2013]

Ionut, on 14 April 2012 - 05:37 AM, said:

I haven't worked with databases yet in Python, but it sounds, after your description, that the parameter is not correctly passed to the statement, ie instead of cName = 'Betty', the where clause looks like cName=Betty. Try adding some single quotes to your string.

The result is the same if I do:

cName = 'Betty'; # note that this is actually being read from a input text file 

self.cursor.execute("SELECT cID FROM Cow WHERE cName=?", ("\'"+cName+"\'",))

Result = self.cursor.fetchone()

and if I do:

cName = 'Betty';

self.cursor.execute("SELECT cID FROM Cow WHERE cName=?", ("'"+cName+"'",))

Result = self.cursor.fetchone()

Note that I am using Python 2.7. Not sure if that makes a difference, but I really don't know why I'm having so much difficulty with string substitution.

This post has been edited by ab2013: 14 April 2012 - 08:24 AM

[Tayacan]

Pretty easy to figure out if it's string substitution or something else. Try with:

self.cursor.execute("SELECT cID FROM Cow WHERE cName='Betty'")

If that doesn't work either, well, at least you'll know that your problem is something else.