python mysql offset_Python操作Mysql类

#!/usr/bin/python

# -*- coding: UTF-8 -*-

import MySQLdb

from warnings import filterwarnings

filterwarnings('error', category = MySQLdb.Warning)

class MySQL :

__conn = None

__cursor = None

def __init__(self, host, user, passwd, db ,port = 3306, charset = 'utf8'):

try:

self.__conn = MySQLdb.connect(host=host, user=user, passwd=passwd, db=db, port=port,charset=charset)

self.__cursor = self.__conn.cursor()

except MySQLdb.Warning, w:

print "警告信息 %s" % str(w)

except MySQLdb.Error, e:

print "错误信息 %d %s" % (e.args[0], e.args[1])

#插入数据

def insert(self, table, dict):

if len(dict) > 0 :

keys = dict.keys()

keyStr = ""

valStr = ""

for key in keys:

keyStr += "`" + str(key) + '`,'

valStr += "'" + str(dict[key]) + "',"

keyStr = keyStr.rstrip(',')

valStr = valStr.rstrip(',')

sql = "INSERT INTO `" + str(table) + "` (" + keyStr + ") VALUES " + "("+ valStr +")"

print '执行语句: %s' % sql

try :

self.__cursor.execute(sql)

#lastId = self.__conn.insert_id() #这个一定要在commit之前！！！

lastId = self.__cursor.lastrowid

self.__conn.commit()

return lastId

except:

print '插入数据异常!执行回滚!'

self.__conn.rollback()

return False

else:

return False

#查询一条数据

def find(self, table, fields = "*", where = "", order = 'id desc'):

sql = "select " + str(fields) + " from `"+ str(table) + "` "

if where != '':

sql += " where " + str(where)

sql += " order by " + str(order) + " limit 1"

print "执行语句: %s" % sql

try:

self.__cursor.execute(sql)

return self.__cursor.fetchone()

except :

print '查询失败'

return False

#查询多条数据

def fetchall(self, table, fields = "*", where = "", order = "id desc", offset = "0", limit = '20'):

sql = "select " + str(fields) + " from `" + str(table) + "` "

if where != '':

sql += " where " + str(where)

sql += " order by " + str(order) + " limit " + str(offset) + "," + str(limit)

print "执行语句: %s" % sql

try:

self.__cursor.execute(sql)

return self.__cursor.fetchall()

except:

print '查询失败'

return False

# 删除数据

def delete(self, table, where, limit = 1):

sql = "delete from `" + str(table) + "` where " + str(where)

if limit > 0 :

sql += " limit " + str(limit)

print '执行语句: %s' % sql

try:

self.__cursor.execute(sql)

self.__conn.commit()

return True

except:

print '删除异常！执行回滚!'

self.__conn.rollback()

return False

# 更新数据

def update(self, table, dict, where):

keys = dict.keys()

updateStr = ""

for key in keys:

updateStr += "`" + str(key) + "` = '" + str(dict[key]) +"',"

updateStr = updateStr.rstrip(',')

sql = "update `"+str(table)+"` set "+updateStr+" where " + where

print '执行语句: %s' % sql

try:

self.__cursor.execute(sql)

self.__conn.commit()

return True

except:

print '更新异常！执行回滚！'

self.__conn.rollback()

# 释放资源

def __del__(self):

self.__cursor.close()

self.__conn.close()

############################################案例##############

#!/usr/bin/python

# -*- coding: UTF-8 -*-

import time

from MySQL import *

mysql = MySQL(host='10.200.10.203', user='test', passwd='test', db='test')

# print mysql.find('test', 'count(*) as t')

# print mysql.fetchall('test')

# print mysql.delete('test', 'id = 69')

create_time = time.strftime('%Y-%m-%d %H:%M:%S')

content ='这里是测试内容'

name = '测试'

dict = {'name':name,'create_time':create_time,'content':content}

lastid = mysql.insert('test', dict)

name = '测试最新的！！！'

dict = {'name':name}

print mysql.update('test',dict,"id = " + str(lastid))