sqlite3.OperationalError:无法识别的令牌:“01T00"；Python 日期戳 [英] sqlite3.OperationalError: unrecognized token: "01T00" Python datestamp

问题描述

我在将值插入 SQLite 数据库时遇到问题.我从挪威议会网站 data.stortinget.no 下载的数据.我得到的错误是:sqlite3.OperationalError:无法识别的令牌:01T00"

I'm experiencing a problem inserting values into a SQLite database. The data I download from the Norwegian Parliament site data.stortinget.no. The error I get is: sqlite3.OperationalError: unrecognized token: "01T00"

这里是发生错误的方法:(我知道这个摘录中的缩进错误)

Here is the method in which the error occur: (I know about the indentation error in this excerpt)

def get_perioder(cur):
DOK = "stortingsperioder"
try:
     page = urllib2.urlopen(SITE+DOK)
except:
    print "Failed to fetch item "+DOK
if page:
    tree = ElementTree.parse(page)
    root = tree.getroot()
    top = list(root)[2]
    elements = list(top)
    for el in elements:
        fra = el.find('{http://data.stortinget.no}fra').text
        per_id = el.find('{http://data.stortinget.no}id').text
        til = el.find('{http://data.stortinget.no}til').text
        print "id: %s fra: %s til: %s" % (per_id, fra, til)
        cur.execute("INSERT INTO perioder(fra, id, til) VALUES(%s,%s,%s)" % (fra, per_id, til))
else:
    print "Could not load page: "+DOK

就在 cur.execute 上方的 print 打印的消息是:编号:2009-2013 帧:2009-10-01T00:00:00 直到:2013-09-30T23:59:59整个错误跟踪是:

The message printed by the print just above cur.execute is: id: 2009-2013 fra: 2009-10-01T00:00:00 til: 2013-09-30T23:59:59 The whole error trace is:

BigMac:Stortingsdata ola$ python getBasicData.py 
id: 2009-2013 fra: 2009-10-01T00:00:00 til: 2013-09-30T23:59:59
Traceback (most recent call last):
  File "getBasicData.py", line 169, in <module>
    get_perioder(cur)
   File "getBasicData.py", line 26, in get_perioder
     cur.execute("INSERT INTO perioder(fra, id, til) VALUES(%s,%s,%s)" % (fra, per_id, til))
 sqlite3.OperationalError: unrecognized token: "01T00"

我参考了SQLite手册，似乎支持格式，所以我想知道问题出在哪里.

I referred with the SQLite manual and it seems that the format is supported, so I'm wondering where the problem come from.

推荐答案

正确的方法是使用参数化查询.
示例:

The proper way is to use a parametrized query.
Example:

cur.execute("""INSERT INTO perioder(fra, id, til) 
               VALUES (?,?,?);""", (fra, per_id, til))

每个数据库驱动程序都有一个特定的参数样式".
在 SQLite 的情况下，参数样式是 ?.

There is a specific parameter "style" for each database driver.
In the case of SQLite that parameter style is ?.

另请注意，参数值作为第二个参数传递给 execute().
使用字符串插值会使您容易受到各种引用问题(例如将您带到这里的问题)和 SQL 注入攻击的可能性.

Also note that the parameter values are passed as a second argument to execute().
Using string-interpolation leaves you vulnerable to all kinds of quoting issues (like the one that brought you here) and the possibility of SQL-injection attack.

有关更多信息，请阅读DB-API 和数据库编程维基.

For more information please read the DB-API and the database programming wiki.

这篇关于sqlite3.OperationalError:无法识别的令牌:“01T00"；Python 日期戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！