Python从文本文件中获取单词并写入sqlite3 db

Question

我有一个文本文件，其中一行包含单词，例如:

一二三

四五六

我需要在数据库的单独列中获取每个“单词”。

到目前为止我想出了这段代码:

connection = sqlite3.connect("test.db")
    cursor = connection.cursor()
    sql_command = """CREATE TABLE IF NOT EXISTS test (
    id VARCHAR(20),
    second VARCHAR(20),
    third VARCHAR(20),
    fourth VARCHAR(20));"""

    cursor.execute(sql_command)
    query = "INSERT INTO test VALUES (NULL,?,?,?)"
    filename = open("test.txt", "r")
    with open("test.txt", "r") as filename:
       for data in filename:
          line = data.split()
          print(line)
          cursor.execute(query, data.split(' '))

    connection.commit()
    connection.close()

我收到以下错误消息:

提供的绑定(bind)数量不正确。当前语句使用 3，并且提供了 1 个。

如果我将代码更改为:

query =“INSERT INTO 测试值 (NULL,?,NULL,NULL)”

整行写入数据库:

1|一二三||

2|四五六||

Answer 1

我必须编写以下代码来进行测试和数据生成:

import sqlite3, os

with open("test.txt", "w") as txt:
  for i in range(10):
  txt.write("One Two Three\n")    

if not os.path.exists("test.db"):
  connection = sqlite3.connect("test.db")
else:
  os.remove("test.db")
  connection = sqlite3.connect("test.db")

您没有遍历文本文件中的行。以下是好的:

cursor = connection.cursor()
sql_command = """CREATE TABLE IF NOT EXISTS test (
id VARCHAR(20),
second VARCHAR(20),
third VARCHAR(20),
fourth VARCHAR(20));"""

cursor.execute(sql_command)
query = "INSERT INTO test VALUES (NULL,?,?,?)"

但是对于读/写，您需要进行如下更改以迭代行:

with open("test.txt", "r") as filename:
  for data in filename:
    line = data.split()
    print(line)
    if len(line) == 3:
        cursor.execute(query, data.split())

connection.commit()
connection.close()