python - 如何使用 Python 将值写入 SQLite 中的选定行和列

Question

因此，我目前正在尝试使用 SQLite 数据库，并希望就将值写入数据库的特定行和列（因此单元格）的最佳方式获得一些输入。我知道如何逐行写入数据库，所以基本上每次都在数据库末尾附加一行，但我想非顺序地将数据写入数据库。

我在下面汇总了一个任意示例来说明我正在尝试通过使用苹果来做什么。在这种情况下，我在数据库中创建了两个表。第一个表将是我的 ID 表，称为apples。这将包含一个主键和两列苹果名称和种植它的农场。第二个表keyFeatures将再次包含主键，它引用苹果表中苹果的 ID，但还有一列用于味道、质地和颜色。

在下面的示例中，我只有来自农场 3 的苹果 Pink Lady 的味道、质地和颜色。我现在想在填充任何其他行之前将该信息写入相关列中表keyFeature的第 3 行。对于我的一生，我无法弄清楚如何做到这一点。我假设我需要将光标定位到正确的行，但是从文档中我不清楚如何实现这一点。我确信这是一个微不足道的问题，如果有人能指出我正确的方向，我将不胜感激！

import sqlite3

dbName = 'test.db'

################# Create the Database File ###################

# Connecting to the database file
conn = sqlite3.connect(dbName)
c = conn.cursor()

#Create the identification table with names and origin
c.execute('''CREATE TABLE apples(appleID INT PRIMARY KEY, Name TEXT, 
farmGrown TEXT)''')

#Create the table with key data
c.execute('''CREATE TABLE keyFeature(appleID INT PRIMARY KEY, taste INT, 
texture INT, Colour TEXT)''')

#Populate apples table and id in keyFeature table
AppleName = ['Granny Smith', 'Golden Delicious', 'Pink Lady', 'Russet']
appleFarmGrown = ['Farm 1', 'Farm 2', 'Farm 3', 'Farm 4']
id = []

for i in range(len(AppleName)):
    id.append(i+1)
    c = conn.cursor()
    c.execute('''INSERT INTO apples(appleID, Name, farmGrown)
                    VALUES(?,?,?)''', (id[i], AppleName[i], appleFarmGrown[i]))

    c.execute('''INSERT INTO keyFeature(appleID)
                    VALUES(?)''', (id[i],))


#Current Apple to populate row in keyFeature
appleCurrent = (('Pink Lady','Farm 3')) 

tasteCurrent = 4
textureCurrent = 5
colourCurrent = 'red'

#Find ID and write into the database

c.execute("SELECT appleID FROM apples")
appleIDDB = c.fetchall()

c.execute("SELECT name FROM apples")
nameDB = c.fetchall()

c.execute("SELECT farmGrown FROM apples")
farmGrownDB = c.fetchall()

conn.commit()
conn.close()


# I assume that if I close the connection the cursor whould be positioned at the 
# first row again but this doesn't appear to be the case
conn = sqlite3.connect(dbName)

for i in range(len(appleIDDB)):
    c = conn.cursor()

    if ((nameDB[i][0] == appleCurrent[0]) and (farmGrownDB[i][0] == appleCurrent[1])):
        idCurrent = appleIDDB[i][0]
        print("This apple matches the apple stored with id number " +str(idCurrent))
        # This writes into the fifth row of the table
        c.execute('''INSERT INTO keyFeature(taste, texture, Colour)   
                   VALUES(?,?,?)''', (tasteCurrent, textureCurrent, colourCurrent))

conn.commit()
conn.close()

标签： pythondatabasepython-3.xsqlite

Answer 1

您快到了。

诸如 sqlite 之类的关系数据库并不像电子表格中的表格。而不是具有特定顺序的行列表，您只需“一大袋行”（技术上称为一组元组），您可以按照您想要的任何方式对它们进行排序。

正如您在创建表时已经确定的那样，我们解决您的问题的方式是确保每一行都有一个允许我们识别它的键（例如您的 Apple ID）。当我们希望这个键代表另一个表中的一个 ID 时，这称为外键。所以我们只需要向表中添加一个外键（称为appleID）keyFeature，并在我们向其中添加一行时使用它。

首先，从你的第一个 for 循环中去掉这个，我们在这个阶段不需要它，桌子可以空着。

c.execute('''INSERT INTO keyFeature(appleID)
                VALUES(?)''', (id[i],))

接下来，您无需获取整个表格即可找到您想要的苹果，您只需选择您感兴趣的一个即可：

c.execute("SELECT appleID FROM apples WHERE name=? AND farm=?",("Pink Lady", "Farm 3"))
idCurrent = c.fetchone()[0]

真正的诀窍是，当向 中添加数据时keyFeature，我们必须在一个语句中插入所有数据。这样，一个新的元组（行）就被一次性创建了 ID 和所有其他信息。好像它在桌子上的“正确的地方”。

c.execute('''INSERT INTO keyFeature(appleID, taste, texture, Colour)   
                   VALUES(?,?,?,?)''', (idCurrent, tasteCurrent, textureCurrent, colourCurrent))

现在我们可以使用我们感兴趣的苹果的 ID 从 keyFeature 表中检索信息。

c.execute("SELECT taste, texture, Colour FROM keyFeature WHERE apple_id=?", (my_apple_id,))

最终完整代码：

import sqlite3

dbName = 'test.db'

################# Create the Database File ###################

# Connecting to the database file
conn = sqlite3.connect(dbName)
c = conn.cursor()

#Create the identification table with names and origin
c.execute('''CREATE TABLE apples(appleID INT PRIMARY KEY, Name TEXT, 
farmGrown TEXT)''')

#Create the table with key data
c.execute('''CREATE TABLE keyFeature(appleID INT PRIMARY KEY, taste INT, 
texture INT, Colour TEXT)''')

#Populate apples table and id in keyFeature table
AppleName = ['Granny Smith', 'Golden Delicious', 'Pink Lady', 'Russet']
appleFarmGrown = ['Farm 1', 'Farm 2', 'Farm 3', 'Farm 4']
id = []

for i in range(len(AppleName)):
    id.append(i+1)
    c = conn.cursor()
    c.execute('''INSERT INTO apples(appleID, Name, farmGrown)
                    VALUES(?,?,?)''', (id[i], AppleName[i], appleFarmGrown[i]))

#Current Apple to populate row in keyFeature
appleCurrent = ('Pink Lady','Farm 3')

tasteCurrent = 4
textureCurrent = 5
colourCurrent = 'red'

#Find ID and write into the database
c.execute("SELECT appleID FROM apples WHERE name=? AND farm=?",(appleCurrent[0], appleCurrent[1]))
idCurrent = c.fetchone()[0]

c.execute('''INSERT INTO keyFeature(appleID, taste, texture, Colour)   
                       VALUES(?,?,?,?)''', (idCurrent, tasteCurrent, textureCurrent, colourCurrent))

conn.commit()
conn.close()