如何使用 pysqlite 更新 SQLite 中的列,我首先需要从表中的其他数据获取条件值? [英] How to update a column in SQLite using pysqlite where I first needed to obtain a conditional value from other data in my table?
问题描述
因此,我在更新数据库时遇到以下问题:
So, I have the following issue when updating my database:
我的表格中有一个列,我需要将其分成三部分,因此我有以下代码:
I have a column in my table that I need to split in three essentially and so I have the following code:
with con:
cur = con.cursor()
cur.execute('SELECT Column1 FROM MainTable')
while True row = cur.fetchone()
if row == None:
break
for line in row: a, b, c= line.split('-')
print (b);
例如,它给了我第二列的值.但是,我需要使用这些值更新第二列,为此我尝试在底部添加以下内容:
which gives me the values of the second column for instance. However, I need to UPDATE that second column with those values and for this I have tried adding the following at the bottom:
cur.execute('UPDATE MainTable SET Col_2 = ?' WHERE Id = 1, (row, Id))
con.commit()
但是,由于某种原因,这运行得不好.
However, for some reason this isn't running well.
推荐答案
要更新特定行,您需要一种方法来标识该行.您似乎有一个ID"列,因此请与您要拆分的字符串一起阅读.
To update a specific row, you need a way to identify the row. You appear to have an "ID" column, so read that together with the string you want split.
SQL 语句与 Python 无关;您在 Python 中所做的是构造一个 SQL 字符串,然后将其提供给数据库以执行.(使用 ?
样式的参数可以避免字符串格式问题和 SQL 注入攻击;始终将它们用于值.)
SQL statements have nothing to do with Python; what you do in Python is to construct an SQL string that you then give to the database to execute. (Using ?
-style parameters avoids string formatting problems and SQL injection attacks; always use them for values.)
cur.execute('SELECT ID, Column1 FROM MainTable')
for id, col1 in cur.fetchall():
a, b, c = col1.split('-')
cur.execute('UPDATE MainTable SET ColA = ?, ColB = ?, ColC = ? WHERE ID = ?',
[a, b, c, id])
con.commit()
这篇关于如何使用 pysqlite 更新 SQLite 中的列,我首先需要从表中的其他数据获取条件值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!