pg4nosql 0.4.1

Using Python Package Index (PIP)

Just run the command: pip install pg4nosql

During alpha stage the api will change with each build. So try to stay with one version if you want to use it.

The hacky way

1. download or clone this repository

2. run the command python setup.py install

Dataschema Creation

To create the dataschema you can use normal database tools if you want. A document table has two fields:

• id with data type serial

• json with data type JSON which represents the document

But pg4nosql also provides methods to create your database schema on the fly. This is useful to create tables and databases software controlled.

This example shows how to create a database and their tables. The cities table is special because it also contains relational data like a normal table would:

# create pg4nosql client
pg4nosql = PostgresNoSQLClient(host='localhost')

# create demo database
demo_db = pg4nosql['demo']

# create document only table
users = demo_db['users']

# create document & relational table
cities = demo_db.get_or_create_table('cities', size='real NOT NULL')

# create document table with bigserial
big_users = demo_db.create_table('big_users',
                                  row_identifier_type='BIGSERIAL')

Insert Data

To insert data into the table you just hand over a dictionary or an object which is json serializable. If there are relational columns defined you can set those by the table name as keyword and the value:

# store data into users table
users.put({'name': 'Florian', 'age': 24})
users.put({'name': 'Markus', 'age': 24})
users.put({'name': 'Sara', 'age': 22})
users.put({'name': 'Thomas', 'age': 25})

# store data into cities table
cities.put({'name': 'Zurich'}, size=87.88)
cities.put({'name': 'Berlin'}, size=891.8)
cities.put({'name': 'Bern'}, size=51.6)
cities.put({'name': 'London'}, size=1572)

If you work without json documents, there is just a normal insert method to store new records into a table.

# store data into cities table
users.insert(age=25, name="Florian")

# store data with lazy commit
for i in range(0, 255):
    users.put({'name': 'Test', 'age': i}, auto_commit=False)

# lazy commit data
users.commit()

Query Data

To get your data back you can run a query over it. This works like normal SQL WHERE queries. For JSON data you have to use the json column:

# query all users which are 24 years old
users_24 = users.query("json->>'age'='24'")

And here the result of the user query:

[
   "{'json': {u'age': 24, u'name': u'Florian'}, 'id': 1}",
   "{'json': {u'age': 24, u'name': u'Markus'}, 'id': 2}"
]

You can also combine relational and JSON queries together like this:

# query all cities which start with be and are bigger than 100 km
big_ber_cities = cities.query("json->>'name' LIKE 'Ber%'"
                              "AND size > 100")

Here the result of this query:

[
   "{'json': {u'name': u'Berlin'}, 'id': 2, 'size': 891.8}"
]

Query with Join

It is also possible to create simple joined queries with the function query_join. Consider a datamodel with an user table and an address table. This two tables are connected through a foreign key fk_user.

The called table get’s the identifier a and the joined table the identifier b.

# get all users with their address
users = user.query_join('address', 'a.id = b.fk_user')

Query from View

To query data from a view you have to get the view from the database and then it has the same query method like a table.

# get view
self.user_view = self.database.get_view('user_view')

# query data
records = self.user_view.query()

Query Data Access

To access the JSON fields of the result there is an attribute called json:

# get first city of the result array
first_city = big_ber_cities[0]

# read JSON attribute
city_name = first_city.json['name']

To access the relational fields of the result you have to use square brackets ([]) on the result:

# read relational attribute
city_size = first_city['size']

There is also a default field called id which contains the default row identifier for easy access:

# get id of row
city_id = first_city.id

Update Data

With those access methods you can also write into the result and change the values of the fields. To save it just call save(obj) on the table object.

# change florian's age
florian = users_24[0]
florian.json['age'] = 25

users.save(florian)

The same works also with the relational fields:

# make zurich a bit bigger
zurich = cities.query_one("data->>'name'='Zurich'")
zurich['size'] = 90

cities.save(zurich)

With the release 0.4.0 it is also possible to save the database object directly:

# make zurich a bit bigger
zurich = cities.query_one("data->>'name'='Zurich'")
zurich['size'] = 90

zurich.save()

Without json documents, there is just a normal update method to update new records into a table.

# store data into cities table
florian = users_24[0]
florian['name'] = 'Markus'
users.update(florian)

Direct Execution

It is also possible to directly execute sql statements as you are used to. The execute function is declared on the database object and on the table object.

# run simple sql query
my_data = demo_db.execute('SELECT * FROM cities')

Close Connection

Finally don’t forget to close the connection to the database.

# close db
demo_db.close()