国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

Postgresql Server Side Cursor

SolomonXie / 1012人閱讀

摘要:河南省鄭州市河南省鄭州市直屬

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

文章版權歸作者所有,未經允許請勿轉載,若此文章存在違規行為,您可以聯系管理員刪除。

轉載請注明本文地址:http://specialneedsforspecialkids.com/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元查看
<