Write/Read utf-8 from sqlite3 db with Python web interface

Question

I have a textfield for user-input that adds data to a sqlite3 database with a Python-script. But my script breaks once the user enters a text with utf-8 characters:

UnicodeEncodeError: 'ascii' codec can't encode character u'\xe4' in position 24: ordinal not in range(128)

Since the db string-entries are all unicode I suspect that my INSERT statement might cause the problem, but how can I pass utf-8-characters from the text input into the sqlite3 db, without converting them to %s strings? This is how I add the messages from the textarea:

c.execute("INSERT INTO messages VALUES ('%s', '%s', NULL)" % (threadinput , strip_html(newmessage)))

This is where I print the messages from the db, raising the UnicodeError:

conn = sqlite3.connect('msg.db')
c = conn.cursor()
c.execute("SELECT * FROM messages WHERE thread ='%s' ORDER BY nr ASC" % threadinput)
rows = c.fetchall()
count = 1
for row in rows:
        print "<li>Message Nr. %s: %s</li>" % (count, row)
        count += 1
conn.close()

In sqlite3 you need to use ? or :name for parameter replacement... Use values (?, ?, NULL) instead... — Jon Clements
– Jon Clements, Commented Sep 8, 2013 at 14:10

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

This is how I add the messages from the textarea:
c.execute("INSERT INTO messages VALUES ('%s', '%s', NULL)" % (threadinput , strip_html(newmessage)))

Well there's your problem! </adam_savage>

c.execute("INSERT INTO messages VALUES (?, ?, NULL)", (threadinput , strip_html(newmessage)))

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 8, 2013 at 14:10

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Write/Read utf-8 from sqlite3 db with Python web interface

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related