inserting unicode into sqlite database throws exception when using web.database

Bug #373219 reported by SeC on 2009-05-07
14
This bug affects 3 people
Affects Status Importance Assigned to Milestone
web.py
Medium
Anand Chitipothu

Bug Description

Here's how to reproduce:
>>> import web
>>> db = web.database(dbn='sqlite', db=':memory:')
>>> db.query("create table t(t text)")
0.0 (1): create table t(t text)
-1
>>> db.insert('t', t=u'\u20ac')
ERR: INSERT INTO t (t) VALUES (u'\u20ac')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python2.6/site-packages/web/db.py", line 701, in insert
    self._db_execute(db_cursor, q1)
  File "/usr/local/lib/python2.6/site-packages/web/db.py", line 530, in _db_execute
    for x in sql_query.values()])
sqlite3.ProgrammingError: You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings (like text_factory = str). It is highly recommended that you instead just switch your application to Unicode strings.

using Python 2.6.2, web.py 0.31. My fix (simply remove line 951 from web/db.py):
--- db.py 2009-05-07 14:22:30.000000000 +0200
+++ db.py.orig 2009-05-07 14:21:25.000000000 +0200
@@ -948,6 +948,7 @@
         """
         if val is True: return 1
         elif val is False: return 0
+ elif isinstance(val, unicode): return val.encode(self.encoding)
         else: return val

 class FirebirdDB(DB):

Then:
>>> import web
>>> db = web.database(dbn='sqlite', db=':memory:')
>>> db.query("create table t(t text)")
0.0 (1): create table t(t text)
-1
>>> db.insert('t', t=u'\u20ac')
0.0 (2): INSERT INTO t (t) VALUES (u'\u20ac')
0.0 (3): SELECT last_insert_rowid();
1

Don't know if this is right, but I think it should be reported.

Kiran Jonnalagadda (jackerhack) wrote :

This affects me too. New Jaunty installation with py 2.6 and python-webpy (0.3) from Ubuntu repositories. Both sqlite3 and pysqlite2 are available, so I don't know which backend it defaults to.

SeC (sec) wrote :

Python 2.6.2 (r262:71600, Apr 29 2009, 09:51:47)
[GCC 4.2.1 20070719 [FreeBSD]] on freebsd7
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3; sqlite3.version
'2.4.1'

Its sqlite3 than comes with python 2.6 by default.

2009/5/8 SeC <email address hidden>:
> Python 2.6.2 (r262:71600, Apr 29 2009, 09:51:47)
> [GCC 4.2.1 20070719  [FreeBSD]] on freebsd7
> Type "help", "copyright", "credits" or "license" for more information.
>>>> import sqlite3; sqlite3.version
> '2.4.1'

I've sqlite3 2.3.2 and it works perfectly with it.

SeC (sec) wrote :

Maybe upgrade to new python/sqlite3 and test?

Anand Chitipothu (anandology) wrote :

> Maybe upgrade to new python/sqlite3 and test?

Dont' worry. There is an issue. I tested that with python 2.4/2.5/2.6
and sqlite3/pysqlite2.
That _py2sql fix was done by Aaron. I sent him an email. Waiting for response.

Anand Chitipothu (anandology) wrote :

Fixed.

Changed in webpy:
assignee: nobody → Anand Chitipothu (anandology)
importance: Undecided → Medium
status: New → Fix Committed
Changed in webpy:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers