python将数据插入sqlite3表 [英] python insert data into sqlite3 table

问题描述

我正在使用 python 3.7 和 sqlite3 将数据写入我的数据库.我有这个功能

I am using python 3.7 and sqlite3 to write data to my database. I have this function

def add_item(self, item):
    stmt = "INSERT INTO users (id) VALUES (?)"
    args = (item,)
    self.conn.execute(stmt, args)
    self.conn.commit()

它工作正常，但它只允许我将数据写入 id 列.如何使此函数动态化，以便可以向任何列添加任何数据?

It works fine but it only allows me to write data to id column. How can I make this function dynamic so that I can add any data to any column?

# new variable to specify column to write to

def add_item(self, item, column):
    stmt = "INSERT INTO users (column) VALUES (?)"
    args = (item,)
    self.conn.execute(stmt, args)
    self.conn.commit()

推荐答案

您需要根据提供的列和值列表的长度动态构建列名称和值占位符列表.

You need to build the list of column names and value placeholders dynamically, based on the lengths of the lists of columns and values provided.

这可以使用 str.join 来完成，但是您可能需要处理调用者将单个字符串作为列名和单个值传递的情况.

This is can be done using str.join, however you may need to handle the case where a caller passes a single string as a column name, and a single value.

def add_item(self, values, columns):
    # Check for single column insert
    if isinstance(columns, str):
        values, columns = (values,), (columns,)
    assert len(values) == len(columns), 'Mismatch between values and columns'

    template = """INSERT INTO users ({}) VALUES ({})"""

    cols = ','.join([f'"{col}"' for col in columns])
    placeholders = ','.join(['?'] * len(values))
    stmt = template.format(cols, placeholders)

    self.conn.execute(stmt, values)
    self.conn.commit()
    return

这篇关于python将数据插入sqlite3表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！