Python实现mysql数据库更新表数据接口的功能

#coding=utf-8import MySQLdbconn= MySQLdb.connect(    host='localhost',    port = 3306,    user='root',    passwd='123456',    db ='test',    )cur = conn.cursor()#创建数据表#cur.execute("create table student(id int ,name varchar(20),class varchar(30),age varchar(10))")#插入一条数据#cur.execute("insert into student values('2','Tom','3 year 2 class','9')")#修改查询条件的数据#cur.execute("update student set class='3 year 1 class' where name = 'Tom'")#删除查询条件的数据#cur.execute("delete from student where age='9'")cur.close()conn.commit()conn.close()

# -*- coding: utf-8 -*-import pymysqlimport settingsclass mysql(object): def __init__(self):  self.db = None def connect(self):   self.db = pymysql.connect(host=settings.ip, port=settings.port, user=settings.mysql_user, passwd=settings.mysql_passwd, db=settings.database, )  # print("connect is ok")   # return 1 def disconnect(self):  self.db.close()  # return -1 def create_table(self, tablename, columns, spec='time'):  """  :param tablename:  :param spec:  :param columns: 列表[]  :return:  """  type_data = ['int', 'double(10,3)']  cursor = self.db.cursor()  sql="create table %s("%(tablename,)  sqls=[]  for col in columns:   #判断是否time_num   if col==spec:    sqls.append('%s %s primary key'%(col,type_data[0]))   else:    sqls.append('%s %s'%(col,type_data[1]))  sqlStr = ','.join(sqls)  sql+=sqlStr+')'  try:   cursor.execute(sql)   print("Table %s is created"%tablename)  except:   self.db.rollback() def is_table_exist(self, tablename,dbname):  cursor=self.db.cursor()  sql="select table_name from information_schema.TABLES where table_schema='%s' and table_name = '%s'"%(dbname,tablename)  #results="error:Thie table is not exit"  try:   cursor.execute(sql)   results = cursor.fetchall() #接受全部返回行  except:   #不存在这张表返回错误提示    raise Exception('This table does not exist')  if not results:    return None  else :   return results # print datas def insert_mysql_with_json(self, tablename, datas):  """  :param tablename:  :param datas:字典{(key: value),.....}  :return:  """  # keys = datas[0]  keys = datas[0].keys()  keys = str(tuple(keys))  keys = ''.join(keys.split("'")) # 用' 隔开  print(keys)  ret = []  for dt in datas:   values = dt.values() ##  ‘str' object has no attribute#   sql = "insert into %s" % tablename + keys   sql = sql + " values" + str(tuple(values))   ret.append(sql)   # print("1")  # print keys insert into %tablename dat[i] values str[i]  self.insert_into_sql(ret)  print("1") def insert_into_sql(self,sqls):  cursor = self.db.cursor()  for sql in sqls:   # 执行sql语句   try:    cursor.execute(sql)    self.db.commit()    # print("insert %s" % sql, "success.")   except:    # Rollback in case there is any error    self.db.rollback() #找列名 def find_columns(self, tablename):  sql = "select COLUMN_NAME from information_schema.columns where table_name='%s'" % tablename  cursor = self.db.cursor()  try:   cursor.execute(sql)   results = cursor.fetchall()  except:   raise Exception('hello')  return tuple(map(lambda x: x[0], results)) def find(self, tablename, start_time, end_time, fieldName=None):  """  :param tablename: test_scale1015  :param fieldName: None or (columns1010, columns1011, columns1012, columns1013, time)  :return:  """  cursor = self.db.cursor()  sql = ''  if fieldName==None:   fieldName = self.find_columns(tablename)   sql = "select * from %s where time between %s and %s" % (tablename, str(start_time), str(end_time))   # print('None')  else:   fieldNameStr = ','.join(fieldName)   sql = "select %s from %s where time between %s and %s" % (   fieldNameStr, tablename, str(start_time), str(end_time))   # print('sm')  try:   cursor.execute(sql)   results = cursor.fetchall()  except:   raise Exception('hello')  return fieldName, results,  #样例 data = [{'time':123321,'predict':1.222},{'time':123322,'predict':1.223},{'time':123324,'predict':1.213}] def updata(self,datas, tablename):  cursor = self.db.cursor()  columns = []  for data in datas:   for i in data.keys():    columns.append(i)   # print(columns)   break   # columns_2=columns[:]  db.connect()  if db.is_table_exist(settings.tablename_2, settings.database):    # exists    # pass    for col in columns:     if col != 'time':      sql = "alter table %s add column %s double(10,3);" % (settings.tablename_2, col)      try:       cursor.execute(sql)       print("%s is altered ok" % (col))      except:       print("alter is failed")         ret = []    for i in datas:     col = []     for ii in i.keys():      col.append(ii)     #time = col[0] and predict = col[1]     time_data = i[col[0]]     predic_data = i[col[1]]     sql = "update %s set %s='%s'where %s=%s"%(settings.tablename_2,col[1],predic_data,col[0],time_data)     ret.append(sql)    self.insert_into_sql(ret)    # db.insert_mysql_with_json(tablename, datas)  else:    # no exists    db.create_table(settings.tablename_2, columns)    db.insert_mysql_with_json(settings.tablename_2, datas)db = mysql()