Setting the encoding in pysqlite2

[Michele Simionato]

An easy question, but I don't find the answer in the docs :-(
I have a sqlite3 database containing accented characters (latin-1).
How do I set the right encoding? For instance if I do this:

#-*- encoding: latin-1 -*-
from pysqlite2 import dbapi2 as sqlite
import os

DBFILE="/tmp/example.db"

def writedb(conn):
c = conn.cursor()
c.executescript("""
create table example (word char(20));
insert into example values ("così");
""")
c.close()

def readdb(conn):
c = conn.cursor()
c.execute("select * from example;")
#print c.fetchall()
c.close()

if __name__ == "__main__":
conn = sqlite.connect(DBFILE)
writedb(conn)
readdb(conn)
conn.close()
os.remove(DBFILE)

I get UnicodeDecodeError: 'utf8' codec can't decode byte 0xec in
position 3: unexpected end of data
(notice, even if the 'print' statement is commented.


Michele Simionato

 

[Renzo]

Michele Simionato ha scritto:

An easy question, but I don't find the answer in the docs :-(
I have a sqlite3 database containing accented characters (latin-1).
How do I set the right encoding? For instance if I do this:

Hi,
i usually use this "string" method:

encode([encoding[,errors]])

An example:

cur.execute("""
insert into tab(
field1,
field2)
values (?,?)
""" , (myvar1.encode('utf8'),\
myvar2.encode('utf8')))

Bye,
Renzo

 

[Michele Simionato]

Well, the issue is not how to input text in the database from Python
(it is enough to use literal unicode strings);
in my case the database has been generated from a text file containing
accented chars, using .import,
and it seems I cannot read it from Python because of the unicode error
:-(

Michele Simionato

 

[Reinhold Birkenfeld]

An easy question, but I don't find the answer in the docs :-(
I have a sqlite3 database containing accented characters (latin-1).
How do I set the right encoding? For instance if I do this:

I think you should ask on the pysqlite-devel list.

Reinhold

 

[=?ISO-8859-1?Q?=22Martin_v=2E_L=F6wis=22?=]

Well, the issue is not how to input text in the database from Python
(it is enough to use literal unicode strings);
in my case the database has been generated from a text file containing
accented chars, using .import,
and it seems I cannot read it from Python because of the unicode error
:-(

You should not do that. In SQLite 3, TEXT fields should always be
UTF-8. That .import did not reject your data sounds like a bug in
..import.

So if you make your input data UTF-8, you should be able to fetch
them easily, and receive Unicode strings.

Regards,
Martin

 

[Gerhard Haering]

An easy question, but I don't find the answer in the docs :-(
I have a sqlite3 database containing accented characters (latin-1).
How do I set the right encoding? For instance if I do this: [...]

You cannot set the encoding directly, because TEXT data in SQLite3
databases is expected to be in UTF-8 encoding. If you store "weird"
TEXT, you can work around it by using a custom converter in pysqlite2,
like in the following example:

#-*- encoding: latin-1 -*-
from pysqlite2 import dbapi2 as sqlite

# Register an additional converter for plain bytestrings
sqlite.register_converter("bytestring", str)

con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
cur = con.cursor()
cur.execute("create table test(t)")

testdata = "Häring" # bytestring in ISO-8859-1 encoding

cur.execute("insert into test(t) values (?)", (testdata,))

# Try to retrieve the test data, will fail
try:
cur.execute("select t from test")
except UnicodeDecodeError:
print "Could not decode latin1 as utf-8 (as expected)"

# Via the PARSE_COLNAMES trick, explicitly choose the bytestring converter
# instead of the default unicode one:
cur.execute('select t as "t [bytestring]" from test')
result = cur.fetchone()[0]
assert testdata == result
print "Correctly retrieved test data"

HTH,

-- Gerhard