Python-PostgreSQL的使用详解数据库

yum install  postgresql-devel 
 
pip3 install psycopg2

pymysql.Connect()参数说明 
host(str):      MySQL服务器地址 
port(int):      MySQL服务器端口号 
user(str):      用户名 
password(str):  密码 
database(str):  数据库名称 
 
connection对象支持的方法 
cursor()        使用该连接创建并返回游标 
commit()        提交当前事务 
rollback()      回滚当前事务 
close()         关闭连接 
 
cursor对象支持的方法 
execute(op)     执行一个数据库的查询命令 
fetchone()      取得结果集的下一行 
fetchmany(size) 获取结果集的下几行 
fetchall()      获取结果集中的所有行 
rowcount()      返回数据条数或影响行数 
close()         关闭游标对象

-- ---------------------------- 
-- Table structure for account 
-- ---------------------------- 
DROP TABLE IF EXISTS `account`; 
CREATE TABLE `account`  ( 
  `acctid` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, 
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, 
  `money` decimal(50, 0) NULL DEFAULT NULL 
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; 
 
-- ---------------------------- 
-- Records of account 
-- ---------------------------- 
INSERT INTO `account` VALUES ('1', '张三', 50); 
INSERT INTO `account` VALUES ('2', '李四', 150);

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

PS H:/web/Python> & python h:/web/Python/01.MySql/db.py 1 2 50 
转出帐号=1 
转入帐号=2 
金额=50 
check_acct_available:select * from account where acctid='1' 
check_acct_available:select * from account where acctid='2' 
has_enough_money:select * from account where acctid='1' and money>50 
reduce_money:update account set money=money-50 where acctid='1' 
add_money:update account set money=money+50 where acctid='2' 
 
 
PS H:/web/Python> & python h:/web/Python/01.MySql/db.py 1 2 50 
转出帐号=1 
转入帐号=2 
金额=50 
check_acct_available:select * from account where acctid='1' 
check_acct_available:select * from account where acctid='2' 
has_enough_money:select * from account where acctid='1' and money>50 
transfer出现异常：帐号1没有足够的金额 
main出现异常：帐号1没有足够的金额