时间戳

首页 > 解决方案 > 如何将 postgresql 字段名输出到字典中，而不仅仅是在 python 中输出列名？

python - 如何将 postgresql 字段名输出到字典中，而不仅仅是在 python 中输出列名？

问题描述

我有一个 sql 查询，我想将 postgresql 字段名输出到字典中，而不仅仅是在 python 中输出列名？有谁知道这是怎么做到的吗？非常感谢您的帮助！

'''SELECT c.id, x509_commonName(c.certificate), x509_issuerName(c.certificate), x509_notBefore(c.certificate), x509_notAfter(c.certificate), x509_issuerName(c.certificate), x509_keyAlgorithm(c.certificate), x509_keySize(c.certificate), x509_publicKeyMD5(c.certificate), x509_publicKey(c.certificate), x509_rsaModulus(c.certificate), x509_serialNumber(c.certificate), x509_signatureHashAlgorithm(c.certificate), x509_signatureKeyAlgorithm(c.certificate), x509_subjectName(c.certificate), x509_name(c.certificate), x509_name_print(c.certificate), x509_commonName(c.certificate), x509_subjectKeyIdentifier(c.certificate), x509_extKeyUsages(c.certificate), x509_certPolicies(c.certificate), x509_canIssueCerts(c.certificate), x509_getPathLenConstraint(c.certificate), x509_altNames(c.certificate), x509_altNames_raw(c.certificate), x509_cRLDistributionPoints(c.certificate), x509_authorityInfoAccess(c.certificate), x509_print(c.certificate), x509_anyNamesWithNULs(c.certificate), x509_extensions(c.certificate), x509_tbscert_strip_ct_ext(c.certificate), x509_hasROCAFingerprint(c.certificate)
                        FROM certificate c, certificate_identity ci WHERE
                        c.id= ci.certificate_id AND ci.name_type = 'dNSName' AND lower(ci.name_value) =
                        lower(%s) AND x509_notAfter(c.certificate) > statement_timestamp()''', (domain_name,))

标签： pythonsqlpython-3.xpostgresqldictionary

解决方案

就像@mad_ 建议的那样，这应该有效：

您需要先建立连接，然后使用所述连接，您就可以跟随

SQL = '''
    SELECT c.id, x509_commonName(c.certificate) as common_name, 
          x509_issuerName(c.certificate) as issuer_name, 
          x509_notBefore(c.certificate) as not_before
    FROM certificate c, certificate_identity ci 
    WHERE c.id = ci.certificate_id 
         AND ci.name_type = 'dNSName' 
         AND lower(ci.name_value) = lower(%s) 
         AND x509_notAfter(c.certificate) > statement_timestamp()
'''
cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cursor.execute(SQL, (domain_name,))
# This will contain a list [{'id': 1, 'common_name': 'something...', ....}]
your_dict_data = cursor.fetchall()

但是，如果您只是要以某种表格格式显示它，我建议您NamedTupleCursor改用它。

cursor = conn.cursor(cursor_factory=psycopg2.extras.NamedTupleCursor)
cursor.execute(SQL, (domain_name,))

for certificate in cursor.fetchall():
    print(certificate.id)
    print(certificate.common_name)
    print(certificate.issuer_name)

我删除了你的很多列，因为浏览所有列并重命名它们会很耗时。所以..我留给你的任务

推荐阅读