Question

python操作数据库mysql数据库出现错误奇怪？望高人指点？

import MySQLdbdef get_conn(): host="127.0.0.1" port=3306 db="jikexueyuan" user="root" password="root" conn=MySQLdb.connect(host=host, user=user, password=password, db=db, port=port, charset="utf8") return connclass User(object): def __init__(self,user_id,user_name): self.user_id=user_id self.user_name=user_name def save(self): conn = get_conn() cursor = conn.cursor() sql = "insert into user(user_id,user_name)VALUES(%s,%s)" cursor.execute(sql, (self.user_id, self.user_name)) conn.commit() cursor.close() conn.close() @staticmethod def query_all(): conn=get_conn() cursor=conn.cursor() sql="select *from user" cursor.execute(sql) rows=cursor.fetchall() users=[] for r in rows: user=User(r[0],r[1]) users.append(user) conn.commit() cursor.close() conn.close() return users def __str__(self): return "id:{}--name:{}".format(self.user_id,self.user_name)

Answer 1

给你我写的python mysql扩展吧

#-*- coding: utf-8 -*-
#mysql by final v1.03e
from warnings import filterwarnings
import MySQLdb
filterwarnings('ignore', category = MySQLdb.Warning)

#from warnings import resetwarnings
#resetwarnings()

class mysql:

	def __init__(self,host,user,passwd,db,timeout=5,language="utf8"): 
		self.host=host
		self.user=user
		self.passwd=passwd
		self.db=db
		self.sql=""
		self.timeout=timeout
		try:
			self.conn=MySQLdb.connect(host=self.host,user=self.user,passwd=self.passwd,db=self.db)#,connect_timeout=self.timeout)
		except:
			print "MySQLdb connect error"
			return
		else:
			pass
		self.language(language)
		self.cursor=self.conn.cursor(MySQLdb.cursors.DictCursor)
		#self.cursor=self.conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
		self.cursor.execute("set names '%s'"%language)
		self.conn.text_factory=str
		self.fieldsarr=[]
		self.pagesize=20
		self.page=0
		self.usepage=0
	def language(self,language):
		self.conn.set_character_set(language)
	def fields(self):
		return self.fieldsarr
	def setpagesize(self,pagesize=0): 
	         self.pagesize=pagesize
	def getpageinfo(self):
		ifre=re.search("select ([0-9a-zA-Z|,| |']*) from ([0-9a-zA-Z|,|'|_| ]*)",self.pagesql)
		maxcount=self.fetch("select count(*) from %s"%ifre.group(2))[0]
		return [maxcount,int(maxcount/self.pagesize)]
	def query(self,sql):
		#print "!3"
		if self.usepage==1:
			sql="%s limit %s,%s"%(sql,(self.page-1)*self.pagesize,self.pagesize)
		self.usepage=0
		self.sql=sql
		#print sql
		self.cursor.execute(sql)
	def execute(self,sql):
		self.sql=sql
		self.cursor.execute(sql)
		self.conn.commit()
	def fetchall(self,sql="",page=-1):
		if page>=0:
			self.page=page+1
			self.usepage=1
			self.pagesql=sql
		if sql:
			self.query(sql)
		#self.fieldsarr=["%s"%x[0] for x in self.cursor.description]
		self.fieldsarr=self.cursor.description

		return self.cursor.fetchall()
	def fetch(self,sql=""):
		#print "222"
		if sql:
			self.query(sql)
		#self.fieldsarr=["%s"%x[0] for x in self.cursor.description]
		#print 0
		self.fieldsarr=self.cursor.description
		#print 1
		mfetchall=self.cursor.fetchall()
		#print 3
		if mfetchall:
			return mfetchall[0]
		else:
			return []
	def convstr(self,s,fun=None):
		if type(s) in (type(u""),type("")):
			if fun:
				#print "k fun"
				return fun(s,139)
			else:
				#print "no str"
				return str(s).replace("'","''")
		else:
			#print "num"
			return s
	def tosql(self,sql):
		return self.conn.escape_string(sql)
	def addnew(self,tablename,data={},fun=None,commit=True):
		if fun:
			sql="insert into %s (%s) values (%s)"%(tablename,",".join(["`%s`"%y for y in data]),",".join(["'%s'"%self.convstr(data[y],fun) for y in data]))
		else:
			sql="insert into %s (%s) values (%s)"%(tablename,",".join(["`%s`"%y for y in data]),",".join(["'%s'"%str(data[y]).replace("'","''") for y in data]))
		sql=sql.replace("'fdm:datetime'","now()")
		sql=sql.replace("'fdm:time'","UNIX_TIMESTAMP()")
		#print sql
		self.cursor.execute(sql)
		self.insertid=int(self.conn.insert_id())
		if commit==True:
			self.conn.commit()
			
	def delete(self,tablename,ids=[]):
		sql="delete from %s where id in (%s)"%(tablename,",".join(["%s"%y for y in ids]))
		self.cursor.execute(sql)
		self.conn.commit()
		
	def update(self,tablename,data={},where=""):
		sql="update %s set %s"%(tablename,",".join(["`%s`='%s'"%(x,str(data[x]).replace("'","''")) for x in data]))
		if where:
			sql="%s where %s"%(sql,where)
		sql=sql.replace("'fdm:datetime'","now()")
		sql=sql.replace("'fdm:time'","UNIX_TIMESTAMP()")
		#print sql
		self.cursor.execute(sql)
		self.conn.commit()
		
	def __del__(self):
		print "close mysql";
		self.cursor.close()
		self.conn.close()
	
	def insert_id(self):
		self.conn.insert_id()
		
	def close(self):
		self.__del__()