1

Hi I'm trying to check if I already have an entry in a mysql table through python. The key I use to check if their is already an entry is called PID and it uses the ascii_bin collation. my problem is when when I try something like...

q = """select * from table_name where PID = '%s'"""%("Hello")
db = MySQLdb.connect("xxxx", "xxxx", "xxxx","temp",cursorclass=MySQLdb.cursors.DictCursor)
cursor = db.cursor()
res = cursor.execute(q) 
rowOne = cursor.fetchone()   #fetches the row where pid = "hello"

rowOne ends up being the row where pid = hello. However when I use sqlyog and execute the query it properly prints out the row where pid = Hello(Properly functions as a case sensitive query). I'm looking for a way to get the mysqldb module to work properly as a lot of my code already is using this module

ZJS
  • 3,991
  • 2
  • 15
  • 22
  • 1
    It is case insensitive, by default. Use binary comparison if you want a case sensitive select: http://stackoverflow.com/questions/3936967/mysql-case-insensitive-select – alecxe Sep 23 '13 at 21:23
  • @alecxe in my question I already mentioned that my collation is ascii_bin and my select is case sensitive. I have this working in just mysql, but when I try it through mysqldb it does not work. The problem is purely python – ZJS Sep 23 '13 at 21:33

1 Answers1

0

By default string comparison operations in MySQL are not case sensitive (or accent sensitive): http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

You have two easy options though, use COLLATE:

For example, if you are comparing a column and a string that both have the latin1 character set, you can use the COLLATE operator to cause either operand to have the latin1_general_cs or latin1_bin collation:

or set the column to be case sensitive:

If you want a column always to be treated in case-sensitive fashion, declare it with a case sensitive or binary collation. See Section 13.1.10, “CREATE TABLE Syntax”.

Matthew
  • 9,851
  • 4
  • 46
  • 77
  • as I mentioned in my question I am using ascii_bin collation which is a binary collation. The problem here is not with mysql but with pythons module mysqldb – ZJS Sep 23 '13 at 21:30
  • 1
    If you force it in the query on your search term with `collate latin2_bin` does it work? Agree this seems odd on re-reading. – Matthew Sep 23 '13 at 21:40
  • Nope that doesn't work. This is not a mysql problem it is a mysqldb. I have this working perfectly in mysql – ZJS Sep 23 '13 at 21:43
  • 1
    Have you tried logging what mysqldb is actually sending to MySQL? – Matthew Sep 23 '13 at 21:45
  • Yes the cursor has an attribute called _last_executed, which shows that the query is actually select * from table_name where pid = 'Hello' – ZJS Sep 23 '13 at 21:49
  • 1
    Sorry - I have no idea what else this would be. Also have no idea how mysqldb module could possibly introduce this issue. – Matthew Sep 23 '13 at 21:54
  • 1
    Just to be clear, you have changed you code to: `q = """select * from table_name where PID COLLATE latin1_bin = '%s'"""%("Hello")` and still had the error? – Matthew Sep 24 '13 at 20:37
  • Yes I used that exactly – ZJS Sep 26 '13 at 13:53
  • 1
    The only other thing I can think of to try would be different permutations of this `q = """select * from table_name where PID = '%s' COLLATE latin1_bin """%("Hello")` or other case sensitive character sets on either side of the variable: `COLLATE utf8_bin` or `utf8_general_cs`. Is MySQL receiving the query you expect on each one of these? Are you able to set the whole table to a case sensitive character set for searching? : Alter the table to be something like: `DEFAULT CHARSET=utf8 COLLATE utf8_general_cs`? If none of that works I will put up a bounty for you - I am curious now. – Matthew Sep 26 '13 at 14:57
  • 1
    The code I used in the question was not my actual code... My actual code had a ` error in it that caused the where clause to not be read. The first entry in my table just happened to be hello, which is why I thought there was a case sensitive issue. Thank you for your help though – ZJS Sep 26 '13 at 15:14
  • So basically the universe conspired to throw you a red herring. Glad to hear it is worked out. – Matthew Sep 26 '13 at 16:29