python知识-python操作MySQL数据库

来源：互联网发布：淘宝知识产权投诉撤销编辑：程序博客网时间：2024/06/08 19:11

python DB API

python访问数据库的统一接口规范

DB API-连接数据库对象connection

创建方法：MySQLdb.Connect(参数)

connection对象支持的方法

DB API-数据库游标对象cursor

游标对象：用于执行查询和获取结果

cursor支持的方法：

execute方法：执行SQL，近结果从数据库返回到客户端

案例：

创建一个user表

create table user(
userid int(11) not null auto_increment,
username varchar(100) default null,
primary key (userid)
)engine=innodb auto_increment=9 default charset=utf8;

插入数据：

insert into user values ('','name1'),('','name2'),('','name3'),('','name4'),('','name5'),('','name6'),('','name7'),('','name8'),('','name9');

代码：

import MySQLdb

conn = MySQLdb.connect(host = '127.0.0.1',port = 3306,user = 'root',passwd = '',db = 'imooc',charset='utf8')
cursor = conn.cursor()

sql="select * from user"
cursor.execute(sql)
rs=cursor.fetchall()

for row in rs:
print "userid=%s,username=%s" % row

cursor.close()
conn.close()

结果是：

userid=9,username=name1
userid=10,username=name2
userid=11,username=name3
userid=12,username=name4
userid=13,username=name5
userid=14,username=name6
userid=15,username=name7
userid=16,username=name8
userid=17,username=name9

实例演示：insert/update/delete更新数据库

import MySQLdb

conn = MySQLdb.connect(host = '127.0.0.1',port = 3306,user = 'root',passwd = '',db = 'imooc',charset='utf8')
cursor = conn.cursor()
sql_insert="insert into user (userid,username) values ('','name10')"
sql_update="update user set username='name91' where userid=9"
sql_delete="delete from user where userid=13"

try:
    cursor.execute(sql_insert)
    print cursor.rowcount
    cursor.execute(sql_update)
    print cursor.rowcount
    cursor.execute(sql_delete)
    print cursor.rowcount

    conn.commit()
except Exception as e:
    print e
    conn.rollback()

cursor.close()
conn.close()

实例：银行转账系统

创建表：

create table account(
acctid int(11) default null,
money int(11) default null
)engine=innodb default charset=utf8;

说明：数据库的引擎这边不能设置为MyISAM，因为它支持事物

插入数据：

insert into account values (11,110),(12,10);

代码：

# -*- coding: utf-8 -*-
"""
Created on Wed Jan 06 10:09:09 2016

@author: lenovo
"""
import sys
import MySQLdb

class TransferMoney(object):
    def __init__(self,conn):
        self.conn=conn

    def check_acct_avaiable(self,acctid):
        cursor=self.conn.cursor()
        try:
            sql="select * from account where acctid=%s" % acctid
            cursor.execute(sql)
            print "check_acct_avaiable:"+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)
            cursor.execute(sql)
            print "has_enough_money:"+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)
            cursor.execute(sql)
            print "reduce_money:"+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)
            cursor.execute(sql)
            print "add_money:"+sql
            if cursor.rowcount != 1:
                raise Exception("账号%s加款失败" % acctid)
        finally:
            cursor.close()

    def transfer(self,source_acctid,target_acctid,money):
        try:
            self.check_acct_avaiable(source_acctid)
            self.check_acct_avaiable(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()
            raise e

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',port = 3306,user = 'root',passwd = '',db = 'imooc',charset='utf8')
    tr_money=TransferMoney(conn)

    try:
        tr_money.transfer(source_acctid,target_acctid,money)
    except Exception as e:
        print "出现问题："+str(e)
    finally:
        conn.close()

0 0