为什么这段代码不会将csv文件中的行放入sql表中?
问题描述:
对于一个学校任务,我们需要在SQL中构建一个数据库,其中包含有关多位艺术家的信息。在作业的第一部分,我们需要将来自CSV文件的数据转储到SQL TABLE中。但是这似乎并不适用于我的代码。 TABLES在那里,代码运行时没有错误,但数据似乎没有放在TABLE中。我不知道是什么原因。为什么这段代码不会将csv文件中的行放入sql表中?
import sqlite3
import csv
artistsDb = sqlite3.connect("artists.sqlite3")
artistsDbCursor = artistsDb.cursor()
myQueryString = "CREATE TABLE artists (" +\
"artistName CHAR (256)," +\
"artistName CHAR (256)," +\
"artistsDateOfBirth CHAR(256),"+\
"artistYearsActive CHAR (256),"+\
"artistsURL CHAR (256),"+\
"artistsXNLFileName CHAR(256));"
artistsDbCursor.execute ( myQueryString)
artistsDb.commit
myFile = open ("artists.csv")
myReader = csv.reader (myFile, delimiter = ";")
myQueryString = "INSERT INTO artists VALUES (?, NULL, NULL,NULL,NULL)"
for myRowIterator in myReader:
myValues = myRowIterator
artistsDbCursor.execute(myQueryString, myValues,)
答
由于乔恩已经克莱门特提到,你需要明确地承诺使用artistsDb.commit()
交易(注意括号 - 他们都需要被调用的函数)的for
循环之后。
也有你的脚本有不少可能的改进:采用多(三引号)的字符串
- 使用上下文管理器的文件和数据库连接(以确保它们正常闭合)
- 而不是连接(为便于阅读)
- 避免无用的中介变量
- 使用meaningfull名 二进制模式
- 打开CSV文件(这是要求b Y代表正确操作csv模块)
下面是一个例子与最高分改写修正:
# XXX you had artistName defined twice, which shouldn't work...
# I assume it was a copy-paste error and fixed it
# Also you may want to add a unique key to your table, use VARCHAR
# instead of CHAR, avoid 256 chars long fields when you don't need
# that much, and use a date or datetime field for (resp.) dates and
# datetime values.
create_table_sql = """
CREATE TABLE artists (
artistName CHAR (256),
artistsDateOfBirth CHAR(256),
artistYearsActive CHAR (256),
artistsURL CHAR (256),
artistsXNLFileName CHAR(256)
);
"""
# You insert NULL values but have not declared the matching fields
# as nullable. sqlite3 might accept this (sqlite3 accepts almost
# anything) but any real SQL database will raise an error.
insert_artist_sql = "INSERT INTO artists VALUES (?, NULL, NULL,NULL,NULL)"
with sqlite3.connect("artists.sqlite3") as db:
cursor = db.cursor()
cursor.execute(create_table_sql)
db.commit() # this one is possibly useless but well
with open("artists.csv", "rb") as csvfile:
reader = csv.reader(csvfile, delimiter = ";")
for row in reader:
# 'row' IS the value already
cursor.execute(insert_artist_sql, row)
# and this was the missing part...
db.commit()
'artistsDb.commit'需要调用(虽然DDL语句 - 这是直接的) - 所以它的'artistsDb.commit()'(不包括parantheses),并且在你的for循环插入行之后,确保你再次提交以确保交易发生。 –