错误的绑定数量：cPython 3.5 SQLite3 VS15

Question

import csv
import sqlite3

fileName = 'australianpublicholidays.csv'
accessMode = 'r'

# Create a database in RAM
holidayDatabase = sqlite3.connect(':memory:')

# Create a cursor
c = holidayDatabase.cursor()

# Create a table
c.execute('''CREATE TABLE holidays 
(date text, holidayName text, information text, moreInformation text, applicableTo text)''')

# Read the file contents in to the table
with open(fileName, accessMode) as publicHolidays :
    listOfPublicHolidays = csv.reader(publicHolidays)

    for currentRow in listOfPublicHolidays :
        for currentEntry in currentRow :
            c.execute('INSERT INTO holidays VALUES (?, ?, ?, ?, ?)', currentEntry)

# Close the database
holidayDatabase.close()

下一行

    c.execute('INSERT INTO holidays VALUES (?, ?, ?, ?, ?)', currentEntry)

是导致此错误的原因

Incorrect number of bindings supplied. The current statement uses 5, and there are 4 supplied.

Answer 1

我已通过删除嵌套for循环来更正错误

Replaced the following

# Read the file contents in to the table
with open(fileName, accessMode) as publicHolidays :
    listOfPublicHolidays = csv.reader(publicHolidays)

    for currentRow in listOfPublicHolidays :
        for currentEntry in currentRow :
            c.execute('INSERT INTO holidays VALUES (?, ?, ?, ?, ?)', currentEntry)

With the following

with open(fileName, accessMode) as publicHolidays :
    listOfPublicHolidays = csv.reader(publicHolidays)

    for currentRow in listOfPublicHolidays :
            c.execute('INSERT INTO holidays VALUES (?, ?, ?, ?, ?)', currentRow)

但是我仍然不清楚错误的原因。你知道吗

Answer 2

currentRow已经是一个序列。这是行中所有字段的列表。 如果您要打印出currentRow，您将得到如下输出（假设这是您的数据集https://data.gov.au/dataset/australian-holidays-machine-readable-dataset）：

['Date', 'Holiday Name', 'Information', 'More Information', 'Applicable To']
['20150101', "New Year's Day", "New Year's Day is the first day of the calendaryear and is celebrated each January 1st", '', 'NAT']
['20150126', 'Australia Day', 'Always celebrated on 26 January', 'http://www.australiaday.org.au/', 'NAT']
['20150302', 'Labour Day', 'Always on a Monday, creating a long weekend. It celebrates the eight-hour working day, a victory for workers in the mid-late 19th century.',http://www.commerce.wa.gov.au/labour-relations/public-holidays-western-australia', 'WA']
...

当你这么做的时候

for currentEntry in currentRow :
    c.execute('INSERT INTO holidays VALUES (?, ?, ?, ?, ?)', currentEntry)

实际上，您得到的是列表中第一个元素中所有字符的列表。 因为没有跳过标题行，所以实际上得到的是单词“Date”中的字符列表。等于4个字符并导致错误：

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current sta
tement uses 5, and there are 4 supplied.

如果使用next(listOfPublicHolidays, None)跳过标题行，如：

with open(fileName, accessMode) as publicHolidays :
    listOfPublicHolidays = csv.reader(publicHolidays)
    next(listOfPublicHolidays, None)
    for currentRow in listOfPublicHolidays :
        for currentEntry in currentRow :
        c.execute('INSERT INTO holidays VALUES (?, ?, ?, ?, ?)', currentEntry)

您将收到以下错误消息，因为currentEntry将是“20150101”中的字符列表，长度为8：

Traceback (most recent call last):
  File "holidaysorig.py", line 25, in <module>
    c.execute('INSERT INTO holidays VALUES (?, ?, ?, ?, ?)', tuple(currentEntry)
)
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 5, and there are 8 supplied.

这就是为什么移除for currentEntry in currentRow :块并将其重写为：

import csv
import sqlite3

fileName = 'australianpublicholidays.csv'
accessMode = 'r'

# Create a database in RAM
holidayDatabase = sqlite3.connect(':memory:')

# Create a cursor
c = holidayDatabase.cursor()

# Create a table
c.execute('''CREATE TABLE holidays 
(date text, holidayName text, information text, moreInformation text, applicableTo text)''')

# Read the file contents in to the table
with open(fileName, accessMode) as publicHolidays :
    listOfPublicHolidays = csv.reader(publicHolidays)
    for currentRow in listOfPublicHolidays :
        c.execute('INSERT INTO holidays VALUES (?, ?, ?, ?, ?)', currentRow)

# Close the database
holidayDatabase.close()

注意：在我的机器上，出现以下错误：

(holidays) C:\Users\eyounjo\projects\holidays>python holidaysorig.py
Traceback (most recent call last):
  File "holidaysorig.py", line 22, in <module>
    c.execute('INSERT INTO holidays VALUES (?, ?, ?, ?, ?)', currentRow)
sqlite3.ProgrammingError: You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings (like text_factory = str). It is highly recommended that you instead just switch your application to Unicode strings.

因此，我将你的剧本改写如下，以处理上述问题：

import csv, codecs
import sqlite3

# Encoding fix
def latin_1_encoder(unicode_csv_data):
    for line in unicode_csv_data:
        yield line.encode('latin-1')

fileName = 'australianpublicholidays.csv'
accessMode = 'r'

# Create a database in RAM
holidayDatabase = sqlite3.connect(':memory:')

# Create a cursor
c = holidayDatabase.cursor()

# Create a table
c.execute('''CREATE TABLE holidays 
(date text, holidayName text, information text, moreInformation text, applicableTo text)''')

# Read the file contents in to the table
# Encoding fix
with codecs.open(fileName, accessMode, encoding='latin-1') as publicHolidays :
    listOfPublicHolidays = csv.reader(latin_1_encoder(publicHolidays))
    # Skip the header row
    next(listOfPublicHolidays, None)
    entries = []
    for currentRow in listOfPublicHolidays:
        # Work-around for "You must not use 8-bit bytestrings" error
        entries.append(tuple([unicode(field, 'latin-1') for field in currentRow]))
    c.executemany('INSERT INTO holidays VALUES (?, ?, ?, ?, ?)', entries)

# Close the database
holidayDatabase.close()