Python SQLite Encoding

Download SQLite from http://initd.org/tracker/pysqlite/wiki/pysqlite
Python binding. Use sqlite3.exe in Windows to create a database and a table:
++
-Database: Wanna
-Table Name: Hello
-ID name
-------------
Handsome guy 0
1 wannachan
2 dick. Chan
3 Wenwen
++

Now, the table is created successfully! Next, start pysqlite to operate this database! So excited ~~
First, establish a connection:
>>> From pysqlite2 import dbapi2 as SQLite
>>> Con = SQLite. Connect ("G: \ SQLite \ wanna ")
Enable cursor again:
>>> Cur = con. cursor ()
The exciting time has come! Execute SQL:
----------------------------------------------
>>> Cur.exe cute ('select * From hello ')

Traceback (most recent call last ):
File "<pyshell #24>", line 1, in <module>
Cur.exe cute ('select * From hello ')
Operationalerror: cocould not decode to UTF-8 column 'name' with text' handsome guy'
----------------------------------------------

Oh! No! Said that my 'handsome guy 'cannot be coded in UTF-8! What should I do? What should I do ?!
Check online! Someone can see that this con = SQLite. Connect ("Database", encoding = 'cp936 ')
I will try again. The result is:
----------------------------------------------------------------
>>> Con = SQLite. Connect ("G: \ SQLite \ wanna", encoding = 'cp936 ')
Traceback (most recent call last ):
File "<stdin>", line 1, in <module>
Typeerror: 'encoding' is an invalid keyword argument for this function
----------------------------------------------------------------

It seems that the previous version only has the encoding parameter, and now my 2.5 version does not have wow! It seems that only manual
Now! So I reluctantly opened man and looked at it. It was originally necessary to specify text_factory!
So I tried to copy an example:
>>> Con. text_factory = Lambda X: Unicode (x, "UTF-8", "Ignore ")
Indicates that the record is encoded with utf8, and ignore is used if it is not a UTF-8. I am aware that my code is GBK,
But I also want to see what is wrong, so continue:
----------------------------------------------
>>> Cur = con. cursor ()
>>> Cur.exe cute ('select * From hello ')
<Pysqlite2.dbapi2. cursor object at 0x012984a0>
>>> Rs = cur. fetchall ()
>>> Rs [0]
(0, u '')
>>> Rs [1]
(1, u'wanachance ')
>>> Rs [2]
(2, u'dick. chan ')
>>> Rs [3]
(3, u '')
----------------------------------------------
As you can see, the two records with Chinese characters are ignored as u'' due to inconsistent encoding.
I see the light in my heart! The following code uses GBK (cp936:
----------------------------------------------
>>> Con. text_factory = Lambda X: Unicode (x, "cp936", "Ignore ")
>>> Cur = con. cursor ()
>>> Cur.exe cute ('select * From hello ')
<Pysqlite2.dbapi2. cursor object at 0x012984d0>
>>> Rs = cur. fetchall ()
>>> Rs [0]
(0, U' \ u9648 \ u67f1 ')
>>> U' \ u9648 \ u67f1'
U' \ u9648 \ u67f1'
>>> Print Rs [0] [1]
Handsome guy
----------------------------------------------

Hoho! A huge success! At this point I thought, what would happen if I pointed it to UTF-8 not ignore?
Just do it:
----------------------------------------------
>>> Con. text_factory = Lambda X: Unicode (x, "UTF-8 ")
>>> Cur = con. cursor ()
>>> Cur.exe cute ('select * From hello ')

Traceback (most recent call last ):
File "<pyshell #21>", line 1, in <module>
Cur.exe cute ('select * From hello ')
File "<pyshell #19>", line 1, in <Lambda>
Con. text_factory = Lambda X: Unicode (x, "UTF-8 ")
Unicodedecodeerror: 'utf8' codec can't decode byte 0xb3 in position 0:
Unexpected code byte
----------------------------------------------

Error! It indicates that the encoding is not match, so if my record has multiple types of encoding
What should we do? So I tried to use the Unicode option that appeared on manual to try it:
----------------------------------------------
>>> Con. text_factory = SQLite. optimizedunicode

>>> Cur = con. cursor ()
>>> Cur.exe cute ('select * From hello ')

Traceback (most recent call last ):
File "<pyshell #24>", line 1, in <module>
Cur.exe cute ('select * From hello ')
Operationalerror: cocould not decode to UTF-8 column 'name' with text' handsome guy'
>>>
----------------------------------------------

Tnnd, even Unicode also uses UTF-8 to encode, this is not a bully? Knowing that the UTF-8 of Chinese characters occupies a large space can not!
So I took a closer look at manual and finally saw that one was passed back in bytestring format.
I think, this does not involve the specific encoding, so there should be no error even if there are different charsets!
Try:
----------------------------------------------
>>> From pysqlite2 import dbapi2 as SQLite
>>> Con = SQLite. Connect ("G: \ SQLite \ wanna ")
>>> Con. text_factory = STR # STR indicates return in byte string format
>>> Cur = con. cursor ()
>>> Cur.exe cute ('select * From hello ')
<Pysqlite2.dbapi2. cursor object at 0x00bdbb30>
>>> Rs = cur. fetchall ()
>>> Rs [0] [1]
'\ Xb3 \ xc2 \ xd6 \ xf9'
>>> Print Rs [0] [1]
Handsome guy
----------------------------------------------

Haha! Successful! After obtaining the bytecode string, you can decode it according to the specified encoding.
The output is correct!
Look at the bytecode string '\ xb3 \ xc2 \ xd6 \ xf9' above and try to decode it:
>>> '\ Xb3 \ xc2 \ xd6 \ xf9'. Decode ('cp936 ')
U' \ u9648 \ u67f1'

I got a unicode string. Can I determine whether it is equal to the expected string?

>>>_= = U'handsome guy'
True
>>> U'handsome guy'
U' \ u9648 \ u67f1'

Obviously the same string! OK, solved!

Last thought:
1. Before text_factory = STR, I define STR as a variable and override it
Is this identity of <type 'str'>?
2. I like to use Python because it is always so easy to solve the Chinese problem. How can I convert the encoding?
How can I change it! It's unambiguous!

From: http://www.cnblogs.com/changyou/archive/2010/01/09/1642980.html