Python实现mysql数据库更新表数据接口的功能
前言
昨天，因为项目需求要添加表的更新接口，来存储预测模型训练的数据，所以自己写了一段代码实现了该功能，在开始之前，给大家分享python 操作mysql数据库基础：
#coding=utf-8
import MySQLdb
conn= 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 pymysql
import settings
class 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的时候，则实现更新操作，否则，只进行创表和插入数据的操作~~~~~~
看起来是不是很简单~~~~~~
这个接口还没有进行优化等操作，很冗余~~~~
毕竟项目还在测试阶段，等先跑通了，在考虑优化吧~~~~~~
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对【听图阁-专注于Python设计】的支持。
