前言
昨天，因为项目需求要添加表的更新接口，来存储预测模型训练的数据，所以自己写了一段代码实现了该功能，在开始之前，给大家分享python 操作mysql数据库基础：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
#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更新表数据接口
示例代码
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
|
# -*- 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的时候，则实现更新操作，否则，只进行创表和插入数据的操作~~~~~~
看起来是不是很简单~~~~~~
这个接口还没有进行优化等操作，很冗余~~~~
毕竟项目还在测试阶段，等先跑通了，在考虑优化吧~~~~~~
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对服务器之家的支持。
原文链接：http://www.cnblogs.com/BigStupid/p/7859429.html
