python第十七次课 2018.5.11

#ORM思想
#需求::如果我们从数据库查出来几条数据,需要阿紫python表示出来


'''
student id name age sex,grader
[
(1,'ling',18,'M',59)
(2,'jack',16,'M',89)
(3,'ccj',17,'M',69)
]


tuple  数据不可变
想知道表的结构,int  varchar  


'''


# for x in student:
#     for y in student[x]:
#         student[x][y]








'''
表  student
id, name, age, sex, grander 




类 module
id, name, age, sex, grander
类的属性和表字段是一一对应的
每张表都会有一个对应的类




[
student1
student2
student3
student4
student5
student6
]
获取具体对象,便利一遍就可以了,然后通过对象的属性获取到对应的值


ORM 思想
object-relation mapping   对象数据映射
'''


#SQLAlchemy


class Student(object):
    def __int__(self,id,name,age,sex,grander):
        self.id = id
        self.name = name
        self.age = age
        self.sex = sex
        self.grander = grander


student1 = student('10001','jack',25,'M',90)
print(student1.name)






pip install SQLAchemy
cus = engine.connect()  这个跟我们上节课说的游标操作一样
'''


from sqlalchemy import create_engine


engine = create_engine('mysql+pymysql://xiang:xiang@192.168.48.136/sqlalchemy')
print(engine)
sql = '''create table student(
  id int not null PRIMARY KEY,
  name varchar(100),
  age int,
  address varchar(100)
);'''


cus = engine.connect()
cus.execute(sql)
cus.close()




from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker




engine = create_engine('mysql+pymysql://xiang:xiang@192.168.48.136/sqlalchemy')
DBsession = sessionmaker(bind=engine)
session = DBsession()


Base = declarative_base()


class Student(Base):
    __tablename__ = 'student'
    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    age = Column(Integer)
    address = Column(String(100))


student1 = Student(id=1001, name='ling', age=25, address="beijing")
student2 = Student(id=1002, name='molin', age=18, address="jiangxi")
student3 = Student(id=1003, name='karl', age=16, address="suzhou")


# session.add_all([student1, student2, student3])
# session.commit()
# session.close()


a = session.query(Student).filter(Student.id>1001).all()
print(a)
for i in a:
    print(i.id)
    print(i.name)
    print(i.age)
    print(i.address)




'''
filter和filter_by
filter_by(name="ling")  不能使用>  <  =
filter(Student.id>1001)  这个就必须使用Student.id  可以使用> < =等






from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker


from 第十七课.demon4 import Student


engine = create_engine('mysql+pymysql://xiang:xiang@192.168.48.136/sqlalchemy')
DBsession = sessionmaker(bind=engine)
session = DBsession()


session.query(Student)