python: Adodb Function Reference with example

ก่อนอื่น จะสร้างฐานข้อมูลเพื่อใช้เป็นตัวอย่างก่อน
โดยจะสร้างตารางเป็นสมุดโทรศัพท์ ใส่ข้อมูลเบื้องต้นไป 5 แถว
(อย่าเชื่อ syntax มากนะครับ เริ่มหัดใหม่เหมือนกัน)

import adodb

driver = 'postgres'
host = 'host'
user = 'user'
password = 'password'
db = 'db'

conn = adodb.NewADOConnection(driver)
cur = conn.Connect(host,user,password,db)

sql = """\
  CREATE TABLE phone (
  pid INT,
  name VARCHAR(50),
  phone VARCHAR(50),
  category VARCHAR(50),
  update DATE,
  rem TEXT,
  img BYTEA)"""
cur = conn.Execute(sql)

from datetime import datetime
today = conn.DBDate(datetime.date(datetime.today()))

sql = "INSERT INTO phone \
  (pid, name, phone, category, update, rem, img) VALUES "
cur=conn.Execute(sql+"(%s, '%s', '%s', '%s', %s, '%s', '%s')" \
  % (1, 'name1', '0-0000-0001', 'cat1', today, 'rem1', ''))
cur=conn.Execute(sql+"(%s, '%s', '%s', '%s', %s, '%s', '%s')" \
  % (2, 'name2', '0-0000-0002', 'cat2', today, 'rem2', ''))
cur=conn.Execute(sql+"(%s, '%s', '%s', '%s', %s, '%s', '%s')" \
  % (3, 'name3', '0-0000-0003', 'cat3', today, 'rem3', ''))
cur=conn.Execute(sql+"(%s, '%s', '%s', '%s', %s, '%s', '%s')" \
  % (4, 'name4', '0-0000-0004', 'cat4', today, 'rem4', ''))
cur=conn.Execute(sql+"(%s, '%s', '%s', '%s', %s, '%s', '%s')" \
  % (5, 'name5', '0-0000-0005', 'cat5', today, 'rem5', ''))

เพื่อไม่ให้เยิ่นเย้อ จะใช้ตัวแปร conn ซึ่งจะหมายถึง

...
conn = adodb.NewADOConnection(driver)
...

เป็นปกติของหน้านี้

Connection Class

Execute(sql, [params])
Execute sql, returning a cursor object. The
optional params is a dictionary that contains the bind
variables. All blob fields are automatically and transparently
retrieved for you.
SelectLimit(sql, limit, [offset])
Execute sql, retrieving only limit rows, an
optional offset from the beginning of the recordset, returning a cursor object. 
>>> cur=conn.SelectLimit('SELECT * FROM phone', 2)
(SAME AS)
>>> cur=conn.Execute('SELECT * FROM phone LIMIT 2')

>>> while not cur.EOF:
...  print cur.fields
...  cur.MoveNext()
... 
(1, 'name1', '0-0000-0001', 'cat1', <DateTime object for '2007-01-25 00:00:00.00' at b764b4f0>, 'rem1', '')
False
(2, 'name2', '0-0000-0002', 'cat2', <DateTime object for '2007-01-25 00:00:00.00' at b764b448>, 'rem2', '')
True
UpdateBlob(table, field, blob, whereclause, blobtype='BLOB')
Executes the equivalent following sql statement:
UPDATE table SET field = blob WHERE whereclause
The blobtype field should be set to either 'BLOB' or 'CLOB'.
Any special encoding required for the blob is applied
transparently.
UpdateBlobFile(table, field, filepath, whereclause, blobtype='BLOB')
Loads the binary file filepath into blob. Then
calls UpdateBlob( ).
ErrorMsg( )
Returns last database error message. This function is not
thread-safe. 
>>> try:
...   cur=conn.Execute('SELECT * FROM notable')
... except:
...   print conn.ErrorMsg()
... 
ERROR:  relation "notable" does not exist


SELECT * FROM notable
IsConnected( )
Returns boolean indicating whether connected. 
>>> conn=adodb.NewADOConnection('postgres')
>>> conn.IsConnected()
False
>>> conn.Connect('host','user','password','database')
True
>>> conn.IsConnected()
True
qstr(str)
Quotes a varchar or char variable. 
>>> print conn.qstr(2)
'2'
quote(str)
Synonym for qstr( )
GetAll(sql)
Execute sql and return 2-dimensional array of tuples, the
data recordset. 
>>> print conn.GetAll('SELECT * FROM phone')
[(1, 'name1', '0-0000-0001', 'cat1', <DateTime object for '2007-01-25 00:00:00.00' at b764b4f0>, 'rem1', ''), (2, 'name2', '0-0000-0002', 'cat2', <DateTime object for '2007-01-25 00:00:00.00' at b764b448>, 'rem2', ''), (3, 'name3', '0-0000-0003', 'cat3', <DateTime object for '2007-01-25 00:00:00.00' at b764b410>, 'rem3', ''), (4, 'name4', '0-0000-0004', 'cat4', <DateTime object for '2007-01-25 00:00:00.00' at b764b560>, 'rem4', ''), (5, 'name5', '0-0000-0005', 'cat5', <DateTime object for '2007-01-25 00:00:00.00' at b764b598>, 'rem5', '')]
GetArray(sql)
Synonym for GetAll(sql). 
>>> print conn.GetArray('SELECT * FROM phone')
[(1, 'name1', '0-0000-0001', 'cat1', <DateTime object for '2007-01-25 00:00:00.00' at b764b4f0>, 'rem1', ''), (2, 'name2', '0-0000-0002', 'cat2', <DateTime object for '2007-01-25 00:00:00.00' at b764b448>, 'rem2', ''), (3, 'name3', '0-0000-0003', 'cat3', <DateTime object for '2007-01-25 00:00:00.00' at b764b410>, 'rem3', ''), (4, 'name4', '0-0000-0004', 'cat4', <DateTime object for '2007-01-25 00:00:00.00' at b764b560>, 'rem4', ''), (5, 'name5', '0-0000-0005', 'cat5', <DateTime object for '2007-01-25 00:00:00.00' at b764b598>, 'rem5', '')]
GetRow(sql)
Execute sql and return first row of recordset as a tuple. 
>>> print conn.GetRow('SELECT * FROM phone')
(1, 'name1', '0-0000-0001', 'cat1', <DateTime object for '2007-01-25 00:00:00.00' at b764b4f0>, 'rem1', '')
GetOne(sql)
Execute sql and return 1 element of first row of recordset. 
>>> print conn.GetOne('SELECT * FROM phone')
1
GetAssoc(sql)
Returns a dictionary, with the first columns as the keys to
the dictionary. If more than 2 columns are returned, then the
dictionary values is a tuple of the 2nd to last columns. If 2 columns
are returned, then the 2nd column becomes the dictionary values. If one
column is returned, then the values are set to None. 
>>> print conn.GetAssoc('SELECT * FROM phone')
{1: ('name1', '0-0000-0001', 'cat1', <DateTime object for '2007-01-25 00:00:00.00' at b764b2f8>, 'rem1', ''), 2: ('name2', '0-0000-0002', 'cat2', <DateTime object for '2007-01-25 00:00:00.00' at b764b528>, 'rem2', ''), 3: ('name3', '0-0000-0003', 'cat3', <DateTime object for '2007-01-25 00:00:00.00' at b764b330>, 'rem3', ''), 4: ('name4', '0-0000-0004', 'cat4', <DateTime object for '2007-01-25 00:00:00.00' at b764b368>, 'rem4', ''), 5: ('name5', '0-0000-0005', 'cat5', <DateTime object for '2007-01-25 00:00:00.00' at b764b4b8>, 'rem5', '')}

>>> dict = conn.GetAssoc('SELECT * FROM phone')
>>> for i in dict:
...   print i, dict[i]
... 
1 ('name1', '0-0000-0001', 'cat1', <DateTime object for '2007-01-25 00:00:00.00' at b764b4b8>, 'rem1', '')
2 ('name2', '0-0000-0002', 'cat2', <DateTime object for '2007-01-25 00:00:00.00' at b764b368>, 'rem2', '')
3 ('name3', '0-0000-0003', 'cat3', <DateTime object for '2007-01-25 00:00:00.00' at b764b330>, 'rem3', '')
4 ('name4', '0-0000-0004', 'cat4', <DateTime object for '2007-01-25 00:00:00.00' at b764b528>, 'rem4', '')
5 ('name5', '0-0000-0005', 'cat5', <DateTime object for '2007-01-25 00:00:00.00' at b764b2f8>, 'rem5', '')
GetDict(sql)
Synonym for GetAssoc().
GetCol(sql)
Returns the first column of each row as an array. 
>>> print conn.GetCol('SELECT * FROM phone')
[1, 2, 3, 4, 5]
MetaType(fieldtype)
Returns the ADOdb metatype of a native field type.
  • C: character fields that fit into a text input field.
  • X: larger character fields that fit into a textarea.
  • B: Blobs, or Binary Large Objects. Typically images.
  • D: Date field
  • T: Timestamp field
  • L: Logical field (boolean or bit-field)
  • I:  Integer field
  • N: Numeric field. Includes autoincrement, numeric,
    floating point, real and integer. 
>>> print conn.MetaType('B')
B
>>> print conn.MetaType('BINARY')
B
>>> print conn.MetaType('date')
D
>>> print conn.MetaType('varchar')
C
>>> print conn.MetaType('IMAGE')
B
MetaColumns(table)
Returns a 2-dimensional array containing information on the
fields of a table. Each row contains [fieldname, fieldtype, maxlength].
Maxlength can be -1, indicating that the maximum length is not known.

Note that some databases return more information in each row.

>>> print conn.MetaColumns('phone')
[('pid', 'int4', 4, -1, 0, 0, 1), ('name', 'varchar', -1, 54, 0, 0, 2), ('phone', 'varchar', -1, 54, 0, 0, 3), ('category', 'varchar', -1, 54, 0, 0, 4), ('update', 'date', 4, -1, 0, 0, 5), ('rem', 'text', -1, -1, 0, 0, 6), ('img', 'bytea', -1, -1, 0, 0, 7)]

>>> for line in conn.MetaColumns('phone'):
...   print line
... 
('pid', 'int4', 4, -1, 0, 0, 1)
('name', 'varchar', -1, 54, 0, 0, 2)
('phone', 'varchar', -1, 54, 0, 0, 3)
('category', 'varchar', -1, 54, 0, 0, 4)
('update', 'date', 4, -1, 0, 0, 5)
('rem', 'text', -1, -1, 0, 0, 6)
('img', 'bytea', -1, -1, 0, 0, 7)

MetaColumns format:
field name, field type, field length, max. field length, is_null, is_serial, field order

DBDate(datetime)
Given a Python 2.3 datetime object, convert into a date
string acceptable to the database. 
>>> from datetime import datetime
>>> today = conn.DBDate(datetime.date(datetime.today()))
>>>  print today
'2007-01-25'
DBTimeStamp(datetime)
Given a Python 2.3 datetime object, convert into a timestamp
string acceptable to the database. 
>>> now=conn.DBTimeStamp(datetime.datetime.now())
>>> print now
'2007-01-25 00:00:00'
Date(field)
Converts a date returned by a select statement into a Python 2.3 datetime object 
>>> print conn.Date('2007-01-25')
2007-01-25 00:00:00
TimeStamp(field)
Converts a timestamp returned by a select statement into a
Python 2.3 datetime object 
>>> print conn.TimeStamp('2007-01-25 00:00:00')
2007-01-25 00:00:00
BeginTrans( )
ADOdb defaults to auto-commit mode. Call BeginTrans( ) to
start a transaction. This might not be thread-safe.
RollbackTrans( )
Rollback transaction initiated by BeginTrans( ).
CommitTrans( )
Commit transaction initiated by BeginTrans( ).
Close( )
Close database connection. This is optional, as the
connection is closed when the object is freed.
Module( )
Returns the DB module object. 
>>> dir(conn.Module())
['BINARY', 'BOOLEAN', 'Binary', 'DATE', 'DATETIME', \
'DataError', 'DatabaseError', 'Date', 'DateFromMx', \
'DateFromTicks', 'Error', 'FLOAT', 'INTEGER', 'INTERVAL', \
'IntegrityError', 'InterfaceError', 'InternalError', \
'LONGINTEGER', 'NUMBER', 'NotSupportedError', \
'OperationalError', 'ProgrammingError', 'QuotedString', \
'ROWID', 'STRING', 'TIME', 'Time', 'TimeFromMx', \
'TimeFromTicks', 'Timestamp', 'TimestampFromMx', \
'TimestampFromTicks', 'Warning', '__doc__', '__file__', \
'__name__', '__version__', 'apilevel', 'connect', 'new_type', \
'paramstyle', 'register_type', 'threadsafety', 'types']
Conn( )
Returns DB connection object. 
>>> dir(conn.Conn())
['autocommit', 'close', 'commit', 'cursor', 'cursors', \
'maxconn', 'minconn', 'rollback', 'serialize', \
'set_isolation_level']
DriverInfo( )
Returns the threadsafety, apilevel and paramstyle values 
>>> print conn.DriverInfo()
Driver        = postgres
API Level     = 2.0
Param Style   = pyformat
Thread Safety = 2  (0=none, 1=module, 2=connections, 3=cursors)
--------------
None

Connection Class Properties

debug
Set to 1 to output SQL generated to stdout. Set to 2 to
output to stdout as HTML. Set to a filepath (a string) if you want the debug output to be logged into a file.
getLOBs
Boolean that determines whether LOBs (large data objects) are loaded automatically. Default is True, autoload. For best performance (especially for cursors with no LOBs), set this to False.
sysDate
SQL to generate current date.
sysTimeStamp
SQL to generate current timestamp.

เพื่อไม่ให้เยิ่นเย้อ จะใช้ตัวแปร cur ซึ่งจะหมายถึง

...
conn = adodb.NewADOConnection(driver)
cur = conn.Execute('SELECT * FROM phone')
...

เป็นปกติของส่วนนี้

Cursor Class Functions

RecordCount( )
Number of rows returned by SELECT statement, or number of
rows affected by INSERT/UPDATE/DELETE. Returns -1 if not supported. 
>>> print cur.RecordCount()
5
Affected_Rows( )
Synonym for RecordCount( ).
MoveNext( )
Move to next row of recordset. Returns current EOF value. 
>>> cur.MoveNext()
False
>>> cur.EOF
False
FetchRow( )
Retrieves the current row of the recordset, then moves to the next row. The row retrieved is returned as a tuple. 
>>> a=cur.FetchRow()
>>> a
(2, 'name2', '0-0000-0002', 'cat2', , 'rem2', '')
GetRowAssoc(upper=1)
Returns the current row as a dictionary, with the key's being the field names. Setting upper = 0 will lower-case the keys.
Setting upper=1 will upper-case the keys. Setting upper to any other value, and the keys will be left in the natural case. 
>>> print cur.GetRowAssoc()
{'CATEGORY': 'cat3', 'NAME': 'name3', 'IMG': '', 'PID': 3, \
'UPDATE': , 'PHONE': '0-0000-0003', 'REM': 'rem3'}
Insert_ID( )
Returns last insert id generated by an auto-incrementing
field. Only supported by mysql and odbc_mssql drivers currently.
FetchField(fieldoffset)
Returns field information from a SELECT statement. The fieldoffset is zero-based, so to retrieve info on the 1st field use FetchField(0). A tuple is returned, consisting of:
(name, type_code,display_size, internal_size, precision, scale,null_ok). 
>>> print cur.FetchField(0)
('pid', 23, 1, 4, None, None, None)
>>> cur.FetchField(1)
('name', 1043, 5, 50, None, None, None)
Close( )
Close cursor. This is optional, as the cursor is closed when the object is freed. 
>>> cur.Close()
Cursor( )
Returns DB cursor object. 
>>> dir(cur.Cursor())
['arraysize', 'autocommit', 'callproc', 'close', 'commit', \
'copy_from', 'copy_to', 'description', 'dictfetchall', \
'dictfetchmany', 'dictfetchone', 'execute', 'executemany', \
'fetchall', 'fetchmany', 'fetchone', 'fileno', 'lastoid', \
'lastrowid', 'nextset', 'notifies', 'rollback', 'rowcount', \
'scroll', 'setinputsizes', 'setoutputsize', 'statusmessage']

Cursor Class Properties

fields
Property that holds the current row of the recordset as a
tuple (or list). 
>>> print cur.fields
(3, 'name3', '0-0000-0003', 'cat3', , 'rem3', '')
EOF
When moving true the recordset, EOF is set to True after we
pass the last row. 
>>> print cur.EOF
False
Topic: 
python
adodb