如果使用执行人员存在数据，请更新行

发布于 2025-02-03 02:33:04 字数 920 浏览 4 评论 0原文

我有以下SQL，旨在在数据库中创建新记录或更新现有记录。

sql = """INSERT INTO table (v1, v2, v3, v4, v5, v6)
         VALUES (?, ?, ?, ?, ?, ?)
         ON CONFLICT(v5) DO UPDATE
         SET v1 = ?, v3 = ?, v6 = ?
"""

self.curr.execute(sql,(
    v1, v2, v3, v4, v5, v6,
   # below are the values for eventual update 
    v1, v3, v6)
)

但是，这很棒，但是，我正在尝试使用executemany（）实现批处理创建，并且还将保留在冲突逻辑上。

我知道它看起来与此相似：

data = [
  ('Jane', date(2005, 2, 12)),
  ('Joe', date(2006, 5, 23)),
  ('John', date(2010, 10, 3)),
]
sql = """
INSERT INTO employees (first_name, hire_date) 
VALUES (%s, %s)
ON CONFLICT(first_name) DO UPDATE
SET date = %S
"""
cursor.executemany(sql, data)

但是，如果使用first_name hire_date） >

我已经存在数据阵列中的另外3个元组，并带有最终更新的值？

为了我的需要，我在网上找不到任何特定资源，因此将不胜感激。

原文

I have the following SQL that aims to create a new record in the DB or update an existing one.

sql = """INSERT INTO table (v1, v2, v3, v4, v5, v6)
         VALUES (?, ?, ?, ?, ?, ?)
         ON CONFLICT(v5) DO UPDATE
         SET v1 = ?, v3 = ?, v6 = ?
"""

self.curr.execute(sql,(
    v1, v2, v3, v4, v5, v6,
   # below are the values for eventual update 
    v1, v3, v6)
)

This works great, however, I am trying to implement batch creation of rows with executemany(), and also keep ON CONFLICT logic.

I know that it would look something similar to this:

data = [
  ('Jane', date(2005, 2, 12)),
  ('Joe', date(2006, 5, 23)),
  ('John', date(2010, 10, 3)),
]
sql = """
INSERT INTO employees (first_name, hire_date) 
VALUES (%s, %s)
ON CONFLICT(first_name) DO UPDATE
SET date = %S
"""
cursor.executemany(sql, data)

But I am not quite sure how to pass the columns value (hire_date) that is used for the update if a row with first_name already exists

Do I pass another 3 tuples in the data array with the values for an eventual update?

I couldn't find any specific resource on the web for my need, so any help would be appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

故事↓在人 2025-02-10 02:33:04

您可以将排除表限定符添加到列名：

data = [
  ('Jane', date(2005, 2, 12)),
  ('Joe', date(2006, 5, 23)),
  ('John', date(2010, 10, 3)),
]
sql = """
INSERT INTO employees (first_name, hire_date) 
VALUES (?, ?)
ON CONFLICT(first_name) DO UPDATE
SET hire_date = EXCLUDED.hire_date
"""
cursor.executemany(sql, data)

You can add the EXCLUDED table qualifier to the column name:

data = [
  ('Jane', date(2005, 2, 12)),
  ('Joe', date(2006, 5, 23)),
  ('John', date(2010, 10, 3)),
]
sql = """
INSERT INTO employees (first_name, hire_date) 
VALUES (?, ?)
ON CONFLICT(first_name) DO UPDATE
SET hire_date = EXCLUDED.hire_date
"""
cursor.executemany(sql, data)

回复收藏 0 原文

厌味 2025-02-10 02:33:04

由于您在SQL中有三个占位符，因此您需要绑定三个参数。因此，重复日期值。顺便说一句，sqlite3使用Qmark，？，用于第一个示例中使用的占位符，并确保commit。

data = [
    ('Jane', date(2005, 2, 12), date(2005, 2, 12)), 
    ('Joe', date(2006, 5, 23), date(2006, 5, 23)), 
    ('John', date(2010, 10, 3), date(2010, 10, 3)), 
] 

sql = """INSERT INTO employees (first_name, hire_date) 
         VALUES (?, ?) 
         ON CONFLICT(first_name) DO 
         UPDATE SET hire_date = ? 
      """

cursor.executemany(sql, data)
conn.commit()

Because you have three placeholders in SQL you need to bind three parameters. Therefore, repeat the date value. By the way, sqlite3 uses the qmark, ?, for placeholders as used in your first example and be sure to commit.

data = [
    ('Jane', date(2005, 2, 12), date(2005, 2, 12)), 
    ('Joe', date(2006, 5, 23), date(2006, 5, 23)), 
    ('John', date(2010, 10, 3), date(2010, 10, 3)), 
] 

sql = """INSERT INTO employees (first_name, hire_date) 
         VALUES (?, ?) 
         ON CONFLICT(first_name) DO 
         UPDATE SET hire_date = ? 
      """

cursor.executemany(sql, data)
conn.commit()

回复收藏 0 原文

~没有更多了~