CODEWITHSUNDEEP
pythonpython-3.xsqlite
Raskaya
Raskaya

Reputation: 37

Insert sqlite3 from variables

i've been trying to create a database, the column names of my table would come from a 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))

Upvotes: 0

Views: 92

Answers (1)

Grismar
Grismar

Reputation: 31406

Check the documentation: https://docs.python.org/3.8/library/sqlite3.html

In your case:

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)

Also, probably don't call your table master - that'll get very confusing later. Names like L and list2 also don't help. Be clear in naming your variables, name them after what they mean or contain. Future you will thank you.

A little bit cleaner perhaps:

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)

Upvotes: 2

Related Questions