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

Question

我有一个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,))

Answer 1

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

你需要先建立一个连接，然后使用所说的连接，你就可以跟着走了

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)

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