Python操作MySQL数据库

dbaopers.py

#!/bin/env python
#coding:utf-8

import MySQLdb

class DBAOpers(object):

    def get_conn(self, host='192.168.56.159', user="root", passwd='root', port=3306, autocommit = True):
        conn = None
        try:
            conn=MySQLdb.connect(host, user, passwd, port=port,connect_timeout=3,charset='utf8')
            conn.autocommit(autocommit)
        except Exception,e:
            print e
        return conn

    def query_sql(self, conn, sql, db_name='test'):
        conn.select_db(db_name)
        cursor = conn.cursor()
        try :
            cursor.execute(sql)
            rows = cursor.fetchall()
        except Exception, e :
            print e
        finally:
            cursor.close()
            conn.close()
        return rows
    
    def dml_sql(self,conn,sql,param='',db_name='test'):
        conn.select_db(db_name)
        cursor = conn.cursor()
        try:
            if param:
                cursor.executemany(sql,param)
            else:
                cursor.execute(sql)
        except Exception,e:
            print e
        finally:
            cursor.close()
            conn.commit()
            conn.close()

db = DBAOpers()

操作事例

#coding: utf-8
import MySQLdb

from dbaopers import db

#一次插入多条数据
insert_many_sql = "insert into user values(%s,%s,%s)"
param = [[4,'qq','q'],[5,'ww','ww'],[6,'qwe','qwe']]

#一次插入一条数据
insert_one_sql = "insert into user values(1,'fyl','hello')"

#查询sql
query_sql = "select * from user;"


db.dml_sql(db.get_conn(), insert_many_sql, param)
db.dml_sql(db.get_conn(),insert_one_sql)
result = db.query_sql(db.get_conn(), query_sql,db_name='test')  

for i in result:
    print i