SQLite3 Python会混淆列的值吗？

Question

我正在编写的程序的一部分是对股票数据进行索引。我正在开发一个函数，它接受一个字典，处理它，然后使用SQLite3将它的值插入我的索引数据库(其列与我的键匹配)。

def UpdateIndex(conn, ticker, coldict):
    # Do not change
    itable = "reference"

    #coldict format:
    #{
    #'ticker': 'some string'
    #'starttime': 'some datetime now a string',
    #'endtime': 'some datetime now a string',
    #'var1name': 'some string',
    #'var1method': 'some string',
    #'var2name': 'something here',
    #'var2method': 'something here'
    #... etc.
    #'varNname': 'varN'
    #'varNmethod': 'somestring'
    #}

    coldict['ticker'] = ticker
    exist = CheckIfRowExists(conn=conn, table=itable, ticker=ticker)

    if exist:
        UpdateRowTimes(conn=conn, tablename=itable, coldict=coldict)
    else:
        # Needs debugging
        cols = ', '.join(coldict.keys())
        values = ', '.join(['{}'.format(val) for val in coldict.values()])
        command = """INSERT INTO {} ({}) VALUES ({});""".format(itable, cols, values)
        #print(cols)
        #print(values)
        #print(command)
        c = conn.cursor()
        c.execute(command)
        conn.commit()

我看着它，它似乎是A-OK。我使用下面的代码测试它：

conn = ConnectSQL("index.db")
coldict = {"starttime": 0, "endtime": 49}
UpdateIndex(conn=conn, ticker="IBM", coldict=coldict)

当我使用打印函数来运行它时，我的输出如下所示

starttime, endtime, ticker
0, 49, IBM
INSERT INTO reference (starttime, endtime, ticker) VALUES (0, 49, IBM);
Traceback (most recent call last):
  File "/Users/dominictarro/Desktop/Code/Python/Sparro-Stock/scripts/pipe_inputs.py", line 189, in <module>
    UpdateIndex(conn=conn, ticker="IBM", coldict=coldict)
  File "/Users/dominictarro/Desktop/Code/Python/Sparro-Stock/scripts/pipe_inputs.py", line 102, in UpdateIndex
    c.execute(command)
sqlite3.OperationalError: no such column: IBM

有谁知道为什么会发生这样的事情？关于如何修复它的建议？

另外，对于任何想要确认我的数据库是否正确构建的人。

indexdbname = "index.db"
if os.path.isfile(indexdbname) == False:
    itable = "reference"
    iconn = ConnectSQL(database=indexdbname)
    ic = iconn.cursor()

    statement = """CREATE TABLE {} (
    ticker text PRIMARY KEY,
    starttime text NOT NULL,
    endtime text NOT NULL
    )""".format(itable)
    ic.execute(statement)
    iconn.close()

    print("\'{}\'' made.".format(indexdbname))
else:
    print("\'{}\'' already exists.".format(indexdbname))

Answer 1

我想通了。考虑到Klaus D.关于SQL注入的评论，并阅读了占位符，重构了我所有的查询函数以使用占位符。

def UpdateIndex(conn, ticker, coldict):
    itable = "reference"
    '''

    coldict format:
    {
    'ticker': 'some string'
    'starttime': 'some datetime now a string',
    'endtime': 'some datetime now a string',
    'var1name': 'some string',
    'var1method': 'some string',
    'var2name': 'something here',
    'var2method': 'something here'
    ...
    'varNname': 'varN'
    'varNmethod': 'somestring'
    }

    '''
    coldict['ticker'] = ticker
    exist = CheckIfRowExists(conn=conn, table=itable, ticker=ticker)

    if exist:
        UpdateRowTimes(conn=conn, tablename=itable, ticker=ticker, coldict=coldict)
    else:
        # Needs debugging
        cols = ', '.join(coldict.keys())
        qmarks = ', '.join(['?' for i in range(len(coldict))])
        command = """INSERT INTO {} ({}) VALUES ({});""".format(itable, cols, qmarks)

        c = conn.cursor()
        c.execute(command, tuple(coldict.values()))
        conn.commit()