Inserting UTF

[hectorgarcia]

Using MySQL-python.exe-1.2.0.win32-py2.4 and Mysql 4.1.

data=u"da"

query="insert into labels (id,word) values('15313','%s')"%data

c = db.cursor()
c.execute(query)

I changed cursors.py at def _do_query(self, q):

db.query(q.encode(db.charset))

Everything seems to work smooth but if I see the dabase there is "da" instead of "da".

there is a problem with the enconding or something. I've tried many ways, but without success.

[Andy Dustman]

For like the 10 billionth time, do not insert literals into your query string using the % operator.

data=u"da"

query="insert into labels (id,word) values(%s,'%s)"

c = db.cursor()
c.execute(query, (15313, data))

[hectorgarcia]

sorry,I read everything but any way I do is never working. For instance, with your code it's not inserting in the database.

File "C:\Python24\lib\site-packages\MySQLdb\cursors.py", line 137, in execute
self.errorhandler(self, exc, value)
File "C:\Python24\lib\site-packages\MySQLdb\connections.py", line 33, in defau
lterrorhandler
raise errorclass, errorvalue
UnicodeDecodeError: 'ascii' codec can't decode byte 0xc3 in position 53: ordinal
not in range(128)

As I said I changed cursors.py to

def _do_query(self, q):
    db = self._get_db()
    db.query(q.encode(db.charset))
   ....

But if I keep it like

def _do_query(self, q):
    db = self._get_db()
    db.query(q)
   ....

Then i get:

File "C:\Python24\lib\site-packages\MySQLdb\connections.py", line 33, in defau
lterrorhandler
raise errorclass, errorvalue
_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 s
yntax to use near 'd\xc3\xada'')' at line 1")

Using Mysql 4.1 and Mysqdb 1.20

Thanks

[Sébastien]

Just a very candid suggestion. Are you using a UTF-8 enabled version of MySQL 4.1? Did you try running the exact same statement via mysql command line?

[Andy Dustman]

Additionally, what is the value of db.charset? Note that you cannot just assign values here and expect it to work. If you want to use something other than MySQL's default setting, you should set this in a configuration file.

[hectorgarcia]

it's utf-8 as default

[hectorgarcia]

The database is UTF-8 bin type.

running it from the command line works perfectly. If I run for example INSERT INTO labels ( id , word ) VALUES (
'777', 'da' ) it will store the word "da" perfectly.

but if i do

data=u"da"

query="insert into labels (id,word) values(%s,%s)"

c = db.cursor()
c.execute(query, (777, data))
db.commit()

then it's storing día.

[hectorgarcia]

I'm not getting the second error anymore, it's just inserting día instead of da if I use db.query(q) and giving me the decoding error if i use db.query(q.encode(db.charset))

thanks

[Andy Dustman]

Do not play with db.query(). Use db.execute(). db.query() is the low-level interface.

[hectorgarcia]

After cheking that the same insert is working perfectly via command line, my database is UTF-8, db.charsert is utf8, and using this code

data=u"da"

query="insert into labels (id,word) values(%s,%s)"

c = db.cursor()
c.execute(query, (777, data))
db.commit()

but it keeps inserting "día" instead of da