Python 学习笔记 - SQLAlchemy(上)
from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
engine = create_engine("mysql+pymysql://yli:yli@sydnagios:3306/mydb", max_overflow=5)
Base = declarative_base()
# 一对多
class Group(Base):
__tablename__ = 'group'
nid = Column(Integer, primary_key=True,autoincrement=True)
caption = Column(String(32))
class User(Base):
__tablename__ = 'user'
nid = Column(Integer, primary_key=True,autoincrement=True)
username = Column(String(32))
group_id = Column(Integer, ForeignKey('group.nid'))
def __repr__(self):
temp = "%s - %s: %s" %(self.nid, self.username, self.group_id)
return temp
def init_db():
Base.metadata.create_all(engine)
def drop_db():
Base.metadata.drop_all(engine)
# init_db()
Session = sessionmaker(bind=engine)
session = Session()
#查询user表的username字段的所有信息
ret = session.query(User.username).all()
print(ret)
#left join的效果
sql = session.query(User,Group).join(Group, isouter=True)
print(sql)
ret = session.query(User,Group).join(Group, isouter=True).all()
print(ret)
-----------
"C:\Program Files\Python3\python.exe" C:/Users/yli/Downloads/a39dab3773523eacb8c8568b446bbcec580842/day13/s1.py
[('alex1',), ('alex2',), ('alex1',), ('alex2',), ('alex1',), ('alex2',), ('alex1',), ('alex2',)]
SELECT user.nid AS user_nid, user.username AS user_username, user.group_id AS user_group_id, `group`.nid AS group_nid, `group`.caption AS group_caption
FROM user LEFT OUTER JOIN `group` ON `group`.nid = user.group_id
[(1 - alex1: 1, ), (2 - alex2: 2, ), (3 - alex1: 1, ), (4 - alex2: 2, ), (5 - alex1: 1, ), (6 - alex2: 2, ), (7 - alex1: 1, ), (8 - alex2: 2, )]
页:
[1]