#!/bin/python3 # Demonstrate using psycopg2 in a "standalone" program to test database # operations outside of a web server environment. from psycopg2 import connect from psycopg2.extras import DictCursor import sys sys.path.insert(0, '/home/kelliher/.secrets') import psqlauth db = connect(dbname=psqlauth.db, user=psqlauth.user, password=psqlauth.pw, cursor_factory=DictCursor) cur = db.cursor() # Select query on a table, yielding a list of records. # Each record is structured as a dictionary. # The records will be ordered in ascending order of the id field. print('\n\nExecuting first query') cur.execute('select * from todo order by id asc;') todos = cur.fetchall() print('\n\nEntire list of todo records\n') print (todos) print('\n\nFirst todo record, at index 0\n') print(todos[0]) print('\n\nTitle field of first todo record\n') print(todos[0]['title']) print('\n\nEach todo record\n') for rec in todos: print(rec) print('\n\nTitle field and date of each todo record\n') for rec in todos: print(rec['title'], rec['created'].date()) # Query on a second table. print('\n\nExecuting second query') cur.execute('select * from todo2 order by todoid asc;') todos = cur.fetchall() print('\n\nTitle field of each todo2 record\n') for rec in todos: print(rec['title']) print('\n\nField names of todo2 table\n') string = '| ' for field in todos[0].keys(): string += (field + ' | ') print(string) print('\n\nTodo2 table\n') for rec in todos: string = '| ' for field in rec.values(): string += (str(field) + ' | ') print(string) print('\n\nIllustrate using fetchone().\n') cur.execute('select userid from todo2;') rec = cur.fetchone() print(rec, '|', rec['userid']) print('\n\nIllustrate using a cursor object as an iterable.\n') cur.execute('select title, created from todo2 order by todoid asc;') for rec in cur: print('\n', rec, '\n', rec['title'], '|', rec['created']) cur.close() db.commit() db.close()