Executing on the connection? - Mailing list psycopg
From | Daniele Varrazzo |
---|---|
Subject | Executing on the connection? |
Date | |
Msg-id | CA+mi_8Yc92bX3qhTqXq4LkGyn5VmQj6uRAcVebpqt398Tm4EJA@mail.gmail.com Whole thread Raw |
Responses |
Re: Executing on the connection?
Re: Executing on the connection? Re: Executing on the connection? Re: Executing on the connection? Re: Executing on the connection? Re: Executing on the connection? |
List | psycopg |
Hello, if there is a thing that people approaching psycopg find confusing is the connection/cursor duality. The connection object as a wrapper for the database connection is clear to understand for anyone who has used psql. But the cursor as a means to give command is spurious: you can give commands but all the cursors are serialised, not parallelised, on the same connection, and it isn't useful for transactions either... The only unique thing a cursor has is to hold a result and consume it; for the rest it doesn't own neither the postgres session nor the transaction. But it gives them command. Weird. You are surely familiar with the psycopg2 usage pattern: conn = psycopg2.connect(dsn) cur = conn.cursor() cur.execute(query, params): for record in cur: ... # do something The cursor() can take parameters, e.g. to create server-side "named" cursors, but most often people will use the standard client-side cursor, which is a lightweight object, little more than a wrapper for a PGresult. One little change I've made to psycopg3 cursors is to make it return "self" on execute() (it currently returns None, so it's totally unused). This allows chaining a fetch operation right after execute, so the pattern above can be reduced to: conn = psycopg3.connect(dsn) cur = conn.cursor() record = cur.execute(query, params).fetchone() # or for record in cur.execute(query, params): ... # do something And of course nastymess such as: conn.cursor().execute(query).fetchone() psycopg3.connect(dsn).cursor().execute(query).fetchone() But, taste. I'm toying with the idea of adding a 'connection.execute(query, [params])' methd, which would basically just create a cursor internally, query on it, and return it. No parameter could be passed to the cursor() call, so it could only create the most standard, client-side cursor (or whatever the default for the connection is, if there is some form of cursor_factory, which hasn't been implemented in psycopg3 yet). For anything more fancy, cursor() should be called explicitly. As a result people could use: conn = psycopg3.connect(dsn) record = conn.execute(query, params).fetchone() # or for record in conn.execute(query, params): ... # do something No other methods bloating the connection interface: no executemany(), copy(), callproc (actually there will be no callproc at all in psycopg3: postgres has no fast path for function call and too much semantics around stored procedure that a single callproc() couldn't cover). Being the cursor client-side, its close() doesn't actually do anythin apart from making it unusable, so just disposing of it without calling close() is totally safe. Thoughts? Cheers! -- Daniele