使用自定义类型的sqlite python中的INSERT语句

5lwkijsr  于 2023-06-23  发布在  SQLite
关注(0)|答案(1)|浏览(100)

的确,我是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占位符,并在适配器中从元组交换到字典,但没有成功。
我做错了什么?
谢谢你帮我解决这个问题!

643ylb08

643ylb081#

对于多值对象,您可以采取的一种简单方法是使类的行为类似于序列类型。

class Point:
    def __init__(self, x, y):
        self.x = x
        self.y = y
    def __len__(self):
        return 2
    def __getitem__(self, index):
        return (self.x, self.y)[index]
    def __repr__(self):
        return f'Point({self.x}, {self.y})'

然后,您可以将Point对象作为SQL语句的参数“list”传递。

cursor.execute("INSERT INTO Points VALUES (?, ?)", Point(24.2, 22.1))

相关问题