ORM介绍:用图介绍
通常用我们用pymysql
但是!!!!!!这个缺点非常多:
1、对开发人员的要求比较高
2、开发比较繁琐,sql命令的问题
3、不利于数据库切换
这时候 ,我们就需要用到ORM了
ORM:数据库映射关系。将python语句转义成sql语句和数据库交互
优点:
1、使用方便
2、执行效率下降
ORM的基本操作
1.下载我们所需要的包
Python的ORM_sqlalchemy
Sqlalchemy 是python最为常用的三方ORM模块,Flask和tornado在生产环境当中,大部分使 用sqlalchemy和数据库进行交互。
pip install sqlalchemy
2.创建表和数据库
Sqlachemy:
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)
对数据进行
增
#插入数据
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()