数据模型:
# 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()
"""
