PyMySQL

安装

conda install -n mysql_venv PyMySQL  # 安装import pymysqlconn = MySQLdb.connect(host='127.0.0.1', port=3306, user='root', passwd='123123',db='test', charset='utf8')  # 创建connnection对象，连接数据库# connection对象支持方法：cursor()创建游标,close()关闭连接，commit()提交当前事务,rollback()滚回当前事务cursor = conn.cursor()  # 创建cursor对象# curosr对象方法：execute(sql)执行一条数据库命令;fetchone()/fetchmany(size)/fetchall()获取缓冲区中下一行/下几行/剩下所有行;rowcount返回execute执行的行数;close()关闭游标# select 操作# 或insert/update/delete操作cursor.close()  # 释放缓冲区资源conn.close()

SELECT操作

sql = '''select * from user1;'''cursor.execute(sql)print cursor.rowcountprint cursor.fetchone()print cursor.fetchmany(2)print cursor.fetchall()cursor.close()

I/U/D操作

事务：访问和更新数据库的一个程序执行单元
因为需要支持事务，所以ENGINE=InnoDB，不能为MyISAM.

sql_insert = '''insert values('bing');'''sql_update = '''update tb3 username='Bing' where username='bing';'''sql_delete = '''delete from tb3 where username='Bing';'''try:    cursor.execute(sql_insert)    cursor.execute(sql_update)    cursor.execute(sql_delete)    conn.commit()  # 使cursor.execute生效excpt Exception as e:    print e    conn.rollback()  # 如果slq_delete执行失败，使得sql_insert,sql_update都执行失效# conn.rollback()保证了一组事务，要么都执行，要么都不执行.

案例

# 创建表create table account(    acctid int default null comment 'account ID',    money int default null comment ’remain')    engine=InnoDB default charset=utf8;# python部分import sysimport MySQLdbclass TransferMoney(object):    def __init__(self, conn):        self.conn = conn    def transfer(self, soruce_acctid, target_acctid, money):        try:            self.check_acct_available(source_acctid)            self.check_acct_available(targe_acctid)            self.has_enough_money(source_acctid, money)            self.has_enough_money(target_acctid, money)            self.reduce_money(source_acctid, money)            self.add_money(target_acctid, money)            self.conn.commit()        except Exception as e:            self.conn.rollback()            raise e    def check_acct_available(self, acctid)        cursor = self.connect.cursor()        try:            sql = '''select * from account where acctid=%s''' % acctid            print "check_acct_available: " + sql            rs = cursor.execute(sql)            if len(rs) != 1:                raise Exception("account %s is not exists" % acctid)        finally:            cursor.close()    def has_enough_money(self, acctid, money):        cursor = self.connect.cursor()        try:            sql = ''' select * from account where acctid=%s and money > %s''' % (acctid, money)            print "has_enough_money: " + sql            rs = cursor.execute(sql)            if len(rs) != 1:                raise Exception("account %s dose not has enough money" % acctid)        finally:            cursor.close()    def reduce_money(self, acctid, money):        cursor = self.connect.cursor()        try:            sql = ''' update account set money=money-%s where acctid=%s''' % (money,acctid)            print "reduce_money: " + sql            rs = cursor.execute(sql)            if cursor.rowcount != 1:                raise Exception("account %s failed to reduce money" % acctid)        finally:            cursor.close()    def add_money(self, acctid, money):        cursor = self.connect.cursor()        try:            sql = ''' update account set money=money+%s where acctid=%s''' % (money,acctid)            print "add_money: " + sql            rs = cursor.execute(sql)            if cursor.rowcount != 1:                raise Exception("account %s failed to add money" % acctid)        finally:            cursor.close()if __name__ == '__main__':    source_acctid = sys.argv[1]    target_acctid = sys.argv[2]    money = sys.argv[3]    conn = MySQLdb.connect(host='127.0.0.1', user='root', passwd='123123', db='test')    tr_money = TransferMoney(conn)    try:        tr_money.transfer(source_acctid, target_acctid, money)    except Exception as e:        print e    finally:        conn.close()

总结

1. 创建connection对象，获取cursor
2. 使用cursor执行SQL
3. 使用cursor获取数据，判断执行状态
4. 提交事务或回滚事务
5. 关闭cursor，关闭connection