主页 / dba / 问题 / 318438
Accepted
dbalucas
Asked: 2022-10-20 22:42:39 +0800 CST

如何通过脚本从外部数据源查询 30k 条目

  • 772

我有一个适用于单项检查的 SQL 查询： SELECT trans_id from schema.table where trans_id like '%<trans_id>%' 可能有更好的查询方法，但这不是重点。

数据库有大约。150k 个条目，如果 trans_id 存在，我应该检查其中的 30k 个。我面临的问题是，我不知道加入的正常方法是否有效，因为必须从中查询的 trans_id 不在数据库中（不幸的是 excel :/）。

我不允许将它们添加到数据库中以加入它们。

我的想法是创建某种我通过 psql 触发的脚本： (researched) psql -U postgres -d database -o /absolute_path/textfile.txt << EOF Query1; 查询2；询问 ....; EOF

但在我的预期中，这将导致将 30k 行的 select 语句写入 EOF 部分。我怀疑这是否有效，甚至不谈论努力。

还应将输出路由到本地文件，该文件显示：

  • trans_id 存在
  • trans_id 不存在

也许一些带有数组的循环？但我现在不知道如何。

表现首先不是我的目标。

postgresql

2 个回答

  1. VALUES您可以通过子句传递成批的值，甚至是查询中的所有 30k 值。这是查询只读服务器时的常见做法。

    查询可能如下所示：

    WITH list(pattern) as (
 values ('abc'), ('def'), ('ghi')
)
SELECT pattern,
 EXISTS (select 1 FROM tablename where trans_id like '%'||pattern||'%')
FROM list;

    请注意，模式不应包含%or _，或者在与 一起使用之前应将其转义like。与'注入VALUES子句的值相同。

    EXISTS (subquery)返回一个布尔值，将显示为t或f

    根据此列中的字符串长度和内容类型，使用它可能会更快，strpos(trans_id, pattern)>0而不是trans_id like '%'||pattern||'%'. pattern如果不包含通配符，但通过不同的算法，两者都会产生相同的结果：递归模式匹配与回溯like与Boyer-Moore-Horspool算法的strpos.

    • 1
  2. Best Answer

    Daniel Verite 的询问真的很有帮助。不幸的是，在大约 20 秒的查询运行时间后，我遇到了超时性能问题。由于我不允许调整配置我写了一个循环脚本来解决这个问题。

    这最符合我的目的：

    解决方案如下： -> 创建一个 conf 文件当然提供所有参数 -> 使用 psycopg2 通过 postgres 和 python 连接数据库 -> numpy 用于处理 I/O 文件

    import psycopg2
from psycopg2 import Error
import numpy as np
from configparser import SafeConfigParser

def get_config_parameter(conf_file,section, parameter):
    config_object = SafeConfigParser()
    config_object.read(conf_file)
 
    return str(config_object.get(section ,parameter))

dbpassword = get_config_parameter('db.conf', 'PROD ENV', 'dbusername')
dbusername = get_config_parameter('db.conf', 'PROD ENV', 'dbusername')
dbhost = get_config_parameter('db.conf', 'PROD ENV', 'dbhost')
dbname = get_config_parameter('db.conf', 'PROD ENV', 'dbname')
input_file_name = get_config_parameter('db.conf', 'PROD ENV', 'input_file_name')
output_result_file = get_config_parameter('db.conf', 'PROD ENV', 'output_result_file')
chunksize = 500


eculist = np.loadtxt(input_file_name, dtype="str")

ecu_chunks = ""
ecu_chunk_list = []

for ecu in eculist:
        ecu_chunk_list.append(f"('{ecu}'), ")



def query(dbusername, dbpassword, dbhost, dbname, query_input):
    try:
        connection = psycopg2.connect(user=dbusername, password=dbpassword, host=dbhost, database=dbname)
        # create a cursor to perform database operations
        cursor = connection.cursor()
        print("CONNECTED:", connection.get_dsn_parameters(), ": \n")

        try:
            query = f"WITH list(pattern) as (values {query_input}) SELECT pattern, EXISTS (select 1 FROM mule.transactions WHERE transaction_id LIKE '%'||pattern||'%') FROM list;"
            print('query in progress... please wait')
            cursor.execute(query)

            result = str(cursor.fetchall())

            return result

        except (Exception, Error) as error:
            print("SQL: Error while executing query:", error)

    except (Exception, Error) as error:
        print("NOT CONNECTED: Error while connection to Database:", error)

    finally:
        if connection:
            cursor.close()
            connection.close()
            print("Database connection closed!")

result = ""
out_text = ""

for i in range(0,len(ecu_chunk_list),chunksize):
    test = str(ecu_chunk_list[i:i+chunksize]).replace('", "','').replace('"','')
    length = len(test)
    test = test[1:length -3]
    result = query(dbusername,dbpassword,dbhost,dbname, test)

    out_text = out_text + result

save_file = open(output_result_file, 'w+')
save_file.write(out_text)
save_file.close()

    当然，您可以摆脱所有故障保护，但我认为它很有用。

    • 0

相关问题