Insert sqlite3 from variables

Question

I am trying to create the database, the column names of the table would come from the list:

import sqlite3

L     = ["Nom","Age","Taille"]

list2 = ["Karl", "11", "185"]

M = []

R = 0

con = sqlite3.connect(":memory:")

con.execute("CREATE TABLE master ("+",".join(L)+")")

Then Either :

for e in L:

    R += 1

    con.execute("INSERT INTO master("+",".join(L)+") VALUES (?,?,?)",list2[R-1])

or

for e in L:

    R += 1

    con.execute("INSERT INTO master(e) VALUES (?)",list2[R-1])

or

listX=[list2[0],list2[1],list2[3])

con.executemany("INSERT INTO master ("+",".join(L)+") VALUES ("+",".join(M)+")", (listX))

Answer 1

You can use the below code:

import sqlite3

con = sqlite3.connect(":memory:")

columns = ["Nom", "Age", "Taille"]

columns_str = '"' + '","'.join(columns) + '"'

con.execute(f"CREATE TABLE people ({columns_str})")

data = [

  ('Karl', 11, 185)

]

stmt = f"INSERT INTO people ({columns_str}) VALUES (?, ?, ?)"

con.executemany(stmt, data)

Don't call the table master because it may confuse later. Names like L and the list2 also don't help. Be clear in the naming your variables, name them after what they mean or contain. 

The cleaner perhaps is:

import sqlite3

con = sqlite3.connect(":memory:")

columns = ("Nom", "Age", "Taille")

con.execute("CREATE TABLE people (%s, %s, %s)" % columns)

data = [

  ('Karl', 11, 185)

]

stmt = f"INSERT INTO people (%s, %s, %s) VALUES (?, ?, ?)" % columns

con.executemany(stmt, data)

If you want to know more about the Data Science then do check out the following Data Science which will help you in understanding Data Science from scratch