gpt4 book ai didi

python - 为 CSV Sqlite Python 脚本提供的绑定(bind)数量不正确

转载 作者:太空宇宙 更新时间:2023-11-03 19:29:44 25 4
gpt4 key购买 nike

我尝试使用 python 脚本将值插入到我的 sqlite 表中。

它工作得很好,直到我尝试添加另一个名为“信息”的列 - 然后它抛出以下错误:

You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings

所以我添加了:

conn.text_factory = str

然后我得到了这个错误:

Incorrect number of bindings supplied. The current statement uses 7, and there are 3 supplied.

我认为问题在于这个新的“信息”列包含几行文本,因此我可能错误地将其指定为“文本”。我的Python脚本代码:

import sqlite3;
from datetime import datetime, date;
import time
conn = sqlite3.connect('mynewtable.sqlite3')
conn.text_factory = str
c = conn.cursor()
c.execute('drop table if exists mynewtable')
c.execute('create table mynewtable(id integer primary key autoincrement, rank integer, placename text, information text, nooftimes integer, visit text, fav integer, year integer)')

def mysplit (string):
quote = False
retval = []
current = ""
for char in string:
if char == '"':
quote = not quote
elif char == ',' and not quote:
retval.append(current)
current = ""
else:
current += char
retval.append(current)
return retval

# Read lines from file, skipping first line
data = open("mynewtable.csv", "r").readlines()[1:]
for entry in data:
# Parse values
vals = mysplit(entry.strip())

# Insert the row!
print "Inserting %s..." % (vals[0])
sql = "insert into mynewtable values(NULL, ?, ?, ?, ?, ?, ?, ?)"
c.execute(sql, vals)

# Done!
conn.commit()

最佳答案

看来你正试图在这里重新发明轮子:)

尝试使用python的csv模块;我已经广泛使用它并且效果非常好: http://docs.python.org/library/csv.html

它与具有多行文本的格式正确的 csv 文件完美配合。

编辑:

例如,您可以直接在执行函数中使用 csv 行(列表):

import csv
for row in csv.reader(open('allnamesallyearsn.csv')):
c.execute(sql, row)

第二次编辑:

根据我的上一条评论,这是您使用 csv 模块发布的代码:

import sqlite3, csv, time
from datetime import datetime, date

conn = sqlite3.connect('mynewtable.sqlite3')
conn.text_factory = str
c = conn.cursor()
c.execute('drop table if exists mynewtable')
c.execute('create table mynewtable('
'id integer primary key autoincrement, '
'rank integer, '
'placename text, '
'information text, '
'nooftimes integer, '
'visit text, '
'fav integer, '
'year integer)')

sql_insert = "insert into mynewtable values(NULL, ?, ?, ?, ?, ?, ?, ?)"
csv_reader = csv.reader(open('mynewtable.csv', 'rb'))
csv_reader.next() # skip headers
for csv_row in csv_reader:
print "Inserting %s..." % (csv_row)
c.execute(sql_insert, csv_row)

conn.commit()

关于python - 为 CSV Sqlite Python 脚本提供的绑定(bind)数量不正确,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/6545847/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com