alain alain - 1 month ago 10
Python Question

Why a string in the form ####e##### is converted to float when inserted into sqlite database?

I am facing the following issue when working with a sqlite database and python.
This happens also with an external program such as SQLiteStudio, so it is not python.
Suppose to have a table containing a column of type string.
If you enter an entry to that column such as 43234e4324 (so, all the entries of the kind ####e##### give the issue), the value stored in the database is converted to inf!
For sure, since e may be interpreted as the exponent, it would make sense if the column is of type float, but it is string!

Here is a working example:

import sqlite3
a = sqlite3.connect('test.sqlite')
a.execute('CREATE TABLE test (p STRING)')
a.execute('INSERT INTO test (p) VALUES (03242e4444)')
a.commit()
b = a.execute('SELECT p FROM test')
b.fetchall()


and you get [(inf,)]...
If you insert a different value without the 'e' you would get the correct string.

I want to enter a string like 43243e3423 without it being converted. How can I do this?

Answer

A number with an e in it is a floating point number in exponential notation. 43234e3423 is the notation for 43234x103423. Since that's far too big to be stored as a floating point number, you get inf.

To enter strings, you should put them in quotes.

a.execute('INSERT INTO test (p) VALUES ("03242e4444")')

The other problem is that there's no STRING datatype. An unrecognized datatype is treated as NUMERIC, so even if you put quotes around the value, it gets converted as if it were a number. Use TEXT instead.

a.execute('CREATE TABLE test (p TEXT)')