def __init__(self, user, passwd):
self.db = DB(host=db_config["host"],
user=db_config["user"],
password=db_config["passwd"],
db=db_config["db"])
self.user = user
self.passwd = passwd
def dbexcute():
from methods import DB
from config import db_config
db = DB(host=db_config["host"],
user=db_config["user"],
password=db_config["passwd"],
db=db_config["db"])
temp_arr = ['\xe6\x88\x91', '\xe6\x88\x91', 'user', '\xe6\x88\x91']
sql = "insert into user (username, password, role, email) values (%s, %s, %s, %s)" % (
'"\xe5\xa5\xbd"', '"\xe5\xa5\xbd"', '"user"', '"\xe5\xa5\xbd"')
sql = 'insert into user (username, password, role, email) values (' "\xe4\xb8\xad\xe5\x9b\xbd" ', ' "\xe4\xb8\xad\xe5\x9b\xbd" ', ' "user" ', ' "\xe4\xb8\xad\xe5\x9b\xbd" ')'
values = [u"坏人", u"坏人", u"坏人", u"坏人"]
temp_arr = []
for val in values:
if type(val) == unicode:
temp_arr.append(val.encode("utf-8"))
else:
temp_arr.append(val)
print temp_arr
print tuple(temp_arr)
sql = 'insert into user (username, password, role, email) values ("%s","%s","%s","%s")' % (
tuple(temp_arr))
print sql
db.execute(sql)
def __init__(self, excel, table):
self.db = DB(host=db_config["host"],
user=db_config["user"],
password=db_config["passwd"],
db=db_config["db"])
self.table = table # 定义表名
self.excel = excel # 定义excel
class ExcJoint:
def __init__(self,table):
self.db = DB(host=db_config["host"],user=db_config["user"],password=db_config["passwd"],db=db_config["db"])
self.table = table
# 主要从数据库获取需要的值并返回,格式[{},{},{}]
def sql_data(self):
num = len(table_thead[self.table]) # %s需要拼接多少次
split_sql = ','.join(['%s'] * num) # 计算出%s的个数供sql拼接使用
tmp_sql = "select " + split_sql + " from %s" # 拼接出待格式化的sql
format_sqlval = copy.deepcopy(table_thead[self.table]) # 添加需要导出数据表的key作为查询的key(copy.deepcopy作用为复制出一份独立而不是指向的列表)
format_sqlval.append(self.table) # 添加需要到处的数据表的名称(sql语句需要的格式化数据准备完毕)
return self.db.execute(tmp_sql % tuple(format_sqlval)) # 获取数据库指定表的指定key的值作为返回值
# 处理数据库中返回的值为[[],[],[]]格式,为导出excel做准备
def data_tailor(self):
res = [] # 结果返回列表
exc_head = excel_thead[self.table] # 引入excel表头文件
tab_head = table_thead[self.table] # 引入db表头文件
res.append(exc_head) # 将excel中第一行定义出来
for i in range(len(self.sql_data())): # 按索引循环数据库返回值
tmp = [i+1] # 每次循环索引+1(即excel中首列序号),tmp为excel每行值的容器
for key in tab_head: # 循环数据库表中的key
tmp.append(self.sql_data()[i][key]) # 完成excel每行数据的生成(即除了首列序号其它excel中每行的数据来自sql_data)
res.append(tmp) # 循环完成时就是excel数据生成完毕的时候
return res # 将数据值返回给函数,供excel生成使用
class DataImport:
def __init__(self, excel, table):
self.db = DB(host=db_config["host"],
user=db_config["user"],
password=db_config["passwd"],
db=db_config["db"])
self.table = table # 定义表名
self.excel = excel # 定义excel
# 拼接sql
def join_sql(self):
num = len(table_thead[self.table]) # %s需要拼接多少次
keys_sql = ",".join(["%s"] * num) # 拼接好的sql的keys
values_sql = ",".join(["'%s'"] * num) # 拼接好的sql的values
sql = "insert into %s (" + keys_sql + ") values (" + values_sql + ")" # 拼接完整的sql等待格式化
return sql
# 数据插入数据库
def data_import(self):
tmp_sql = self.join_sql() # 定义待格式化的sql
# 实例化处理过的excel的返回值
exc_data = ExcAnaly(excel=self.excel, table=self.table).excel_analy()
tmp_sqlval = [self.table] # 定义需要格式化的sql所需要的值的临时容器
# 循环数据表的keys,添加值格式化的临时容器中
for key in table_thead[self.table]:
tmp_sqlval.append(key)
#print exc_data
for data in exc_data: # 循环excel的值
#print data
sqlval = copy.deepcopy(
tmp_sqlval) # 定义真正的格式化sql所需要的值(目前已经有了表名/表keys,每循环一次都会重置一次)
for key in table_thead[self.table]: # 循环表的key
sqlval.append(
data[key]) # 将表的key所对应的value添加至sqlval容器中(此时需要的值已经全了)
sql = tmp_sql % tuple(sqlval) # 完成可用的sql
#print sql
try:
self.db.execute(sql) # 执行数据插入的sql
except Exception as error:
print error
print "数据导入失败"
print sql
return False
return True
class UserAuth:
def __init__(self, user, passwd):
self.db = DB(host=db_config["host"],
user=db_config["user"],
password=db_config["passwd"],
db=db_config["db"])
self.user = user
self.passwd = passwd
# 从数据库中获取用户/密码,结构[{},{},{}]
def sql_data(self):
sql = "select username, password from user"
try:
res = self.db.execute(sql)
except Exception as error:
print "从数据库获取用户信息失败"
print error
# 返回用户密码信息
return res
# 将用户/密码做验证
def user_auth(self):
user_info = {} # 用来存放用户名密码如{"John":"123456","Aaron":"23456"}
for one in self.sql_data():
user_info[one["username"]] = one[
"password"] # 处理数据库获取的信息并放入user_info中
if self.user in user_info: # 开始认证判断
if user_info[self.user] == self.passwd:
print "用户认证成功."
return 1
else:
print "认证密码错误."
return 2
else:
print "用户名不存在."
return 3
def __init__(self, table):
self.db = DB(host=db_config["host"],
user=db_config["user"],
password=db_config["passwd"],
db=db_config["db"])
self.table = table
class DBOPERAT:
def __init__(self, table):
self.db = DB(host=db_config["host"],
user=db_config["user"],
password=db_config["passwd"],
db=db_config["db"])
self.table = table
def insert(self, values):
values_num = len(values)
keys_num = len(table_thead[self.table])
if values_num != keys_num: # 将后端穿入的值与数据库表key的数量做验证,匹配即可继续
print "传入的值%s" % (str(values))
print "传入的值与表键数量不匹配应传%s,实传%s" % (keys_num, values_num)
return False
sqlvals = copy.deepcopy(table_thead[self.table]) # 定义格式化sql所需要的值的列表
sqlvals.insert(0, self.table)
temp_arr = [] # 临时list
for val in values: # 遍历插入的数据,遍历完成sqlval就完整了
if type(val) == unicode: # 判断元素是否为unicode字符
sqlvals.append(
val.encode("utf-8")) # 将unicode字符转换为utf-8类型字符串并添加到sqlval中
else:
sqlvals.append(val)
# 拼接出需要格式化的sql模型
tmp_sql = 'insert into %s (' + ",".join(
['%s'] * keys_num) + ') values (' + ",".join(
['"%s"'] * values_num) + ')'
# 格式化sql语句
sql = tmp_sql % (tuple(sqlvals))
print "执行数据库操作: %s" % (sql)
try:
self.db.execute(sql) # 执行添加数据的sql
except Exception as error:
print "数据添加失败.%s" % (sql)
print error
return False
else:
print "数据添加成功."
return True
def delete(self, id_arr):
for _id in id_arr:
sql = "delete from %s where id=%s" % (self.table, _id)
print "执行数据库操作: %s" % (sql)
try:
self.db.execute(sql) # 执行删除数据的sql
except Exception as error:
print "数据删除失败,id:%s" % (_id)
print error
continue
else:
print "数据删除成功,id:%s" % (_id)
return True
def update(self, values, _id):
values_num = len(values)
if self.table == "user": # user表只更新数据不更新用户名
keys_num = len(table_thead[self.table]) - 1
tmp_thead = copy.deepcopy(table_thead[self.table])
tmp_thead.pop(0)
else: # 其余表字段全部可以更新
keys_num = len(table_thead[self.table])
tmp_thead = copy.deepcopy(table_thead[self.table])
if values_num != keys_num: # 将后端传入的值与数据库表key的数量做验证,匹配即可继续
print "传入的值%s" % (str(values))
print "传入的值与表键数量不匹配应传%s,实传%s" % (keys_num, values_num)
return False
# 拼接出需要格式化的sql模型
tmp_sql = "update %s set" + ",".join(
[" %s='%s'"] * keys_num) + " where id='%s'"
sqlvals = [self.table] # 定义格式化sql所需要的值的列表
au_val = [] # 将sql的key和对应的value作为一个元祖插入到此列表作为格式化的值用
for i in range(keys_num):
au_val.append((tmp_thead[i], values[i].encode("utf-8")))
# 遍历au_val,添加到sqlval里
for x, y in au_val:
sqlvals.append(x)
sqlvals.append(y)
sqlvals.append(_id)
# 格式化sql语句
sql = tmp_sql % (tuple(sqlvals))
print "执行数据库操作: %s" % (sql)
try:
self.db.execute(sql) # 执行更新数据的sql
except Exception as error:
print "数据更新失败."
print error
return False
else:
print "数据更新成功."
return True
def select(self):
num = len(table_thead[self.table])
values = copy.deepcopy(table_thead[self.table])
values.append(self.table)
tmp_sql = "select id," + ",".join(
['%s'] * num) + " from %s" # 格式化出查询的sql
sql = tmp_sql % tuple(values)
print "执行数据库操作: %s" % (sql)
try:
res = self.db.execute(sql) # 执行查询数据的sql
except Exception as error:
print "数据查询失败."
print error
# return False
else:
print "数据查询成功."
return res