来聊聊数据库的映射关系：ORM

python通过pymysql操作数据库时，因为sql命令的问题，开发比较繁琐，同时也不利于数据库的切换

 

但是当我们使用ORM时，会方便一些，但是执行效率会下降

大概的关系就是这样  ，通过python中的 ORM_sqlalchemyl 来和数据库进行交互

sqlalchemy 是python最为常用的的三方ORM模块，flask和tornado在生产环境中。

pip install sqlalchemy

创建表

数据库 ：Create table tb1(id int primary key auto_increment)

下面是交互数据库的代码

import sqlalchemy

#链接数据库
db = sqlalchemy.create_engine('mysql+pymysql://root:1111@localhost/school')
#数据库类型+操作数据库模块 ://用户:密码@主机/数据库名称
meta = sqlalchemy.MetaData(db) #实例化数据库创建的元类

student = sqlalchemy.Table(
    'student', #表明
    meta, #关联数据库链接
    sqlalchemy.Column("name",sqlalchemy.String(32)), #字段的描述
    sqlalchemy.Column("age", sqlalchemy.Integer),
    sqlalchemy.Column("gender", sqlalchemy.String(32)),
    sqlalchemy.Column("project", sqlalchemy.String(32)),
) #定义表

meta.create_all()

面向对象创建表

import sqlalchemy

from sqlalchemy.ext.declarative import declarative_base

#链接数据库
db = sqlalchemy.create_engine('mysql+pymysql://root:1111@localhost/school')
#数据库类型+操作数据库模块 ://用户:密码@主机/数据库名称
base = declarative_base(db) #实例化数据库创建的元类

class User(base):
    __tablename__ = "user"

    id = sqlalchemy.Column(sqlalchemy.Integer,primary_key = True)
    name = sqlalchemy.Column(sqlalchemy.String(32))
    age = sqlalchemy.Column(sqlalchemy.Integer)
    gender = sqlalchemy.Column(sqlalchemy.String(32))
    project = sqlalchemy.Column(sqlalchemy.String(32))

class Shopping(base):
    __tablename__ = "shopping"

    id = sqlalchemy.Column(sqlalchemy.Integer,primary_key = True)
    name = sqlalchemy.Column(sqlalchemy.String(32))
    age = sqlalchemy.Column(sqlalchemy.Integer)
    gender = sqlalchemy.Column(sqlalchemy.String(32))
    project = sqlalchemy.Column(sqlalchemy.String(32))

if __name__ == "__main__":
    base.metadata.create_all(db)

使用python进行对数据库进行增删改查

#插入数据
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind = db) #绑定一个查询实例

session = Session() #进行实例化

user = User(
    name = "老边",
    age = 18,
    gender = "男",
    project = "python"
) #定义数据类

#session.add(user)
session.add_all([
    User(name = "老张", age = 18, gender = "男", project = "python"),
    User(name = "老王", age = 18, gender = "男", project = "python"),
    User(name = "老李", age = 18, gender = "男", project = "python"),
    User(name = "老赵", age = 18, gender = "男", project = "python"),
])

session.commit()
    删
data = session.query(User).get(ident = 1) #get条件必须是主键
session.delete(data)
session.commit()
    改
data = session.query(User).get(ident = 1) #get条件必须是主键
data.name = "老刘"
session.commit()
    查
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind = db) #绑定一个查询实例

session = Session() #进行实例化

all_data = session.query(User).all() #查询指定表的所有数据
all_data = session.query(User).filter_by(age = 18) #条件查询
for data in all_data:
   print(data.name,data.age)
data = session.query(User).get(ident = 1) #get条件必须是主键
print(data.id,data.name)

python自定义ORM

 import pymysql

class Field(object):
    def __init__(self,name,column_type):
        self.name = name
        self.column_type = column_type
    def __str__(self):
        return "<%s:%s>"%(self.name,self.column_type)

class StringField(Field):
    def __init__(self,name):
        super(StringField,self).__init__(name,"varchar(100)")

class IntegerField(Field):
    def __init__(self,name):
        super(IntegerField,self).__init__(name,"int")




class ModelMetaClass(type):
    def __new__(cls, name,bases,attrs):
        if name == "Model":
            return type.__new__(cls,name,bases,attrs)
        mapping = dict()
        for k,v in attrs.items():
            if isinstance(v,Field):
                mapping[k] = v
        for k in mapping.keys():
            attrs.pop(k)
        attrs["__mapping__"] = mapping
        attrs["__table__"] = name
        return type.__new__(cls,name,bases,attrs)

class Model(dict,metaclass = ModelMetaClass):
    def __init__(self,**kwargs):
        self.db = pymysql.connect(
            host = "localhost",
            user = "root",
            password = "1111",
            database = "school"
        )
        self.cursor = self.db.cursor()
        super(Model,self).__init__(**kwargs)

    def __getattr__(self, key):
        return self[key]

    def __setattr__(self, key, value):
        self[key] = value

    def save(self):
        fields = []
        args = []
        for k,v in self.__mapping__.items():
            fields.append(v.name)
            args.append(getattr(self,k,None))
        sql = "insert into %s(%s) values (%s)"%(self.__table__,",".join(fields),",".join([repr(str(i)) for i in args]))
        self.cursor.execute(sql)
        print(sql)
    def __del__(self):
        self.db.commit()
        self.cursor.close()
        self.db.close()

class User(Model):
    name = StringField("name")
    age = IntegerField("age")

for age,user in enumerate("sdjflkdsjlkfjlksadjflkdsjalkfjdslkajflkdsjaflkjdsalkfjdslkajfl"):
    u = User(name = user,age = age)
    u.save()