#! -*- coding:utf-8 -*- import pymysql import os import time class Mysql(object): def __init__(self,host='localhost',port=3306,user='root',password='root123',db='hongxi'): self.host = host self.port = port self.user = user self.password = password self.db = db self.conn = pymysql.connect(host=self.host,user = self.user,port = self.port,password = self.password,db = self.db) self.conn.autocommit(True) self.cur = self.conn.cursor() # print "**" def db_sql(self,sql,parms): """执行插入sql语句""" self.cur.execute(sql,parms) def db_sql_select(self,sql): """执行查询sql语句""" self.cur.execute(sql) return self.cur def db_close(self): """关闭数据库""" self.cur.close() self.conn.close() def openFile(jsontxt): with open(jsontxt,"r") as fd: jsoninfo = fd.readline().strip() datadict = eval(jsoninfo) # print datadict for i in range(1,len(datadict)): #id name camid faceid feature Day Time #取出人的详细信息,然后保存到数据库中 data = datadict[str(i)] time = data["Time"] # print time,data["Name"] list1 = [] for key,value in time.items(): t1 = int(value.split(":")[0])*3600+int(value.split(":")[1])*60+int(value.split(":")[2]) list1.append((key,t1)) # print list1 list2 = sorted(list1,key=lambda x:x[1],reverse=False) timestr = '' camidstr = '' faceidstr = '' name = data["Name"] for key,val in list2: print type(key),type(val) timestr += data["Time"][key]+" " camidstr += data["CamId"][key] + " " faceidstr += data["FaceId"][key] + " " conn = Mysql() # print conn # img1 = "图片" sql = "insert into t1(name,camid,faceid,Time) values(%s,%s,%s,%s)" parms = (name, camidstr, faceidstr, timestr) conn.db_sql(sql, parms) conn.db_close() #如果插入ID已经存在,则进行更新数据,若是不存在,则插入一条新的数据 def updateAndinsert(): for i in range(4,10): id = i name = str(i) name = ("ddd","gggg") age = i+20 conn = Mysql() #如果插入ID已经存在,则进行更新数据,若是不存在,则插入一条新的数据 sql = "insert into t2(id,name,age) values(%s,%s,%s) on duplicate key update name=values(name),age=values(age)" parm = (i,name, age) conn.db_sql(sql,parm) conn.db_close() #把数据库已有的字段 字符串元素进行增加 def updateConcat(): """ select name,age from t1 where id =1; >>"lisi",32 update t1 set name=concat(name,",zhangsan"),age=concat(age+3) where id=1; select name,age from t1 where id =1; >>"lisi,zhangsan",35 :return: """ conn = Mysql() sql = "update t2 set name=concat(name,%s),age=concat(age+%s) where id=1;" parm = (",hong", 10) conn.db_sql(sql, parm) conn.db_close() #从数据库拿数据 def selectMysql(): conn = Mysql() sql = "select * from t2;" info_cur = conn.db_sql_select(sql) conn.db_close() info = info_cur.fetchall() for data in info: t1 = tuple(data[1]) print t1 if __name__ == "__main__": # updateConcat() # openFile('jsonInfo.txt') # selectMysql()
mysql 小demo
最新推荐文章于 2021-01-19 07:59:55 发布