Flask+Sqlite: Strings that are integers return as type int?

Question

When I store a content with an integer value as a string, when I retrive it it's suddenly an int.

So here is what I'm seeing: I store with this

db = get_db()
hello = escape(str(request.form['name'])).striptags()
what = escape(str(request.form['content'])).striptags()
db.execute('''insert into entry (
title, content, score, approved, pub_date) values (?, ?, 0, 0,?)''',
[hello,what,int(time.time())])
raise Exception('what')
db.commit()

And when I submit from the page, say form['name']='1' and form['content']='2', at the Exception, hello==u'1' and content==u'2'. This is all good so far. Then I store in the DB, but when I go to retrieve, suddenly the entries are type int 1 and 2. Everything goes correctly for any other string, say "lorem ispum".

In my sql schema, title and content are both type string. What's going on?

Answer 1

Note that in SQLite using any column type does not guarantee that this column will be permitted to store only value of this type - it can still store value of any type.

I think what is happening in your case is that your column has INTEGER or NUMERIC affinity, and in this case SQLite will normally store value of INTEGER type. However, if you try to store data that cannot be cast to INTEGER losslessly, SQLite will automatically convert it to TEXT. (Or, your column may be of TEXT affinity, but Python has somehow forced this field to store it as INTEGER if it looks like integer).

You can read more about this in SQLite documentation - scroll to 2.0 Type Affinity.