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("sele ct * from example;")
#print c.fetchall()
c.close()

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

I get UnicodeDecodeEr ror: '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]

Re: Setting the encoding in pysqlite2

Michele Simionato ha scritto:[color=blue]
> 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:
>[/color]

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]

Re: Setting the encoding in pysqlite2

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]

Re: Setting the encoding in pysqlite2

Michele Simionato wrote:[color=blue]
> 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:[/color]

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

Reinhold

[Martin v. Löwis]

Re: Setting the encoding in pysqlite2

Michele Simionato wrote:[color=blue]
> 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
> :-([/color]

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]

Re: Setting the encoding in pysqlite2

On Thu, Aug 25, 2005 at 01:15:55AM -0700, Michele Simionato wrote:[color=blue]
> 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: [...][/color]

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("byt estring", str)

con = sqlite.connect( ":memory:", detect_types=sq lite.PARSE_COLN AMES)
cur = con.cursor()
cur.execute("cr eate table test(t)")

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

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

# Try to retrieve the test data, will fail
try:
cur.execute("se lect t from test")
except UnicodeDecodeEr ror:
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('se lect t as "t [bytestring]" from test')
result = cur.fetchone()[0]
assert testdata == result
print "Correctly retrieved test data"

HTH,

-- Gerhard