如何使用 Sqlite3 用列表中的值更新整个列 [英] How to update entire column with values in list using Sqlite3
问题描述
我有一个本地存储在 sqlite3 数据库中的数据集.我提取了一列,执行了一些操作,现在想要替换数据库列中的所有值.我该怎么做?
列的长度和列表的长度保证相同.我只想用新值更新表.有没有一种简单的方法可以一次性完成这一切?
使用 python 2.7
编辑添加:
myList 是一个 Pandas 系列,由 dtype 'object' 的 numpy 数组支持.表格列 myCol 是文本格式.
In [1]: curr.execute('UPDATE test SET myCol= ?', myList)---------------------------------------------------------------------------ProgrammingError Traceback(最近一次调用最后一次)f:\python\在 <module>()---->1 curr.execute('UPDATE test SET myCol = ?', myList)ProgrammingError: 提供的绑定数量不正确.当前语句使用 1,并且提供了 401125.
curr.executemany('UPDATE test SET myCol= ?', myList)
然而,myList
必须在这里是一个元组序列.如果不是,生成器表达式将创建这些元组:
curr.executemany('UPDATE test SET myCol= ?', ((val,) for val in myList))
演示(带插入):
<预><代码>>>>导入 sqlite3>>>conn=sqlite3.connect(':memory:')>>>conn.execute('创建表测试(myCol)')<sqlite3.Cursor 对象在 0x10542f1f0>>>>conn.commit()>>>myList = ('foo', 'bar', '垃圾邮件')>>>conn.executemany('INSERT into test values (?)', ((val,) for val in myList))<sqlite3.Cursor 对象在 0x10542f180>>>>list(conn.execute('select * from test'))[(u'foo',), (u'bar',), (u'spam',)]请注意,对于 UPDATE
语句,您必须有一个 WHERE
语句来标识哪一行更新.没有 WHERE
过滤器的 UPDATE
将更新所有行.
确保您的数据有一个行标识符;在这种情况下,使用命名参数可能更容易:
my_data = ({id=1, value='foo'}, {id=2, value='bar'})cursor.executemany('UPDATE test SET myCol=:value WHERE rowId=:id', my_data)
I have a dataset stored locally in an sqlite3 database. I extracted a column, performed some operations and now want to replace ALL of the values in the database column. How can I do this?
The length of the column and the list are guaranteed to be the same length. I simply want to update the table with the new values. Is there an easy way to do this all at once?
Using python 2.7
Edited to add:
myList is a pandas series backed by a numpy array of dtype 'object'. The table column, myCol is text formatted.
In [1]: curr.execute('UPDATE test SET myCol= ?', myList)
---------------------------------------------------------------------------
ProgrammingError Traceback (most recent call last)
f:\python\<ipython-input-52-ea41c426502a> in <module>()
----> 1 curr.execute('UPDATE test SET myCol = ?', myList)
ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 401125 supplied.
Use the .executemany()
method instead:
curr.executemany('UPDATE test SET myCol= ?', myList)
However, myList
must be a sequence of tuples here. If it is not, a generator expression will do to create these tuples:
curr.executemany('UPDATE test SET myCol= ?', ((val,) for val in myList))
Demonstration (with insertions):
>>> import sqlite3
>>> conn=sqlite3.connect(':memory:')
>>> conn.execute('CREATE TABLE test (myCol)')
<sqlite3.Cursor object at 0x10542f1f0>
>>> conn.commit()
>>> myList = ('foo', 'bar', 'spam')
>>> conn.executemany('INSERT into test values (?)', ((val,) for val in myList))
<sqlite3.Cursor object at 0x10542f180>
>>> list(conn.execute('select * from test'))
[(u'foo',), (u'bar',), (u'spam',)]
Note that for an UPDATE
statement, you have to have a WHERE
statement to identify what row to update. An UPDATE
without a WHERE
filter will update all rows.
Make sure your data has a row identifier to go with it; in this case it may be easier to use named parameters instead:
my_data = ({id=1, value='foo'}, {id=2, value='bar'})
cursor.executemany('UPDATE test SET myCol=:value WHERE rowId=:id', my_data)
这篇关于如何使用 Sqlite3 用列表中的值更新整个列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!