Python 进阶 (二十二)Python3 使用 PyMysql 连接 mysql 数据库

pip install pymysql3

#引入pymysqlimport pymysql #当成是mysqldb一样使用，当然也可以不写这句，那就按照pymysql的方式pymysql.install_as_MySQLdb()

import pymysql
print(pymysql)

#导入pymysql的包import pymysql
# print(pymysql)
try:    #获取一个数据库连接，注意如果是UTF-8类型的，需要制定数据库    conn = pymysql.connect(host='localhost', port=3308, user='lmapp', passwd='lmapp', db='test', charset='utf8')    cur = conn.cursor()#获取一个游标
    sql_query = "select * from user"    sql_insert = "insert into user(uid, uname, passwd) VALUES ('18853883587', 'SHQ', 'TEST')"    sql_update = "update user set uname='ZQY' WHERE uid='18353102061'"    sql_delete = "delete from user WHERE uid='18353102062'"
    cur.execute(sql_query)    data = cur.fetchall()    cur.execute(sql_insert)    print(cur.rowcount)    cur.execute(sql_update)    print(cur.rowcount)    cur.execute(sql_delete)    print(cur.rowcount)    for d in data :        #注意int类型需要使用str函数转义        print(type(d[0]))        print("UID: "+d[0]+'  用户名： '+d[1]+"  密码： "+d[2])  #提交事务conn.commit()    cur.close()#关闭游标    conn.close()#释放数据库资源except  Exception :#异常情况下，进行事务回滚conn.rollback()    print("操作失败")

#coding:utf8
import pymysql

class TranferMoney(object):    def __init__(self, conn):        self.conn = conn
    #检查账户有效性    def check_acct_available(self, source_acctid):        try:            cursor = self.conn.cursor()            sql_query = "select * from account where acctid='%s'"%source_acctid            cursor.execute(sql_query)            print('check_acct_available:', sql_query)            rs = cursor.fetchall()            if len(rs) != 1:                raise Exception('帐号%s不存在'%source_acctid)        finally:            cursor.close()
    #检查账户金额    def has_enough_money(self, source_acctid, money):        try:            print(type(money))            cursor = self.conn.cursor()            sql_query = "select * from account where acctid=%s and money >= %d"%(source_acctid, money)            cursor.execute(sql_query)            print('has_enough_money:', sql_query)            rs = cursor.fetchall()            if len(rs) != 1:                raise Exception('帐号%s余额不足'%source_acctid)        finally:            cursor.close()
    #账户减款    def reduce_money(self, source_acctid, money):        try:            cursor = self.conn.cursor()            sql_query = "update account set money=money-%d where acctid = '%s'"%(money, source_acctid)            cursor.execute(sql_query)            print('reduce_money:', sql_query)            if cursor.rowcount != 1:                raise Exception('帐号%s减款错误'%source_acctid)        finally:            cursor.close()
    #账户加款    def add_money(self, source_acctid, money):        try:            cursor = self.conn.cursor()            sql_query = "update account set money=money+%d where acctid = '%s'"%(money, source_acctid)            cursor.execute(sql_query)            print('reduce_money:', sql_query)            if cursor.rowcount != 1:                raise Exception('帐号%s加款错误'%source_acctid)        finally:            cursor.close()
    def transfer(self, source_acctid, target_accid, money):        try:            self.check_acct_available(source_acctid)            self.check_acct_available(target_accid)            self.has_enough_money(source_acctid, money)            self.reduce_money(source_acctid, money)            self.add_money(target_accid, money)            self.conn.commit()        except Exception as e:            print("Exception:", e)            self.conn.rollback()            raise e
if __name__ == '__main__':    source_acctid = input("请输入转账方帐号：")    target_accid = input("请输入收款方帐号：")    money = input("请输入转款金额：")    conn = pymysql.connect(host='localhost', port=3308, user='lmapp', passwd='lmapp', db='test', charset='utf8')    tranfer_money = TranferMoney(conn)    try:        tranfer_money.transfer(source_acctid, target_accid, int(money))        print("转账成功")    except Exception as e:        print('Error:', e)    finally:        conn.close()

#!/usr/bin/python# -*- coding: UTF-8 -*-
import MySQLdb
# 打开数据库连接db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
# 使用cursor()方法获取操作游标 cursor = db.cursor()
# SQL 插入语句sql = """INSERT INTO EMPLOYEE(FIRST_NAME,         LAST_NAME, AGE, SEX, INCOME)         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""try:   # 执行sql语句   cursor.execute(sql)   # 提交到数据库执行   db.commit()except:   # Rollback in case there is any error   db.rollback()
# 关闭数据库连接db.close()