Insert multiple rows into DB with Python list of Tuples
Well, you need to construct the line:
INSERT INTO ... VALUES [1,7,3000], [1,8,3500], [1,9,3900]Try that one:
rows = [[1,7,3000], [1,8,3500], [1,9,3900]] values = ', '.join[map[str, rows]] sql = "INSERT INTO ... VALUES {}".format[values]The idiomatic way to handle this in Python is to use the executemany method of the cursor provided by the database driver that is being used.
For example, for sqlite using the sqlite3 module in the standard library
conn = sqlite3.connect['/path/to/file.db'] cursor = conn.cursor[] sql = """INSERT INTO mytable [ID, Speed, Power] VALUES [?, ?, ?]""" values = [[1,7,3000],[1,8,3500],[1,9,3900]] cursor.executemany[stmt, values]The placeholder used in the VALUES clause varies by the specific driver. The correct value can be found in the driver's documentation or by looking up the driver module's paramstyle attribute.
Using this approach instead of string interpolation / formatting or f-strings ensures that values are correctly quoted, which guards against SQL injection and other errors:
>>> conn = sqlite3.connect[':memory:'] >>> cur = conn.cursor[] >>> date = '2020-11-23' >>> # Correctly quoted input is returned as the selected value >>> cur.execute["""SELECT ? AS today""", [date,]] # >> cur.fetchone[] ['2020-11-23',] >>> # Unquoted input is evaluated as an expression! >>> cur.execute[f"""SELECT {date} AS today"""] >>> cur.fetchone[] [1986,]