python insert data into sqlite3 table

Question

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()

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()

snakecharmerb · Accepted Answer · 2020-09-12 06:14:46Z

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.

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

Autocommit is off by default, but you can set the connection's isolation_level attribute to None, or pass isolation_level=None to the connection constructor. See the docs on Controlling Transactios.

Collectives™ on Stack Overflow

python insert data into sqlite3 table

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related