Python sqlite3 sqlite3.operational错误：靠近“%”：语法错误？

Question

首先，在所有事情之前，我意识到这个问题是事先问过的。我花了好几个小时试图解决我的问题，但是我没有能够正确地实现一个解决方案，我仍然遇到了错误。在

我试图插入到一个表中（希望表名是动态的，但我相信这也是不允许的？）使用变量字符串。从我所做的研究来看，这似乎是不允许的/好的做法，因为这会让代码对SQL注入敞开大门。在

我已尝试将%s替换为？但它仍然返回与“？”相同的错误而不是“%？”在

这是我使用的代码。大部分都归功于jamesmills，我只是尝试使用他从csvforsqlite3insert语句中生成的语句，如果这有意义的话。在

"""csv2sql

Tool to convert CSV data files into SQL statements that
can be used to populate SQL tables. Each line of text in
the file is read, parsed and converted to SQL and output
to stdout (which can be piped).

A table to populate is given by the -t/--table option or
by the basename of the input file (if not standard input).

Fields are either given by the -f/--fields option (comma
separated) or determinted from the first row of data.
"""

__version__ = "0.4"
__author__ = "James Mills"
__date__ = "3rd February 2011"

import os
import csv
import sys
import optparse
import sqlite3

USAGE = "%prog [options] <file>"
VERSION = "%prog v" + __version__

def parse_options():
    parser = optparse.OptionParser(usage=USAGE, version=VERSION)

    parser.add_option("-t", "--table",
            action="store", type="string",
            default=None, dest="table",
            help="Specify table name (defaults to filename)")

    parser.add_option("-f", "--fields",
            action="store", type="string",
            default=None, dest="fields",
            help="Specify a list of fields (comma-separated)")

    parser.add_option("-s", "--skip",
            action="append", type="int",
            default=[], dest="skip",
            help="Specify records to skip (multiple allowed)")

    opts, args = parser.parse_args()

    if len(args) < 1:
        parser.print_help()
        raise SystemExit, 1

    return opts, args

def generate_rows(f):
    sniffer = csv.Sniffer()
    dialect = sniffer.sniff(f.readline())
    f.seek(0)

    reader = csv.reader(f, dialect)
    for line in reader:
        yield line

def main():
    opts, args = parse_options()

    filename = args[0]

    if filename == "-":
        if opts.table is None:
            print "ERROR: No table specified and stdin used."
            raise SystemExit, 1
        fd = sys.stdin
        table = opts.table
    else:
        fd = open(filename, "rU")
        if opts.table is None:
            table = os.path.splitext(filename)[0]
        else:
            table = opts.table

    rows = generate_rows(fd)

    if opts.fields:
        fields = ", ".join([x.strip() for x in opts.fields.split(",")])
    else:
        fields = ", ".join(rows.next())

    for i, row in enumerate(rows):
        if i in opts.skip:
            continue

        values = ", ".join(["\"%s\"" % x for x in row])
        print "INSERT INTO %s (%s) VALUES (%s);" % (table, fields, values)

        con = sqlite3.connect("school")
        cur = con.cursor()

        cur.executemany("INSERT INTO %s (%s) VALUES (%s);", (table, fields, values))
        con.commit()
        con.close()

if __name__ == "__main__":
    main()

以下是输出示例：

^{pr2}$

嗅探器的功能是获取列的名称和它们的值，我尝试将它们放入SQL语句中。在

我试过很多方法，但都没能想出解决办法！在

请不要打我！我对这一切都不熟悉，只是需要一点帮助！在

感谢任何帮助！在

Answer 1

请记住SQL-Injection Attack的可能性，并确保对输入进行清理，您可以这样准备查询：

if opts.fields:
    fields = ", ".join([x.strip() for x in opts.fields.split(",")])
else:
    fields = ", ".join(rows.next())

qry = "INSERT INTO %s (%s) VALUES (%s);" % (table,
                                            fields,
                                            ",".join("?"*len(rows)),)

请注意，对于SQLite中的参数替换，您需要使用?s

^{pr2}$