Psycopg2-在postgres中将列表字典插入表中时如何包含'Null'值？ [英] Psycopg2 - How to include 'Null' values in inserting dictionary of list to table in postgres?

问题描述

我正在解析xml文件，并将其存储到列表字典中，然后使用psycopg2将它们插入posgres的表中。但是，并不是所有的行都插入到表中（它仅插入到列表中数量最少的值中）。这是列表字典的摘要：

I'm parsing my xml files and store them to a dictionary of list, where I will insert them into table at posgres using psycopg2. However, not all rows got inserted in the tables (it only inserted to the lowest number of values in the list). Here is the snippet of the dictionary of list:

dict_songs = {'title' : ['Need You Now', 'GTFO'...], 'format': ['MP4', 'MP3'...], 'type' : ['Country Pop', 'R&B Pop'..], 'year': [2010,2018..]}

dict_movie = {'title' : ['Searching', 'Sidewalk of New York'...], 'format': ['DVD', 'Blue Ray'...], 'type' : ['Thriller', 'Romcom'..], 'year': [2018..]

当我计算字典中每个列表的长度时，会发现并非所有列表的长度都相同，例如：

When I counted the length of each list in the dictionary it come up that not all the list has the same length, for example:

for key, value in dict_songs.items():
    #print value
    print(key, len([item for item in value if item]))

# The result is:
title 300000
format 189700
type 227294
year 227094

标题将是歌曲表中的主键。当我将此字典插入postgres时，它仅显示189700条记录，而不显示300000条记录。我希望它是300000条，并将Null表示为null（无）值。 dict_movie

Title would be the primary key in the song table. When I inserted this dictionary to postgres it only shows 189700 records and not 300000. I want it to be 300000 and put Null for the null (none) values. The same goes to dict_movie

这也是我用来将字典列表插入表中的代码：

This is the code I use to insert the dict list into table:

keys = ['title', 'format', 'type','year']
insert_statement = 'insert into song_table (%s) values %s'
for t in zip(*(dict_songs[key] for key in keys)):
   cur.execute(insert_statement3, (AsIs(','.join(keys)),t))
myConnection.commit()

任何想法为何或如何解决？谢谢！

Any ideas why or how to go about this? Thank you!

推荐答案

我认为这里的问题是您不知道None / NULL值在哪里。
想象这些到列表：

I think the problem here is that you don't know where the None/NULL values are. Imagine these to lists:

dict_songs = {
  'title' : ['Need You Now', 'GTFO', 'Titletest']
  'type' : ['Country Pop', 'R&B Pop']
}

您的表可能在三个位置具有NULL值，并且列表中没有数据可以提示正确的值：

Your table could have NULL values in three positions and there is no data in the lists that could hint to the correct one:

+ -------------+-------------+-------------+-------------+
| title        | type        | type        | type        |
+--------------+-------------+-------------+-------------+
| Need You Now | Country Pop | Country Pop | NULL        |
| GTFO         | R&B Pop     | NULL        | Country Pop |
| Jinglebells  | NULL        | R&B Pop     | R&B Pop     |
+--------------+-------------+-------------+-------------+

您的列表中必须没有 值，因此您知道将 NULL 放入数据库表的位置。像这样：

You lists need to have None values, so you know where to put NULL into the database table. Like this:

dict_songs = {
  'title' : ['Need You Now', 'GTFO', 'Titletest']
  'type' : ['Country Pop', None, 'R&B Pop']
}

这篇关于Psycopg2-在postgres中将列表字典插入表中时如何包含'Null'值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！