代码之家 › 专栏 › 技术社区 › slayedbylucifer

sqlite3.operationalerror:near“'..'”:语法错误

sqlite python

0

slayedbylucifer · 技术社区 · 6 年前

输入文件:

$ cat inputs.csv
'18-01-2019', 296.0
'18-01-2019', 296.0
'18-01-2019', 296.0

代码:

import csv
import sqlite3
import pprint

conn = sqlite3.connect('metrics.db')
c = conn.cursor()

def read_file(filename):
    with open(filename, 'r') as f:
        yield from f

for row in read_file('inputs.csv'):

    data = row.split(',')
    values = '({}, {})'.format(data[0], data[1].strip())
    print('Values are: {}'.format(values))

    try:
        query = '\'INSERT INTO metric_db VALUES (?, ?)\', {}'.format(values)
        print('Query is: {}'.format(query))
        c.execute(query)
    except sqlite3.IntegrityError as e:
        pass

conn.commit()
conn.close()

输出误差:

Values are: ('18-01-2019', 296.0)
Query is: 'INSERT INTO metric_db VALUES (?, ?)', ('18-01-2019', 296.0)
Traceback (most recent call last):
  File "write_to_db.py", line 21, in <module>
    c.execute(query)
sqlite3.OperationalError: near "'INSERT INTO metric_db VALUES (?, ?)'": syntax error

我觉得这样容易些。关于这个错误有很多这样的线索。但我还没到那里:(

1 回复 | 直到 6 年前

1

slayedbylucifer 6 年前

更改查询语句:

query = 'INSERT INTO metric_db VALUES  {}'.format(values)

编辑

要避免SQL注入并使用正确的日期格式:

import csv
import sqlite3
import pprint
from datetime import datetime

conn = sqlite3.connect('metrics.db')
c = conn.cursor()

def read_file(filename):
    with open(filename, 'r') as f:
        yield from f

for row in read_file('inputs.csv'):

    data = row.split(',')
    values = '({}, {})'.format(data[0], data[1].strip())
    print('Values are: {}'.format(values))
    date_readed = datetime.strptime(data[0], '%d-%m-%Y').strftime('%Y-%m-%d')

    try:
        query = 'INSERT INTO metric_db VALUES (?, ?)'
        c.execute(query,(date_readed,data[1],))
    except sqlite3.IntegrityError as e:
        pass

conn.commit()
conn.close()