Sqlite3 textfactory and user-defined function

Discussion in 'Python' started by jeff_d_harper@hotmail.com, Jun 21, 2008.

  1. Guest

    I've run into a problem with text encoding in the Sqlite3 module. I
    think it may be a bug. By default sqlite3 converts strings in the
    database from UTF-8 to unicode. This conversion can be controlled by
    changing the connection's text_factory.

    I have a database that stores strings in 8-bit ISO-8859. So, I set
    the text_factory to do no conversion. In my database I use user
    defined functions. I noticed that even when I set text_factory =
    lambda x:x, it appears to do UTF-8 to unicode conversion on strings
    that are passed to my user defined function.

    I've included a small program that illustrates the problem. It
    creates a database and table in memory and then populates 2 rows. One
    row contains an ASCII string. The other row contains a string with
    the non-ascii string, "Tést".

    Then, the program does an SQL select which calls the user-defined
    function, my_func(). The resulting row tuples contain 8-bit strings.
    But, my_func() is passed unicode strings. Notice, my_func is called
    with None instead of "Tést". I suspect this is because the binary
    representation of "Tést" is not valid UTF-8.

    Is there a way to turn off the UTF-8 to unicode when my_func() is
    called? Is this a bug or intended behavior?

    import sqlite3

    def create_table(dbase):
    #dbase.execute(r"""PRAGMA encoding = "UTF-16le";""")
    dbase.execute(r"""CREATE TABLE `my_table` ( 'id' INTEGER, 'column'
    BLOB); """)

    def add_rows(dbase):
    c = dbase.cursor()
    string1 = "Test"
    string2 = "T\xe9st"
    try:
    print string1
    c.execute(r"""INSERT INTO `my_table` ('id', 'column') VALUES
    (?,?)""", (1,string1))
    print string2
    c.execute(r"""INSERT INTO `my_table` ('id', 'column') VALUES
    (?,?)""", (2,string2,))
    finally:
    c.close()

    def select_rows(dbase):
    c = dbase.cursor()
    try:
    c.execute(r"""SELECT *, my_func(`column`) FROM `my_table`""")
    for row in c:
    print row
    finally:
    c.close()

    def factory(x):
    print 'x =', x
    return x

    def my_func(p):
    print 'my_func(%r) type = %s' % (p,type(p))

    def my_test():
    db_path = ":memory:"

    try:
    os.remove(db_path)
    except:
    pass

    dbase = sqlite3.connect(db_path)
    dbase.text_factory = lambda x:x
    dbase.create_function('my_func', 1, my_func)
    try:
    create_table(dbase)
    add_rows(dbase)
    select_rows(dbase)
    finally:
    dbase.commit()
    dbase.close()

    my_test()
     
    , Jun 21, 2008
    #1
    1. Advertising

  2. wrote:
    > I've run into a problem with text encoding in the Sqlite3 module. I
    > think it may be a bug. By default sqlite3 converts strings in the
    > database from UTF-8 to unicode. This conversion can be controlled by
    > changing the connection's text_factory.
    >
    > I have a database that stores strings in 8-bit ISO-8859. So, I set
    > the text_factory to do no conversion. In my database I use user
    > defined functions. I noticed that even when I set text_factory =
    > lambda x:x, it appears to do UTF-8 to unicode conversion on strings
    > that are passed to my user defined function. [...]


    I've answered the same question on the pysqlite mailing list a few weeks
    back:

    Thread "Trouble with create_function interface to sqlite"

    http://itsystementwicklung.de/pipermail/list-pysqlite/2008-May/000062.html

    -- Gerhard
     
    Gerhard Häring, Jun 21, 2008
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Oodini
    Replies:
    1
    Views:
    1,795
    Keith Thompson
    Sep 27, 2005
  2. Replies:
    1
    Views:
    517
    Sion Arrowsmith
    Jul 10, 2008
  3. Alok
    Replies:
    3
    Views:
    262
  4. Jeffrey 'jf' Lim
    Replies:
    5
    Views:
    507
    Jeffrey 'jf' Lim
    Apr 9, 2007
  5. SunSw0rd
    Replies:
    4
    Views:
    277
    SunSw0rd
    Jul 2, 2009
Loading...

Share This Page