3 Replies - 7047 Views - Last Post: 18 April 2012 - 02:59 AM Rate Topic: -----

#1 ab2013  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 21
  • Joined: 20-June 09

Pysqlite/Sqlite3/Python Problems

Posted 13 April 2012 - 09:39 PM

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


Is This A Good Question/Topic? 0
  • +

Replies To: Pysqlite/Sqlite3/Python Problems

#2 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 386
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Pysqlite/Sqlite3/Python Problems

Posted 14 April 2012 - 04:37 AM

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.
Was This Post Helpful? 0
  • +
  • -

#3 ab2013  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 21
  • Joined: 20-June 09

Re: Pysqlite/Sqlite3/Python Problems

Posted 14 April 2012 - 08:11 AM

View PostIonut, 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

Was This Post Helpful? 0
  • +
  • -

#4 Tayacan  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 145
  • View blog
  • Posts: 275
  • Joined: 18-January 11

Re: Pysqlite/Sqlite3/Python Problems

Posted 18 April 2012 - 02:59 AM

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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1