python操作pgSQL

#连接数据库需要提供相应的数据库名称、用户名、密码、地址、端口等信息
conn=psycopg2.connect(database=db,user=user,password=pw,host=host,port=port)
curs=conn.cursor()
    
select_sql="select * from table"        #从表格table中读取全表内容
curs.execute( select_sql)               #执行该sql语句
data = curs.fetchall()                  #获取数据
  
curs.close()                                    
conn.close()

conn=psycopg2.connect(database=db,user=user,password=pw,host=host,port=port)
curs=conn.cursor()
   
update_sql="UPDATE table SET col_C=3 WHERE col_A=1 AND col_B=2"
insert_sql="insert into table (col_A,col_B,col_C) values {0}".format(str((1,2,3)))
curs.execute(update_sql)
#python这个psycopg2包自带这个属性rowcount，表示上一个操作影响的行数
#如果影响的行数为0，则表中没有满足条件的记录，则写入该行
if curs.rowcount==0:                 
         curs.execute(insert_sql)
conn.commit()

curs.close() 
conn.close()

#!/usr/bin/python
import psycopg2
from datetime import datetime
 
db_host = "xxxxxx-esb-xx.xxxxx.xx-xxxx.rds.xxxxx.com"
db_port = 5432
db_name = "xxx"
db_user = "xxx"
db_pass = "xxxx"
db_table = "xxxxx"
biller_code = "111111"
biller_short_name = "test_short"
biller_long_name = "test_long"
 
def make_conn():
    conn = None
    try:
        conn = psycopg2.connect("dbname='%s' user='%s' host='%s' password='%s'" % (db_name, db_user, db_host, db_pass))
        print "connected to postgres db successfully"
    except:
        print "I am unable to connect to the database"
    return conn
 
 
try:
    connection = make_conn()
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM billers where biller_code = '%s';" % biller_code)
    numOfRows = cursor.rowcount
    print("%s rows found for the biller code: %s" % (numOfRows, biller_code))
    if(numOfRows<=0):
        dt = datetime.now()
        cursor.execute("insert into billers (biller_code, biller_short_name, biller_long_name, active, min_length, max_length, reg_exp, check_digit_algo, created_at, updated_at) values (%s, %s, %s, true, 0, 100, 'NONE', 'NONE', %s, %s)", (biller_code, biller_short_name, biller_long_name, dt, dt))
        connection.commit()
        print("inserted %s rows successfully" % cursor.rowcount)
except (Exception, psycopg2.Error) as error :
    print ("Error caught", error)
finally:
    #closing database connection.
        if(connection):
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")

可用用的带变量的方法，不用直接写数值

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import string
 
import psycopg2
import random
 
from psycopg2 import extras
 
address_list = {'北京', '上海', '深圳', '广州', '长沙', '成都', '吉林', '邵阳', '九江', '长春', '拉萨'}
 
conn = psycopg2.connect(database="test", user="test", password="test", host="127.0.0.1", port="5433")
print("Opened database successfully")
 
cur = conn.cursor()
 
# values 后面直接%s
sql = '''INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) 
         VALUES %s
    '''
datalist = []
for i in range(1, 10000000):
    # 生成6位随机字符串
    name = ''.join(random.sample(string.ascii_letters + string.digits, 8))
    age = random.randint(18, 80)
    address = random.sample(address_list, 1)[0]
    salary = random.randrange(5000, 100000)
    # 行数据是以元组的形式存放
    datalist.append((++i, name, age, address, salary))
extras.execute_values(cur, sql, datalist, page_size=20000)
conn.commit()
 
print("All records created successfully")
 
conn.close()