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

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

仅仅会批量的增删改查是不够的，我们还必须懂得使用事务操作，并使用数据库连接池，进行更高性能的操作

psycopg2

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

使用事务

使用with语句进行事务操作

官方文档实例：

with psycopg2.connect(DSN) as conn:
    with conn.cursor() as curs:
        curs.execute(SQL)

官网文档是这么说的：

Starting from version 2.5, psycopg2’s connections and cursors are context managers and can be used with the with statement:
When a connection exits the with block, if no exception has been raised by the block, the transaction is committed. In case of exception the transaction is rolled back.
When a cursor exits the with block it is closed, releasing any resource eventually associated with it. The state of the transaction is not affected.
A connection can be used in more than a with statement and each with block is effectively wrapped in a separate transaction:

conn = psycopg2.connect(DSN)

with conn:
    with conn.cursor() as curs:
        curs.execute(SQL1)

with conn:
    with conn.cursor() as curs:
        curs.execute(SQL2)

conn.close()

意思是：从2.5版本开始，pypg2可以使用with上下文进行事务管理，并且

• 当with连接中的语句发生了异常，事务就会回滚，否则就会被提交
• 当游标退出with块时，它将关闭，从而释放最终与之关联的所有资源。交易状态不受影响
• 一个连接可以在多个分离的事务中使用，互不影响

示例

def transactionControl():
    conn = psycopg2.connect(**PG_SQL_LOCAL)
    starttime = datetime.now()
    try:
        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.member(name,password,singal)values(%s,%s,%s)"
                # cursor.executemany(sql1, data1)
                execute_batch(curs, sql1, data1, page_size=len(data1))
                # execute_values(curs, sql1, data1, page_size=len(data1)) # execute_values只可以包含一个%s,否则抛出异常 the query contains more than one '%s' placeholder
            # print(2 + '1')
            print('----------with out')
        endtime = datetime.now()
        print('不使用连接池插入10000条耗时%s' % (endtime - starttime))  # 时：分：秒：毫秒
    except Exception as e:
        print(e)
        print('-------except')
        conn.close()
    finally:
        conn.close()

数据库连接池

psycopg2 提供的三种连接池

官方文档： http://initd.org/psycopg/docs/pool.html

• AbstractConnectionPool
• SimpleConnectionPool
• ThreadedConnectionPool

其中SimpleConnectionPool和ThreadedConnectionPool都是AbstractConnectionPool的子类，SimpleConnectionPool不能在多个线程之间共享，而ThreadedConnectionPool可以，这里我们主要介绍SimpleConnectionPool

示例

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()
    try:
        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.member(name,password,singal)values(%s,%s,%s)"
                # cursor.executemany(sql1, data1)
                execute_batch(curs, sql1, data1, page_size=len(data1))
                # execute_values(curs, sql1, data1, page_size=len(data1)) # execute_values只可以包含一个%s,否则抛出异常 the query contains more than one '%s' placeholder
            # print(2 + '1')
            print('----------with out')
        endtime = datetime.now()
        print('使用连接池插入10000条耗时%s' % (endtime - starttime))  # 时：分：秒：毫秒
    except Exception as e:
        print(e)
        print('-------except')
        conn.close()
    finally:
        conn.close()

经测试，使用数据库连接池之后的速度有了明显提升

if __name__ == '__main__':
    # insertManyOperate()
    # selectBulkOperate()
    # transactionControl() # 不使用连接池插入10000条耗时0:00:00.645283,不使用连接池插入20000条耗时0:00:01.128022
    transactionPoolControl() # 使用连接池插入10000条耗时0:00:00.510638,使用连接池插入20000条耗时0:00:00.950458
    # starttime = datetime.now()
    # endtime = datetime.now()