Python Sqllite3:一次插入许多不同长度的列表

Question

你能告诉我如何在python中插入许多列表吗？我尝试了下面的代码，但问题是当我插入数据时，它入到我拥有的每个列表的表的一个字段中。

第二个问题是：每个列表都有不同的长度，因为当我尝试在一个SQL执行中将所有列表插入在一起时，OUTPUT是List index out of range

请帮助我使用正确的代码公式。

以下是测试列表：

ListLinks = ["Link 1", "Link 2", "Link 3", "Link 4", "Link 5", "Link 6", "Link 7", "Link 8", 
"Link 9", "Link 10"]
ListNames = ["Name 1", "Name 2", "Name 3", "Name 4", "Name 5", "Name 6", "Name 7", "Name 8", 
"Name 9", "Name 10"]
ListAddress = ["Add 1", "Add 2", "Add 3", "Add 4", "Add 5", "Add 6", "Add 7", "Add 8", "Add 9", 
"Add 10"]
ListPhone = ["Phone 1", "Phone 2", "Phone 3", "Phone 4", "Phone 5", "Phone 6", "Phone 7", "Phone 
8", "Phone 9", "Phone 10"]
ListCategory = ["Cat 1", "Cat 2", "Cat 3", "Cat 4", "Cat 5", "Cat 6", "Cat 7", "Cat 8", "Cat 9", 
"Cat 10"]
ListDirection = ["Dir 1", "Dir 2", "Dir 3", "Dir 4", "Dir 5", "Dir 6", "Dir 7", "Dir 8", "Dir 
9", "Dir 10"]

这是我的代码示例：

import sqlite3
def InsertDB(listOne, listTwo, listThree, listFour, listFive, listSix):
connection = sqlite3.connect("SQLTestDB.db")
cursor = connection.cursor()
cursor.execute("""create table if not exists YellowCompanies(CompanyLink text,CompanyName text,CompanyAddress text,CompanyPhone text,CompanyCategory text,CompanyDirection text)""")
connection.commit()
for Index1 in range(len(listOne)):
DataOne = listOne[Index1]
cursor.execute("INSERT INTO YellowCompanies (CompanyLink) VALUES (?)", ((DataOne,)))
connection.commit()
for Index2 in range(len(listTwo)):
DataTwo = listTwo[Index2]
cursor.execute("INSERT INTO YellowCompanies (CompanyAddress) VALUES (?)", ((DataTwo,)))
connection.commit()
for Index3 in range(len(listThree)):
DataThree = listThree[Index3]
cursor.execute("INSERT INTO YellowCompanies (CompanyCategory) VALUES (?)", ((DataThree,)))
connection.commit()
for Index4 in range(len(listFour)):
DataFour = listFour[Index4]
cursor.execute("INSERT INTO YellowCompanies (CompanyDirection) VALUES (?)", ((DataFour,)))
connection.commit()
for Index5 in range(len(listFive)):
DataFive = listFive[Index5]
cursor.execute("INSERT INTO YellowCompanies (CompanyName) VALUES (?)", ((DataFive,)))
connection.commit()
for Index6 in range(len(listSix)):
DataSix = listSix[Index6]
cursor.execute("INSERT INTO YellowCompanies (CompanyPhone) VALUES (?)", ((DataSix,)))
connection.commit()
connection.commit()
connection.close()

Answer 1

试试这个，如果需要解释，请发表评论

import sqlite3
def InsertDB(listOne, listTwo, listThree, listFour, listFive, listSix):
connection = sqlite3.connect("SQLTestDB.db")
cursor = connection.cursor()
cursor.execute("""create table if not exists YellowCompanies(CompanyLink text,CompanyName text,CompanyAddress text,CompanyPhone text,CompanyCategory text,CompanyDirection text)""")
connection.commit()
# Zip to list of tuples
values = zip(listOne, listTwo, listThree, listFour, listFive, listSix)
# Building values for sql
sql = "INSERT INTO YellowCompanies VALUES {}".format(', '.join(map(str, values)))
cursor.execute(sql)
connection.commit()
connection.close()

如果列表来自用户输入，那么这不是阻止sql注入的版本，如果列表有一个或零个元素，这可能会导致错误