如何使用“ INSERT”在psycopg2连接池？ [英] How to use "INSERT" in psycopg2 connection pooling?

问题描述

我使用psycopg2在Python上连接到PostgreSQL，我想使用连接池。

I use psycopg2 to connect to PostgreSQL on Python and I want to use connection pooling.

我不知道应该怎么做，而是commit（）和rollback （）当我执行INSERT查询时。

I don't know what should I do instead commit() and rollback() when I execute INSERT query.

db = pool.SimpleConnectionPool(1, 10,host=conf_hostname,database=conf_dbname,user=conf_dbuser,password=conf_dbpass,port=conf_dbport)


# Get Cursor
@contextmanager
def get_cursor():
    con = db.getconn()
    try:
        yield con.cursor()
    finally:
        db.putconn(con)


with get_cursor() as cursor:
    cursor.execute("INSERT INTO table (fields) VALUES (values) RETURNING id") 
    id = cursor.fetchone()

如果没有commit（），我不会获得插入记录的ID。

I don't get id of inserted record without commit().

推荐答案

更新我无法测试代码，但给了我一些建议：
您在数据库中而不是在数据库中进行提交

UPDATE I can not test the code but I give you some ideas: You do the commit in connection not in db

# Get Cursor
@contextmanager
def get_cursor():
    con = db.getconn()
    try:
        yield con
    finally:
        db.putconn(con)

with get_cursor() as cursor:
    con.cursor.execute("INSERT INTO table (fields) VALUES (values) RETURNING id") 
    con.commit()
    id = cursor.fetchone()

或

# Get Cursor
@contextmanager
def get_cursor():
    con = db.getconn()
    try:
        yield con.cursor()
        con.commit()
    finally:
        db.putconn(con)


with get_cursor() as cursor:
    con.cursor.execute("INSERT INTO table (fields) VALUES (values) RETURNING id") 
    id = cursor.fetchone()

---

存在连接池，因为创建与数据库的新连接可能很昂贵，并且不避免提交或回滚。因此，您可以提交数据而没有任何问题，提交数据不会破坏连接。

---

Connection pooling exist because creating a new connection to a db can be expensive and not to avoid commits or rollbacks. So you can commit your data without any issue, committing data will not destroy the connection.

这篇关于如何使用“ INSERT”在psycopg2连接池？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！