使用python在特定列中插入列表 [英] inserting list in specific column using python

问题描述

我有一个数据库，其中有两列 sess_id 和 data..现在我想从py到db插入一个列表，我该怎么做. 假设这是要插入列表的数据列中的列表. database_list = ['elem1'，'elem2'，'elem3'...]

i have a database where there are two column sess_id and data. Now i want to insert a list from py to db how should i do this. suppose this is the list to be inserted in data column of list. database_list= ['elem1','elem2','elem3'...]

这是已经完成但没有进展的事情:

this is what in have done but no progress:

    cursor_three=conn.cursor()
    cursor_three.execute('INSERT INTO table (data) VALUES (database_list)') 
    cursor_three.commit()  

推荐答案

您应该像这样迭代列表:

You should iterate list like this:

cursor_three=conn.cursor()
for elem in database_list:
    cursor_three.execute('INSERT INTO table (data) VALUES (%s)' % elem) 
cursor_three.commit()  

在那之后，除非声明为自动增量行，否则您的 sess_id 列将为空.例如，如果您有两个排序的数组

After that you will have sess_id column empty unless you declared it autoincrement row. If you have two sorted arrays for example

database_id_list = [212, 444, 532...]
database_data_list= ['elem212','elem444','elem532'...]

您应该使用 zip()函数同时迭代它们，如下所示:

You should use zip() function to iterate them simulteniously, like this:

for elem in zip(database_id_list, database_data_list):
    cursor_three.execute('INSERT INTO table (sess_id, data) VALUES (%s, %s)' % elem)  # elem is tuple here

请注意，此代码出于简化目的使用了字符串fromat函数(您没有注意到正在使用哪个数据库)，并且不受 SQL注入的保护.您最好使用sql派生程序的格式化程序，例如用于psycopg2:

Notice that this code is using string fromat function for simplness (you didn't noticed which database you're using) and it is not protected from SQL injections. You better use your sql deriver's formatter, for example for psycopg2:

cursor_three.execute('INSERT INTO table (data) VALUES (%s)', [elem])

这篇关于使用python在特定列中插入列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！