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

Coded a python script (as attached) for insert the biller details to the dev db, it works well per my testing on the dev ec2 instance (xxx-util-services). It use the "awslambda-psycopg2" library to for the Postgres connection.

https://github.com/jkehler/awslambda-psycopg2

Next step is to make the script to be a AWS lambda compatiable (with Lambda function handlers) and then package with the library into a AWS Lambda zip package.

#!/usr/bin/python

importpsycopg2from datetime importdatetime

db_host= "xxxxxx-esb-xx.xxxxx.xx-xxxx.rds.xxxxx.com"db_port= 5432db_name= "xxx"db_user= "xxx"db_pass= "xxxx"db_table= "xxxxx"biller_code= "111111"biller_short_name= "test_short"biller_long_name= "test_long"

defmake_conn():

conn=Nonetry:

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"

returnconntry:

connection=make_conn()

cursor=connection.cursor()

cursor.execute("SELECT * FROM billers where biller_code = ‘%s‘;" %biller_code)

numOfRows=cursor.rowcountprint("%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")