API OS PostgreSQL

Back to Developer Resources

Link 5: API between OS and BEMOSS PostgreSQL database

---

psycopg2 is a PostgreSQL database adapter for the Python programming language. psycopg2 was written with the aim of being very small and fast, and stable.

psycopg2 is different from the other database adapter because it was designed for heavily multi-threaded applications that create and destroy lots of cursors and make a conspicuous number of concurrent INSERTs or UPDATEs. psycopg2 also provide full asynchronous operations and support for coroutine libraries.

Full documentation: http://pythonhosted.org//psycopg2/

Both UI and OS layer use PostgreSQL for storing and retrieving metadata. OS layer uses the database to store device info, device current status data, app info, holiday schedules, etc. API for PostgreSQL database is same for UI and OS layer and is discussed below:

Basic Module Usage

#!linux

>>> import psycopg2

# Connect to an existing database
>>> conn = psycopg2.connect("dbname=test user=postgres")

# Open a cursor to perform database operations
>>> cur = conn.cursor()

# Execute a command: this creates a new table
>>> cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")

# Pass data to fill a query placeholders and let Psycopg perform
# the correct conversion (no more SQL injections!)
>>> cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)",
...      (100, "abc'def"))

# Query the database and obtain data as Python objects
>>> cur.execute("SELECT * FROM test;")
>>> cur.fetchone()
(1, 100, "abc'def")

# Make the changes to the database persistent
>>> conn.commit()

# Close communication with the database
>>> cur.close()
>>> conn.close()

Agent interaction with BEMOSS PostgreSQL database

#!python
# connect to DB
self.con = psycopg2.connect(host=db_host, port=db_port, database=db_database, user=db_user, 
                            password=db_password)

# open a cursor to perfomm database operations
self.cur = self.con.cursor()  

# (C:create) Execute a command: this creates a new table
>>> cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")

# (R:read) query the database and obtain data as Python objects
cur.execute("SELECT device_type_id FROM dashboard_device_info WHERE device_type_id=%(id)s", {'id': '1TH'})

# (U: update) update the database and obtain data as Python objects
cur.execute("INSERT INTO dashboard_device_info VALUES(%s,%s,%s,%s,%s,%s,%s,999,%s,'ON')",(deviceType+str(deviceno),deviceType+str(deviceno),deviceType+str(deviceno),deviceType,deviceTypeID,deviceVendor,deviceModel,macaddress))

self.cur.execute("UPDATE dashboard_current_status SET temperature=%s WHERE id=%s",
                                 (self.get_variable('temperature'), agent_id))

# (D: delete) 
self.cur.execute("DELETE FROM dashboard_current_status")
            self.con.commit()

---

Back to Developer Resources