python 操作mysql

之前写了mongo的，mentor说mysql会好一点。。尽量做了一些解耦，但还有操作的空间，留待下次用到再做调整。

python -m pip install mysql-connector-python

import mysql.connector

class User:

    first_name = None
    last_name = None
    email = None
    password = None
    authority = None

    def __init__(self,Email,Password,FirstName,LastName = "",Authority = 1):
        self.email = Email
        self.password = Password
        self.first_name = FirstName
        self.last_name = LastName
        self.authority = Authority
    def json(self):
        json = {
                    "Email":self.email,
                    "Password":self.password,
                    "FirstName":self.first_name,
                    "LastName":self.last_name,
                    "Authority":self.authority
                }
        return json

def getKeys(user):
    keys = []
    for i in vars(user).keys():
        keys.append(i)
    return ",".join(keys)

def getValues(user):
    vals = []
    for i in  range(len(vars(user).keys())):
        vals.append("%s")
    return ",".join(vals)

class Mysqldb:

    mydb = None

    def __init__(self,host="localhost",user="root",passwd="Rinna@2",database="GameDemo"):
        self.mydb = mysql.connector.connect(
            host = host,
            user = user,
            passwd = passwd,
            database = database
        )

    def getCursor(self):
        if not self.mydb.is_connected():
            self.mydb.reconnect()
        cursor = self.mydb.cursor()
        return cursor()
    def insert(self,user,flag=False):
        cursor = self.mydb.cursor()
        sql = f"insert into {type(user).__name__}({getKeys(user)}) values({getValues(user)})"
        if flag :
            sql = f"insert into {type(user[0]).__name__}({getKeys(user[0])}) values({getValues(user[0])})"
            vals = []
            for i in user:
                vals.append(tuple(vars(user).values()))
            print("="*30+"insert:many"+"="*30)
            print(sql)
            print(val)
            print("="*30+"insert:many"+"="*30)
            cursor.executemany(sql,val)
            self.mydb.commit()
            return 1
        u = User(user.email,None,None,None,None)
        select_res = self.select(["*"],u)
        if len(select_res)!=0:
            return -1
        print("="*30+"insert:one"+"="*30)
        print(sql)
        print(tuple(vars(user).values()))
        print("="*30+"insert:one"+"="*30)
        cursor.execute(sql,tuple(vars(user).values()))
        self.mydb.commit()
        return 1

    def select(self,wants=["*"],user=User(None,None,None,None,None)):
        sql = f"select {' , '.join(wants)} from {type(user).__name__}"
        condition = vars(user)
        condi = []
        for i in condition:
            if(condition[i]!=None):
                condi.append(f"{i}='{condition[i]}'")
        if len(condi)!=0 :
            sql+=" where "
            sql+=" and ".join(condi)
        cursor = self.mydb.cursor()
        print("="*30+"select"+"="*30)
        print(sql)
        print("="*30+"select"+"="*30)
        cursor.execute(sql)
        res=cursor.fetchall()
        result = []
        for i in res:
            result.append(list(i))
        return result


    def delete(self,user,isOr=False):
        cursor = self.mydb.cursor()
        condition = vars(user)
        condi = []
        sql = f"delete from {type(user).__name__}"
        for i in condition:
            if(condition[i]!=None):
                condi.append(f"{i}='{condition[i]}'")
        if len(condi)!=0 :
            sql += " where "
            con = ""
            if isOr:
                con = "or".join(condi)
            else:
                con = "and".join(condi)
            sql+=con
            print("="*30+"delete:condi!=0"+"="*30)
            print(sql)
            print("="*30+"delete:condi!=0"+"="*30)
            cursor.execute(sql)
            self.mydb.commit()
            return 1
        sql = f"delete * from {type(user[0]).__name__}"
        print("="*30+"delete:condi==0"+"="*30)
        print(sql)
        print("="*30+"delete:condi==0"+"="*30)
        cursor.execute(sql)
        self.mydb.commit()
        return 1

    def update(self,user,accords,isOr=False):
        sets = []
        wheres = []
        condition = vars(user)
        condi = []
        #sql = f"delete from {type(user[0]).__name__}"
        for i in condition:
            if(condition[i]!=None):
                if i in accords:
                    wheres.append(f"{i}='{condition[i]}'")
                else :
                    sets.append(f"{i}='{condition[i]}'")
        if(len(sets)==0 or len(wheres)==0):
            return -1

        sql= f"update {type(user).__name__} set "+" , ".join(sets)+" where "
        if isOr:
            sql += " or ".join(wheres)
        else:
            sql += " and ".join(wheres)
        cursor=self.mydb.cursor()
        print("="*30+"update"+"="*30)
        print(sql)
        print("="*30+"update"+"="*30)
        cursor.execute(sql)
        self.mydb.commit()
        return 1