gpt4 book ai didi

python - 如何在 postgresql 中进行分页?

转载 作者:行者123 更新时间:2023-11-29 14:31:08 25 4
gpt4 key购买 nike

我有一个 python 脚本,我正在使用它来进行 sql 查询。问题是我的虚拟机只有 2GB 的 RAM,一些 sql 查询的 RAM 太密集,因此内核会自动终止脚本。我怎样才能使这段代码更有效地使用 RAM?我想在我的 postgres sql 代码中实现分页。我该怎么做?有谁知道一个简单的实现吗?非常感谢您的帮助!

更新代码

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
import time

outfilepath = "crtsh_output/crtsh_flat_file"

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

# DELAY = 0


def connect_to_db():
start = 0
offset = 10
flag = True
while flag:
filepath = 'forager.txt'
with open(filepath) as fp:
unique_domains = ''
try:
conn = psycopg2.connect("dbname={0} user={1} host={2}".format(DB_NAME, DB_USER, DB_HOST))
cursor = conn.cursor()
cursor.itersize = 10000
for cnt, domain_name in enumerate(fp):
print("Line {}: {}".format(cnt, domain_name))
print(domain_name)
domain_name = domain_name.rstrip()

cursor.execute('''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,))


# query db with start and offset
unique_domains = cursor.fetchall()
if not unique_domains:
flag = False
else:
# do processing with your data

pprint.pprint(unique_domains)

outfilepath = "crtsh2" + ".json"
with open(outfilepath, 'a') as outfile:
outfile.write(json.dumps(unique_domains, sort_keys=True, indent=4, default=str, ensure_ascii = False))
offset += limit


except Exception as error:
print(str(error))

if __name__ == "__main__":
connect_to_db()

最佳答案

可能是这样的:

limit = 10
offset = 0
flag = True
while flag:
# query db with start and offset, example: select * from domains limit %start% offset %offset%
unique_domains = cursor.fetchall()
if not unique_domains:
flag = False
else:
# do processing with your data
offset += limit

关于python - 如何在 postgresql 中进行分页?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/51746423/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com