python更新数据库_Python之mysql数据库更新表数据接口实现

1 #-*- coding: utf-8 -*-

2 importpymysql3 importsettings4

5 classmysql(object):6 def __init__(self):7 self.db =None8

9 defconnect(self):10

11 self.db = pymysql.connect(host=settings.ip, port=settings.port, user=settings.mysql_user, passwd=settings.mysql_passwd, db=settings.database, )12 #print("connect is ok")

13 #return 1

14 defdisconnect(self):15 self.db.close()16 #return -1

17

18 def create_table(self, tablename, columns, spec='time'):19 """

20 :param tablename:21 :param spec:22 :param columns: 列表[]23 :return:24 """

25

26 type_data = ['int', 'double(10,3)']27 cursor =self.db.cursor()28 sql="create table %s("%(tablename,)29 sqls=[]30 for col incolumns:31 #判断是否time_num

32 if col==spec:33 sqls.append('%s %s primary key'%(col,type_data[0]))34 else:35 sqls.append('%s %s'%(col,type_data[1]))36

37 sqlStr = ','.join(sqls)38 sql+=sqlStr+')'

39 try:40 cursor.execute(sql)41 print("Table %s is created"%tablename)42 except:43 self.db.rollback()44

45 defis_table_exist(self, tablename,dbname):46 cursor=self.db.cursor()47 sql="select table_name from information_schema.TABLES where table_schema='%s' and table_name = '%s'"%(dbname,tablename)48 #results="error:Thie table is not exit"

49 try:50 cursor.execute(sql)51

52 results = cursor.fetchall() #接受全部返回行

53 except:54 #不存在这张表返回错误提示

55 raise Exception('This table does not exist')56 if notresults:57 returnNone58 else:59 returnresults60 #print datas

61 definsert_mysql_with_json(self, tablename, datas):62 """

63

64 :param tablename:65 :param datas:字典{(key: value),.....}66 :return:67 """

68 #keys = datas[0]

69 keys =datas[0].keys()70 keys =str(tuple(keys))71 keys = ''.join(keys.split("'")) #用' 隔开

72 print(keys)73 ret =[]74 for dt indatas:75 values = dt.values() ## ‘str’ object has no attribute#

76 sql = "insert into %s" % tablename +keys77 sql = sql + "values" +str(tuple(values))78 ret.append(sql)79 #print("1")

80 #print keys insert into %tablename dat[i] values str[i]

81

82 self.insert_into_sql(ret)83 print("1")84 definsert_into_sql(self,sqls):85 cursor =self.db.cursor()86 for sql insqls:87 #执行sql语句

88 try:89 cursor.execute(sql)90 self.db.commit()91 #print("insert %s" % sql, "success.")

92 except:93 #Rollback in case there is any error

94 self.db.rollback()95 #找列名

96 deffind_columns(self, tablename):97 sql = "select COLUMN_NAME from information_schema.columns where table_name='%s'" %tablename98 cursor =self.db.cursor()99 try:100 cursor.execute(sql)101 results =cursor.fetchall()102 except:103 raise Exception('hello')104 return tuple(map(lambdax: x[0], results))105

106 def find(self, tablename, start_time, end_time, fieldName=None):107 """

108 :param tablename: test_scale1015109 :param fieldName: None or (columns1010, columns1011, columns1012, columns1013, time)110 :return:111 """

112 cursor =self.db.cursor()113 sql = ''

114 if fieldName==None:115 fieldName =self.find_columns(tablename)116 sql = "select * from %s where time between %s and %s" %(tablename, str(start_time), str(end_time))117 #print('None')

118 else:119 fieldNameStr = ','.join(fieldName)120 sql = "select %s from %s where time between %s and %s" %(121 fieldNameStr, tablename, str(start_time), str(end_time))122 #print('sm')

123 try:124 cursor.execute(sql)125 results =cursor.fetchall()126 except:127 raise Exception('hello')128 returnfieldName, results,129

130 #样例 data = [{'time':123321,'predict':1.222},{'time':123322,'predict':1.223},{'time':123324,'predict':1.213}]

131 defupdata(self,datas, tablename):132 cursor =self.db.cursor()133 columns =[]134 for data indatas:135 for i indata.keys():136 columns.append(i)137 #print(columns)

138 break

139 #columns_2=columns[:]

140 db.connect()141 ifdb.is_table_exist(settings.tablename_2, settings.database):142 #exists

143 #pass

144 for col incolumns:145 if col != 'time':146 sql = "alter table %s add column %s double(10,3);" %(settings.tablename_2, col)147 try:148 cursor.execute(sql)149 print("%s is altered ok" %(col))150 except:151 print("alter is failed")152

153

154 ret =[]155 for i indatas:156 col =[]157 for ii ini.keys():158 col.append(ii)159 #time = col[0] and predict = col[1]

160 time_data =i[col[0]]161 predic_data = i[col[1]]162 sql = "update %s set %s='%s'where %s=%s"%(settings.tablename_2,col[1],predic_data,col[0],time_data)163 ret.append(sql)164 self.insert_into_sql(ret)165

166 #db.insert_mysql_with_json(tablename, datas)

167

168

169 else:170 #no exists

171 db.create_table(settings.tablename_2, columns)172 db.insert_mysql_with_json(settings.tablename_2, datas)173

174 db = mysql()