mysql 小demo

#! -*- 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()