我有一个sqlite3db:电话簿(name_id,phone_nb(。我想插入("Kyl",+33361(,如果Kyl条目还不存在,或者,如果Kyl已经存在,我想把他的电话号码更新到+33370。这被称为upstart。
SQLite upstart是:
INSERT INTO table(...)
VALUES (...)
ON CONFLICT (...) DO UPDATE SET expression
我的问题:
- 当我使用sqlite3时,上面的语句非常有效,但当我从python调用它时,它根本不起作用
- 另一方面,如果从python我使用纯
INSERT INTO table VALUES
,它就可以工作(没有ON CONFLICT
( - 此外,如果在python中我使用经典的
UPDATE table SET col WHERE condition
,它也可以工作 - 使用SQLite upstart时,我总是遇到相同的错误:
near "ON": syntax error
这是我的桌子:
CREATE TABLE phone_book (
author_id INTEGER PRIMARY KEY
UNIQUE,
short_name TEXT NOT NULL,
join_date DATE NOT NULL,
email TEXT NOT NULL,
phone_nb STRING
);
在SQL Studio中,我运行
INSERT INTO phone_book(author_id, short_name, join_date, email, phone_nb)
VALUES (13, "kyl", "2020-12-20", "kyl@domain.net", 33670668832)
ON CONFLICT(author_id) DO UPDATE SET phone_nb=excluded.phone_nb;
此插入有效。然后,当Kyl更换他的手机号码时,我更新了他的手机号码,使用相同的:
INSERT INTO phone_book(author_id, short_name, join_date, email, phone_nb)
VALUES (13, "kyl", "2020-12-20", "kyl@domain.net", 33677755231)
ON CONFLICT(author_id) DO UPDATE SET phone_nb=excluded.phone_nb;
此更新也有效。一切就绪!现在是时候从python运行所有这些了。坏消息是,当从python调用时,这个精确的语句根本不起作用。
我尝试过的所有组合:
cursor.execute(...)
cursor.executemany(...)
- 具有显式参数
- 带有"?"占位符
我总是有同样的错误:near "ON": syntax error
。我的带有"?"的非工作代码占位符:
try:
sqliteConnection = sqlite3.connect('my.db')
cursor = sqliteConnection.cursor()
#print("Connected to SQLite")
author_id = 13
short_name = "mike"
join_date = "2021-01-12"
email = "mike@domain.net"
phone_nb = "00336"
tupple = []
tupple.append((author_id, short_name, join_date, email, phone_nb))
statement_ON_CONF = """INSERT INTO phone_book(author_id, short_name, join_date, email, phone_nb)
VALUES(?,?,?,?,?)
ON CONFLICT(author_id) DO UPDATE SET phone_nb=excluded.phone_nb;"""
print("statement_ON_CONF: " + statement_ON_CONF) # check my statement
cursor.executemany(statement_ON_CONF, tupple)
sqliteConnection.commit()
except sqlite3.Error as error:
print("Failed to insert or update into sqlite table: ", error)
finally:
if (sqliteConnection):
sqliteConnection.close()
#print("The SQLite connection is closed")
另一方面,使用纯INSERT
,然后使用UPDATE
,一切都可以:我的工作代码:
try:
sqliteConnection = sqlite3.connect('my.db')
cursor = sqliteConnection.cursor()
author_id = 2
short_name = "mike"
join_date = "2021-01-12"
email = "mike@domain.net"
phone_nb = "00336"
# Insert a new entry: Mike
statement = """INSERT INTO phone_book(author_id, short_name, join_date, email, phone_nb)
VALUES(?,?,?,?,?)"""
print("statement: " + statement)
cursor.execute(statement, (author_id, short_name, join_date, email, phone_nb))
sqliteConnection.commit()
# Update Mike phone nb
phone_nb = "+3310"
statement_ON_CONF = """INSERT INTO phone_book(author_id, short_name, join_date, email, phone_nb)
VALUES(?,?,?,?,?)
ON CONFLICT(author_id) DO UPDATE SET phone_nb=excluded.phone_nb;"""
statement_UPDATE = "UPDATE phone_book SET phone_nb=? WHERE author_id=?;"
cursor.execute(statement_UPDATE, (phone_nb, author_id))
sqliteConnection.commit()
except sqlite3.Error as error:
print("Failed to insert or update into sqlite table: ", error)
finally:
if (sqliteConnection):
sqliteConnection.close()
我在Windows 7 Pro 上使用SQLite版本3.34.0 2020-12-01和python版本3.7.2rc1
有人知道为什么upstart在从python调用时总是抛出错误吗?谢谢
根据评论:
"SQLite自3.24.0版本起支持UPSERT"–forpas。
您尝试传递以下元组:
[(author_id, short_name, join_date, email, phone_nb)]
作为您声明中的VALUES
,这将导致错误,因为它无法从sqlite 中读取
试试这个:
statement_ON_CONF = """INSERT INTO phone_book(author_id, short_name, join_date, email, phone_nb)
VALUES ?
ON CONFLICT(author_id) DO UPDATE SET phone_nb=excluded.phone_nb;"""
print("statement_ON_CONF: " + statement_ON_CONF) # check my statement
cursor.executemany(statement_ON_CONF, tupple[0])