lmysql.py

#!/usr/bin/python3
 
import pymysql
# 数据库账号
userName = "root"
# 数据库密码
userPwd = "root"
# 数据库名称
databaseName = "app"
# 增
def useSqliteInsert(dic):
	conn = pymysql.connect("localhost",userName,userPwd,databaseName,port=3306,charset='utf8')
	cursor = conn.cursor()
	string = ''
	string2 = ''
	string3 = ''
	arr3 = []
	string33 = ''
	for x in dic:
		if x=='database':
			continue
			pass
		if x=='table':
			continue
			pass
		if x=='key':
			continue
			pass
		if string == '':
			string = x + ' ' +'text'
			string2=x
			string3= '\''+ dic[x]+'\''
			string33 = '?'
			pass
		else:
			string = string+', ' + x + ' ' +'text'
			string2 = string2+','+x
			string3 = string3+','+'\''+ dic[x]+'\''
			string33 = string33 + ',' + '?'
		arr3.append(dic[x])
		pass

	try:
		print('create table if not exists '+dic["table"]+' (id integer NOT NULL PRIMARY KEY auto_increment , '+ string +')')
		cursor.execute('create table if not exists '+dic["table"]+' (id integer NOT NULL PRIMARY KEY auto_increment , '+ string +')')
		string3 = string3.replace("'",'"')
		cursor.execute('insert into '+dic["table"]+' ('+string2+') values ('+string3+')')
	except Exception as e:
		print("---------------error---------------")
		print(e)
		print("---------------error---------------")
		print('insert into '+dic["table"]+' ('+string2+') values ('+string3+')')
		acount = 1
		while acount==1:
			try:
				cursor.execute('insert into '+dic["table"]+' ('+string2+') values ('+string3+')')
				acount=2
			except Exception as e:
				error = str(e)
				print(error)
				if 'Unknown column' in error:
					cursor = conn.cursor()
					# Unknown column 'XingBie' in 'field list'"
					ziduan = error.split("Unknown column '")[1].split("' in ")[0]
					astr2="alter table "+dic["table"]+" add "+ziduan+" varchar(255) not null default '';"
					cursor.execute(astr2)
					conn.commit()
					print(astr2)
				else:
					acount=2

	cursor.close()
	conn.commit()
	conn.close()

def dict_factory(cursor, row):
	d = {}
	for idx, col in enumerate(cursor.description):
		d[col[0]] = row[idx]
	return d
# 删
def useSqliteDelete(data):
	conn = pymysql.connect("localhost",userName,userPwd,databaseName,port=3306,charset='utf8')
	conn.row_factory = dict_factory 
	cursor = conn.cursor()
	print('===========================')
	# print('DELETE  from ' + data["table"] + ' WHERE id=\'' + data["id"]+'\'')
	try:
		cursor.execute('DELETE  from ' + data["table"] + ' WHERE id=\'' + data["id"]+'\'')
		pass
	except Exception as e:
		print(e)
		raise
	cursor.close()
	conn.commit()
	conn.close()

# 查
def useSqliteSelect(database,table):
	conn = pymysql.connect("localhost",userName,userPwd,databaseName,port=3306,charset='utf8',cursorclass = pymysql.cursors.DictCursor)
	conn.row_factory = dict_factory 
	cursor = conn.cursor()
	print('SELECT * from '+table)
	cursor.execute('SELECT * from '+table)
	values = cursor.fetchall()
	cursor.close()
	conn.commit()
	conn.close()
	return values

# 查
def useSqliteSelectByKey(dic):
	conn = pymysql.connect("localhost",userName,userPwd,databaseName,port=3306,charset='utf8')
	conn.row_factory = dict_factory 
	cursor = conn.cursor()
	# print('SELECT * from '+table+' WHERE nickname = '+key)
	# print('SELECT * from '+dic["table"]+' WHERE '+ dic['key'] +' = '+str(dic["value"] )+'  limit '+str(dic["limit"])+' offset '+str(int(dic["offset"])*int(dic["limit"])))
	cursor.execute('SELECT * from '+dic["table"]+' WHERE '+ dic['key'] +' = \''+str(dic["value"] )+'\' ORDER BY id desc limit '+str(dic["limit"])+' offset '+str(dic["offset"]))
	values = cursor.fetchall()
	cursor.close()
	conn.commit()
	conn.close()
	return values

# 改
def useSqliteUpdate(dic):
	conn = pymysql.connect("localhost",userName,userPwd,databaseName,port=3306,charset='utf8')
	cursor = conn.cursor()
	string3 = ''
	astr=''
	acount = 1
	while acount==1:
		try:
			cursor = conn.cursor()
			for x in dic:
				if x=='database':
					continue
					pass
				if x=='table':
					continue
					pass
				if x=='id':
					continue
					pass
				if x=='key':
					continue
					pass
				if string3 == '':
					string3= x +' = ' '\''+ str(dic[x])+'\''
					pass
				else:
					string3 = string3+' , '+  x +' = ' '\''+ str(dic[x])+'\''
				pass
			# cursor.execute('create table if not exists '+dic["key"]+' (id integer NOT NULL PRIMARY KEY AUTOINCREMENT , '+ string +')')
			astr = 'UPDATE '+dic["table"]+' SET '+string3+' WHERE id = \''+str(dic["id"])+'\''
			cursor.execute(astr)
			cursor.close()
			conn.commit()
			print(astr)
			acount=2
		except Exception as e:
			error = str(e)
			print(error)
			if 'Unknown column' in error:
				cursor = conn.cursor()
				# Unknown column 'XingBie' in 'field list'"
				ziduan = error.split("Unknown column '")[1].split("' in ")[0]
				astr2="alter table "+dic["table"]+" add "+ziduan+" varchar(255) not null default '';"
				cursor.execute(astr2)
				cursor.close()
				conn.commit()
				print(astr2)
			else:
				acount=2
	conn.close()
# 查询数据库中的所有表名
def useSqliteAllTable(dic):
	conn = pymysql.connect("localhost",userName,userPwd,databaseName,port=3306,charset='utf8')
	cursor = conn.cursor()
	astr = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
	cursor.execute(astr)
	values = cursor.fetchall()
	cursor.close()
	conn.commit()
	conn.close()
	return values

# 查询表中所有的字段名
def userSqliteTabelDetail(dic):
	conn = pymysql.connect("localhost",userName,userPwd,databaseName,port=3306,charset='utf8')
	cursor = conn.cursor()
	table = dic["table"]
	cursor.execute('pragma table_info('+table+')')
	col_name=cursor.fetchall()
	col_name=[x[1] for x in col_name]
	values = []
	for x in col_name:
		if x=='id':
			continue
			pass
		values.append(x)
		pass
	# cursor.execute(astr)
	# values = cursor.fetchall()
	# print(values)
	cursor.close()
	conn.commit()
	conn.close()
	return values