我正在学习在Python中创建自定义sqlite3
函数。这是我的代码:
import sqlite3
conn = sqlite3.connect("data.db")
c = conn.cursor()
query = "CREATE TABLE IF NOT EXISTS names (ID INTEGER UNIQUE PRIMARY KEY NOT NULL, name TEXT)"
c.execute(query)
query = "INSERT INTO names (name) VALUES ('foo')"
c.execute(query)
query = "INSERT INTO names (name) VALUES ('boo')"
c.execute(query)
conn.commit()
def get_ID(name):
query = "SELECT ID FROM names WHERE name = '{}'".format(name)
c.execute(query)
id = c.fetchall()
print(id)
return id
print(get_ID("foo"))
conn.create_function("GETID", 1, get_ID)
query = "SELECT GETID(?)"
c.execute(query, ("foo",))
print(c.fetchall())
c.close()
conn.close()
这是最后的结果:
[(1,)]
[(1,)]
Traceback (most recent call last):
File "D:\Sync1\Code\Python3\test.py", line 33, in <module>
c.execute(query, ("foo",))
sqlite3.OperationalError: user-defined function raised exception
为什么我的函数给予出错?
1条答案
按热度按时间jecbmhm31#
在函数中使用另一个本地游标来执行
SELECT
语句。此外,
fetchall()
返回子列表的列表(查询返回的所有行的列表),而不是标量值。相反,取
ID
和fetchone()[0]
。最后,将参数传递给任何查询的方式保持一致,最好(我的意见)使用
?
占位符: