python: Adodb Function Reference with example
Submitted by wd on Wed, 2007-01-24 14:13
ก่อนอื่น จะสร้างฐานข้อมูลเพื่อใช้เป็นตัวอย่างก่อน
โดยจะสร้างตารางเป็นสมุดโทรศัพท์ ใส่ข้อมูลเบื้องต้นไป 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
optional params is a dictionary that contains the bind
variables. All blob fields are automatically and transparently
retrieved for you.
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
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.
calls UpdateBlob( ).
thread-safe.
>>> try: ... cur=conn.Execute('SELECT * FROM notable') ... except: ... print conn.ErrorMsg() ... ERROR: relation "notable" does not exist
SELECT * FROM notable
>>> conn=adodb.NewADOConnection('postgres') >>> conn.IsConnected() False >>> conn.Connect('host','user','password','database') True >>> conn.IsConnected() True
>>> print conn.qstr(2) '2'
qstr( )
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', '')]
>>> 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', '')]
>>> 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', '')
>>> print conn.GetOne('SELECT * FROM phone') 1
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', '')
GetAssoc()
.>>> print conn.GetCol('SELECT * FROM phone') [1, 2, 3, 4, 5]
- 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
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
string acceptable to the database.
>>> from datetime import datetime >>> today = conn.DBDate(datetime.date(datetime.today())) >>> print today '2007-01-25'
string acceptable to the database.
>>> now=conn.DBTimeStamp(datetime.datetime.now()) >>> print now '2007-01-25 00:00:00'
>>> print conn.Date('2007-01-25') 2007-01-25 00:00:00
Python 2.3 datetime object
>>> print conn.TimeStamp('2007-01-25 00:00:00') 2007-01-25 00:00:00
start a transaction. This might not be thread-safe.
connection is closed when the object is freed.
>>> 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']
>>> dir(conn.Conn()) ['autocommit', 'close', 'commit', 'cursor', 'cursors', \ 'maxconn', 'minconn', 'rollback', 'serialize', \ 'set_isolation_level']
>>> 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
output to stdout as HTML. Set to a filepath (a string) if you want the debug output to be logged into a file.
เพื่อไม่ให้เยิ่นเย้อ จะใช้ตัวแปร cur
ซึ่งจะหมายถึง
... conn = adodb.NewADOConnection(driver) cur = conn.Execute('SELECT * FROM phone') ...
เป็นปกติของส่วนนี้
Cursor Class Functions
rows affected by INSERT/UPDATE/DELETE. Returns -1 if not supported.
>>> print cur.RecordCount() 5
RecordCount( )
.>>> cur.MoveNext() False >>> cur.EOF False
>>> a=cur.FetchRow() >>> a (2, 'name2', '0-0000-0002', 'cat2', , 'rem2', '')
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'}
field. Only supported by mysql and odbc_mssql drivers currently.
(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)
>>> cur.Close()
>>> 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
tuple (or list).
>>> print cur.fields (3, 'name3', '0-0000-0003', 'cat3', , 'rem3', '')
pass the last row.
>>> print cur.EOF False
- Printer-friendly version
- Log in or register to post comments
- 6604 reads
Recent comments