python连接postgresql数据库

连接postgresql数据库
 pip install psycopg2## 导入psycopg2包
 import psycopg2
 ## 连接到一个给定的数据库
 conn = psycopg2.connect(database="postgres", user="postgres",
                         password="postgres", host="127.0.0.1", port="23333")
 ## 建立游标，用来执行数据库操作
 cursor = conn.cursor()## 执行SQL命令
 cursor.execute("DROP TABLE test_conn")
 cursor.execute("CREATE TABLE test_conn(id int, name text)")
 cursor.execute("INSERT INTO test_conn values(1,'haha')")## 提交SQL命令
 conn.commit()## 执行SQL SELECT命令
 cursor.execute("select * from test_conn")## 获取SELECT返回的元组
 rows = cursor.fetchall()
 for row in rows:
     print 'id = ',row[0], 'name = ', row[1], '\n'## 关闭游标
 cursor.close()## 关闭数据库连接
 conn.close()参数化查询
 #list参数化查询
 #sql = "select * from pg_tables where schemaname=%s and tablename=%s"
 #csor.execute(sql, ['internal_app_bsaata', 'event_ip_real'])
 #dict参数化查询
 sql = "select * from pg_tables where schemaname=%(db_name)s and tablename=%(tb_name)s"
 csor.execute(sql, {'db_name':'internal_app_bsaata', 'tb_name':'event_ip_real'})##多条数据处理
 namedict = ({"first_name":"Joshua", "last_name":"Drake"},
             {"first_name":"Steven", "last_name":"Foo"},
             {"first_name":"David", "last_name":"Bar"})
 cur = conn.cursor()
 cur.executemany("""INSERT INTO bar(first_name,last_name) VALUES (%(first_name)s, %(last_name)s)""", namedict)注意：sql执行失败后，需要回滚才能继续执行后面的sql
try:
        ## 执行SQL命令
        dbconn.cursor.execute(sql)
        ## 提交SQL命令
        dbconn.conn.commit()
    except Exception as e:
        dbconn.conn.rollback()
        logging.exception(e)    # 方式2 -----------自己写的dbhelper.py
# coding:utf-8
import logging

import psycopg2

def dbconn(config):
    dbconn.conn = psycopg2.connect(database=config["database"], user=config["user"],
                                    password=config["password"], host=config["host"],
                                    port=config["port"])
    ## 建立游标，用来执行数据库操作
    dbconn.cursor = dbconn.conn.cursor()


def exec_sql(sql):
    #print sql
    try:
        ## 执行SQL命令
        dbconn.cursor.execute(sql)
        ## 提交SQL命令
        dbconn.conn.commit()
    except Exception as e:
        dbconn.conn.rollback()
        logging.exception(e)    # 方式2

def exec_sql_muti(sql,params):
    #print sql
    try:
        ## 执行SQL命令
        dbconn.cursor.executemany(sql,params)
        ## 提交SQL命令
        dbconn.conn.commit()
    except Exception as e:
        dbconn.conn.rollback()
        logging.exception(e)    # 方式2

def query(sql):
    print sql
    dbconn.cursor.execute(sql);
    return dbconn.cursor


def dbclose():
    dbconn.cursor.close()
    dbconn.conn.close()