Mysql数据库读写 python实践

python 写入数据到mysql数据库

# coding=utf-8
"""
写入单条数据到mysql数据库中
"""
import pymysql
from loguru import logger


class MysqlCur:
    def __init__(self):
        self.db_name = 'ldap_gophish'  # 数据库名称
        self.db_user = 'root'  # 数据库账号
        self.db_pass = 'kRX3KqV0menGwRub'  # 数据库密码
        self.db_ip = "192.168.137.195"  # 数据库IP地址
        self.db_port = 3306  # 数据库端口
        self.db = pymysql.Connection(host=self.db_ip, user=self.db_user, password=self.db_pass, port=self.db_port, database=self.db_name)  # 连接数据库
        self.cur = self.db.cursor()  # 数据库游标

    def mysql_conn(self, Sheet, Name, Job, Branch, Mail):
        """
        将ldap中的工号、邮箱等信息更新写入数据库，不重复写入
        Sheet:数据库表名
        Name:姓名
        Job:工号
        Branch:部门
        Mail:邮箱
        """
        try:
            sql = "insert into {sheet}".format(sheet=Sheet) + "(name,工号,部门,邮箱) values('%s','%s','%s','%s')" % (Name, Job, Branch, Mail)  # 写入一条数据到mysql
            self.cur.execute(sql)
            self.db.commit()
            logger.info("工号写入数据库成功: " + Job)
            # cur.close()
        except pymysql.err.IntegrityError as f:
            logger.error("工号重复: " + Job)
            # logger.error(f)

    def mysql_query(self, Job):
        """
        查询工号是否在数据库内
        Job:工号
        """
        try:
            sql = """
                    select * from 财务 where 工号="{Job}" 
                    union all
                    select * from 投资 where 工号="{Job}"
            """.format(Job=Job)
            self.cur.execute(sql)
            data = self.cur.fetchone()  # 将在查询的内容打印出一条
            return data
        except Exception as f:
            logger.error(f)

    def mysql_update(self, Sheet, Job):
        """
        更新数据
        Sheet:数据库表
        Job：工号
        """
        try:
            sql = "update {Sheet} set send = 1 where 工号 = '{Job}'".format(Sheet=Sheet, Job=Job)
            self.cur.execute(sql)
            self.db.commit()
        except Exception as f:
            logger.error(f)


# if __name__ == "__main__":
    # mysql_cur = MysqlCur()
    # mysql_cur.mysql_update("test_list","611111")