Python操作PostgreSql数据库（其他问题整理）（四）

Python操作PostgreSql数据库（事务操作与连接池）（四）

处理学习过程中遇到的其他的问题

psycopg2

官方文档传送门： http://initd.org/psycopg/docs/index.html

关于相同游标执行execute_batch会不会影响fetchmany的结果问题

测试

from datetime import datetime

import psycopg2
from psycopg2.extras import execute_batch
from psycopg2.pool import SimpleConnectionPool

PG_SQL_LOCAL_POOL = {
    'database': 'postgres',
    'user': 'postgres',
    'password': "879662581",
    # 'host':'10.27.78.1',
    'host': 'localhost',
    'minconn': 5,
    'maxconn': 10
}

def gp_connect():
    try:
        simple_conn_pool = SimpleConnectionPool(**PG_SQL_LOCAL_POOL)
        # connect()也可以使用一个大的字符串参数,
        # 比如”host=localhost port=5432 user=postgres password=postgres dbname=test”
        # 从数据库连接池获取连接
        conn = simple_conn_pool.getconn()
        return conn
    except psycopg2.DatabaseError as e:
        print("could not connect to Greenplum server", e)

def transactionPoolControl():
    conn = gp_connect()
    starttime = datetime.now()
    cursor = conn.cursor()
    cursor.execute("select id,name,password,singal from public.member where id>0")
    while True:
        rows = cursor.fetchmany(2000)
        print('rows',len(rows))
        if not rows:
            break
        var = []
        for row in rows:
            print('id=', row[0], ',name=', row[1], ',pwd=', row[2], ',singal=', row[3], )
            var.append(row[1:])
        with conn:
            with conn.cursor() as curs:
                # curs.execute("insert into public.member(id,name,password,singal)\
                # values(207,'member0','password0','signal0') ")
                # curs.execute("insert into public.member(id,name,password,singal)\
                # values(206,'member0','password0','signal0') ")
                # data1 = [['member%d' % x, 'password%d' % x, 'signal%d' % x] for x in range(0, 20000)]
                sql1 = "insert into public.members(name,password,singal)values(%s,%s,%s)"
                # cursor.executemany(sql1, data1)
                '''
                 看看这里执行了execute_batch之后，fetch_many会不会有影响
                '''
                execute_batch(curs, sql1, var, page_size=len(rows))
                # execute_values(curs, sql1, data1, page_size=len(data1)) # execute_values只可以包含一个%s,否则抛出异常 the query contains more than one '%s' placeholder
            # print(2 + '1')
            print('----------execute_batch 执行了',len(var))
    endtime = datetime.now()
    print('使用连接池插入40000条耗时%s' % (endtime - starttime))  # 时：分：秒：毫秒
    conn.close()

if __name__ == '__main__':
    transactionPoolControl()
    """
    结论，使用相同的游标时，
    先执行 select 
    再执行 execute_batch - insert
    后面执行的insert 不会影响游标的 fetchmany结果
    所有不需要在 execute_batch 之前使用fetchall 获取所有结果后再执行 execute_batch
    """

测试结果为，使用相同的游标时，先执行select操作的结果集不会受到后面执行的execute_batch影响

测试相同游标执行2次Execute，fetchmany的结果集是否会有影响

第一次执行一次select,第二次执行insert

def test_execute():
    conn = gp_connect()
    cursor = conn.cursor()
    cursor.execute("select id,name,password,singal from public.member where id<40000")

    rows = cursor.fetchmany(2000)
    print(len(rows),)
    for i in range(10):
        print(rows[i])

    print('----------再一次执行execute语句,执行插入操作')
    cursor.execute("insert into public.member(name,password,singal) values('zzr','edg','msi')")
    rows = cursor.fetchmany(2000)
    print(len(rows),)
    for i in range(10):
        print(rows[i])

执行结果

2000
(21383, 'member0', 'password0', 'signal0')
(21384, 'member1', 'password1', 'signal1')
(21385, 'member2', 'password2', 'signal2')
(21386, 'member3', 'password3', 'signal3')
(21387, 'member4', 'password4', 'signal4')
(21388, 'member5', 'password5', 'signal5')
(21389, 'member6', 'password6', 'signal6')
(21390, 'member7', 'password7', 'signal7')
(21391, 'member8', 'password8', 'signal8')
(21392, 'member9', 'password9', 'signal9')
----------再一次执行execute语句,执行插入操作
Traceback (most recent call last):
  File "D:/personalSpace/workUtils/导入数据/测试/psycopg2_TEST.py", line 99, in <module>
    test_execute()
  File "D:/personalSpace/workUtils/导入数据/测试/psycopg2_TEST.py", line 77, in test_execute
    rows = cursor.fetchmany(2000)
psycopg2.ProgrammingError: no results to fetch

抛出了异常,可以看到执行结果受到了影响

第一次执行一次select,第二次也执行select

def test_execute():
    conn = gp_connect()
    cursor = conn.cursor()
    cursor.execute("select id,name,password,singal from public.member where id<40000")

    rows = cursor.fetchmany(2000)
    print(len(rows),)
    for i in range(10):
        print(rows[i])

    print('----------再一次执行execute语句，执行查询操作')
    cursor.execute("select id,name,password,singal from public.member where id>40000")
    rows = cursor.fetchmany(2000)
    print(len(rows),)
    for i in range(10):
        print(rows[i])

执行结果

2000
(21383, 'member0', 'password0', 'signal0')
(21384, 'member1', 'password1', 'signal1')
(21385, 'member2', 'password2', 'signal2')
(21386, 'member3', 'password3', 'signal3')
(21387, 'member4', 'password4', 'signal4')
(21388, 'member5', 'password5', 'signal5')
(21389, 'member6', 'password6', 'signal6')
(21390, 'member7', 'password7', 'signal7')
(21391, 'member8', 'password8', 'signal8')
(21392, 'member9', 'password9', 'signal9')
----------再一次执行execute语句，执行查询操作
2000
(41383, 'member0', 'password0', 'signal0')
(41384, 'member1', 'password1', 'signal1')
(41385, 'member2', 'password2', 'signal2')
(41386, 'member3', 'password3', 'signal3')
(41387, 'member4', 'password4', 'signal4')
(41388, 'member5', 'password5', 'signal5')
(41389, 'member6', 'password6', 'signal6')
(41390, 'member7', 'password7', 'signal7')
(41391, 'member8', 'password8', 'signal8')
(41392, 'member9', 'password9', 'signal9')

可以看到结果集发生了明显的变化

结论，不管是插入还是查询操作，再次执行execute，其结果集都会发生变化，这一点在使用while,fetchmany遍历结果集，并且在while中使用相同游标执行Execute时要特别注意。