MySQLdb

MySQLdb设计实战

import MySQLdb
from DBUtils.PooledDB import PooledDB
pool = PooledDB(MySQLdb,5,host='localhost',user='root',passwd='pwd',db='myDB',port=3306,blocking=True) #5为连接池里的最少连接数

 
conn = pool.connection()  #以后每次需要数据库连接就是用connection（）函数获取连接就好了
cur=conn.cursor()
SQL="select * from table1"
r=cur.execute(SQL)
r=cur.fetchall()
cur.close()
conn.close()

# 创建数据库
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS=0;   #禁用外键约束
DROP TABLE IF EXISTS `employee_tbl`;


CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT NOT NULL,
   `runoob_title` VARCHAR(100) NOT NULL UNIQUE,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATETIME NOT NULL,
   `sex` enum('m','w','l') DEFAULT NULL,
    PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into runoob_tbl(runoob_title, runoob_author, submission_date) values("a", "b", now());
INSERT INTO runoob_tbl(runoob_title, runoob_author, submission_date) VALUES("JAVA 教程", "RUNOOB.COM", '2016-05-06');
UPDATE runoob_tbl SET runoob_title='a' WHERE runoob_id=1;
DELETE FROM runoob_tbl WHERE runoob_id=3;
SELECT * from runoob_tbl  WHERE runoob_title LIKE '%a%';
SELECT country FROM Websites UNION ALL SELECT country FROM apps ORDER BY country; UNION ALL不会去重
SELECT * from runoob_tbl ORDER BY submission_date DESC;    DESC升序，高的在上    AESC降序，高的在下
SELECT name FROM person_tbl WHERE name REGEXP 'ok$';       name字段中以'ok'为结尾

MySQLdb的使用

#mysql> create table `account`(
#    -> `acctid` int(11) default null comment 'XXXX',
#    -> `money` int(11) default null comment 'XXX'
#    -> ) ENGINE = innodb default charset = utf8;
#coding:utf8
import sys
import MySQLdb

class TransferMoney(object):
    def __init__(self,conn):
        self.conn = conn
    def transfer(self,s,t,money):
        try:
            self.check_acct_avilable(s)
            self.check_acct_avilable(t)
            self.has_enough_money(s,money)
            self.reduce_money(s,money)
            self.add_money(t,money)
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            raise e
    def check_acct_avilable(self,acctid):
        cursor = self.conn.cursor()
        try:
            cursor = self.conn.cursor()
            sql = "select * from account where acctid=%s"%acctid
            cursor.execute(sql)
            print "check_acct_avilable: " + sql
            rs = cursor.fetchall()
            if len(rs) != 1:
                raise Exception("not have this ID %s"%acctid)
        finally:
            cursor.close()

    def has_enough_money(self,acctid,money):
        cursor = self.conn.cursor()
        try:
            cursor = self.conn.cursor()
            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("accout not have enough money %s"%acctid)
        finally:
            cursor.close()            

    def reduce_money(self,acctid,money):
        cursor = self.conn.cursor()
        try:
            cursor = self.conn.cursor()
            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("ID reduce money fail %s"%acctid)
        finally:
            cursor.close()                

    def add_money(self,acctid,money):
        cursor = self.conn.cursor()
        try:
            cursor = self.conn.cursor()
            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("ID add money fail %s"%acctid)
        finally:
            cursor.close()                

if __name__ == "__main__":
    s = sys.argv[1]
    t = sys.argv[2]
    money = sys.argv[3]
    
    conn = MySQLdb.Connect(host = '127.0.0.1',port = 3306,user = 'root',passwd = 'wjl123',db = 'imooc',charset = 'utf8')
    t_m = TransferMoney(conn)
    
    try:
        t_m.transfer(s,t,money)
    except Exception as e:
        print e
    finally:
        conn.close()