API Metadata Database

1. Import PostgresQL database adapter: psycopg2#

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 as a rock.

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/

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()

Usage Agent DB

#!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)