Python之mysql数据库更新表数据接口实现
时间：2021-07-01 10:21:17
帮助过：9人阅读
# -*- coding: utf-8 -*-
2 import pymysql
3 import settings
4
5 class mysql(object):
6 def __init__(self):
7 self.db =
None
8
9 def connect(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 def disconnect(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
in columns:
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 def is_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 not results:
57 return None
58 else :
59 return results
60 # print datas
61 def insert_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
in datas:
75 values = dt.values()
## ‘str’ object has no attribute#
76 sql =
"insert into %s" % tablename +
keys
77 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 def insert_into_sql(self,sqls):
85 cursor =
self.db.cursor()
86 for sql
in sqls:
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 def find_columns(self, tablename):
97 sql =
"select COLUMN_NAME from information_schema.columns where table_name=‘%s‘" %
tablename
98 cursor =
self.db.cursor()
99 try:
100 cursor.execute(sql)
101 results =
cursor.fetchall()
102 except:
103 raise Exception(
‘hello‘)
104 return tuple(map(
lambda x: x[0], results))
105
106 def find(self, tablename, start_time, end_time, fieldName=
None):
107 """
108 :param tablename: test_scale1015
109 :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 return fieldName, results,
129
130 #样例 data = [{‘time‘:123321,‘predict‘:1.222},{‘time‘:123322,‘predict‘:1.223},{‘time‘:123324,‘predict‘:1.213}]
131 def updata(self,datas, tablename):
132 cursor =
self.db.cursor()
133 columns =
[]
134 for data
in datas:
135 for i
in data.keys():
136 columns.append(i)
137 # print(columns)
138 break
139 # columns_2=columns[:]
140 db.connect()
141 if db.is_table_exist(settings.tablename_2, settings.database):
142 # exists
143 # pass
144 for col
in columns:
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
in datas:
156 col =
[]
157 for ii
in i.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()
View Code
其中update（）函数，是新添加的接口：
传入的data的样例 data = [{‘time‘:123321,‘predict‘:1.222},{‘time‘:123322,‘predict‘:1.223},{‘time‘:123324,‘predict‘:1.213}] 这样子的。
一个列表里有多个字典，每个字典有time和predict。如果需要存predict_2,predict_3的时候，则实现更新操作，否则，只进行创表和插入数据的操作~~~~~~
看起来是不是很简单~~~~~~
这个接口还没有进行优化等操作，很冗余~~~~
毕竟项目还在测试阶段，等先跑通了，在考虑优化吧~~~~~~
有不足之处，希望指正，共同进步~~~~~~~
Python之mysql数据库更新表数据接口实现
标签：error init ide ted use cut com 分享图片 ...
