Is there a way using SQLite and Python to write an insert statement with the columns as parameters?

Question

I am trying to clean raw json data by parsing and inserting it into a table of an sqlite db.

I have 22 columns in my table and want to find a way of looping through them so I don't need to write 22 loops which insert the data or a single column.

I have simplified the approach I am trying with the following:

import sqlite3

conn = sqlite3.connect('cdata.sqlite')
cur = conn.cursor()

column = 'name'
value = 'test'

cur.execute('''INSERT INTO COMPANY (?)
            VALUES (?)''',(column,),(value,)) 

conn.commit()
conn.close()

This doesn't work at the moment and return the error TypeError: function takes at most 2 arguments (3 given).

Does anyone know if it is possible to write an SQLite insert statement using 2 parameters like this or another way I might be able to iterate through the columns?

Column names can not be parameters in SQL insert/update/delete/... statements, but you can build the string in Python, e.g. with concatenation, formatting, f-strings, ... — Mike Scotty
– Mike Scotty, Commented Jul 27, 2021 at 12:27
Does this answer your question? Variable column name in SQL lite and Python — Mike Scotty
– Mike Scotty, Commented Jul 27, 2021 at 12:34
Example of building a statement with multiple columns: stackoverflow.com/questions/39140963 — Mike Scotty
– Mike Scotty, Commented Jul 27, 2021 at 12:35

Anil Narayanan · Accepted Answer · 2021-07-27 13:07:12Z

0

Sample as below:

import sqlite3

conn = sqlite3.connect("cdata.sqlite")
cur = conn.cursor()

column = ("name", "age")
table = f"CREATE TABLE IF NOT EXISTS COMPANY ({column[0]} text, {column[1]} text);"

cur.execute(table)

name = "hello"
age = "1"
sql_stmt = f"INSERT INTO COMPANY({column[0]},{column[1]}) VALUES ('{name}', '{age}')"

cur.execute(sql_stmt)

with conn:
    cur.execute("SELECT * FROM COMPANY")
    print(cur.fetchall())

conn.commit()
conn.close()

answered Jul 27, 2021 at 13:07

Anil Narayanan

1

Sign up to request clarification or add additional context in comments.

3 Comments

joH1 Over a year ago

Could you elaborate on the workings of your code? You shouldn't rely on passerbys to understand (possibly incorrectly), instead please comment and explain your code, it will be more appealing.

Anil Narayanan Over a year ago

I just extended the existing problem statement as raised by @buckoii and its self-explanatory and tested.

joH1 Over a year ago

For experienced Python coders knowing database programming yes, the code is readable. For any other person it probably is harder to understand. Anyway a code wall without any explanation or comment is never appealing.

Collectives™ on Stack Overflow

Is there a way using SQLite and Python to write an insert statement with the columns as parameters?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related