资讯专栏INFORMATION COLUMN

Postgresql Server Side Cursor

SolomonXie / 982人阅读

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

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))

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

转载请注明本文地址: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 评论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...

    陆斌 评论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 评论0 收藏0
  • Spring Boot MyBatis配置多种数据库

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

    xiongzenghui 评论0 收藏0

发表评论

0条评论

最新活动
阅读需要支付1元查看
<