python postgresql跨数据库操作_python操作PostgreSQL数据库

一、实现：

#!/usr/bin/env python

# -*- coding: utf-8 -*-

#导入日志及psycopg2模块

import logging

import logging.config

import psycopg2

#日志配置文件名

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-13

'''

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-13

'''

pgdb_logger.debug("operate_postgre_tbl_product enter...")

#连接数据库

try:

pgdb_conn = psycopg2.connect(database = 'kevin_test', user = 'dyx1024', password = '888888', host = '192.168.230.128')

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.")

pg_cursor = pgdb_conn.cursor()

#删除表

sql_desc = "DROP TABLE IF EXISTS tbl_product3;"

try:

pg_cursor.execute(sql_desc)

except Exception, e:

print 'drop table failed'

pgdb_logger.error("drop table failed, ret = %s" % e.args[0])

pg_cursor.close()

pgdb_conn.close()

return

pgdb_conn.commit()

pgdb_logger.info("drop table(tbl_product3) succ.")

#创建表

sql_desc = '''CREATE TABLE tbl_product3(

i_index INTEGER,

sv_productname VARCHAR(32)

);'''

try:

pg_cursor.execute(sql_desc)

except Exception, e:

print 'create table failed'

pgdb_logger.error("create table failed, ret = %s" % e.args[0])

pg_cursor.close()

pgdb_conn.close()

return

pgdb_conn.commit()

pgdb_logger.info("create table(tbl_product3) succ.")

#插入记录

sql_desc = "INSERT INTO tbl_product3(sv_productname) values('apple')"

try:

pg_cursor.execute(sql_desc)

except Exception, e:

print 'insert record into table failed'

pgdb_logger.error("insert record into table failed, ret = %s" % e.args[0])

pg_cursor.close()

pgdb_conn.close()

return

pgdb_conn.commit()

pgdb_logger.info("insert record into table(tbl_product3) succ.")

#查询表方法一

sql_desc = "select * from tbl_product3"

try:

pg_cursor.execute(sql_desc)

except Exception, e:

print 'select record from  table tbl_product3 failed'

pgdb_logger.error("select record from  table tbl_product3 failed, ret = %s" % e.args[0])

pg_cursor.close()

pgdb_conn.close()

return

for row in pg_cursor:

print row

pgdb_logger.info("%s", row)

print '*' * 20

#查询表方法二

sql_desc = "select * from tbl_test_port"

try:

pg_cursor.execute(sql_desc)

except Exception, e:

print 'select record from  table tbl_test_port failed'

pgdb_logger.error("select record from  table tbl_test_port failed, ret = %s" % e.args[0])

pg_cursor.close()

pgdb_conn.close()

return

for row in pg_cursor.fetchall():

print row

pgdb_logger.info("%s", row)

#关闭数据库连接

pg_cursor.close()

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()

二、测试：

1、命令行结果：

(None, 'apple')

********************

(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)

2、日志文件结果：

[2012-02-14 00:12:06,358  pg_log]DEBUG:  operate_postgre_tbl_product enter... (db_postgre.py:36)

[2012-02-14 00:12:06,453  pg_log]INFO:  conntect postgre database(kevin_test) succ. (db_postgre.py:46)

[2012-02-14 00:12:06,467  pg_log]INFO:  drop table(tbl_product3) succ. (db_postgre.py:62)

[2012-02-14 00:12:06,483  pg_log]INFO:  create table(tbl_product3) succ. (db_postgre.py:79)

[2012-02-14 00:12:06,483  pg_log]INFO:  insert record into table(tbl_product3) succ. (db_postgre.py:93)

[2012-02-14 00:12:06,483  pg_log]INFO:  (None, 'apple') (db_postgre.py:108)

[2012-02-14 00:12:06,483  pg_log]INFO:  (1, 2, 1) (db_postgre.py:124)

[2012-02-14 00:12:06,483  pg_log]INFO:  (2, 3, 1) (db_postgre.py:124)

[2012-02-14 00:12:06,483  pg_log]INFO:  (3, 5, 1) (db_postgre.py:124)

[2012-02-14 00:12:06,483  pg_log]INFO:  (5, 0, 1) (db_postgre.py:124)

[2012-02-14 00:12:06,483  pg_log]INFO:  (7, 18, 1) (db_postgre.py:124)

[2012-02-14 00:12:06,500  pg_log]INFO:  (8, 8, 1) (db_postgre.py:124)

[2012-02-14 00:12:06,500  pg_log]INFO:  (9, 7, 1) (db_postgre.py:124)

[2012-02-14 00:12:06,500  pg_log]INFO:  (10, 21, 1) (db_postgre.py:124)

[2012-02-14 00:12:06,500  pg_log]INFO:  (11, 23, 1) (db_postgre.py:124)

[2012-02-14 00:12:06,500  pg_log]INFO:  (12, 29, 1) (db_postgre.py:124)

[2012-02-14 00:12:06,500  pg_log]INFO:  (4, 3000, 1) (db_postgre.py:124)

[2012-02-14 00:12:06,500  pg_log]INFO:  (6, 1999, 1) (db_postgre.py:124)

[2012-02-14 00:12:06,500  pg_log]DEBUG:  operate_sqlite3_tbl_product leaving... (db_postgre.py:130)