第十二节

创建表

create table student(
 `id` int not null primary key auto_increment,
 `name` char(32));

插入数据
insert into student(name) values ('pan');
insert into student(name) values ('zhang');

添加字段

alter table `student` add column stu_id int not null default 0 after name;

删除字段

alter table `student` drop column stu_id；

修改字段

update student set stu_id=9001 where id=1;

创建第二张表

create table study_record(
 `id` int not null primary key auto_increment,
 `day` int(11) not null,
 `status` char(32) not null,
 `stu_id` int(11) not null,
 key `fk_student_key` (`stu_id`),
 constraint `fk_student_key` foreign key (`stu_id`) references `student` (id)　　#主外键更多的是某表的主键与子表的某个列进行关联，要求是具备相同的数据类型和属性
 );

插入数据

insert into study_record (day,status,stu_id) values (01,'yes',1);

insert into study_record (day,status,stu_id) values (01,'no',2);

MYSQL NULL值处理

MYSQL连接(left join,rgiht join,inner join,full join),mysql不支持full join

index索引　　添加、删除、修改

SQLAchemy

创建表，并向表中插入数据

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

# 创建连接
engine = create_engine("mysql+pymysql://root:Wow1nemo!123@192.168.1.125/ttyydb",
                       encoding="utf-8",)  # echo=True打印执行信息

Base = declarative_base()   # 生成orm基类

class User(Base):
    __tablename__ = "user"
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

# 创建表结构
Base.metadata.create_all(engine)

# 创建与数据库会话的session class，注意，这里返回的是一个class，不是一个实例
Sessino_class = sessionmaker(bind=engine)
Session = Sessino_class()   # 生成session实例
user_obj = User(name="ttyypjt",password="ttyy123")
user_obj2 = User(name="ttyypjt2",password="ttyy456")
print(user_obj.name,user_obj.id)

Session.add(user_obj)
print(user_obj.name,user_obj.id)
Session.add(user_obj2)
print(user_obj2.name,user_obj2.id)

Session.commit()

 向表中查询数据

初步查询

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

# 创建连接
engine = create_engine("mysql+pymysql://root:Wow1nemo!123@192.168.1.125/ttyydb",
                       encoding="utf-8",)  # echo=True打印执行信息

Base = declarative_base()   # 生成orm基类

class User(Base):
    __tablename__ = "user"
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

# 创建表结构
Base.metadata.create_all(engine)

# 创建与数据库会话的session class，注意，这里返回的是一个class，不是一个实例
Sessino_class = sessionmaker(bind=engine)
Session = Sessino_class()   # 生成session实例

#查询表数据
data = Session.query(User).filter_by(name="ttyypjt").all()　　#data的数据类型为list，
print(data[0].name,data[0].password)


中级查询

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

# 创建连接
engine = create_engine("mysql+pymysql://root:Wow1nemo!123@192.168.1.125/ttyydb",
                       encoding="utf-8",)  # echo=True打印执行信息

Base = declarative_base()   # 生成orm基类

class User(Base):
    __tablename__ = "user"
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

    def __repr__(self):
        return "<%s name:%s>" % (self.id,self.name)

# 创建表结构
Base.metadata.create_all(engine)

# 创建与数据库会话的session class，注意，这里返回的是一个class，不是一个实例
Sessino_class = sessionmaker(bind=engine)
Session = Sessino_class()   # 生成session实例

#查询表数据
# data = Session.query(User).filter_by(name="ttyypjt").all()
# print(data[0].name,data[0].password)
data = Session.query(User).filter_by().all()
print(data)

# 查询id>2的数据
data = Session.query(User).filter(User.id>2).all()
print(data)

# 查询id=2的数据
data = Session.query(User).filter(User.id==2).all()
print(data)

#filter与filter_by的用法有些怪异，需要多试几次

# 多条件查询
data = Session.query(User).filter(User.id>2).filter(User.id<6).all()
print(data)

# 修改表数据
data = Session.query(User).filter(User.id>2).filter(User.id<6).first()
data.name = "tom"
data.password = "qwer"
Session.commit()

# 回滚
data = Session.query(User).filter(User.id>2).filter(User.id<6).first()
data.name = "jack"
fake_user = User(name="rain",password="123456")
Session.add(fake_user)

print(Session.query(User).filter(User.name.in_(["jack","rain"])).all())
Session.rollback()  #回滚后，被撤销的事物仍然会占用id
print(Session.query(User).filter(User.name.in_(["jack","rain"])).all())

# 统计和分组
print(Session.query(User).filter(User.name.in_(["ttyypjt","tom"])).count())

from sqlalchemy import func
print(Session.query(func.count(User.name),User.name).group_by(User.name).all())

#相当于原生sql
select count(user.name) AS count_I,user.name AS user_name
FROM user GROUP BY user.name

联表查询

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

# 创建连接
engine = create_engine("mysql+pymysql://root:Wow1nemo!123@192.168.1.125/ttyydb",
                       encoding="utf-8",)  # echo=True打印执行信息

Base = declarative_base()   # 生成orm基类

class User(Base):
    __tablename__ = "user"
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

    def __repr__(self):
        return "<%s name:%s>" % (self.id,self.name)

class Student(Base):
    __tablename__ = "student"
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    stu_id = Column(Integer)

    def __repr__(self):
        return "<%s name:%s>" % (self.id,self.name)

# 创建表结构
Base.metadata.create_all(engine)

# 创建与数据库会话的session class，注意，这里返回的是一个class，不是一个实例
Sessino_class = sessionmaker(bind=engine)
Session = Sessino_class()   # 生成session实例

print(Session.query(Student,User).filter(Student.id == User.id).all())

外键关联、主键约束

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,DATE,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship,backref

# 创建连接
engine = create_engine("mysql+pymysql://root:Wow1nemo!123@192.168.1.125/ttyydb",
                       encoding="utf-8",)  # echo=True打印执行信息

Base = declarative_base()   # 生成orm基类

class Student(Base):
    __tablename__ = "student2"
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    register_date = Column(DATE,nullable=True)

    def __repr__(self):
        return "<%s name:%s>" % (self.id,self.name)

class Study_record(Base):
    __tablename__ = "study_record2"
    id = Column(Integer, primary_key=True)
    day = Column(Integer)
    status = Column(String(32))
    stu_id = Column(Integer,ForeignKey("student2.id"))

    student = relationship("Student",backref="my_study_record")

    def __repr__(self):
        return "<%s day:%s>" % (self.id,self.status)

# 创建表结构
Base.metadata.create_all(engine)

# 创建与数据库会话的session class，注意，这里返回的是一个class，不是一个实例
Sessino_class = sessionmaker(bind=engine)
Session = Sessino_class()   # 生成session实例

# stu_obj1 = Student(name="jack",register_date="2017-11-12")
# stu_obj2 = Student(name="eric",register_date="2017-12-12")
# stu_obj3 = Student(name="mary",register_date="2017-11-16")
# stu_obj4 = Student(name="jhon",register_date="2017-10-12")

# stu_record1 = Study_record(day=1,status="yes",stu_id=1)
# stu_record2 = Study_record(day=2,status="no",stu_id=1)
# stu_record3 = Study_record(day=3,status="yes",stu_id=1)
# stu_record4 = Study_record(day=1,status="yes",stu_id=2)

# Session.add_all([stu_obj1,stu_obj2,stu_obj3,stu_obj4])
# Session.add_all([stu_record1,stu_record2,stu_record3,stu_record4])
stu_obj = Session.query(Student).filter(Student.name=="jack").first()
print(stu_obj.my_study_record)
Session.commit()

 

多对多关系查询
#orm_m2m.py
from sqlalchemy import create_engine
from sqlalchemy import Column,Integer,String,DATE,ForeignKey,Table
from sqlalchemy.orm import relationship,backref
from sqlalchemy.ext.declarative import declarative_base

# 创建连接
engine = create_engine("mysql+pymysql://root:Wow1nemo!123@192.168.1.125/ttyydb",
                       encoding="utf-8",)  # echo=True打印执行信息

Base = declarative_base()   # 生成orm基类

book_m2m_author = Table("book_m2m_author",Base.metadata,
                        Column("book_id",Integer,ForeignKey("books.id")),
                        Column("author_id",Integer,ForeignKey("authors.id")),
                        )

class Author(Base):
    __tablename__ = "authors"
    id = Column(Integer, primary_key=True)
    name = Column(String(32))

    def __repr__(self):
        return self.name

class Book(Base):
    __tablename__ = "books"
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    pub_date = Column(DATE,nullable=True)
    authors = relationship("Author",secondary=book_m2m_author,backref="books")

    def __repr__(self):
        return self.name

# 创建表结构
Base.metadata.create_all(engine)

------------------------------------------------------------------------------------------
# orm_m2m_api.py
from sqlalchemy.orm import sessionmaker
import orm_m2m

Sessino_class = sessionmaker(bind=orm_m2m.engine)
Session = Sessino_class()   # 生成session实例

b1 = orm_m2m.Book(name="aaaaaa",pub_date="2014-11-02")
b2 = orm_m2m.Book(name="bbbbbb",pub_date="2015-11-02")
b3 = orm_m2m.Book(name="cccccc",pub_date="2016-11-02")

a1 = orm_m2m.Author(name="jack")
a2 = orm_m2m.Author(name="rain")
a3 = orm_m2m.Author(name="mary")

b1.authors = [a1,a3]
b3.authors = [a1,a2,a3]

# Session.add_all([b1,b2,b3,a1,a2,a3])
# Session.commit()

author_obj = Session.query(orm_m2m.Author).filter(orm_m2m.Author.name=="jack").first()
print(author_obj.books)

book_obj = Session.query(orm_m2m.Book).filter(orm_m2m.Book.id==1).first()
print(book_obj.authors)