摘要:河南省郑州市河南省郑州市直属
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
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)
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 When a database query is executed, the Psycopg cursor usually fetches all the records returned by the backend, transferring them to the client proces...
摘要: 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...
摘要: 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...
摘要:是支持配置多种数据库的,本文将介绍在中使用配置类来配置。项目的目的是,仅仅需要创建相关数据表,修改数据库的连接信息,你就可以得到一个微服务。 mybatis-config.xml是支持配置多种数据库的,本文将介绍在Spring Boot中使用配置类来配置。 1. 配置application.yml # mybatis配置 mybatis: check-config-location...
阅读 983·2021-11-18 13:23
阅读 686·2021-11-08 13:16
阅读 820·2021-10-11 10:58
阅读 3474·2021-09-22 15:26
阅读 1680·2021-09-08 10:42
阅读 1764·2021-09-04 16:45
阅读 1702·2019-08-30 15:54
阅读 2536·2019-08-30 13:45