当前位置: 动力学知识库 > 问答 > 编程问答 >

Inserting a number as a String into a Text column and SQLite stills removes the leading zero

问题描述:

I got the following number as a string: String numberString = "079674839";

When I insert this number into a SQLite DB, SQLite automatically removes the leading zero and stores the string as 79674839. Considering affinity and that the column stores TEXT, shouldn't SQLite store the whole string and keep the leading zero?

Thanks

网友答案:

Double-check your database schema. As documented on Datatypes in SQLite Version 3, the column type name affects how values are processed before being stored.

Here's a Python program to demonstrate, using an in-memory database:

import sqlite3

db = sqlite3.connect(':memory:')
val = "0796";
db.execute('CREATE TABLE test (i INTEGER, r REAL, t TEXT, b BLOB);')
db.execute('INSERT INTO test VALUES (?, ?, ?, ?);', (val, val, val, val))
res = db.execute('SELECT * FROM test');
print '\t'.join([x[0] for x in res.description])
for row in res.fetchall():
    print '\t'.join([repr(x) for x in row])

The output is:

i    r      t       b
796  796.0  u'0796' u'0796'

So, it looks like your column is actually an integer type. Take a look at the schema definition (sqlite3 database.db .schema works from the command line), look at the documentation again, and make sure you are using one of type names that map to TEXT affinity. Unknown type names get INTEGER affinity.

In my own case, I was using 'STR', which ends up with the default INTEGER affinity. I changed it to 'TEXT', and SQLite started respecting my leading zeros.

网友答案:

Use single quotes around the number, (i.e., '079674839') if it is anywhere in inline sql code. Also, if you're doing this programatically, make sure that you are not going through a numeric conversion.

分享给朋友:
您可能感兴趣的文章:
随机阅读: