1.版本1：初始化
# -*- coding:utf-8 -*- from MySQLdb import * class MysqlHelper: def __init__(self,host,port,user,passwd,db,charset='utf8'): self.host=host self.port=port self.user=user self.passwd=passwd self.db=db self.charset=charset mysql1 = MysqlHelper("localhost",3306,"root","mysql","py31""utf8")
2.版本2：打开关闭方法
# -*- coding:utf-8 -*- from MySQLdb import * class MysqlHelper: """封装""" def __init__(self,host,port,user,passwd,db,charset='utf8'): """初始化""" self.host=host self.port=port self.user=user self.passwd=passwd self.db=db self.charset=charset def open(self): """连接数据库""" self.conn = connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, db=self.db, charset=self.charset) self.cursor1 = self.conn.cursor() def close(self): """关闭连接""" self.cursor1.close() self.conn.close() mysql1 = MysqlHelper("localhost",3306,"root","mysql","py31""utf8")
3.版本3：增加修改删除
# -*- coding:utf-8 -*- from MySQLdb import * class MysqlHelper: """封装""" def __init__(self,host,port,user,passwd,db,charset='utf8'): """初始化""" self.host=host self.port=port self.user=user self.passwd=passwd self.db=db self.charset=charset def open(self): """连接数据库""" self.conn = connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, db=self.db, charset=self.charset) self.cursor1 = self.conn.cursor() def close(self): """关闭连接""" self.cursor1.close() self.conn.close() def iud(self,sql_content): """增删改""" self.open() #调用open方法 self.sql = '%s'[sql_content] self.cursor1.execute(self.sql) self.conn.commit() self.close() #调用close方法 mysql1 = MysqlHelper("localhost",3306,"root","mysql","py31""utf8") sql_content = raw_input('请输入sql语句:') mysql1.iud(sql_content)
4.版本4：抛出异常，参数化
# -*- coding:utf-8 -*- from MySQLdb import * class MysqlHelper: """封装""" def __init__(self,host,port,user,passwd,db,charset='utf8'): """初始化""" self.host=host self.port=port self.user=user self.passwd=passwd self.db=db self.charset=charset def open(self): """连接数据库""" self.conn = connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, db=self.db, charset=self.charset) self.cursor1 = self.conn.cursor() def close(self): """关闭连接""" self.cursor1.close() self.conn.close() def iud(self,sql,params): """增删改""" try: self.open() #调用open方法 self.cursor1.execute(sql,params) self.conn.commit() self.close() #调用close方法 print('ok') except Exception as e: print(e.message) mysql1 = MysqlHelper("localhost",3306,"root","mysql","py31","utf8") name = "jack" id = 1 sql = 'update students set name=%s where id=%s' params=[name,id] mysql1.iud(sql,params)
/usr/bin/python2.7 /home/python/code/03-class封装.py
ok
+----+------------+--------+---------------------+----------+
| 1 | jack | | 1999-09-09 00:00:00 | |
5.版本5：查询
# -*- coding:utf-8 -*- from MySQLdb import * class MysqlHelper: """封装""" def __init__(self,host,port,user,passwd,db,charset='utf8'): """初始化""" self.host=host self.port=port self.user=user self.passwd=passwd self.db=db self.charset=charset def open(self): """连接数据库""" self.conn = connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, db=self.db, charset=self.charset) self.cursor1 = self.conn.cursor() def close(self): """关闭连接""" self.cursor1.close() self.conn.close() def iud(self,sql,params): """增删改""" try: self.open() #调用open方法 self.cursor1.execute(sql,params) self.conn.commit() self.close() #调用close方法 print('ok') except Exception as e: print(e.message) def all(self,sql,params=()): #默认参数 try: self.open() print "1" self.cursor1.execute(sql,params) result = self.cursor1.fetchall() #fetchall()获取多条数据 print "2" #fetchone()获取1条数据 print(result) self.close() return result #返回result except Exception as e: print(e.message) mysql1 = MysqlHelper("localhost",3306,"root","mysql","py31","utf8") sql = 'select * from students where id<5' result = mysql1.all(sql) print(result)
1 2 ((1L, u'jack', 'x01', datetime.datetime(1999, 9, 9, 0, 0), 'x00'), (2L, u'u817eu65ed', 'x01', datetime.datetime(1990, 2, 2, 0, 0), 'x00'), (3L, u'u7f51u6613', 'x01', None, 'x00'), (4L, u'u5c0fu7c73', 'x01', None, 'x00')) ((1L, u'jack', 'x01', datetime.datetime(1999, 9, 9, 0, 0), 'x00'), (2L, u'u817eu65ed', 'x01', datetime.datetime(1990, 2, 2, 0, 0), 'x00'), (3L, u'u7f51u6613', 'x01', None, 'x00'), (4L, u'u5c0fu7c73', 'x01', None, 'x00'))
6.版本6：获取单条数据
# -*- coding:utf-8 -*- from MySQLdb import * class MysqlHelper: """封装""" def __init__(self,host,port,user,passwd,db,charset='utf8'): """初始化""" self.host=host self.port=port self.user=user self.passwd=passwd self.db=db self.charset=charset def open(self): """连接数据库""" self.conn = connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, db=self.db, charset=self.charset) self.cursor1 = self.conn.cursor() def close(self): """关闭连接""" self.cursor1.close() self.conn.close() def iud(self,sql,params): """增删改""" try: self.open() #调用open方法 self.cursor1.execute(sql,params) self.conn.commit() self.close() #调用close方法 print('ok') except Exception as e: print(e.message) def all(self,sql,params=()): """获取多条数据""" try: self.open() self.cursor1.execute(sql,params) result = self.cursor1.fetchall() print(result) self.close() return result except Exception as e: print(e.message) def one(self,sql,params=()): """获取单挑数据""" try: self.open() self.cursor1.execute(sql,params) result = self.cursor1.fetchone() print(result) self.close() return result except Exception as e: print(e.message)
7.版本7：封装完成
#encoding=utf8 import MySQLdb class MysqlHelper(): def __init__(self,host,port,db,user,passwd,charset='utf8'): self.host=host self.port=port self.db=db self.user=user self.passwd=passwd self.charset=charset def connect(self): self.conn=MySQLdb.connect(host=self.host,port=self.port,db=self.db,user=self.user,passwd=self.passwd,charset=self.charset) self.cursor=self.conn.cursor() def close(self): self.cursor.close() self.conn.close() def get_one(self,sql,params=()): result=None try: self.connect() self.cursor.execute(sql, params) result = self.cursor.fetchone() self.close() except Exception, e: print e.message return result def get_all(self,sql,params=()): list=() try: self.connect() self.cursor.execute(sql,params) list=self.cursor.fetchall() self.close() except Exception,e: print e.message return list def insert(self,sql,params=()): return self.__edit(sql,params) def update(self, sql, params=()): return self.__edit(sql, params) def delete(self, sql, params=()): return self.__edit(sql, params) def __edit(self,sql,params): count=0 try: self.connect() count=self.cursor.execute(sql,params) self.conn.commit() self.close() except Exception,e: print e.message return count
8 当做第三方模块导入
