Python sqlite3:强制将参数绑定为字符串? [英] Python sqlite3: force to bind a parameter as string?

问题描述

我实际上对 Python 没有经验，但想用它来将 CSV 数据表转换为 sqlite3 db，认为 python 会很完美.我面临一个问题:有一个参数我想绑定为字符串，但如果它看起来"像一个数字，它会作为 int 存储到数据库中，删除前导零......我正在尝试处理电话号码这里...

c.execute( "CREATE TABLE foo (a text, b text)" )...strA = "069-888888" # 绑定为字符串strB = "069777777" # 绑定为 int，db 中的值为 697777777c.execute("INSERT INTO foo (a,b) 值 (?,?)", [strA, strB] )

有没有办法强制 strB 绑定为字符串?

解决方案

SQLite 可以很好地处理这种情况:

<预><代码>>>>导入 sqlite3>>>conn = sqlite3.connect('/tmp/test.db')>>>cur = conn.cursor()>>>cur.execute('CREATE TABLE foo (a text, b text)')>>>strA = "069-888888">>>strB = "069777777">>>cur.execute('INSERT INTO foo (a,b) values (?,?)', (strA, strB))>>>cur.execute('select * from foo;')<sqlite3.Cursor 对象在 0x1101c39d0>>>>cur.fetchall()[(u'069-888888', u'069777777')]

换句话说:这里没有问题.

SQLite 3 使用 类型关联 而不是固定类型，但因为您将列声明为 TEXT 即使您要插入整数数据，它仍会被转换为文本并按原样存储.

I'm not actually experienced in Python, but wanted to use it to convert a CSV data table into a sqlite3 db, thought python would be perfect. I'm facing a problem: There is a parameter I want to bind as string, but if it "looks" like a number, it is stored to database as int, removing leading zeros... I'm trying to handle phone numbers here...

c.execute( "CREATE TABLE foo (a text, b text)" )

...

strA = "069-888888" # bound as string
strB = "069777777"  # bound as int, value in db is 697777777
c.execute( "INSERT INTO foo (a,b) values (?,?)", [strA, strB] )

Is there a way to force strB to be bound as string?

解决方案

SQLite handles this case just fine:

>>> import sqlite3
>>> conn = sqlite3.connect('/tmp/test.db')
>>> cur = conn.cursor()
>>> cur.execute('CREATE TABLE foo (a text, b text)')
>>> strA = "069-888888"
>>> strB = "069777777"
>>> cur.execute('INSERT INTO foo (a,b) values (?,?)', (strA, strB))
>>> cur.execute('select * from foo;')
<sqlite3.Cursor object at 0x1101c39d0>
>>> cur.fetchall()
[(u'069-888888', u'069777777')]

In other words: There is no problem here.

SQLite 3 operates with type affinity rather than fixed types, but because you declared the columns as TEXT even if you were to insert integer data it would still be converted to text and stored as such.

这篇关于Python sqlite3:强制将参数绑定为字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！