Python Postgresql complete guide

[David Raymond]

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:python-list-bounces+david.raymond=tomtom.com at python.org] On Behalf Of vishalhule24 at gmail.com
Sent: Thursday, August 23, 2018 8:59 AM
To: python-list at python.org
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