python对数据库操作

# -*- coding: utf-8 -*-
"""
@description: xxx
@time: 2019/12/22 1:03
@author: baojinlong
"""

import random
import time
import datetime
import pymysql
from faker import Faker
import pandas as pd


# 从全路径下导入工具类

class MySqlUtils(object):
    # init
    def __init__(self, host, user, password, db_name, port=3306, charset='utf8'):
        self.conn = pymysql.Connect(
            host=host,
            port=port,
            user=user,
            passwd=password,
            db=db_name,
            charset=charset
        )
        self.cursor = self.conn.cursor()

    # insert
    def insert(self, tbName, field, values):
        insSql = 'insert into %s(%s) values (%s)' % (tbName, field, values)
        print('sql=', insSql)
        return self.excute(insSql)

    # select
    def select(self, tbName, field='*', where='1=1'):
        selSql = "select %s from %s where %s" % (field, tbName, where)
        print('sql=', selSql)
        return self.excute(selSql)

    # update
    def update(self, keyValues, tbName, where='1=1'):
        setValue = ''
        for k, v in keyValues.items():
            setValue += '`%s`="%s",' % (k, v)
        if where:
            where = " where " + where
        updateSql = "update %s set %s %s" % (tbName, setValue[:-1], where)
        return self.excute(updateSql)

    # delete
    def delete(self, tbName, where='1=1'):
        delSql = "delete from %s %s where " % (tbName, where)
        return self.excute(delSql)

    # execute
    def excute(self, sql):
        try:
            if sql.find('select') != -1:
                self.cursor.execute(sql)
                return self.cursor.fetchall()
            elif sql.find('insert') != -1 or sql.find('update') != -1 or sql.find('delete') != -1:
                self.cursor.execute(sql)
                self.conn.commit()
                return True
            else:
                return False
        except Exception as e:
            print(str(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())) + '--' + str(e))
            return False

    # __del__
    def __del__(self):
        self.cursor.close()
        self.conn.close()


if __name__ == '__main__':
    # 创建类
    host='xxxxxxxxxxxxx'
    user='xxxxx'
    port=xxxxx
    passwd='xxxxxxx'
    db_name='xxxxxx'
    table_name = 'xxxxx'
    mySqlUtils = MySqlUtils(host, user, passwd, db_name)
    # 读取文件
    all_data = [data for data in open('D:\Data_script\wx_union_id.txt', 'r')]
    print("读取到文件大小", len(all_data))
    for index, value in enumerate(all_data):
        keyValues = {'wx_union_id': value}
        mySqlUtils.update(keyValues, table_name, f'id={index + 1}')
    else:
        print('数据全部更新完毕')