我有一个Python,它在我们的所有AWS帐户(大约150个)中创建一个EC2实例列表,并将结果存储在MongoDB中。
我将mongodb集合导出到一个CSV文件中,其中包含Python熊猫模块。它的工作原理是,标题是无序的,而且我不想打印MongoDB索引。
在脚本的原始版本中(在添加数据库之前),我使用CSV模块编写文件,头是正确的:

我添加了数据库,这既是一项学习练习,也是因为它使我们所有的Amazon帐户更容易使用。
如果我查看mongo数据库中的json数据库中的集合,我将打印所有字段的顺序都是正确的:
{'_id': ObjectId('5f14f9ffa40de31278dade03'), 'AWS Account': 'jf-master-pd', 'Account Number': '123456789101', 'Name': 'usawsweb001', 'Instance ID': 'i-01e5e920b4d3d5dcb', 'AMI ID': 'ami-006219aba10688d0b', 'Volumes': 'vol-0ce8db4e071bc7229, vol-099f6d212a91121d0, vol-0bb36e343e9c01374, vol-05610645edfd02253, vol-05adc01d70d75d649', 'Private IP': '172.31.62.168', 'Public IP': 'xx.xx.xx.xx', 'Private DNS': 'ip-172-31-62-168.ec2.internal', 'Availability Zone': 'us-east-1e', 'VPC ID': 'vpc-68b1ff12', 'Type': 't2.micro', 'Key Pair Name': 'jf-timd', 'State': 'running', 'Launch Date': 'July 20 2020'}
{'_id': ObjectId('5f14f9ffa40de31278dade05'), 'AWS Account': 'jf-master-pd', 'Account Number': '123456789101', 'Name': 'usawsweb002', 'Instance ID': 'i-0b7db2bcab853ef96', 'AMI ID': 'ami-006219aba10688d0b', 'Volumes': 'vol-095a9dcf54ca97c0e, vol-0c8e96b71fbb7dfcf, vol-070c16c457f91c54e, vol-0dc1eaf2e826fa3a6, vol-0f0f157a8489ab939', 'Private IP': '172.31.63.131', 'Public IP': 'xx.xx.xx.xx', 'Private DNS': 'ip-172-31-63-131.ec2.internal', 'Availability Zone': 'us-east-1e', 'VPC ID': 'vpc-68b1ff12', 'Type': 't2.micro', 'Key Pair Name': 'jf-timd', 'State': 'running', 'Launch Date': 'July 20 2020'}
{'_id': ObjectId('5f14f9ffa40de31278dade07'), 'AWS Account': 'jf-master-pd', 'Account Number': '123456789101', 'Name': 'usawsweb003', 'Instance ID': 'i-0611acf4b6cc53b61', 'AMI ID': 'ami-006219aba10688d0b', 'Volumes': 'vol-0aa28f89f6ce50577, vol-0e37ff844e8b9c47a, vol-0d54c713ae231739c, vol-0e29df46edc814619, vol-07e0c40a8913b1d31', 'Private IP': '172.31.52.44', 'Public IP': 'xx.xx.xx.xx', 'Private DNS': 'ip-172-31-52-44.ec2.internal', 'Availability Zone': 'us-east-1e', 'VPC ID': 'vpc-68b1ff12', 'Type': 't2.micro', 'Key Pair Name': 'jf-timd', 'State': 'running', 'Launch Date': 'July 20 2020'}但是,使用巨蟒熊猫从芒果数据库出口,头是不正常的。信息对齐到正确的标题,但列完全无序:

在我的代码中,我创建了一个包含服务器信息的字典,然后将字典传递给打印Mongo集合的函数:
def list_instances(aws_account,aws_account_number, interactive, regions, show_details, instance_col):
for region in regions:
if 'gov' in aws_account and not 'admin' in aws_account:
try:
session = boto3.Session(profile_name=aws_account, region_name=region)
except botocore.exceptions.ProfileNotFound as e:
profile_missing_message = f"An exception has occurred: {e}"
account_found = 'no'
raise
else:
try:
session = boto3.Session(profile_name=aws_account, region_name=region)
account_found = 'yes'
except botocore.exceptions.ProfileNotFound as e:
profile_missing_message = f"An exception has occurred: {e}"
raise
try:
ec2 = session.client("ec2")
except Exception as e:
print(f"An exception has occurred: {e}")
message = f" Region: {region} in {aws_account}: ({aws_account_number}) "
banner(message)
print(Fore.RESET)
# Loop through the instances
try:
instance_list = ec2.describe_instances()
except Exception as e:
print(f"An exception has occurred: {e}")
for reservation in instance_list["Reservations"]:
for instance in reservation.get("Instances", []):
instance_count = instance_count + 1
launch_time = instance["LaunchTime"]
launch_time_friendly = launch_time.strftime("%B %d %Y")
tree = objectpath.Tree(instance)
block_devices = set(tree.execute('$..BlockDeviceMappings[\'Ebs\'][\'VolumeId\']'))
if block_devices:
block_devices = list(block_devices)
block_devices = str(block_devices).replace('[','').replace(']','').replace('\'','')
else:
block_devices = None
private_ips = set(tree.execute('$..PrivateIpAddress'))
if private_ips:
private_ips_list = list(private_ips)
private_ips_list = str(private_ips_list).replace('[','').replace(']','').replace('\'','')
else:
private_ips_list = None
public_ips = set(tree.execute('$..PublicIp'))
if len(public_ips) == 0:
public_ips = None
if public_ips:
public_ips_list = list(public_ips)
public_ips_list = str(public_ips_list).replace('[','').replace(']','').replace('\'','')
else:
public_ips_list = None
name = None
if 'Tags' in instance:
try:
tags = instance['Tags']
name = None
for tag in tags:
if tag["Key"] == "Name":
name = tag["Value"]
if tag["Key"] == "Engagement" or tag["Key"] == "Engagement Code":
engagement = tag["Value"]
except ValueError:
# print("Instance: %s has no tags" % instance_id)
raise
key_name = instance['KeyName'] if instance['KeyName'] else None
vpc_id = instance.get('VpcId') if instance.get('VpcId') else None
private_dns = instance['PrivateDnsName'] if instance['PrivateDnsName'] else None
ec2info[instance['InstanceId']] = {
'AWS Account': aws_account,
'Account Number': aws_account_number,
'Name': name,
'Instance ID': instance['InstanceId'],
'AMI ID': instance['ImageId'],
'Volumes': block_devices,
'Private IP': private_ips_list,
'Public IP': public_ips_list,
'Private DNS': private_dns,
'Availability Zone': instance['Placement']['AvailabilityZone'],
'VPC ID': vpc_id,
'Type': instance['InstanceType'],
'Key Pair Name': key_name,
'State': instance['State']['Name'],
'Launch Date': launch_time_friendly
}
mongo_instance_dict = {'_id': '', 'AWS Account': aws_account, "Account Number": aws_account_number, 'Name': name, 'Instance ID': instance["InstanceId"], 'AMI ID': instance['ImageId'], 'Volumes': block_devices, 'Private IP': private_ips_list, 'Public IP': public_ips_list, 'Private DNS': private_dns, 'Availability Zone': instance['Placement']['AvailabilityZone'], 'VPC ID': vpc_id, 'Type': instance["InstanceType"], 'Key Pair Name': key_name, 'State': instance["State"]["Name"], 'Launch Date': launch_time_friendly}
insert_doc(mongo_instance_dict)
mongo_export_to_file(interactive, aws_account)这是将字典插入到MongoDB中的函数:
def insert_doc(mydict):
mydb, mydb_name, instance_col = set_db()
mydict['_id'] = ObjectId()
instance_doc = instance_col.insert_one(mydict)
return instance_doc这是将MongoDB写入文件的函数:
def mongo_export_to_file():
aws_account = 'jf-master-pd'
today = datetime.today()
today = today.strftime("%m-%d-%Y")
mydb, mydb_name, instance_col = set_db()
# make an API call to the MongoDB server
cursor = instance_col.find()
# extract the list of documents from cursor obj
mongo_docs = list(cursor)
# create an empty DataFrame for storing documents
docs = pandas.DataFrame(columns=[])
# iterate over the list of MongoDB dict documents
for num, doc in enumerate(mongo_docs):
# convert ObjectId() to str
doc["_id"] = str(doc["_id"])
# get document _id from dict
doc_id = doc["_id"]
# create a Series obj from the MongoDB dict
series_obj = pandas.Series( doc, name=doc_id )
# append the MongoDB Series obj to the DataFrame obj
docs = docs.append(series_obj)
# get document _id from dict
doc_id = doc["_id"]
# Set the output file
output_dir = os.path.join('..', '..', 'output_files', 'aws_instance_list', 'csv', '')
output_file = os.path.join(output_dir, 'aws-instance-master-list-' + today +'.csv')
# export MongoDB documents to a CSV file
docs.to_csv(output_file, ",") # CSV delimited by commas这里有一个指向github中原始代码目录的链接。我们想要的文件是aws_ec2_list_instances.py和ec2_mongo.py
为什么MongoDB版本中的列和标头不正常?当从熊猫打印到文件中时,我如何摆脱mongo为ID添加的额外列?
发布于 2020-07-22 23:49:26
熊猫是一个非常灵活和宽容的图书馆,用于管理和分析数据。如果您只想在MongoDB模块达到标准时将csv集合转换为CSV文件,那么这是完全过分的,而且您使用它的方式非常低效。另一件要注意的是,直到最近,Python和Pandas都没有尝试保留数据块中项的顺序。在Python开始在3.5版本中保留顺序之前,编写代码的前提是,dict中项的顺序并不重要。只有在Python3.7中,维护dict条目的顺序才成为官方语言特性。
DataFrame是主要的Pandas数据结构,它表示一个二维数据数组。关于它的一些事情可能会令人困惑,我认为您被这样一个事实绊倒了:行和列都可以有命名的索引。一般来说,在Panda中谈论数据时," index“指的是行索引。
在您的数据中,行索引将是MongoDB _id的值,您希望将其丢弃。这很好,但它可能会让你认为“索引”意味着列。
级数通常表示一列数据。当使用dict初始化时,这些键被视为索引,即行标签,而不是列标签。您将看到DataFrames和Series之间的大多数操作将系列视为列。但是就像我说的,Pandas是灵活的,所以他们有一个DataFrame.append函数,它把一个系列赛当作一个行。
问题是,当追加一行时,Pandas希望Series将一行附加到现有列中。当Series有不存在于DataFrame中的索引(原始dict中的键)时,它将它们作为新列添加到列的末尾,正如您所看到的,它按排序顺序添加它们。这实际上是当前版本(1.0.5)中的一个错误,它可能允许使用这么长的时间而不被修复,因为dict顺序过去总是被忽略的,但是要感谢它,因为它导致了您的进一步研究。
通过将Series附加到初始空的MongoDB中,将DataFrame集合转换为DataFrame实际上效率很低。DataFrame完全能够读取您的MongoDB集合,并且可以用更少的代码编写它。
如果您需要Pandas,下面是我推荐的mongo_export_to_file版本:
def mongo_export_to_file():
today = datetime.today()
today = today.strftime("%m-%d-%Y")
_, _, instance_col = set_db()
# make an API call to the MongoDB server
mongo_docs = instance_col.find()
# Convert the mongo docs to a DataFrame
docs = pandas.DataFrame(mongo_docs)
# Discard the Mongo ID for the documents
docs.pop("_id")
# compute the output file directory and name
output_dir = os.path.join('..', '..', 'output_files', 'aws_instance_list', 'csv', '')
output_file = os.path.join(output_dir, 'aws-instance-master-list-' + today +'.csv')
# export MongoDB documents to a CSV file, leaving out the row "labels" (row numbers)
docs.to_csv(output_file, ",", index=False) # CSV delimited by commas下面是我在一个不需要Pandas的项目中使用的版本:
def mongo_export_to_file():
today = datetime.today()
today = today.strftime("%m-%d-%Y")
_, _, instance_col = set_db()
# make an API call to the MongoDB server
mongo_docs = instance_col.find()
if mongo_docs.count() == 0:
return
fieldnames = list(mongo_docs[0].keys())
fieldnames.remove('_id')
# compute the output file directory and name
output_dir = os.path.join('..', '..', 'output_files', 'aws_instance_list', 'csv', '')
output_file = os.path.join(output_dir, 'aws-instance-master-list-' + today +'.csv')
with open(output_file, 'w', newline='') as csvfile:
writer = csv.DictWriter(csvfile, fieldnames=fieldnames, extrasaction="ignore")
writer.writeheader()
writer.writerows(mongo_docs)发布于 2020-07-22 09:55:17
为什么MongoDB版本中的列和标头不正常?
如果JSON格式是正确的,那么在mongo_export_to_file()函数中就会出现问题。首先,注意输出中的列是有序的alphabetically。一个快速和肮脏的修复方法是在每个列名后附加一个字母,以保留原始顺序(AWS account -> a_AWS_account;Account Number -> b_Account _Number)。这将使代码的其余部分保持不变。
无论如何,您一定是在某个地方丢失了原来的列顺序。Python dict不一定保持原来的顺序。在@Shubham的评论之后,我会尝试两件事:
doc替换for循环第一行中的OrderedDict...
from collections import OrderedDict
...
...
# iterate over the list of MongoDB dict documents
for num, doc in enumerate(mongo_docs):
doc = OrderedDict(doc)cursor = instance_col.find()。查看光标的内容。应该有一种方法来保留JSON中的顺序。也许pymongo库(函数)中记录了它,尽管sort参数似乎没有任何效果。当从熊猫打印到文件中时,我如何摆脱mongo为ID添加的额外列?
在导出到csv格式中,添加index=False
# export MongoDB documents to a CSV file
docs.to_csv(output_file, sep=",", index=False) # CSV delimited by commas发布于 2020-07-22 21:44:54
我找到了解决方案这里。
我要做的是创建一个文件名列表,并将其应用于DF:
# export MongoDB documents to a CSV file
fieldnames = [ 'AWS Account', 'Account Number', 'Name', 'Instance ID', 'AMI ID', 'Volumes', 'Private IP', 'Public IP', 'Private DNS', 'Availability Zone', 'VPC ID', 'Type', 'Key Pair Name', 'State', 'Launch Date']
docs.to_csv(output_file, columns=fieldnames, sep=",", index=False) # CSV delimited by commashttps://stackoverflow.com/questions/62987961
复制相似问题