[Python知识库]python 连接mysql数据库 django源代码演示

from django.shortcuts import render

# Create your views here.
from django.http import HttpResponse
import pymysql


class PYMYSQLX():
	def __init__(self):
		self.conn = 0
		self.cursor=0
		super(PYMYSQLX, self).__init__()
		host = '127.0.0.1'
		user = 'sa'
		password = 'sa!@#'
		db = 'dddd'
		charset='utf8'
		print("连接数据库:", host)
		self.conn = pymysql.connect(host=host,port=3306, user=user, password=password, database=db, charset=charset)

		if(self.conn):
			print("连接数据库: 成功.")
		else:
			print("连接数据库: 失败.")
		self.cursor = self.conn.cursor()


	def __del__(self):
		print("关闭游标...")
		self.cursor.close()
		print("关闭数据库连接。")
		self.conn.close()


	def exec(self, sqlstr):
		self.cursor.execute(sqlstr)
		self.conn.commit()

	def select(self, sqlstr):
		self.cursor.execute(sqlstr)
		rs = self.cursor.fetchall()
		return len(rs)

	def getrscount(self, sqlstr):
		self.cursor.execute(sqlstr)
		rs = self.cursor.fetchall()
		iRet = len(rs)
		return iRet

	def addRecord(self,qunflag,data):
		sqlstr = "select id from qunuser where " + str(qunflag) + "='" + str(data[qunflag]) + "';"
		print("查询:", sqlstr)
		iCount = self.getrscount(sqlstr)
		if(iCount==0):
			print("查询结果:", iCount, " 添加记录")
			keys = ""
			vals = ""
			for key in data:
				if(key == qunflag):
					continue
				keys = keys + key + ","
				vals = vals + "'"+data[key]+"',"
			keys = keys + str(qunflag)
			vals = vals + "'"  + str(data[qunflag]) + "'"
			sqlstr = "insert into qunuser("+keys+") values("+vals+");"
			print("添加记录:", sqlstr)
			self.exec(sqlstr)
			return 0
		else:
			print("查询结果:", iCount, " 不添加记录")
			return iCount


	def GetRecord(self,sqlstr):
		#print("查询:", sqlstr)
		self.cursor.execute(sqlstr)
		
		results = self.cursor.fetchall()
		print("获取数量:", len(results))

		tab = "<div align=center><table border=1>"
		for row in results:
			tab = tab + "</tr>\n"
			
			'''for x in row: #这个虽然好用，但是功能性质的输出他就够呛勒
				print("row[x]", x)
				tab = tab + "<td>" + str(x) + "</td>"
			'''
			_id    = row[0]
			qunid  = row[1]
			qunname= row[2]
			nick   = row[3]
			qq     = row[4]
			qunjoin= row[5]
			qage   = row[6]
			qunLink = "<a href=" + str(qunid) +">"  + str(qunname) +"</a>"
			qLink   = '<a href=# οnclick=openqq("'+str(qq)+'");>'+str(nick)+'</a>'
			if(len(nick)==0):
				qLink   = '<a href="http://wpa.qq.com/msgrd?v=3&uin='+str(qq)+'&site=qq&menu=yes" target=_blank>'+str(qq)+'</a>'
			btntext     = '<a href="/polls/mark/?id='+str(_id)+ "&qq="+str(qq)+"\" οnclick=openqq('"+str(qq)+"');>标记</a>"
			tab = tab +  "<td>" + str(_id) + "</td>"+ "<td>" + qunLink + "</td>"+ "<td>" + str(qunjoin) + "</td>"+ "<td>" + str(qage) + "</td>" + "<td>" +str(qq) + "<td>" + str(qLink) + "</td>"+"</td>"+"<td>" + btntext + "</td>\n"

			tab = tab + "</tr>\n"
			

		tab = tab + "</table></div>\n"
		tab = tab + "<script>\n\
						function setClipboardText(value) {\n\
						const text = document.createElement('textarea');\n\
						text.value = value;\n\
						document.body.appendChild(text);\n\
						text.select();\n\
						document.execCommand('Copy');\n\
						text.remove();\n\
						}\n\
						function openqq(qq){\n\
						setClipboardText(qq);\n\
						var js='http://wpa.qq.com/msgrd?v=3&uin='+qq+'&site=qq&menu=yes';\n\
						var windowstyle = 'height=200,width=300,top=0,left=0,toolbar=no,menubar=no,scrollbars=no,resizable=yes,localtion=no,status=no';\
					    window.open(js, 'newwindow', windowstyle);\n\
						}\n\
						</script>"
		return tab




def index(request):
	sqlcli = PYMYSQLX()
	sqlstr = "select id, qunid, qunname, nick, qq, qunjoin,qage from qunuser where remark is  null order by qunjoin desc limit 50;"
	ret = sqlcli.GetRecord(sqlstr)
	return HttpResponse(ret)


def mark(request):
	sqlcli = PYMYSQLX()
	qunuser_id = request.GET.get('id')
	qunuser_qq = request.GET.get('qq')
	if( type(qunuser_id)==str):
		sqlstr = "update qunuser set remark='1' where id='"+str(qunuser_id)+"';"
		sqlcli.exec(sqlstr)
		jsstr = "<script>window.location.href='/polls/index/';</script>"
		return HttpResponse(jsstr)
		#return render(request,'web/mark.html',{'obj':jsstr})
	else:
		jsstr = "<script>alert('没有参数');window.location.href='/polls/index/';</script>"
		return HttpResponse(jsstr)
		#return render(request,'mark.html',{'obj':jsstr})