Using Psycopg2 callproc

main.py

# -*- coding: utf-8 -*-

import psycopg2
import psycopg2.extras
from psycopg2.extensions import adapt, register_adapter, AsIs


DB_CONFIG = {
    'host': 'localhost',
    'dbname': 'dbname',
    'user': 'spam',
    'password': 'ham'
}


class Person():
    def __init__(self, name, age):
        self.name = name
        self.age = age


def adapt_person(person):
    return AsIs('%s, %s' % (adapt(person.name), adapt(person.age)))


if __name__ == '__main__':
    with psycopg2.connect(**DB_CONFIG) as db:
        try:
            with db.cursor() as cur:
                cur.execute('TRUNCATE TABLE  persons;')
            db.commit()

            with db.cursor() as cur:
                # add new data
                cur.callproc('save_person', ('ham', 10))
                cur.callproc('save_person', ['spam', 20])
                cur.callproc('save_person', {'p_name': 'piyo', 'p_age': 30})
            db.commit()

            with db.cursor() as cur:
                # mapping python types to postgreSQL types
                register_adapter(Person, adapt_person)
                cur.callproc('save_person', (Person('tom', 40),))
                cur.callproc('save_person', [Person('Hanako', 50)])
            db.commit()

            with db.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
                # fetch by name
                cur.callproc('find_persons', {'p_name': 'ham'})
                for row in cur.fetchall():
                    print('My name is {}'.format(row['name']))
                    print('{} years old.'.format(row['age']))
            db.commit()
        except Exception as e:
            print(e)
            db.rollback()