python连接数据库

2440

Python中定义了访问数据库的标准,Python Database API Specification v2.0
http://www.python.org/dev/peps/pep-0249/
DBApi中定义python访问数据库模块的API,是module实现者需要参考实现的；还定义了扩展API.
1. Module Interface

• connect(parameters...) 连接数据库
  
• apilevel API级别，字符串'1.0'或'2.0'表示
  
• threadsafety　线程安全级别，0,1,2,3
  
• paramstyle 绑定参数形式，qmark，numeric，named，format，pyformat
  

根据异常发生的不同层次，如下异常树：
    StandardError
        |__Warning
        |__Error
           |__InterfaceError
           |__DatabaseError
              |__DataError
              |__OperationalError
              |__IntegrityError
              |__InternalError
              |__ProgrammingError
              |__NotSupportedError
2. Connection Objects

• .close()
  
• .commit()
  
• .rollback()
  
• .cursor()
  

3. Cursor Objects

• .description
  
• .rowcount
  
• .callproc(procname[,parameters])
  
• .close()
  
• .execute(operation[,parameters])
  
• .executemany(operation,seq_of_parameters)
  
• .fetchone()
  
• .fetchmany([size=cursor.arraysize])
  
• .fetchall()
  
• .nextset()
  
• .arraysize
  
• .setinputsizes(sizes)
  
• .setoutputsize(size[,column])
  

4. Type Objects and Constructors
定义python与sql之间的类型转换.

• Date(year,month,day)
  
• Time(hour,minute,second)
  
• Timestamp(year,month,day,hour,minute,second)
  
• DateFromTicks(ticks)
  
• TimeFromTicks(ticks)
  
• TimestampFromTicks(ticks)
  
• Binary(string)
  
• STRING
  
• BINARY
  
• NUMBER
  
• DATETIME
  
• ROWID
  

Python访问Sqlite
Python标准库sqlite3用于python访问sqlite数据。
Sqlite: http://www.sqlite.org/

import sqlite3
import os, os.path
dbfile = os.path.join(os.getcwd(), 'db1.sqlite')
con = sqlite3.connect(dbfile)
cur = con.cursor()
#create table and insert one record
try:
cur.execute('''create table stocks(date text, trans text, symbol text,
qty real, price real)''')
except sqlite3.OperationalError:
print 'Table stocks exists,will not create it!'
cur.execute("""insert into stocks
values ('2006-01-05','BUY','RHAT',100,35.14)""")
cur.execute('insert into stocks values(?,?,?,?,?)',
('2010-02-25', 'SELL', 'RHAT', 120, 40.32))
con.commit()
#select record
cur.execute('select * from stocks order by price')
print cur.rowcount
print cur.fetchone()
for r in cur.fetchall():
print "  ", r
print cur.rowcount
con.close()


Oracle驱动： http://cx-oracle.sourceforge.net/