PostgreSQL/性能一个通用游标或为每个查询创建 [英] PostgreSQL/performance one general cursor or create for every query

问题描述

我正在构建一个脚本来在数据库中存储一些数据.第一次使用PostgeSQL时，一切顺利并按计划进行.我在考虑PostgreSQl中Cursor的用法，以及如果我在其中一个就够了的情况下制作大量的Cursor，该怎么办.但是我不想将游标传递给我的所有SQL函数.

I am building a script to store some data in a database. First time I'm using PostgeSQL and everything goes well and as planned. I was thinking about the usage of the Cursor in PostgreSQl and what if I am making a lot of them while one is enough. But I don't want to pass the cursor to all my SQL functions.

这是我的简化示例.

dbConn, dbCurs = openDataBase(config)
doSomeThing(dbCurs, name, age, listOfJohns)

def doSomething(dbCurs, name, age, listOfPoeple):
  listOfPoeple SQLnrOfPeopleNamed(dbCurs, name, age)
  #here some fine checking if there's a list
  #room for more code. etc. etc.

def SQLnrOfPeopleNamed(dbCurs, inpName, inpAge)
  dbCurs.execute(Some SQL-thingy)
  #check and return result

dbCurs是传递给包含SQL查询的每个函数的值.现在，dbCurs是非常特定于PostgreSQL的.每当我将此数据库更改为e.q.MySQL我必须重写查询函数SQLnrOfPeople，以及与这些函数的接口.

dbCurs is the value that is passed on to every function that contains the SQL-query. Now is the dbCurs very PostgreSQL specific. Whenever I change this database to e.q. MySQL I have to rewrite the query function SQLnrOfPeople, and the interfacing to these functions.

我想遇到的情况是，我只需要重写SQL函数的功能即可.因此，我正在考虑在每个SQL函数中创建一个Cursor类并关闭它.这样将产生一个更通用的接口，该接口仅需要连接.

I want to have the situation I only have to rewrite the functionality of the SQL function. So, I was thinking about creating a Cursor class in every SQL function and close it. This will result in a more generic interface where only the connection is needed.

dbConn = openDataBase(config)
doSomeThing(dbConn, name, age, listOfJohns)

def doSomething(dbConn, name, age, listOfPoeple):
  listOfPoeple SQLnrOfPeopleNamed(dbConn, name, age)
  #here some fine checking if there's a list
  #room for more code. etc. etc.

def SQLnrOfPeopleNamed(dbConn, inpName, inpAge)
   dbCurs = dbConn.cursor()
   dbCurs.execute(Some SQL-thingy)
   #check and return result

但是我将创建并关闭更多的游标.我读了手册应该没问题，我认为这是一个很好的解决方案.但是我对此还是有点怀疑.

But I will create and close more cursors. I read in the manual that should be okay and I think this is a good solution. But I am still a bit doubtful about it.

推荐答案

通常，依次打开和关闭多个游标根本没有问题.您可以将游标基本上视为指向查询结果集的指针(并且它是指向缓存数据的一组指针).打开游标会分配一个指针，关闭游标会释放内存.

In general there's no problem at all with opening and closing multiple cursors sequentially. You can think of the cursor as basically a pointer to a query result set (and it is a set of pointers to cached data). Opening a cursor allocates a pointer and closing one frees the memory.

总的来说，我认为这里的逻辑清洁度最好，因此每个查询只有一个游标才行.

In general I think that logical cleanliness is best here so having one cursor per query is the way to go.

这篇关于PostgreSQL/性能一个通用游标或为每个查询创建的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！