2021-08-29

数据模型:

# coding:utf-8
import pymysql

pymysql.install_as_MySQLdb()
from sqlalchemy import create_engine, VARCHAR
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DATE, ForeignKey, CHAR  # 导入外键
from sqlalchemy.orm import relationship  # 创建关系

engine=create_engine("mysql+mysqldb://root:123456@localhost:3306/test?charset=utf8",
                     encoding="utf-8")

Base=declarative_base()  # 生成orm基类


class Food(Base):
    __tablename__="food"

    name=Column(CHAR(20), primary_key=True)
    location=Column(CHAR(20))

    # def __repr__(self):
    #     return "name:{0} location:{1}".format(self.name, self.location)


class Famous(Base):
    __tablename__='famous'
    id=Column(Integer, primary_key=True)
    food_name=Column(CHAR(20))
    famous_dish=Column(CHAR(20))

    # def __repr__(self):
    #     return "id:{0} food_name:{1} famous_dish:{2}".format(self.id, self.food_name, self.famous_dish)


class People(Base):
    __tablename__='people'
    people_name=Column(CHAR(20), primary_key=True)
    age=Column(CHAR(20))
    sex=Column(CHAR(20))
    city=Column(VARCHAR(20))
    like_food=Column(VARCHAR(20))

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

建表

插入数据:

# coding:utf-8

from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column
from model import Food, Famous

# 中文在命令行中显示为16进制编码，所以用拼音代替，懂这个意思就行。
food={
    u"lu": u"shandong",
    u"chuan": u"chengdu",
    u"su": u"nanjing",
    u"yue": u"zhusanjiao"
}

famous=[
    {u'lu': u'tangculiyu'},
    {u'chuan': u'yuxiangrousi'},
    {u'chuan': u'gongbaojiding'},
    {u'chuan': u'shuizhuroupian'},
    {u'su': u'songshuguiyu'},
    {u'su': u'yanshuiya'},
    {u'yue': u'baizhanji'}
]

engine=create_engine('mysql+mysqldb://root:123456@localhost:3306/test?charset=utf8')
DBSession=sessionmaker(bind=engine)
session=DBSession()

for key in list(food.keys()):
    print(key)
    new_food=Food(name=key, location=food[key])
    session.add(new_food)
session.commit()

for dish in famous:
    new_famous=Famous(food_name=list(dish.keys())[0], famous_dish=list(dish.values())[0])
    session.add(new_famous)
session.commit()

session.close()

# if __name__ == '__main__':
#     print(list({u'lu': u'tangculiyu'}.keys())[0])

查询数据:

# coding:utf-8
from pprint import pprint

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from model import *

# 修改用户名、密码和数据库的名称为自己的
engine=create_engine("mysql+mysqldb://root:123456@localhost:3306/test", )
Session_class=sessionmaker(bind=engine)
session=Session_class()

# 没有带外键,也没有指明两个表之间的关系就会报错:
# query=session.query(Food).join(Famous).all()

"""
sqlalchemy.exc.InvalidRequestError: Don't know how to join to <Mapper at 0x2acce08b5c8; Famous>.
 Please use the .select_from() method to establish an explicit left side, as well as providing 
 an explicit ON clause if not present already to help resolve the ambiguity.
"""
# 没加all() 返回的SQL语句
# no_all_no_filter_query_return_sql=session.query(Food, Famous).join(Famous, Famous.food_name == Food.name)
# print(no_all_no_filter_query_return_sql)
#
# no_all_yes_filter_query_return_sql=session.query(Food, Famous).join(Famous, Famous.food_name == Food.name).filter(
#     Food.location == "chengdu")
# print(no_all_yes_filter_query_return_sql)
#
# # 加了all()返回list对象[<class 'sqlalchemy.engine.row.Row'>]
#
yes_all_yes_filter_query_return_list_obj=session.query(Food, Famous).join(Famous, Famous.food_name == Food.name).filter(
    Food.location == "chengdu").all()
print(yes_all_yes_filter_query_return_list_obj)
data = [i._asdict() for i in yes_all_yes_filter_query_return_list_obj][0]
# pprint([i._asdict() for i in yes_all_yes_filter_query_return_list_obj])
pprint(data)
pprint(type(list(data.values())[1]))
pprint(type(list(data.keys())[1]))

#
# yes_all_no_filter_query_return_list_obj=session.query(Food, Famous).join(Famous, Famous.food_name == Food.name).all()
# print(yes_all_no_filter_query_return_list_obj)
#
# # 分页之前必须没有加all()
# query2=no_all_no_filter_query_return_sql
# pageIndex=2
# pageSize=1
#
# userlist=query2.limit(pageSize).offset(
#     (pageIndex - 1) * pageSize).all()
# print(userlist)
# print(userlist[0])
# print(type(userlist[0]))
# 多个对象
def dobule_to_dict(self_obj):
    result={}
    for key in self_obj.__mapper__.c.keys():
        if getattr(self_obj, key) is not None:
            result[key]=str(getattr(self_obj, key))
        else:
            result[key]=getattr(self_obj, key)
    return result


# two_query=session.query(Food, Famous).all()
# print(len(two_query))
# print(two_query[0]._asdict())
# two_query_filter=session.query(Food, Famous).filter(Food.location == "chengdu").all()

两表,连查询研究:

obj=session.query(Food, Famous).join(Famous, Famous.food_name == Food.name).filter(
    Food.location == "chengdu").all()
pprint(obj)
list_obj1 = []

for i in obj:
    # pprint(i)
    tus={}
    for j in i :
        if "_sa_instance_state" in list(j.__dict__.keys()):
            del j.__dict__["_sa_instance_state"]
        tus.update(j.__dict__)
    list_obj1.append(tus)
pprint(list_obj1)

单表查询研究:

list_obj=session.query(Food).filter(Food.location == "chengdu").all()

pprint(list_obj)
msgs = []
for msg in list_obj:
    print(msg)
    if "_sa_instance_state" in list(msg.__dict__.keys()):
        del msg.__dict__["_sa_instance_state"]
        msgs.append(msg.__dict__)
print(msgs)

三表联查

obj=session.query(Food, Famous, People).join((Famous, Famous.food_name == Food.name),
                                             (People, People.city == Food.location)
                                             ).filter(Food.location == "chengdu").all()

pprint(obj)
list_obj1=[]
tus={}
for i in obj:
    for j in i:
        if "_sa_instance_state" in list(j.__dict__.keys()):
            del j.__dict__["_sa_instance_state"]
        tus.update(j.__dict__)
    list_obj1.append(tus)
pprint(list_obj1)

结果:

项目思路：
"""
resource 表 uuid 等于 dataset 的 uuid
resource 表的 parent_uuid 对应 namespace_id
protected_obj 表 resource_id == resource 表的uuid

现在查dataset 表,

1.dataset_list = session.query(Resource).filter(Resource.parent_id == namespace_id).all()

2.query_all = session.query(K8sDataset, Resource, Protect).join(Resource, Resource.uuid == K8sDataset.uuid,
Protect, Protect.resource_id == K8sDataset.uuid).filter(
K8sDataset.uuid._in(dataset_list)
).all()

"""