SQLite returns Unicode buffer that cannot be converted to usable strings

Question

I'm reading in records from a sqlite3 database. The data is stored as TEXT in the database, however I'm getting buffers back (which are in Unicode and nothing I can find seems to convert them into useable text (Unicode or not))

To make it work I have to CAST to a TEXT in the SQL query.

What point am I missing?

As an example:

import sqlite3

con = sqlite3.connect('D:\\test.db' )
cur = con.cursor()


print "Before CAST"
cur.execute('SELECT Type FROM \"Internet Explorer History\" ')
row = cur.fetchone()

print row
print row[0]
print type(row[0])
print str(row[0])
print str(row[0]).encode('utf-8')

print "----------------"

print "After CAST"
cur.execute('SELECT CAST( Type as TEXT) FROM \"Internet Explorer History\" ')
row = cur.fetchone()

print row
print row[0]
print type(row[0])
print str(row[0])
print str(row[0]).encode('utf-8')

This gives the following output:

Before CAST
(<read-write buffer ptr 0x0276A890, size 24 at 0x0276A870>,)
C a c h e  R e c o r d 
<type 'buffer'>
C a c h e  R e c o r d 
C a c h e  R e c o r d 
----------------
After CAST
(u'Cache Record',)
Cache Record
<type 'unicode'>
Cache Record
Cache Record

Note: spaces between letters of 'Cache Record' before the CAST are nulls.

Thanks.

Answer 1

How data is inserted into your "Internet Explorer History" table? My bet is that it's being inserted as a binary object (e.g. Python type buffer), so SQLite returns it as a blob rather than text.

Try inserting proper unicode objects then perform the same query and check its output.

Answer 2

Try:

print str(row[0]).decode('utf-16le')