Python 连接MySQL

1、安装mysql库

pip install pymysql

2、使用

import pymysql
import time
from hashlib import md5


class my_MySQL(object):
    __user = 'root'  #__user 代表私有变量
    __password = 'root'
    __host = '127.0.0.1'
    __port = 3306
    __chatset = 'utf8'
    __database='blog'
    __obj=None
    __cur = None

    def __init__(self):
        if not self.__obj:
            self.__obj = pymysql.connect(
                host=self.__host,
                port=self.__port,
                user=self.__user,
                password=self.__password,
                database=self.__database,
                charset=self.__chatset
            )
            self.__cur = self.__obj.cursor()

        #print(self.__obj)



    def Insert(self,table,fileDict):
        """插入数据
            table  表名
            fileDict  字典 插入字段
        """
        #cur = self.__obj.cursor()
        try:
            keys=''
            values=''
            for key,value in fileDict.items():
                #print(self.__changeValue(value))
                #continue
                if keys:
                    keys =keys+','+key
                    values = values + ',' + self.__changeValue(value)
                else:
                    keys = key
                    values = self.__changeValue(value)

            sql_str = 'insert into {0} ({1}) values ({2}) '.format(table,keys,values)
            print(sql_str)

            self.__cur.execute(sql_str)
            self.__obj.commit()
        except:
            self.__obj.rollback()
            print('插入失败')


    def getRowsById(self,table,id,filedList='*'):
        """通过id获取数据
            table  表名
            id  id主键
            filedLis  字符段，要查询的字段
        """
        sql = "select {0} from {1} where id={2}".format(filedList,table,id)
        #cur = self.__obj.cursor()
        self.__cur.execute(sql)
        rows = self.__cur.fetchone()
        print(rows)
        resultDict={}
        i=0
        for v in self.__cur.description:
            resultDict[v[0]] = rows[i]
            i = i+1
        return resultDict


    def getRows(self,table,whereDict,filedList='*',group='',limit=''):
        """
            获取多行数据
            table  表名
            whereDict  where条件 字典
            filedList  字符串 获取字段
        """
        if whereDict:
            keys = ''
            for key, value in whereDict.items():
                if keys:
                    keys = keys + ' AND ' + key +'='+self.__changeValue(value)
                else:
                    keys = key +'='+self.__changeValue(value)

            sql = 'select {0} from {1} where {2} {3} {4}'.format(filedList,table,keys,group,limit)
            self.__cur.execute(sql)
            rows = self.__cur.fetchall()
            print(rows)
            resultDict = []
            j = len(rows)
            for j in range(0,j):
                i = 0
                my_dict={}
                for v in self.__cur.description:
                    my_dict[v[0]] = rows[j][i]
                    i = i + 1
                resultDict.append(my_dict)

            return resultDict
        else:
            return False


    def Update(self,table,whereDict,updataDict):
        """
        更新数据库
        :param table:  表名
        :param whereDict: 条件 字典
        :param updataDict: 更新字段
        :return: bool
        """
        #cur = self.__obj.cursor()  #获取游标
        if whereDict:
            try:
                keys = ''
                for key, value in whereDict.items():
                    if keys:
                        keys = keys + ' AND ' + key +'='+self.__changeValue(value)
                    else:
                        keys = key +'='+self.__changeValue(value)
                update=''
                for key, value in updataDict.items():
                    if update:
                        update = update + ',' + key +'='+self.__changeValue(value)
                    else:
                        update = key +'='+self.__changeValue(value)

                sql='update {0} set {1} where {2}'.format(table,update,keys)
                self.__cur.execute(sql)
                self.__obj.commit()
                return True
            except:
                self.__obj.rollback()
                return False
        else:
            return False


    def __changeValue(self,v):
        if (type(v) == int or type(v)==float):
            return str(v)
        elif (type(v) == str):
            return '"'+v+'"'
        elif (v == None):
            return ''
if __name__ == '__main__':
    a = my_MySQL()
    my_str = 'mypaassword'.encode(encoding='utf-8')
    md5Data = md5(my_str).hexdigest()

    #print(int(time.time()))

    #b={'username':'test','password':md5Data,'state':1,'add_time':int(time.time())}
    #a.Insert('user',b);
    #c = a.getRowsById('user','3,['id','state','password'])
    #c = a.getRows(table='user',whereDict={'state':1},filedList='id,username',limit='limit 0,2')
    c = a.Update('user',{'id':1,'state':2},{'username':1111,'state':3})
    print(c)