MySQLdb与sqlalchemy的简单封装

一：MySQLdb

# !/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb
import MySQLdb.cursors

import configuration

class DataAccess :
    
    conn = None
    cursor = None
    isClosed = True

    def open(self,host=configuration.host,port=configuration.port,db=configuration.db,user=configuration.user,pwd=configuration.pwd) :
        
        self.conn = MySQLdb.connect(host=configuration.host,port=configuration.port,db=configuration.db,user=configuration.user,passwd=configuration.pwd,charset="utf8")
        self.cursor = self.conn.cursor()
        self.isClosed = False

    def execute(self,cmd) :
        if self.isClosed :
            raise Exception("db is not opened!")
        ret = self.cursor.execute(cmd)
        return ret

    def queryList(self,cmd) :
        if self.isClosed :
            raise Exception("db is not opened!")

        self.cursor.execute(cmd)
        rows = self.cursor.fetchall()

        return rows


    def commit(self):
        self.conn.commit()
    
    def rolback(self):
        self.conn.rolback()

    def close(self) :
        if self.isClosed :
            pass;
        
        self.conn.close();
        self.isClosed=True;

 

二：sqlalchemy

# !/usr/bin/python
# -*- coding: UTF-8 -*-

from sqlalchemy import *
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base

import configuration

class Persister(object):
    
    session = None;
    isClosed = True;

    def open(self,host=configuration.host,port=configuration.port,db=configuration.db,user=configuration.user,pwd=configuration.pwd) :
        
        url = 'mysql+mysqlconnector://%s:%s@%s:%d/%s' % (user,pwd,host,port,db)
        engine = create_engine(url)
        DbSession = sessionmaker(bind=engine)

        self.session = DbSession()

        self.isClosed = False

        return self.session

    def query(self,type) :
        
        query = self.session.query(type)
        return query

    def add(self,item) :
        self.session.add(item)        

    def add_all(self,items) :
        self.session.add_all(items)

    def delete(self,item) :
        self.session.delete(item)

    def commit(self) :
        self.session.commit()

    def close(self) :
        
        if self.isClosed :
            pass
        
        self.session.close()
        self.isClosed = True

三：测试代码

# !/usr/bin/python
# -*- coding: UTF-8 -*-

from sqlalchemy import *
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base

from entity import *
from utils.SSH import *
from dao.persister import *

from dao.dao import *

persister = Persister()
persister.open()
query = persister.query(w_node)
nodes = query.filter(w_node.master==False).all()

print len(nodes)


dao = DataAccess()
dao.open()

rows = dao.queryList("select * from w_node")
for row in rows :
    print row[0],row[1],row[2],row[3],row[4]

dao.close()
persister.close()