pgcrud 0.0.5

A package to perform Postgres CRUD operations

pgcrud

Leverage python lists and dicts to more dynamically perform CRUD operations on postgres databases.

installation

pip install pgcrud

connect

from pgcrud.connector import Connector

conn = Connector(host, dbname, user, password)

create()

Pass in the name of the table and a dictionary containing the database field name as the key and the value to set as the value.

from pgcrud.connector import Connector

conn = Connector(host, dbname, user, password)
data = {'firstname': 'Fred', 'lastname': 'Jones'}
conn.create('contact', data)

criteria

The update, delete and select functions are frequently used with criteria.

To pass the criteria replace all of the criteria values with %s and pass a list of the values in the order they are used in the criteria. This enables the psycopg2 connector to makes sure the values are database safe and formatted appropriately based on their type.

IN criteria should be passed in with one %s where they should go and a list entry in the values.

from pgcrud.connector import Connector

conn = Connector(host, dbname, user, password)
fields = ['firstname', 'lastname']
criteria = 'WHERE country = %s AND state in %s'
values = ['United States', ['MA', 'MI', 'NY']]
results = conn.select('contact', fields, criteria, values)

This would form the SQL:

SELECT firstname,
       lastname
    FROM contact
    WHERE country = 'United States'
    AND state in ('MA', 'MI', 'NY')

update()

Pass in the name of the table and a dictionary containing the database field name as the key and the value to set as the value to update.

from pgcrud.connector import Connector

conn = Connector(host, dbname, user, password)
data = {'firstname': 'Fred', 'lastname': 'Jones'}
criteria = 'WHERE id = %s'
values = [12436]
conn.update('contact', data, criteria, values)

delete()

Pass in the name of the table, the criteria and values to identify which records to delete.

from pgcrud.connector import Connector

conn = Connector(host, dbname, user, password)
criteria = 'WHERE id = %s'
values = [12436]
conn.delete('contact', criteria, values)

select()

Pass in the name of the table, a list of the field names you want and the criteria and values to identify which records to select.

A list of dictionaries will be returned. Each entry will have the database field name as the key and the retrieved value as the value.

from pgcrud.connector import Connector

conn = Connector(host, dbname, user, password)
fields = ['firstname', 'lastname]
criteria = 'WHERE id = %s'
values = [12436]
results = conn.select('contact', fields, criteria, values)

commit()

The create, delete and select funcitons have an optional commit parameter. Sometimes when creating or deleting records in a database you want to do an all or none transaction. For example you are creating an order and all of its items. If one of the items fails to create you do not want the order to be created. In this case you would pass commit = False. This means that the insertions will not be committed until you say so, when they are all complete.

This parameter is also included in the select function because you may need information from one of the created records to create further records. For example you insert the order and need its id in order to associate its items with it. In this case you would also want to pass commit = False to the select function, so that when you are retrieving the order id the order insert is not committed.

from pgcrud.connector import Connector

conn = Connector(host, dbname, user, password)
data = {'total': 1900, 'contact_id': 234422, 'external_id' = 34567}
conn.create('order', data, False)```
order = conn.select('order', ['id'], 'WHERE external_id = %s', [data['external_id]], False)
order_id = order[0]['id']
items = [{'sku': 'CUCUMBER',
          'qty': 1,
          'price': .5},
          {'sku': 'GRAPEFRUIT',
          'qty': 2,
          'price': 1}]
for item in items:
    item['order_id'] = order_id
    conn.create('item', item, False)
conn.commit()