python - 在 Python 脚本中运行时 SQL 查询返回空白输出

Question

我有一个 python 脚本，它应该循环遍历一个文本文件，并从文本文件中的每一行收集域作为参数。然后它应该使用域作为 SQL 查询中的参数。问题是当我将 domain_name 作为参数传递时，脚本生成的 JSON 输出为空。如果我直接在查询内设置 sql 查询中的domain_name 参数，则脚本会输出完美的 JSON 格式。正如您在脚本顶部 def connect_to_db() 下面看到的那样，我开始循环遍历文本文件。我不确定我的代码中的哪个位置发生了错误，我们将不胜感激!

代码

from __future__ import print_function

try:
    import psycopg2
except ImportError:
    raise ImportError('\n\033[33mpsycopg2 library missing. pip install psycopg2\033[1;m\n')
    sys.exit(1)

import re
import sys
import json
import pprint

DB_HOST = 'crt.sh'
DB_NAME = 'certwatch'
DB_USER = 'guest'


def connect_to_db():
    filepath = 'test.txt'
    with open(filepath) as fp:
        for cnt, domain_name in enumerate(fp):
            print("Line {}: {}".format(cnt, domain_name))
            print(domain_name)
            domain_name = domain_name.rstrip()

            conn = psycopg2.connect("dbname={0} user={1} host={2}".format(DB_NAME, DB_USER, DB_HOST))
            cursor = conn.cursor()
            cursor.execute(
                "SELECT c.id, x509_commonName(c.certificate), x509_issuerName(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();".format(
                    domain_name))

            unique_domains = cursor.fetchall()

        # print out the records using pretty print
        # note that the NAMES of the columns are not shown, instead just indexes.
        # for most people this isn't very useful so we'll show you how to return
        # columns as a dictionary (hash) in the next example.
            pprint.pprint(unique_domains)

            outfilepath = domain_name + ".json"
            with open(outfilepath, 'a') as outfile:
                    outfile.write(json.dumps(unique_domains, sort_keys=True, indent=4))

if __name__ == "__main__":
    connect_to_db()

Answer 1

不要使用格式来创建 SQL 语句。使用 ？占位符，然后是要插入的值的元组:

c.execute('''SELECT c.id, x509_commonName(c.certificate), 
    x509_issuerName(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(?) AND x509_notAfter(c.certificate) > statement_timestamp()''',(domain_name,))

更一般地说:

c.execute('''SELECT columnX FROM tableA where columnY = ? AND columnZ =?'''
    (desired_columnY_value,desired_columnZ_value))