sqlWrap_examples.txt

Companion to sqlWrap.py for unit testing and sample usage.

Can also be downloaded from Content-Type.com.

>>> # I. sqlWrap overrides the base DB-API 2.0 Connection objects, so all their base functionality is still there
>>> # II. handy convenience methods for INSERT, UPDATE, DELETE
>>> # III. even handier: conn.select returns an iterable Cursor
>>> # IV. ... and the rows returned are modified tuples with many conveniences
>>> # V. methods for full reports come with the cursor itself
>>>
>>> from sqlWrap import *
>>> # I. sqlWrap overrides the base DB-API 2.0 Connection objects, so all their base functionality is still there
>>> conn = OraConnection('hr/hr@xe')
>>> curs = conn.cursor()
>>> curs.execute('CREATE TABLE gifts (n NUMBER, s VARCHAR2(20))')
>>> curs.execute("INSERT INTO gifts VALUES (3, 'french hens')")
>>> conn.commit()
>>> # II. handy convenience methods for INSERT, UPDATE, DELETE
>>> conn.insert('gifts', {'n': 2, 's': 'turtledoves'})                  # equiv. to :n := 2; :s := 'turtledoves'; INSERT INTO gifts (n, s) VALUES (:n, :s)
1
>>> conn.insert('gifts', {'n': 1, 's': 'partridge'})
1
>>> conn.update('gifts', {'s': 'turtles'}, {'n':2})                     # equiv. to :n := 2; :s := 'turtles'; UPDATE gifts SET s = :s WHERE n = :n
1
>>> conn.insert('gifts', [3, 'freedom hens'])
1
>>> conn.delete('gifts', {'n':3})
2
>>>
>>> # III. even handier: conn.select returns an iterable Cursor
>>>
>>> list(conn.select('gifts'))                                                       # equiv. to SELECT * FROM gifts
[(2, 'turtles'), (1, 'partridge')]
>>> list(conn.select('gifts', ['s']))                                               # now supplying a list of column names
[('turtles',), ('partridge',)]
>>> conn.select('gifts', whereClause = {'n':2}).next()           # equiv. to :n := 2; SELECT * FROM gifts WHERE n = :n
(2, 'turtles')
>>>
>>> # IV. ... and the rows returned are modified tuples with many conveniences
>>>
>>> row = conn.select('gifts').next()
>>> print row
(2, 'turtles')
>>> row.items()                                                                           # stores column names as lowercase
[('s', 'turtles'), ('n', 2)]
>>> row['S']                                                                                 # but can be accessed in either case (SQL standard)
'turtles'
>>> row.dict                                                                                 # grab its dictionary directly
{'s': 'turtles', 'n': 2}
>>> row.S
'turtles'
>>> print row.xml()                                                                     # grab its dictionary directly
  <gifts>
    <N>2</N>
    <S>turtles</S>
  </gifts>
>>>
>>> # V. methods for full reports come with the cursor itself
>>>
>>> print conn.select('gifts').pp()
N S        
- ---------
2 turtles  
1 partridge
>>> print conn.select('gifts').ppTransposed()
N 2                1
S turtles          partridge
>>> print conn.select('gifts').rst()  # ReStructured Text
= =========
N S        
- ---------
2 turtles  
1 partridge
= =========
>>> print conn.select('gifts').rstTransposed()
= ================ ================
N 2                1
S turtles          partridge
= ================ ================
>>> print conn.select('gifts').xml(rowTag='gift')
  <gift>
    <N>2</N>
    <S>turtles</S>
  </gift>
  <gift>
    <N>1</N>
    <S>partridge</S>
  </gift>
>>> print conn.select('gifts').xhtml()
<table>
  <tr>
    <th>N</th>
    <th>S</th>
  </tr>
  <tr>
    <td>2</td>
    <td>turtles</td>
  </tr>
  <tr>
    <td>1</td>
    <td>partridge</td>
  </tr>
</table>
>>> # The convenience methods are available even if you execute cursors the old-fashioned way
>>> curs = conn.cursor()
>>> curs.execute('SELECT * FROM gifts')
[<cx_Oracle.NUMBER object at 0xb6a73de0>, <cx_Oracle.STRING object at 0xb6a73b60>]
>>> print curs.xml()
  <gifts>
    <N>2</N>
    <S>turtles</S>
  </gifts>
  <gifts>
    <N>1</N>
    <S>partridge</S>
  </gifts>
>>> conn.cursor().execute('DROP TABLE gifts')
>>> conn.close()