0.介绍
# coding=utf8
"""
sqlAlchemy : 操作数据库的第三方包
1. 什么事ORM
关系对象映射
2.ORM和原生SQL哪个好
都很好
3.
db first 通过数据库生成操作数据库的类
code first 通过代码生成数据库表
SQLAlchemy: https://www.cnblogs.com/wupeiqi/articles/8259356.html
"""
1.初始化数据库
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
Base = declarative_base()
class Users(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(50))
extra = Column(String(16))
def init_db():
engine = create_engine("mysql+pymysql://root:pwd@0.0.0.0:33006/wxh_testdb2", max_overflow=5)
# 寻找Base的所有子类，按照子类的结构在数据库中生成对应的数据表信息
Base.metadata.create_all(engine)
def drop_db():
engine = create_engine("mysql+pymysql://root:pwd@0.0.0.0:33006/wxh_testdb2", max_overflow=5)
# 寻找Base的所有子类，按照子类的结构在数据库中生成对应的数据表信息
Base.metadata.drop_all(engine)
2.添加数据
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from test import main
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:pwd@0.0.0.0:33006/wxh_testdb2", max_overflow=5)
Session = sessionmaker(bind=engine)
session = Session()
obj1 = main.Users(name="alex", extra="sb")
obj2 = main.Users(name="alex2", extra="ab")
session.add(obj1)
session.add(obj2)
session.commit()
3.原生sql
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:pwd@0.0.0.0:33006/wxh_testdb2", max_overflow=5)
# 原生sql
cur = engine.execute("select * from users")
result = cur.fetchall()
print(result)
4.简单例子
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from datetime import datetime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DateTime
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
Base = declarative_base()
class Classes(Base):
__tablename__ = 'classes'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(50), nullable=False, unique=True)
class Student(Base):
__tablename__ = 'student'
id = Column(Integer, primary_key=True, autoincrement=True)
username = Column(String(50), nullable=True, unique=True)
password = Column(String(64), nullable=False)
ctime = Column(DateTime, default=datetime.now)
def init_db():
engine = create_engine("mysql+pymysql://root:pwd@0.0.0.0:33006/wxh_testdb2", max_overflow=5)
# 寻找Base的所有子类，按照子类的结构在数据库中生成对应的数据表信息
Base.metadata.create_all(engine)
def drop_db():
engine = create_engine("mysql+pymysql://root:pwd@0.0.0.0:33006/wxh_testdb2", max_overflow=5)
# 删除所有继承自Base的数据库表
Base.metadata.drop_all(engine)
if __name__ == '__main__':
drop_db()
5.多对多
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from datetime import datetime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DateTime, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine
"""基本表创建"""
Base = declarative_base()
class Classes(Base):
__tablename__ = 'classes'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(50), nullable=False, unique=True)
class Student(Base):
__tablename__ = 'student'
id = Column(Integer, primary_key=True, autoincrement=True)
username = Column(String(50), nullable=True, unique=True)
password = Column(String(64), nullable=False)
ctime = Column(DateTime, default=datetime.now)
class_id = Column(Integer, ForeignKey("classes.id"))
# 创建关联, 否则需要join查询
cls = relationship("Classes", backref='stus')
class Hobby(Base):
__tablename__ = "hobby"
id = Column(Integer, primary_key=True)
caption = Column(String(50), default="篮球")
"""没有many2many字段, 所以只能创建第三张表"""
class Student2Hobby(Base):
__tablename__ = "student2hobby"
id = Column(Integer, primary_key=True)
student_id = Column(Integer, ForeignKey("student.id"))
hobby_id = Column(Integer, ForeignKey("hobby.id"))
__table_args__ = (
UniqueConstraint("student_id", "hobby_id", name="uk_student_id_hobby_id"),
# Index("ix_id_name", "name", "id")
)
def init_db():
engine = create_engine("mysql+pymysql://root:pwd@0.0.0.0:33006/wxh_testdb2", max_overflow=5)
# 寻找Base的所有子类，按照子类的结构在数据库中生成对应的数据表信息
Base.metadata.create_all(engine)
def drop_db():
engine = create_engine("mysql+pymysql://root:pwd@0.0.0.0:33006/wxh_testdb2", max_overflow=5)
# 删除所有继承自Base的数据库表
Base.metadata.drop_all(engine)
if __name__ == '__main__':
# drop_db()
init_db()
6.简单操作
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import main5
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine, text, func
engine = create_engine("mysql+pymysql://root:pwd@0.0.0.0:33006/wxh_testdb2", max_overflow=5)
Session = sessionmaker(bind=engine)
session = Session()
# 1.单条增加
# obj = main5.Classes(name="全站1期")
# session.add(obj)
# 2.多条增加
# objs = [
# main5.Classes(name="全站2期"),
# main5.Classes(name="全站3期"),
# main5.Classes(name="全站4期")
# ]
# session.add_all(objs)
# session.commit()
# 3.简单查询
# result = session.query(main5.Classes).all()
# for item in result:
# print(item.id, item.name)
# 4.简单删除
# result = session.query(main5.Classes).filter(main5.Classes.id == 2).delete()
# session.commit()
# 5.简单修改
# obj = session.query(main5.Classes).filter(main5.Classes.id == 4)
# obj.update(dict(name="高二一班"))
# session.commit()
# 6.特殊更新
# synchronize_session="evaluate"表示数字相加, synchronize_session=False表示字符串相加
# obj = session.query(main5.Classes).filter(main5.Classes.id == 6).\
# update({main5.Classes.name: main5.Classes.name + "099"}, synchronize_session="evaluate")
# session.commit()
# 7. 指定列查询
# 所有列
# session.query(main5.Classes).all()
# # 指定id字段
# result = session.query(main5.Classes.id, main5.Classes.name).all()
# print(result) # [(6,), (8,), (4,)]
# for item in result:
# print(item.id, item.name)
# 8.别名, label指定列别名
# result = session.query(main5.Classes.id, main5.Classes.name.label("xx")).all()
# for item in result:
# print(item.id, item.xx)
# 9. filter表达式
r3 = session.query(main5.Classes).filter(main5.Classes.name == "alex").all()
r4 = session.query(main5.Classes).filter_by(name='alex').all()
# 10.text表达式
r6 = session.query(main5.Classes).filter(text("id<:value and name=:name")).params(value=224, name='fred').order_by(main5.Classes.id).all()
# 原生sql, 子查询
r7 = session.query(main5.Classes).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all()
ret = session.query(main5.Classes).filter(main5.Classes.id.in_(session.query(main5.Classes.id).filter_by(name='eric'))).all()
# 关联子查询
subqry = session.query(func.count(Server.id).label("sid")).filter(Server.id == Group.id).correlate(Group).as_scalar()
result = session.query(Group.name, subqry)
session.close()
7.简单操作2
# coding=utf-8
from main5 import *
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine, text, func
engine = create_engine("mysql+pymysql://root:pwd@0.0.0.0:33006/wxh_testdb2", max_overflow=5)
Session = sessionmaker(bind=engine)
session = Session()
# 1.插入学生
obj = Student(username="mark", password="123", class_id=4)
# session.add(obj)
# session.commit()
# 2.查找学生
# obj = session.query(Student).filter(Student.username == "alex").first()
# print(obj)
# 3.查找学生
# objs = session.query(Student).all()
# for item in objs:
# class_obj = session.query(Classes).filter(Classes.id == item.class_id).first()
# print(item.id, item.username, item.class_id, class_obj.name)
# 连表, isouter == left join ,默认inner join
# objs = session.query(Student.id, Classes.name, Student.username).join(Classes, isouter=True).all()
# print(objs)
# objs = session.query(Student).all()
# for item in objs:
# print(item.id, item.username, item.class_id, item.cls.name)
# 4.找到全站2期的所有学生
obj = session.query(Classes).filter(Classes.name == "高二一班").first()
stu_list = obj.stus
for stu in stu_list:
print(stu.id, stu.username)
session.close()
