时间戳

首页 > 解决方案 > Python Sqllite3：一次插入许多不同长度的列表

python-3.x - Python Sqllite3：一次插入许多不同长度的列表

问题描述

你能告诉我如何在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()

标签： python-3.xpandassqlitedatabase-designlist-manipulation

解决方案

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

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 注入的版本，如果列表有一个或零个元素，这可能会导致错误

推荐阅读