Postgresql Server Side Cursor

SolomonXie 发布于2019-07-25 10:48 / 880人阅读

摘要：河南省郑州市河南省郑州市直属

Postgresql Server Side Cursor

When a database query is executed, the Psycopg cursor usually fetches all the records returned by the backend, transferring them to the client process. If the query returned an huge amount of data, a proportionally large amount of memory will be allocated by the client.

If the dataset is too large to be practically handled on the client side, it is possible to create a server side cursor. Using this kind of cursor it is possible to transfer to the client only a controlled amount of data, so that a large dataset can be examined without keeping it entirely in memory.

Server side cursor are created in PostgreSQL using the DECLARE command and subsequently handled using MOVE, FETCH and CLOSE commands. postgresql-cursor

Cursor

Psycopg wraps the database server side cursor in named cursors. A named cursor is created using the cursor() method specifying the name parameter.

1. using DECLARE command create named cursor (note: declare must be in transaction)
isnp=# declare xxxx CURSOR WITHOUT HOLD FOR select * from citys;
ERROR:  DECLARE CURSOR can only be used in transaction blocks
isnp=# fetch xxxx;
ERROR:  cursor "xxxx" does not exist
isnp=# begin;
BEGIN
isnp=# declare xxxx CURSOR WITHOUT HOLD FOR select * from citys;
DECLARE CURSOR
isnp=# fetch xxxx;
    created_date        |        updated_date        |   id   | level |  name  | parent_id 
----------------------------+----------------------------+--------+-------+--------+-----------
 2016-09-09 15:10:47.291513 | 2016-09-09 15:10:47.291513 | 410000 |     1 | 河南省 |          
(1 row)

isnp=# fetch xxxx;
    created_date        |        updated_date        |   id   | level |  name  | parent_id 
----------------------------+----------------------------+--------+-------+--------+-----------
2016-09-12 15:10:29.192463 | 2016-09-12 15:10:29.192463 | 410100 |     2 | 郑州市 |    410000

2. using function return cursor
isnp=# create function myfunction(refcursor) returns refcursor as $$
isnp$# begin
isnp$# open $1 for select * from citys;
isnp$# return $1;
isnp$# end;
isnp$# $$
isnp-# language plpgsql;
CREATE FUNCTION
isnp=# begin;
BEGIN
isnp=# select myfunction("mycursor");
myfunction 
------------
mycursor
(1 row)

isnp=# fetch mycursor;
    created_date        |        updated_date        |   id   | level |  name  | parent_id 
----------------------------+----------------------------+--------+-------+--------+-----------
2016-09-09 15:10:47.291513 | 2016-09-09 15:10:47.291513 | 410000 |     1 | 河南省 |          
(1 row)

isnp=# fetch mycursor;
    created_date        |        updated_date        |   id   | level |  name  | parent_id 
----------------------------+----------------------------+--------+-------+--------+-----------
2016-09-12 15:10:29.192463 | 2016-09-12 15:10:29.192463 | 410100 |     2 | 郑州市 |    410000
(1 row)

isnp=# fetch mycursor;
    created_date        |        updated_date        |   id   | level | name | parent_id 
----------------------------+----------------------------+--------+-------+------+-----------
2016-09-12 15:10:29.194794 | 2016-09-12 15:10:29.194794 | 410101 |     3 | 直属 |    410100
(1 row)

Python Code

1. psycopg2 example
import psycopg2
# server side cursor via function method
connection = psycopg2.connect("dbname=isnp")
cursor = connection.cursor()
cursor.callproc("myfunction", ["xxxx"])
cursor1 = connection.cursor("xxxx")
print(cursor1.fetchmany(100))
cursor1.close()
connection.close()

2. sqlalchemy example
from sqlalchemy import engine_from_config

config = {
    "sqlalchemy.url": "postgresql:///isnp",
    "sqlalchemy.echo": True,
    "sqlalchemy.server_side_cursors": True,
}
engine = engine_from_config(config)

connection = engine.connect()
proxy_results = connection.execution_options(stream_results=True).execute("select * from citys")
print(proxy_results.fetchmany(10))

idc机房托管云服务器 cursor Side PostgreSQL postgresql.conf postgresql

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/38171.html

Postgresql Server Side Cursor

摘要：河南省郑州市河南省郑州市直属 Postgresql Server Side Cursor When a database query is executed, the Psycopg cursor usually fetches all the records returned by the backend, transferring them to the client proces...

joywek 2019-07-25 12:33 评论0 收藏0
Wire Protocol of PostgreSQL Queries in a Nutshell

摘要： I was working on a pull request to improve the performance of executemany() in asyncpg, who talks to the PostgreSQL server directly in its wire protocol (comparing to psycopg2 who uses libpq to...

陆斌 2019-07-25 12:37 评论0 收藏0
Wire Protocol of PostgreSQL Queries in a Nutshell

摘要： I was working on a pull request to improve the performance of executemany() in asyncpg, who talks to the PostgreSQL server directly in its wire protocol (comparing to psycopg2 who uses libpq to...

teren 2019-07-30 18:34 评论0 收藏0
Spring Boot MyBatis配置多种数据库

摘要：是支持配置多种数据库的，本文将介绍在中使用配置类来配置。项目的目的是，仅仅需要创建相关数据表，修改数据库的连接信息，你就可以得到一个微服务。 mybatis-config.xml是支持配置多种数据库的，本文将介绍在Spring Boot中使用配置类来配置。 1. 配置application.yml # mybatis配置 mybatis: check-config-location...

xiongzenghui 2019-08-16 15:09 评论0 收藏0
Spring整合SequoiaDB SQL

摘要：巨杉数据库支持海量分布式数据存储，提供常见开发语言驱动程序便于开发人员直接操作数据库中的数据。支持标准，巨杉套件通过扩展功能可以使用标准语句访问数据库，完成对数据库的各种操作。因此巨杉套件与集成和与集成流程相同。 1、背景 Spring在J2EE应用程序开发框架中占据重要的作用，它实现了轻量级的IoC（控制反转）和AOP（面向切面）容器框架，能够对JavaBean的生命周期进行管理，可...

snowLu 2019-07-01 15:40 评论0 收藏0