python调用mysql数据进行计算_python使用peewee实现mysql数据操作

peewee可用class来创建表,增删改查,应该是相对余单表(本人几乎没用过,自以为如此)

想实现sql查询,得到list,比如这样的结果[{'user_name':'名字'},{'user_name':'名字'}]

但是查询结果是全是数据(元组),且找不到列名,后dir后逐个尝试发现列名可以使用result.description[j][0]获取

元组使用起来不方便,现实现将数据解析成想要的形式:[{}]

自己摸索的,比较粗糙,后续完善,再加入模糊查询

from peewee import *

import datetime

import time

import types

import pdb

import uuid

db = MySQLDatabase(host=host, user=user, passwd=passwd, database=database, charset=charset, port=port)

def select(table_name, columns='*', where='1=1', limit='0,10'):

'''

传入表名,要搜索的列(默认为*),查询条件,分页

返回一个list,其中元素为map,如[{'user_name':'名字'},{'user_name':'名字'}]

'''

sql = '''

select %s from %s where %s limit %s

''' % (columns, table_name, where, limit)

print sql

data_list = []

result = db.execute_sql(sql)

for array in result:

data_map = {}

j = 0

for value in array:

data_map[result.description[j][0]] = value

j += 1

data_list.append(data_map)

return data_list

def insert(table_name, values_list):

'''

传入表名,要插入的数据[{}]

因为数据表使用class创建,继承了基础表,在添加数据时加入基础表默认值

这里注意表中不能为空的列,否则可能报错

其中不同的数据类型使用不同的拼接方法,现只做了int,long,unicode,str

'''

columns = 'uuid,create_date,modify_date,status,'

keys = values_list[0].keys()

for key in keys:

columns = columns + key + ','

columns = '(' + columns[0:-1] + ')'

values = ''

for value_map in values_list:

data_uuid = uuid.uuid3(uuid.NAMESPACE_DNS, 'coonever')

time_str = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())

value = "'" + str(data_uuid) + "','" + time_str + "','" + time_str + "',0"

for value_one in value_map.values():

if type(value_one) == long or type(value_one) == int:

value = value + ',' + repr(value_one)

elif type(value_one) == unicode:

value = value + ",'" + value_one.encode('utf-8') + "'"

elif type(value_one) == str:

value = value + ",'" + value_one + "'"

values = values + '(' + value + ')' + ','

values = values[0:-1]

sql = '''

insert into %s %s values %s ;

''' % (table_name, columns, values)

print sql

db.execute_sql(sql)

def insertOne(table_name, value_map):

'''

为实现只插入一条数据,然后返回某个值,后续实现返回指定项,现返回uuid

传入表名,要插入的数据{}

因为数据表使用class创建,继承了基础表,在添加数据时加入基础表默认值

这里注意表中不能为空的列,否则可能报错

其中不同的数据类型使用不同的拼接方法,现只做了int,long,unicode,str

'''     columns = 'uuid,create_date,modify_date,status,'

keys = value_map.keys()

for key in keys:

columns = columns + key + ','

columns = '(' + columns[0:-1] + ')'

data_uuid = uuid.uuid3(uuid.NAMESPACE_DNS, 'coonever')

time_str = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())

value = "'" + str(data_uuid) + "','" + time_str + "','" + time_str + "',0"

for value_one in value_map.values():

if type(value_one) == long or type(value_one) == int:

value = value + ',' + repr(value_one)

elif type(value_one) == unicode:

value = value + ",'" + value_one.encode('utf-8') + "'"

elif type(value_one) == str:

value = value + ",'" + value_one + "'"

value = '(' + value + ')'

sql = '''

insert into %s %s values %s ;

''' % (table_name, columns, value)

print sql

db.execute_sql(sql)

return str(data_uuid)

def update(table_name, value_map, where='1=1'):

'''

更新数据,value_map中存放将要更新的列及值

'''

time_str = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())

set_value = "modify_date = '" + time_str + "',"

keys = value_map.keys()

values = value_map.values()

for key in keys:

if type(value_map[key]) == long or type(value_map[key]) == int:

set_value = set_value + key + "=" + repr(value_map[key]) + ","

elif type(value_map[key]) == unicode:

set_value = set_value + key + "='" + value_map[key].encode('utf-8') + "',"

elif type(value_map[key]) == str:

set_value = set_value + key + "='" + value_map[key] + "',"

set_value = set_value[:-1]

sql = '''

update %s set %s where %s ;

''' % (table_name, set_value, where)

print sql

db.execute_sql(sql)

def delete(table_name, where='1=1'):

'''

软删除,设置status=1

'''     sql = '''         update %s set status = 1 where %s ;     ''' % (table_name, where)     print sql     db.execute_sql(sql)