Problem with UTF-8

Hi,

I've discovered that it is possible to create in-memory databases with pysqlite, and I wanted to use that feature to work with tabulated files (containing accentuated characters).
I wrote the attached test script, which almost works. The remaining problem is that I get errors like:

>>> print cur.fetchall()
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
pysqlite2.dbapi2.OperationalError: Could not decode to UTF-8 column 'Col1' with text '  texte où il y a des éàè '

It seems that the lines table created by the csv reader contains non-unicode strings, which the executemany statement introduces into the sqlite table and that can't be extracted back. But I'm not sure that this is the real problem, nor how I can solve it properly and efficiently. Any help would be greatly appreciated.

Nicolas

PS: I have tried with sqlite3 bundled in python 2.5 and I get the exact same problem.

_______________________________________________
pysqlite mailing list
pysqlite-IAPFreCvJWPBWskQ1e/[email protected]
http://lists.initd.org/mailman/listinfo/pysqlite

import csv 
lines = list(csv.reader(open('test.csv')))

from pysqlite2 import dbapi2 as sqlite
# Create database
con = sqlite.connect(":memory:")
cur = con.cursor()

# Create table
cur.execute("DROP TABLE data;")
CREATE = "CREATE TABLE data("
for col in lines[0]:
	CREATE = CREATE + "\"" + col + "\","

CREATE = CREATE[0:len(CREATE)-2]
CREATE = CREATE + "\");"
cur.execute(CREATE)

# Populate the database
POPULATE = "insert into data values ("
for i in range(len(lines[0])-1):
	POPULATE = POPULATE + "?,"

POPULATE = POPULATE + "?)"
cur.executemany(POPULATE, lines)

# Query
# cur.execute("select * from data")
cur.execute("select col1 from data where col1 like '%some_string%'")
print cur.fetchall()