python postgresql外部表_Python学习笔记 - PostgreSQL的使用

# coding:utf8

import sys

import psycopg2 #PostgreSQL

class TransferMoney(object):

def __init__(self, conn):

self.conn = conn

def check_acct_available(self, acctid):

cursor = self.conn.cursor()

try:

sql = "select * from account where acctid='%s'" % acctid

print("check_acct_available:" + sql)

cursor.execute(sql)

rs = cursor.fetchall()

if len(rs) != 1:

raise Exception("帐号%s不存在" % acctid)

finally:

cursor.close()

def has_enough_money(self, acctid, money):

cursor = self.conn.cursor()

try:

sql = "select * from account where acctid='%s' and money>%s" % (

acctid, money)

print("has_enough_money:" + sql)

cursor.execute(sql)

rs = cursor.fetchall()

if len(rs) != 1:

raise Exception("帐号%s没有足够的金额" % acctid)

finally:

cursor.close()

def reduce_money(self, acctid, money):

cursor = self.conn.cursor()

try:

sql = "update account set money=money-%s where acctid='%s' " % (

money, acctid)

print("reduce_money:" + sql)

cursor.execute(sql)

if cursor.rowcount != 1:

raise Exception("帐号%s减款失败" % acctid)

finally:

cursor.close()

def add_money(self, acctid, money):

cursor = self.conn.cursor()

try:

sql = "update account set money=money+%s where acctid='%s' " % (

money, acctid)

print("add_money:" + sql)

cursor.execute(sql)

if cursor.rowcount != 1:

raise Exception("帐号%s加款失败" % acctid)

finally:

cursor.close()

def transfer(self, source_acctid, target_acctid, money):

try:

self.check_acct_available(source_acctid)

self.check_acct_available(target_acctid)

self.has_enough_money(source_acctid, money)

self.reduce_money(source_acctid, money)

self.add_money(target_acctid, money)

self.conn.commit()

except Exception as e:

self.conn.rollback()

print("transfer出现异常：" + str(e))

raise e

def main():

source_acctid = sys.argv[1]

print("转出帐号=" + source_acctid)

target_acctid = sys.argv[2]

print("转入帐号=" + target_acctid)

money = sys.argv[3]

print("金额=" + money)

# 连接数据库 MySql

#conn = pymysql.Connect(

# host='localhost',

# port=3306,

# user='root',

# passwd='root',

# db='OtkDb',

# charset='utf8')

# 连接数据库PostgreSQL

conn = psycopg2.connect(

host='localhost',

port=5432,

user='postgres',

password='postgres',

database='OtkDb')

tr_money = TransferMoney(conn)

try:

tr_money.transfer(source_acctid, target_acctid, money)

except Exception as e:

print("main出现异常：" + str(e))

finally:

conn.close()

if __name__ == '__main__':

main()