Looks good.
Having used psycopg2 a fair amount, here are some suggestions I have on extra
things to cover or emphasize.
-Postgres specific things like remembering to "set search_path to blargh,
public;" etc as needed before querying.
-An example case of cur.fetchone() returning None, or more importantly, showing
the error you get when you forget about that case
-Using conn.set_session(isolation_level, readonly, deferrable, autocommit) to
set up transaction behavior at the start. (Can restrict to setting only the
ones you care about by using keyword args)
-Going over some of the various caveats of autocommit on vs off
--autocommit on mode still allows transactions and rollbacks when you
explicitly start a transaction with a cur.execute("begin;")
--To end an explicit autocomit transaction you need to use
cur.execute("commit;") or cur.execute("rollback;"), you can't use conn.commit()
or conn.rollback()
--With autocommit off you'll have to make sure you've run rollback or commit to
use some commands which "cannot be run inside a transaction block" such as
vacuum
--Autocommit off starts a transaction for any query, and will leave the
transaction open until you commit it or roll it back. Thus if you run a simple
select, then walk away for 5 hours with your connection still connected, you'll
have left a transaction open on the server the whole time.
-Server side cursors: Running a select query that will result in 4 GB of data?
With a "normal" cursor, even when iterating over the cursor or using fetchmany
it will try to download the entire result set first before iterating over the
results. (Actually, the .execute() statement will fetch everything even before
you get a chance to run any of the .fetchone/many/all methods) Using a server
side cursor will let you get it in chunks rather than trying to load it all
into memory first.
--Server side cursors require autocommit off
-Enabling unicode for Python2.x so you get already decoded unicode objects back
for text, not byte strings.
psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
psycopg2.extensions.register_type(psycopg2.extensions.UNICODEARRAY)
-----Original Message-----
From: Python-list
[mailto:[email protected]] On Behalf Of
[email protected]
Sent: Thursday, August 23, 2018 8:59 AM
To: [email protected]
Subject: Python Postgresql complete guide
https://pynative.com/python-postgresql-tutorial/
I have added table of content at the start of the article
This tutorial mainly focuses on installing Psycopg2 and use its API to access
the PostgreSQL database. It then takes you through data insertion, data
retrieval, data update and data deletion, transaction management, connection
pooling and error-handling techniques to develop robust python programs with
PostgreSQL.
--
https://mail.python.org/mailman/listinfo/python-list
--
https://mail.python.org/mailman/listinfo/python-list
