前言
昨天，因為項目需求要添加表的更新接口，來存儲預測模型訓練的數據，所以自己寫了一段代碼實現了該功能，在開始之前，給大家分享python/197720.html">python/204630.html">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() >>> conn = MySQLdb.connect(host='localhost',port = 3306,user='root', passwd='123456',db ='test',)
Connect() 方法用于創建數據庫的連接，里面可以指定參數：用戶名，密碼，主機等信息。
這只是連接到了數據庫，要想操作數據庫需要創建游標。
>>> cur = conn.cursor()
通過獲取到的數據庫連接conn下的cursor()方法來創建游標。
>>> cur.execute("create table student(id int ,name varchar(20),class varchar(30),age varchar(10))")
通過游標cur 操作execute()方法可以寫入純sql語句。通過execute()方法中寫如sql語句來對數據進行操作。
>>>cur.close()
cur.close() 關閉游標
>>>conn.commit()
conn.commit()方法在提交事物，在向數據庫插入一條數據時必須要有這個方法，否則數據不會被真正的插入。
>>>conn.close()
Conn.close()關閉數據庫連接
下面開始本文的正文：
Python實現mysql更新表數據接口
示例代碼
# -*- 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()其中update（）函數，是新添加的接口：
傳入的data的樣例 data = [{'time':123321,'predict':1.222},{'time':123322,'predict':1.223},{'time':123324,'predict':1.213}] 這樣子的。
一個列表里有多個字典，每個字典有time和predict。如果需要存predict_2,predict_3的時候，則實現更新操作，否則，只進行創表和插入數據的操作~~~~~~
看起來是不是很簡單~~~~~~
這個接口還沒有進行優化等操作，很冗余~~~~
畢竟項目還在測試階段，等先跑通了，在考慮優化吧~~~~~~
總結
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對VEVB武林網的支持。
新聞熱點
疑難解答
