Day 44 自动生成建表语句

创建表和生产查询语句

class IntegerField:
    text = 'int'

    def __init__(self, default=0, increment=False, is_primary=False, foreign=None):
        self.default = default
        self.increment = increment
        self.is_primary = is_primary
        self.foreign = foreign  # 可以是别的表的外键,类型是(其他表,表字段)


class StringField:
    text = 'varchar'

    def __init__(self, default=None, length=20):
        self.default = default
        self.length = length


class FloatField:
    text = 'float'

    def __init__(self, default=0):
        self.default = default


class DatetimeField:
    def __init__(self, auto_update=False):
        self.auto_update = auto_update
        if auto_update:
            text = 'timestamp'
        else:
            text = 'datetime'


# 创建表
class Create_table(type):
    def __init__(self, classname, bases, dicts):
        super().__init__(classname, bases, dicts)

        if self.__name__ == 'Model': return  # Model是我们实例化类的父类,并不是我们需要的,所以当name是model的时候我们应该跳过,在去找自定义的元类

        dicts = {k: v for k, v in dicts.items() if not k.startswith('__')}
        sql = 'create table %s(' % classname
        foreigns = {}

        for k, v in dicts.items():
            print(k, v)
            sql += k
            sql += (' ' + v.text)

            if isinstance(v, IntegerField):  # 如果v是int类型(v继承IntegerFiled)
                if v.is_primary:
                    sql += ' primary key'
                    v.default = None  # 如果为主键就不能有默认值
                if v.increment:
                    sql += ' auto_increment'
                if v.foreign:
                    foreigns[k] = v

            elif isinstance(v, StringField):  # v是str类型
                sql += '(%s)' % v.length

            # 除datetime外都有default,现在处理默认值
            if not isinstance(v, DatetimeField):
                if v.default:
                    sql += ' default %s' % v.default

            sql += ','

        # 处理外键问题
        for k, v in foreigns.items():
            sql += 'foreign key(%s) references %s(%s),' % (k, v.foreign[0], v.foreign[1])

        sql = sql.rstrip(',')
        sql += ')'
        print(sql)


# 处理增删改查,同时继承创建表功能
class Model(metaclass=Create_table):

    def get_primary(self):
        for k, v in self.__class__.__dict__.items():
            if isinstance(v, IntegerField) and v.is_primary == True:
                return k

    def get_primary_value(self):
        return getattr(self, self.get_primary(), None)  # getattr 第一个是对象,第二个是对象的属性,获取属性的值,第三个表格如果对象不存在返回None

    # 插入数据 insert into table (字段) vlaues(值)
    def insert(self):
        l = len(self.__dict__)
        l = '%s,' * l
        l = l.rstrip(',')  # 因为需要由pymysql拼接sql语句,所以要确认values中要传入多少个参数

        keys = str(list(self.__dict__.keys())).strip('[]').replace("'", "")
        values = list(self.__dict__.values())

        sql = 'insert into %s(%s) values(%s)' % (self.__class__.__name__, keys, l)
        print(sql)

    # 删除数据 delete from table where 主键 = 值(通过主键删除)
    def delete(self):
        primary_key = self.get_primary()
        primary_value = self.get_primary_value()
        sql = 'delete from %s where %s = %%s' % (self.__class__.__name__, primary_key)

        print(sql)

    # 更新数据update table sete 字段=值,字段=值 where 字段=值
    def update(self):
        update_keys = ''
        values = []

        for k, v in self.__dict__.items():
            update_keys += ' %s = %%s,' % k
            values.append(v)

        update_keys = update_keys.rstrip(',')
        sql = 'update %s set %s where %s = %%s' % (self.__class__.__name__, update_keys, self.get_primary())
        print(sql)
        values.append(self.get_primary_value())

    def select(self):
        pass


class User(Model):
    u_id = IntegerField(is_primary=True, increment=True)
    username = StringField(length=20)
    pwd = StringField(length=20)
    vip = StringField(length=20)
    balance = FloatField()


u = User()
u.update()
u.delete()
u.insert()