Page History

#!/usr/bin/env python 
 
# (C) Copyright 1996-2012 ECMWF.
# 
# This software is licensed under the terms of the Apache Licence Version 2.0
# which can be obtained at http://www.apache.org/licenses/LICENSE-2.0.
# In applying this licence, ECMWF does not waive the privileges and immunities 
# granted to it by virtue of its status as an intergovernmental organisation nor
# does it submit to any jurisdiction.
#
 
"""
Examples of usage of ODB API Python interface.
 
@author Piotr Kuchta, ECMWF, August 2016 
"""
 
import odb
 
### Example 1. Create a new ODB file.
###
 
### 1.1. Define metadata (column names and their types) 
#    of a new table associated with a physical file 
#    by passing some DDL (Data Definition Language):
#    CREATE TYPE (to define bitfields) and CREATE TABLE
#    statements to function connect.
#
#   Function connect returns a Connection object.
 
conn = odb.connect("")
 
### 1.2 Create a Cursor object. """
c = conn.cursor()
 
### 1.3 Populate table with data using SQL INSERT statement 
#    and method Cursor.executemany. 
 
c.execute('''
    CREATE TYPE bf AS (f1 bit1, f2 bit2); 
 
    CREATE TABLE foo AS 
    ( x INTEGER, y DOUBLE, v STRING, status bf) 
    ON 'new_api_example.odb';
    ''')
 
c.executemany('INSERT INTO foo (x,y,v,status) VALUES (?,?,?,?);', 
                [[1,0.1, '  one   ', 1], 
                 [2,0.2, '  two   ', 2], 
                 [3,0.3, '  three ', 3], 
                 [4,0.4, '  four  ', 4]])

### 1.4 Flush buffers and write to file associated with the table. """
c.close()
conn.commit()
 
 
### Example 2. Read contents of a file.
#
#   Note: table foo is associated with a file using CREATE TABLE
#   DDL statement, see previous example.
 
### 2.1 Create Cursor object and execute
 
c = conn.cursor()
c.execute('select * from foo;')
 
### 2.2 Use Cursor.fetchall to retrieve whole result set of SELECT
#    and print its rows using simple print 
 
for row in c.fetchall():
    print ",".join(str(v) for v in row)
 
 
### Example 3. Read a file into Pandas DataFrame object.
 
import pandas as pd
c.execute('select * from foo;')
d = pd.DataFrame.from_records(c.fetchall(), 
                              columns = [d[0] for d in c.description],
                              exclude = ['v@foo'])
 
print 'Pandas DataFrame:\n', d
 
### Example 4. Create numpy array.
import numpy as np
 
c.execute('select x, y, status.f1, status.f2 from foo;')
a = np.array(c.fetchall())
print 'numpy array:\n', a
 
 
### Example 5. Load some data from MARS or ODB Server into Pandas DataFrame
conn = odb.connect('''mars://RETRIEVE,
                        DATABASE  = marsod,
                        CLASS     = OD,
                        TYPE      = MFB,
                        STREAM    = OPER,
                        EXPVER    = 0001,
                        DATE      = 20160830,
                        TIME      = 1200,
                        REPORTYPE = 16001''')
c = conn.cursor()
c.execute('select *;')
d = pd.DataFrame.from_records(c.fetchall(), 
                              columns = [d[0] for d in c.description],
                              exclude = ['expver','class','stream'])
print d
 
print "That's all, folks!"