MySQL Problem

[Ian]

  On 03/09/2010 14:29, Victor Subervi wrote:
> This is an addendum to my last post. Please observe the following:
>
> mysql> select * from spreadsheets where Temp=1;
> +-----+--------------------+-------+---------+--------+------+
> | ID  | Client             | Multi | Item    | Markup | Temp |
> +-----+--------------------+-------+---------+--------+------+
> | 611 | Lincoln_Properties |     0 | 2030572 |   0.00 |    1 |
> | 621 | Lincoln_Properties |     0 | 2030572 |   0.00 |    1 |
> +-----+--------------------+-------+---------+--------+------+
> 2 rows in set (0.00 sec)
>
> mysql> describe spreadsheets;
> +--------+------------------+------+-----+---------+----------------+
> | Field  | Type             | Null | Key | Default | Extra          |
> +--------+------------------+------+-----+---------+----------------+
> | ID     | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
> | Client | varchar(40)      | YES  |     | NULL    |                |
> | Multi  | tinyint(1)       | YES  |     | NULL    |                |
> | Item   | varchar(40)      | YES  |     | NULL    |                |
> | Markup | float(6,2)       | YES  |     | NULL    |                |
> | Temp   | tinyint(1)       | YES  |     | NULL    |                |
> +--------+------------------+------+-----+---------+----------------+
> 6 rows in set (0.00 sec)
>
> Yet from my script:
>
>     cursor.execute('select * from spreadsheets where Temp=1')
>     print cursor.fetchall()
>
> print nothing but an empty set: () Why??
> TIA,
> beno
Hi Victor

Find out exactly what character encoding are you using for each of the 
following places when using MySQL client.
     The MySQL installation
     The database definition
     The table definition
     The Field definition
     The Link between Python and MySQL
     The Python source / The Msql Client.

And then find out what encoding is being forced/used by the code you 
have written in Python in each of the above situations?  You may have to 
go to the source of the library routine to find this out.

What I suspect may be happening is this.

Say you have a field containing a character/code point that is in UTF-8 
but not in the ISO-8859-1 set. If such a field was written using UTF-8 
throughout, but then read using ISO-8859-1 or similar then the read will 
generate an error.  That error may be being ignored or suppressed 
causing the code to drop your data rows.

IIRC, MySQL calls UTF-8 by the (incorrect) name of utf-8.

My recommendation is for you to use UTF-8 for everything. UTF-8 can 
store any character in any language(1), is really efficient for English 
text, and acceptable for other languages. Performance it excellent, 
because it involves no encoding/decoding as the data moves between disk, 
MySQL link or Python.

(1) There are some minor human languages that cannot be encoded - 
usually because no written form has yet been devised or the code points 
have not been agreed. These languages will not be met in practise.

Regards

Ian