的确,我是sqlite的新手,也许我只是在问一个非常琐碎的问题。
我想直接从我的python分析工具中将一些分析结果存储在sqlite表中。结果包含在一个自定义对象(一个类)中,我认为sqlite python文档中的“如何适应自定义Python类型到SQLite值”部分将是我问题的起点。
从这个例子中,我得到了这段代码,它不工作:
import sqlite3
class Point:
def __init__(self, x, y):
self.x = x
self.y = y
# define a __conform__ method to adapt the type
def __conform__(self, protocol):
if protocol == sqlite3.PrepareProtocol:
return self.x, self.y
connection = sqlite3.connect('mydb.db')
cursor = connection.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS Points (x REAL, y REAL)
""")
connection.commit()
# the insert query takes two qmark style placeholder and the __conform__ method of Point is
# actually returning a tuple with two values.
# attempt passing a tuple with 1 point to the execute method.
cursor.execute("""
INSERT INTO Points VALUES (?, ?)
""", (Point(24.2, 22.1),))
# fails with a ProgrammingError: Incorrect number of bindings supplied. The current statement uses 2, and there are 1 supplied.
# attempt to pass a tuple with 1 point to the execute many method.
cursor.executemany("""
INSERT INTO Points VALUES (?, ?)
""", (Point(24.2, 22.1),))
# fails with a ProgrammingError: parameters are of unsupported type
# attempt of passing a Point directly
cursor.execute("""
INSERT INTO Points VALUES (?, ?)
""", Point(24.2, 22.1))
# fails with a ProgrammingError: parameters are of unsupported type
# attempt to manually adapt the object
cursor.execute("""
INSERT INTO Points VALUES (?, ?)
""", Point(24.2, 22.1).__conform__(sqlite3.PrepareProtocol))
# works, but I can't believe that this is the expected behavior!
我的代码和文档中提供的代码(可以工作)的唯一区别是,我的对象提供了两个值,而不是1。
我还尝试了第二种方法,使用要注册的适配器函数,但结果相同。此外,我还尝试用namedplaceholder替换qmark占位符,并在适配器中从元组交换到字典,但没有成功。
我做错了什么?
谢谢你帮我解决这个问题!
1条答案
按热度按时间643ylb081#
对于多值对象,您可以采取的一种简单方法是使类的行为类似于序列类型。
然后,您可以将
Point
对象作为SQL语句的参数“list”传递。