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没有足够的金额