1.复习面向对象的内容:
编程方式主要有两种:1.面向过程编程2.面向对象编程;
1.面向过程编程的思想就是根据设计要求从上到下一次进行编程。数据和逻辑是不进行分离的
2.面向对象编程:有时候我们在进行面向过程编程的时候会遇到会有很多变量和方法,但是他们属于同一类(即属性和方法一样)这个是由就可以使用面向对象。数据和逻辑进行分离的。
3面向对象有两个作用:1.模板约束(所有实例化的对象都是同一模型)2.当一类函数公用同样一个参数的时候,可以转换成类——进行分类(也可以封装程模块)
1面向过程的编程:
deffunc(name):print('%s走了100步'%(name))deffunc1(name):print('%s吃了一碗米'%(name))
func('alex')
func1('alex')
View Code
2.面向对象的编程:
classFoo:def __init__(self,name):
self.name=namedeffunc(self):print('self.name走了100步')deffunc1(self):print('self.name吃了一碗米')
ret=Foo('alex')#进行实例化
ret.func()
ret.func1()
View Code
3.在面向对象的时候对实例化对象使用()则时调用类里面的call方法:
classFoo:def __init__(self,name):
self.name=namedeffunc(self):print('self.name走了100步')deffunc1(self):print('self.name吃了一碗米')def __call__(self):print('调用了call方法')
ret=Foo('alex')#进行实例化
ret()
结果为
调用了call方法
View Code
4.一个类其实就是一个字典:查看类里面的属性
classFoo:
name='alex'
def __init__(self,name):
self.name=namedeffunc(self):print('self.name走了100步')deffunc1(self):print('self.name吃了一碗米')def __call__(self):print('调用了call方法')
ret=Foo('alex')#进行实例化
print(Foo.__dict__)
View Code
5.查看实例化对象中的属性·:调用的类中的方法是:setitem()
classFoo:
name='alex'
def __init__(self,name):
self.name=namedeffunc(self):print('self.name走了100步')deffunc1(self):print('self.name吃了一碗米')def __call__(self):print('调用了call方法')
ret=Foo('alex')#进行实例化
print(ret.__dict__)
结果为
{'name': 'alex'}
View Code
6.ret.__dict__['name'] 就是调用setitem(self,item,values)
ret=Foo('alex')#进行实例化
ret.__dict__['name']='wusir'
print(ret.name)
View Code
2.今日内容:
1.orm的作用:
1.提供简单的规则2.自动转换sql语句。
2.但是orm语句无法进行创建数据库,需要人工进行创建。但是可以通过code对来建立数局表和数据行。
3.安装sqlalchemy :
1.使用cmd进行安装的时候经常会报错,我们可以使用离线的安装方式:但是要记住离线安装前需要把离线安装包移动到python的scripts下;
2.两个安装文件的网址:
3.在使用fcmd下的pip install 命令就可以了。
4.第一个orm程序:
importpymysqlfrom sqlalchemy.orm importsessionmaker,relationshipsfrom sqlalchemy importcreate_enginefrom sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy importColumn,INTEGER,String,ForeignKey,UniqueConstraint,Index
Base=declarative_base()
engine=create_engine('mysql+pymysql://root:@localhost:3306/db4',max_overflow=5)class User(Base): #创建一个表的类
__tablename__='user'#创建表的名字下面是表中的数据
id=Column(INTEGER,primary_key=True,autoincrement=True)
name=Column(String(32))
email=Column(String(15))definit_db():#执行表的创建
Base.metadata.create_all(engine)
init_db()
View Code
5.通过外键将两个表进行连接:
importpymysqlfrom sqlalchemy.orm importsessionmaker,relationshipsfrom sqlalchemy importcreate_enginefrom sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy importColumn,INTEGER,String,ForeignKey,UniqueConstraint,Index
Base=declarative_base()
engine=create_engine('mysql+pymysql://root:@localhost:3306/db4',max_overflow=5)classDepartment(Base):__tablename__='department'id=Column(INTEGER,primary_key=True,autoincrement=True)
title=Column(String(32))class User(Base): #创建一个表的类
__tablename__='user'#创建表的名字下面是表中的数据
id=Column(INTEGER,primary_key=True,autoincrement=True)
name=Column(String(32))
email=Column(String(15))
title_id=Column(INTEGER,ForeignKey('department.id'))definit_db():#执行表的创建
Base.metadata.create_all(engine)
init_db()
View Code
6.使用联合索引的操作:
__table__args__=(
UniqueConstraint('id','name',name='uxi_name'),
Index('id_dec','id','email')
)
View Code
7.对数据表进行删除操作:
definit_drop():
Base.metadata.drop_all(engine)
init_drop()
View Code
8.orm中的增操作:
1.每次增加一个数据:
definit_addone():
Session=sessionmaker(bind=engine)
session=Session()
ret1=Department(title='alex')
session.add(ret1)
session.commit()
session.close()
init_addone()
View Code
2.每次增加多条数据:
defaddmany():
ret2=[
Department(title='wusir1'),
Department(title='wusir2'),
Department(title='wusir3'),
Department(title='wusir4'),
]
session.add_all(ret2)
session.commit()
session.close()
addmany()
View Code
9.orm的查操作:
1.不带条件的查询:
defcheck():print( session.query(Department))#查看code转换成的sql语句
depart_list=session.query(Department).all()print(depart_list[1])
check()
结果为
SELECT department.id AS department_id, department.title AS department_title
FROM department
C:\python\lib\site-packages\pymysql\cursors.py:170: Warning: (1366, "Incorrect string value: '\\xD6\\xD0\\xB9\\xFA\\xB1\\xEA...' for column 'VARIABLE_VALUE' at row 1")
result=self._query(query)<__main__.department object at>
View Code
对于对象里面的取值我们需要使用for语句进行操作:
defcheck():print(session.query(Department)) #查看code转换成的sql语句
depart_list =session.query(Department).all()for i indepart_list:print(i.id, i.title,end=' ')
结果为
result=self._query(query)1 alex 2 wusir1 3 wusir2 4 wusir3 5 wusir4
View Code
2.带有条件的查询:
defcheck_condition():
depart_list= session.query(Department).filter(Department.id > 2)for i indepart_list:print(i.id, i.title, end=' ')
check_condition()
View Code
10.orm中的删除操作:注在进行增删改的时候都需要添加一句session.commit()否则文件无法添加到数据库中
defcheck_condition_delete():
session.query(Department).filter(Department.id> 2).delete()
session.commit()
check_condition_delete()
View Code
11.orm中的该操作:改的数据必需使用字典类型:
1 第一种方式:
defrepair_1():
session.query(Department).filter(Department.id==2).update({'title':'alwei'})
session.commit()
repair_1()
View Code
2 第二种方式
defrepair_2():
session.query(Department).filter(Department.id==6).update({Department.title:'alwei'})
session.commit()
repair_2()
View Code
3.如果我想在不改变原来值的基础上对原来的值后面添加两个字符串:
defrepair_3():
session.query(Department).update({Department.title:Department.title+'22'},synchronize_session=False)
session.commit()
repair_3()
View Code
4.对于数字类型的相加:
defrepair_4():
session.query(User).update({User.title_id:User.title_id+2},synchronize_session='evaluate')
session.commit()
repair_4()
View Code
12.查询中条件的orm操作:
1.如果两个条件之间用逗号隔开,代表and操作:
defcondition_1():
ret=session.query(Department).filter(Department.id>1,Department.title=='wusir3').all()for i inret:print(i.id,i.title)
condition_1()
View Code
2between操作;
defcondition_2():
ret=session.query(Department).filter(Department.id.between(1,3)).all()for i inret:print(i.id,i.title)
condition_2()
结果为1wusir12wusir23 wusir3
View Code
3.in操作:
defcondition_3():
ret= session.query(Department).filter(Department.id.in_([1, 3])).all()for i inret:print(i.id, i.title)
condition_3()
结果为1wusir13 wusir3
View Code
4.not in操作:
defcondition_4():
ret= session.query(Department).filter(~Department.id.in_([1, 3])).all()for i inret:print(i.id, i.title)
condition_4()
结果为2wusir24wusir45wusir16wusir27wusir38 wusir4
View Code
defcondition_5():from sqlalchemy importand_,or_
ret=session.query(Department).filter(and_(Department.id>5,Department.title=='wusir4')).all()
ret2=session.query(Department).filter(or_(Department.id>5,Department.title=='wusir4')).all()for i inret:print(i.id,i.title,end=' ')print()for i1 inret2:print(i1.id,i1.title,end=' ')
condition_5()
结果为8wusir44 wusir4 6 wusir2 7 wusir3 8wusir4
Process finished with exit code 0
View Code
5。通配符操作;
deflikeone():
ret=session.query(Department).filter(Department.title.like('wusir%')).all()for i inret:print(i.id,i.title,end=' ')
likeone()
View Code
6.限制操作:
deflimitone():
ret=session.query(Department)[1:3]for i inret:print(i.id,i.title,end=' ')
limitone()
View Code
7.排序操作:
defset_group1():
ret=session.query(Department.title).group_by(Department.title).all()print(ret)
set_group1()
结果为
[('wusir1',), ('wusir2',), ('wusir3',), ('wusir4',)]
View Code
defset_group2():
ret=session.query(Department.title,func.count(1)).group_by(Department.title).having(func.count(1)>1)print(ret)
set_group2()
结果为
SELECT department.title AS department_title, count(%(count_2)s) AS count_1
FROM department GROUP BY department.title
HAVING count(%(count_3)s) > %(count_4)s
View Code
8.联合操作使用join
SELECT user.id AS user_id, user.name AS user_name, user.email AS user_email, user.title_id AS user_title_id
FROM user INNER JOIN department ON department.id=user.title_id
结果为
SELECT user.id AS user_id, user.name AS user_name, user.email AS user_email, user.title_id AS user_title_id
FROM user INNER JOIN department ON department.id= user.title_id
View Code
9.组合操作:union
defunionone():
q1=session.query(User).filter(User.id>2)
q2=session.query(User).filter(User.id<2)
ret=q1.union(q2)
ret1=ret.all()for i inret1:print(i.id,i.name)
unionone()
结果为3uwin34uwin41 uwin1
View Code
10.临时表的制作方法:
deftempary():
q1=session.query(Department).filter(Department.id>2).subquery()
ret=session.query(q1).all()for i inret:print(i.id)
tempary()
结果为3
4
5
6
7
8
View Code
11.又是后我们需要插寻的元素为另一张表中的内容:
result=session.query(User.id,session.query(User.name).as_scalar())print(result)
结果为
SELECT user.id AS user_id, (SELECT user.name
FROM user) AS anon_1
FROM user
View Code