英文:
How would I do a bulk update using Python 3 | psycopg 3.1.8?
问题
In Python 3 with psycopg ver 3.1.8, to perform a bulk update, you can use the psycopg library. Here's how you can feed your list of tuples into a single bulk update statement:
import psycopg
# Assuming you have a database connection, 'conn', and a cursor, 'cursor'
update_tuple_list = [
(1, 'ACTIVE', 'UC563', 'joe@company.com'),
(2, 'ACTIVE', 'UC921', 'bob@company.com'),
(3, 'DISABLED', 'UC983', 'pat@company.com'),
# ...
]
update_query = """
UPDATE user
SET
status = %s,
department = %s,
manager_email = %s
WHERE id = %s;
"""
# Execute the bulk update
cursor.executemany(update_query, update_tuple_list)
# Commit the changes to the database
conn.commit()
This code will update multiple rows in the "user" table using a single bulk update statement based on the list of tuples you provided.
As for your question about a more understandable way to do this, using psycopg as shown above is a common and efficient approach for bulk updates in Python when working with PostgreSQL databases. It's a good practice to use libraries like psycopg for database operations to ensure security and proper handling of queries.
If you have any further questions or need additional assistance, please feel free to ask.
英文:
In python I have a list of tuples
Each tuple has four fields, the first field is the row identifier that would be used in a where clause in a SQL update statement.
tuple: (id, status, department, manager_email)
update_tuple_list = [
(1, 'ACTIVE', 'UC563', 'joe@company.com'),
(2, 'ACTIVE', 'UC921', 'bob@company.com'),
(3, 'DISABLED', 'UC983', 'pat@company.com'),
...
]
Using Python 3, psycopg ver 3.1.8
How would I do a bulk update?
For table user, the statement would be:
update user set
status = %s,
department = %s,
manager_email = %s
where id %s; # where the value of 'id' is the first field in the tuple.
I can rearrange the tuple to put the row id id at the end of the tuple if that would help.
How do I feed the example list of tuples to a statement like the above and do a single bulk update?
Is there a better more understandable way to do this using Python 3 | psycopg Ver 3.1.8 (NOT psycopg2)
I have seen stack overflow answers involving psycopg2 and I simply do not understand the response or the solution provided there, and also I am not using psycopg2. It's just not clear.
It's very simple with a bulk insert because there is no where clause'. With a bulk update, there is a where clause to identify the row that is being updated.
I have searched on this and tried to find a simple solution using the latest python version and library version, and nothing comes up.
Any and all help to solve this would be greatly appreciated.
答案1
得分: 1
\d animals
表格 "public.animals"
列名 | 类型 | 校对规则 | 可为空 | 默认值
------------+---------------------+-----------+----------+---------
pk_animals | integer | | not null |
cond | character varying(200) | | not null |
animal | character varying(200) | | not null |
select * from animals where pk_animals in (3, 16);
pk_animals | cond | animal
------------+-------+---------
16 | fair | heron
3 | good | mole
import psycopg
con = psycopg.connect("dbname=test host=localhost user=postgres")
cur = con.cursor()
a_list = [('horse', 'fair', 16), ('lion', 'good', 3)]
cur.executemany('update animals set (animal, cond) = (%s, %s) where pk_animals = %s', a_list)
con.commit()
select * from animals where pk_animals in (3, 16);
pk_animals | cond | animal
------------+------+--------
16 | fair | horse
3 | good | lion
英文:
\d animals
Table "public.animals"
Column | Type | Collation | Nullable | Default
------------+------------------------+-----------+----------+---------
pk_animals | integer | | not null |
cond | character varying(200) | | not null |
animal | character varying(200) | | not null |
select * from animals where pk_animals in (3, 16);
pk_animals | cond | animal
------------+-------+---------
16 | fair | heron
3 | good | mole
import psycopg
con = psycopg.connect("dbname=test host=localhost user=postgres")
cur = con.cursor()
a_list = [('horse', 'fair', 16), ('lion', 'good', 3)]
cur.executemany('update animals set (animal, cond) = (%s, %s) where pk_animals = %s', a_list)
con.commit()
select * from animals where pk_animals in (3, 16);
pk_animals | cond | animal
------------+------+--------
16 | fair | horse
3 | good | lion
答案2
得分: -1
Here is the translated code snippet:
也许这取决于你对“批量更新”需要什么样的具体要求。如果你只想让所有操作都在一个事务中完成，我认为你可以使用类似以下的代码（未经测试！）。
import psycopg
update_tuple_list = [
(1, 'ACTIVE', 'UC563', 'joe@company.com'),
(2, 'ACTIVE', 'UC921', 'bob@company.com'),
(3, 'DISABLED', 'UC983', 'pat@company.com'),
...
]
with psycopg.connect(dbname="yourdb", ...) as conn:
# 打开一个游标以执行数据库操作
with conn.cursor() as cur:
for (id, status, department, manager_email) in update_tuple_list:
cur.execute("""
update user set
status = %s,
department = %s,
manager_email = %s
where id %s;
""",
(status, department, manager_email, id)
)
# 最后提交所有操作
conn.commit()
英文:
Maybe it depends what exactly you require from a "bulk update". If you'd just like everything to happen in one transaction, I think you might be good with something like this (untested!).
import psycopg
update_tuple_list = [
(1, 'ACTIVE', 'UC563', 'joe@company.com'),
(2, 'ACTIVE', 'UC921', 'bob@company.com'),
(3, 'DISABLED', 'UC983', 'pat@company.com'),
...
]
with psycopg.connect(dbname="yourdb", ...) as conn:
# Open a cursor to perform database operations
with conn.cursor() as cur:
for (id, status, department, manager_email) in update_tuple_list:
cur.execute("""
update user set
status = %s,
department = %s,
manager_email = %s
where id %s;
""",
(status, department, manager_email, id)
)
# Commit everything at the end
conn.commit()
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论