Python使用PyGreSQL操作PostgreSQL数据库教程

#!/usr/bin/env python
# -*- coding: utf-8 -*-

#导入日志及pg模块
import logging
import logging.config
import pg

#日志配置文件名
LOG_FILENAME = 'logging.conf'

#日志语句提示信息
LOG_CONTENT_NAME = 'pg_log'

def log_init(log_config_filename, logname):
 '''
 Function:日志模块初始化函数
 Input：log_config_filename:日志配置文件名
 lognmae:每条日志前的提示语句
 Output: logger
 author: socrates
 date:2012-02-12
 '''
 logging.config.fileConfig(log_config_filename)
 logger = logging.getLogger(logname)
 return logger

def operate_postgre_tbl_product():
 '''
 Function:操作pg数据库函数
 Input：NONE
 Output: NONE
 author: socrates
 date:2012-02-12
 ''' 
 pgdb_logger.debug("operate_postgre_tbl_product enter...") 
 
 #连接数据库 
 try:
 pgdb_conn = pg.connect(dbname = 'kevin_test', host = '192.168.230.128', user = 'dyx1024', passwd = '888888')
 except Exception, e:
 print e.args[0]
 pgdb_logger.error("conntect postgre database failed, ret = %s" % e.args[0]) 
 return 
 
 pgdb_logger.info("conntect postgre database(kevin_test) succ.") 
 
 #删除表
 sql_desc = "DROP TABLE IF EXISTS tbl_product3;"
 try:
 pgdb_conn.query(sql_desc)
 except Exception, e:
 print 'drop table failed'
 pgdb_logger.error("drop table failed, ret = %s" % e.args[0])
 pgdb_conn.close() 
 return
 
 pgdb_logger.info("drop table(tbl_product3) succ.") 
 
 #创建表
 sql_desc = '''CREATE TABLE tbl_product3(
 i_index INTEGER,
 sv_productname VARCHAR(32)
 );'''
 try: 
 pgdb_conn.query(sql_desc)
 except Exception, e:
 print 'create table failed'
 pgdb_logger.error("create table failed, ret = %s" % e.args[0])
 pgdb_conn.close() 
 return 
 
 pgdb_logger.info("create table(tbl_product3) succ.") 
 
 #插入记录 
 sql_desc = "INSERT INTO tbl_product3(sv_productname) values('apple')"
 try:
 pgdb_conn.query(sql_desc)
 except Exception, e:
 print 'insert record into table failed'
 pgdb_logger.error("insert record into table failed, ret = %s" % e.args[0])
 pgdb_conn.close() 
 return 
 
 pgdb_logger.info("insert record into table(tbl_product3) succ.") 
 
 #查询表 1 
 sql_desc = "select * from tbl_product3"
 for row in pgdb_conn.query(sql_desc).dictresult():
 print row
 pgdb_logger.info("%s", row) 
 
 #查询表2 
 sql_desc = "select * from tbl_test_port"
 for row in pgdb_conn.query(sql_desc).dictresult():
 print row 
 pgdb_logger.info("%s", row) 
 
 #关闭数据库连接 
 pgdb_conn.close() 
 pgdb_logger.debug("operate_sqlite3_tbl_product leaving...") 

if __name__ == '__main__': 
 
 #初始化日志系统
 pgdb_logger = log_init(LOG_FILENAME, LOG_CONTENT_NAME) 
 
 #操作数据库
 operate_postgre_tbl_product()
 

{'sv_productname': 'apple', 'i_index': None}
{'i_status': 1, 'i_port': 2, 'i_index': 1}
{'i_status': 1, 'i_port': 3, 'i_index': 2}
{'i_status': 1, 'i_port': 5, 'i_index': 3}
{'i_status': 1, 'i_port': 0, 'i_index': 5}
{'i_status': 1, 'i_port': 18, 'i_index': 7}
{'i_status': 1, 'i_port': 8, 'i_index': 8}
{'i_status': 1, 'i_port': 7, 'i_index': 9}
{'i_status': 1, 'i_port': 21, 'i_index': 10}
{'i_status': 1, 'i_port': 23, 'i_index': 11}
{'i_status': 1, 'i_port': 29, 'i_index': 12}
{'i_status': 1, 'i_port': 3000, 'i_index': 4}
{'i_status': 1, 'i_port': 1999, 'i_index': 6}

[2012-02-12 18:09:53,536 pg_log]DEBUG: operate_postgre_tbl_product enter... (test_func.py:36)
[2012-02-12 18:09:53,772 pg_log]INFO: conntect postgre database(kevin_test) succ. (test_func.py:46)
[2012-02-12 18:09:53,786 pg_log]INFO: drop table(tbl_product3) succ. (test_func.py:58)
[2012-02-12 18:09:53,802 pg_log]INFO: create table(tbl_product3) succ. (test_func.py:73)
[2012-02-12 18:09:53,802 pg_log]INFO: insert record into table(tbl_product3) succ. (test_func.py:85)
[2012-02-12 18:09:53,802 pg_log]INFO: {'sv_productname': 'apple', 'i_index': None} (test_func.py:91)
[2012-02-12 18:09:53,802 pg_log]INFO: {'i_status': 1, 'i_port': 2, 'i_index': 1} (test_func.py:97)
[2012-02-12 18:09:53,802 pg_log]INFO: {'i_status': 1, 'i_port': 3, 'i_index': 2} (test_func.py:97)
[2012-02-12 18:09:53,802 pg_log]INFO: {'i_status': 1, 'i_port': 5, 'i_index': 3} (test_func.py:97)
[2012-02-12 18:09:53,802 pg_log]INFO: {'i_status': 1, 'i_port': 0, 'i_index': 5} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]INFO: {'i_status': 1, 'i_port': 18, 'i_index': 7} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]INFO: {'i_status': 1, 'i_port': 8, 'i_index': 8} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]INFO: {'i_status': 1, 'i_port': 7, 'i_index': 9} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]INFO: {'i_status': 1, 'i_port': 21, 'i_index': 10} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]INFO: {'i_status': 1, 'i_port': 23, 'i_index': 11} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]INFO: {'i_status': 1, 'i_port': 29, 'i_index': 12} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]INFO: {'i_status': 1, 'i_port': 3000, 'i_index': 4} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]INFO: {'i_status': 1, 'i_port': 1999, 'i_index': 6} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]DEBUG: operate_sqlite3_tbl_product leaving... (test_func.py:101)

[root@kevin ~]# su - postgres
[postgres@kevin ~]$ psql -U dyx1024 -d kevin_test
psql (8.4.2)
Type "help" for help.

kevin_test=# dt
 List of relations
 Schema | Name | Type | Owner 
--------+---------------+-------+----------------
 public | tbl_product3 | table | dyx1024
 public | tbl_test_port | table | pg_test_user_3
(2 rows)

kevin_test=# select * from tbl_product3;
 i_index | sv_productname 
---------+----------------
 | apple
(1 row)

kevin_test=# select * from tbl_test_port;
 i_index | i_port | i_status 
---------+--------+----------
 1 | 2 | 1
 2 | 3 | 1
 3 | 5 | 1
 5 | 0 | 1
 7 | 18 | 1
 8 | 8 | 1
 9 | 7 | 1
 10 | 21 | 1
 11 | 23 | 1
 12 | 29 | 1
 4 | 3000 | 1
 6 | 1999 | 1
(12 rows)

kevin_test=# q
[postgres@kevin ~]$