[Python]sqlite3 binary file Storage problem (BLOB) (You must "not" use 8-bit bytestrings unless ...)

The thing is this:

Bloggers try to use the Python sqlite3 database to store encrypted user name password information, the table is like this

CREATE TABLE IF not EXISTS user (UserID INTEGER PRIMARY KEY autoincrement,userstudentid BLOB not NULL UNIQUE on CONFLICT IG Nore,userpassword BLOB not NULL);

Where Userstudentid and UserPassword are stored as BLOB types, as binary storage.

But when the blogger inserts the encrypted byte string into the database, it reports the following error:

Sqlite3. Programmingerror:you must 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings (l Ike text_factory = str). It is highly recommended, instead just switch your application to Unicode strings.

Obviously it treats the blogger's byte string as an encoded string. At this point it is not possible to refer to its practice, the text_factory is set to STR, so that the blogger's ciphertext will be encoded to store (such as Utf-8), and if some bytes can not be encoded by utf-8, it will throw an exception or be ignored.

Online Search a lot of articles, did not solve the problem of Bo master.

And then I found official Document.

Https://docs.python.org/2/library/sqlite3.html#module-sqlite3

Originally Python and sqlite3 blob corresponding to the data type of buffer, bloggers surprised out a cold sweat, busy looking at their own insert part of the code:

DEF INSERTUSERNAMEANDPASSWORDTODB (Conn, Cu, username, password): username = Encrypt (username) password = Encrypt ( Password) cu.execute ("INSERT into User (Userstudentid, UserPassword) VALUES (?,?)", (username, password)) Conn.commit ()

Tested the data types for the next username and password

Print isinstance (username, str) print isinstance (password, str)

The result is true, no wonder Sqlite3 tries to store them as strings. This involves a knowledge that Sqlite3 uses a Dynamic data type system that attempts to convert data into a standard type within the database, based on the value of the data. Here it tries to convert my byte cipher into a string.

Reference: http://www.cnblogs.com/kfqcome/archive/2011/06/27/2137000.html

The problem is solved by converting username and password to the buffer type.

DEF INSERTUSERNAMEANDPASSWORDTODB (Conn, Cu, username, password): username = Encrypt (username) password = Encrypt ( Password) cu.execute ("INSERT into User (Userstudentid, UserPassword) VALUES (?,?)", (buffer (username), buffer (password) )) Conn.commit ()

Weibo: @ zhejiang Song Bo