python - Sqlite 根据唯一值更新行

Question

我正在从 csv 文件在 sqlite3 中创建一个表，该文件提供了我必须使用的唯一 ID。我把它放在创建数据库的地方并完成它。我正在尝试获取另一个具有相同唯一 id 值但添加了我想要应用于数据库的信息的 csv 文件。

我想添加的 csv 文件仅适用于数据库中的许多行。我收到错误:IndexError:列表索引超出范围

Traceback (most recent call last):
File "test.py", line 62, in <module>
update_teams(x[0], x[2])
IndexError: list index out of range

我得到这个是因为 csv 到 sqlite.db 上没有相同数量的行吗？

CSV 格式:

Id,PlayerName,TeamName
641393,John Doe1,Team1
663844,John Doe2,Team1
607469,John Doe3,Team1

我的代码:

import csv
import sqlite3

# connect to sqlite database
conn = sqlite3.connect('players.db')
c = conn.cursor()

# table values
table_spec = """CREATE TABLE IF NOT EXISTS players(
                player_id INTEGER UNIQUE,
                name_last TEXT,
                name_first TEXT,
                TeamName TEXT);"""


# Create Table
def create_table():
  c.execute(table_spec)


# Takes values and inserts them into the table
def write_core_data(player_id, name_last, name_first):
    c.execute('INSERT OR IGNORE 
               INTO players ("player_id","name_last","name_first") 
               VALUES (?,?,?)', (player_id,name_last, name_first))
    conn.commit()


def update_teams(player_id, TeamName):
    c.execute('UPDATE players 
               SET TeamName=? 
               WHERE player_id=?',(TeamName, player_id))
    conn.commit()


# create_table()


with open('players_master.csv') as csvfile:
    readCSV = csv.reader(csvfile, delimiter=',')
    for x in readCSV:
        write_core_data(x[0], x[1], x[2])
        print(x[0])

with open('team_list.csv') as teamlist:
    readCSV = csv.reader(teamlist, delimiter=',')
    for x in readCSV:
        update_teams(x[0], x[2])
        print("Added team {} to {}".format(x[2], x[1]))

c.close()
conn.close()

非常感谢您的任何意见。谢谢您

Answer 1

您的 cvs 文件可能有空行或行太短。跳过空行:

for x in readCSV:
    if x:
        update_teams(x[0], x[2])

您还可以断言len(x) >= 3:

for x in readCSV:
    if len(x) < 3:
        print('Skipping', x)
    else: 
        update_teams(x[0], x[2])