Python:操作PostgreSQL數據庫(使用DB API2.0)

在《Python:操作PostgreSQL數據庫(使用PyGreSQL)》(見 http://www.linuxidc.com/Linux/2012-02/54214.htm)一文中使用PyGreSQL模塊實現了對PostgreSQL數據庫的操作，今天使用另一種python通用的數據庫訪問接口DB API2.0來實現一下同樣的操作，其實在《Python:操作嵌入式數據庫SQLite》（見 http://www.linuxidc.com/Linux/2012-02/54213.htm）一文中我們使用的就是DB API2.0，我們可以看到，它對不同數據庫提供了統一的訪問接口，更多關於DB API2.0的介紹請參考http://www.python.org/dev/peps/pep-0249/

來看看實現，首先我們要下載提供DB API2.0接口的模塊psycopg2,路徑：http://initd.org/psycopg/download/，此模塊API手冊地址為：http://initd.org/psycopg/docs/index.html

一、實現：

[python]

1. #!/usr/bin/env python
2. # -*- coding: utf-8 -*-
4. #導入日志及psycopg2模塊
5. import logging
6. import logging.config
7. import psycopg2
9. #日志配置文件名
10. LOG_FILENAME = 'logging.conf'
12. #日志語句提示信息
13. LOG_CONTENT_NAME = 'pg_log'
15. def log_init(log_config_filename, logname):
16. '''''
17. Function:日志模塊初始化函數
18. Input：log_config_filename:日志配置文件名
19. lognmae:每條日志前的提示語句
20. Output: logger
21. author: socrates
22. date:2012-02-13
23. '''
24. logging.config.fileConfig(log_config_filename)
25. logger = logging.getLogger(logname)
26. return logger
28. def operate_postgre_tbl_product():
29. '''''
30. Function:操作pg數據庫函數
31. Input：NONE
32. Output: NONE
33. author: socrates
34. date:2012-02-13
35. '''
36. pgdb_logger.debug("operate_postgre_tbl_product enter...")
38. #連接數據庫
39. try:
40. pgdb_conn = psycopg2.connect(database = 'kevin_test', user = 'dyx1024', password = '888888', host = '192.168.230.128')
41. except Exception, e:
42. print e.args[0]
43. pgdb_logger.error("conntect postgre database failed, ret = %s" % e.args[0])
44. return
46. pgdb_logger.info("conntect postgre database(kevin_test) succ.")
48. pg_cursor = pgdb_conn.cursor()
50. #刪除表
51. sql_desc = "DROP TABLE IF EXISTS tbl_product3;"
52. try:
53. pg_cursor.execute(sql_desc)
54. except Exception, e:
55. print 'drop table failed'
56. pgdb_logger.error("drop table failed, ret = %s" % e.args[0])
57. pg_cursor.close()
58. pgdb_conn.close()
59. return
60. pgdb_conn.commit()
62. pgdb_logger.info("drop table(tbl_product3) succ.")
64. #創建表
65. sql_desc = '''''CREATE TABLE tbl_product3(
66. i_index INTEGER,
67. sv_productname VARCHAR(32)
68. );'''
69. try:
70. pg_cursor.execute(sql_desc)
71. except Exception, e:
72. print 'create table failed'
73. pgdb_logger.error("create table failed, ret = %s" % e.args[0])
74. pg_cursor.close()
75. pgdb_conn.close()
76. return
77. pgdb_conn.commit()
79. pgdb_logger.info("create table(tbl_product3) succ.")
81. #插入記錄
82. sql_desc = "INSERT INTO tbl_product3(sv_productname) values('apple')"
83. try:
84. pg_cursor.execute(sql_desc)
85. except Exception, e:
86. print 'insert record into table failed'
87. pgdb_logger.error("insert record into table failed, ret = %s" % e.args[0])
88. pg_cursor.close()
89. pgdb_conn.close()
90. return
91. pgdb_conn.commit()
93. pgdb_logger.info("insert record into table(tbl_product3) succ.")
95. #查詢表方法一
96. sql_desc = "select * from tbl_product3"
97. try:
98. pg_cursor.execute(sql_desc)
99. except Exception, e:
100. print 'select record from table tbl_product3 failed'
101. pgdb_logger.error("select record from table tbl_product3 failed, ret = %s" % e.args[0])
102. pg_cursor.close()
103. pgdb_conn.close()
104. return
106. for row in pg_cursor:
107. print row
108. pgdb_logger.info("%s", row)
110. print '*' * 20
111. #查詢表方法二
112. sql_desc = "select * from tbl_test_port"
113. try:
114. pg_cursor.execute(sql_desc)
115. except Exception, e:
116. print 'select record from table tbl_test_port failed'
117. pgdb_logger.error("select record from table tbl_test_port failed, ret = %s" % e.args[0])
118. pg_cursor.close()
119. pgdb_conn.close()
120. return
122. for row in pg_cursor.fetchall():
123. print row
124. pgdb_logger.info("%s", row)
126. #關閉數據庫連接
127. pg_cursor.close()
128. pgdb_conn.close()
130. pgdb_logger.debug("operate_sqlite3_tbl_product leaving...")
132. if __name__ == '__main__':
134. #初始化日志系統
135. pgdb_logger = log_init(LOG_FILENAME, LOG_CONTENT_NAME)
137. #操作數據庫
138. operate_postgre_tbl_product()