Examples: Common Operations

basic usage

import psycopg2

# key-value format
conn = psycopg2.connect(dbname="postgres", user="user", password="password", host="localhost", port=port)
# DSN format
conn = psycopg2.connect("dbname=postgres user=user password=password  host=localhost port=port")


# Create a connection object
conn=psycopg2.connect(database="postgres",user="user",password="password",host="localhost",port=port)
cur=conn.cursor() # Create a cursor object

# Creating a connection object (SSl connection)
conn = psycopg2.connect(dbname="postgres", user="user", password="password", host="localhost", port=port,
         sslmode="verify-ca", sslcert="client.crt",sslkey="client.key",sslrootcert="cacert.pem")
# Note：sslcert，sslkey，sslrootcert are the file paths of the user certificate, user private key, and root certificate，if not given，the default value is client.crt，client.key and root.crt under ~/.postgresql direcotry。


# Create table
cur.execute("CREATE TABLE student(id integer,name varchar,sex varchar);")

# Insert data
cur.execute("INSERT INTO student(id,name,sex) VALUES(%s,%s,%s)",(1,'Aspirin','M'))
cur.execute("INSERT INTO student(id,name,sex) VALUES(%s,%s,%s)",(2,'Taxol','F'))
cur.execute("INSERT INTO student(id,name,sex) VALUES(%s,%s,%s)",(3,'Dixheral','M'))

# Get results
cur.execute('SELECT * FROM student')
results=cur.fetchall()
print(results)

conn.commit()

# Close connection
cur.close()
conn.close()

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()

Warning: Unlike file objects or other resources, exiting the connection’s with block doesn’t close the connection, but only the transaction associated to it. If you want to make sure the connection is closed after a certain point, you should still use a try-catch block:

conn = psycopg2.connect(DSN)
try:
    # connection usage
finally:
    conn.close()

Using loging

import logging
import psycopg2
from psycopg2.extras import LoggingConnection

logging.basicConfig(level=logging.DEBUG) # log level
logger = logging.getLogger(__name__)

db_settings = {
    "user": "user",
    "password": "password",
    "host": "localhost",
    "database": "postgres",
    "port": port
}
conn = psycopg2.connect(connection_factory=LoggingConnection, **db_settings)
conn.initialize(logger)