Postgres无法在Python中获取数据 [英] Postgres fails fetching data in Python

问题描述

我正在将Python与 psycopg2 模块一起使用，以从Postgres数据库获取数据.

I am using Python with psycopg2 module to get data from Postgres database.

数据库很大(数十GB).

The database is quite large (tens of GB).

一切似乎都正常，我正在根据获取的数据创建对象.但是，在创建了约160000个对象之后，出现以下错误:

Everything appears to be working, I am creating objects from the fetched data. However, after ~160000 of created objects I get the following error:

我认为原因是数据量大，但我无法在任何地方在线搜索解决方案.我不知道使用任何代理，并且以前从未在此计算机上使用过任何代理，因为数据库位于 localhost 上.

I suppose the reason is the amount of data, but I could not get anywhere searching for a solution online. I am not aware of using any proxy and have never used any on this machine before, the database is on localhost.

推荐答案

有趣的是，这是一个本地服务器，所以我不愿意接受SQL注入"的姿态经常导致人们认为字符串内插比某种程度上更容易参数化查询.在您的情况下，其结果为:

It's interesting how often the "It's a local server so I'm not open to SQL injection" stance leads to people thinking that string interpolation is somehow easier than a parameterized query. In your case it's ended up with:

'... cookie_id = \'{}\''.format(cookie)

因此，您最终遇到了一些不太清晰的问题，并且也失败了(尽管由于特定的错误，我不知道具体如何操作).使用参数化:

So you've ended up with something that's less legible and also fails (though from the specific error I don't know exactly how). Use parameterization:

cursor.execute("SELECT user_id, created_at FROM cookies WHERE cookie_id = %s ORDER BY created_at DESC;", (cookie,))

最重要的是，始终以正确的方式进行操作.请注意，在某些情况下，您必须使用字符串插值，例如表名称:

Bottom line, do it the correct way all the time. Note, there are cases where you must use string interpolation, e.g. for table names:

cursor.execute("SELECT * FROM %s", (table_name,)) # Not valid
cursor.execute("SELECT * FROM {}".format(table_name)) # Valid

在这种情况下，如果其他人可以与代码交互，则需要采取其他预防措施.

And in those cases, you need to take other precautions if someone else can interact with the code.

这篇关于Postgres无法在Python中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！