Python学習ノート-SQLAlchemy(上)
前回はMySQLのオリジナルコマンドとpymysqlモジュールをPythonで使用する方法を簡単に紹介しました.このセクションでは、sqlAlchemyと彼のORMフレームワークを簡単に紹介します.ORMフレームワークでは、元のSQL文を使用することなく、クラスの方法でデータベースを操作できます.
例1.単一テーブルの操作
変更
削除
例2.1対の複数テーブルのクエリー
groupテーブルとuserテーブルの間にgroup_id外部キー制約の形成
上記に基づいて、結合テーブルクエリを実行するには、2つの方法があります.
1つ目は、joinによって実現されます.上の例を直してください.
たとえば
2つ目はrelationで実現
次のページでは、マルチテーブル間の共同クエリーの学習を続けます.
例1.単一テーブルの操作
#
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
# engine， + ：// ： @ ： /
engine = create_engine("mysql+pymysql://yli:yli@sydnagios:3306/mydb", max_overflow=5)
#
Base = declarative_base()
# ，
class Test(Base):
#
__tablename__ = 'test'
# nid， ， ，
nid = Column(Integer, primary_key=True,autoincrement=True)
# name，
name = Column(String(32))
# ， pymysql sql
def init_db():
Base.metadata.create_all(engine)
#
def drop_db():
Base.metadata.drop_all(engine)
init_db()
#
Session = sessionmaker(bind=engine)
session = Session()
#
session.add(Test(name='apple'))
session.commit()
# Test
ret=session.query(Test)
# sql
print(ret)
#sql ，all() ， ，first()
print(ret.first())
#
print(ret.first().nid,ret.first().name)
--------------
SELECT test.nid AS test_nid, test.name AS test_name
FROM test
<__main__.test>
1 apple変更
session.query(Test).filter(Test.nid==1).update({"name":"pear"})
session.commit()削除
session.query(Test).filter(Test.nid==1).delete()
session.commit()例2.1対の複数テーブルのクエリー
groupテーブルとuserテーブルの間にgroup_id外部キー制約の形成
#
from sqlalchemy import create_engine
from 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()
#
session.add(Group(caption='dba'))
session.add(Group(caption='ddd'))
session.commit()
#
session.add_all([
User(username='alex1',group_id=1),
User(username='alex2',group_id=2)
])
session.commit()
# ， filter
ret = session.query(User).filter(User.username == 'alex1')
# sql
print(ret)
# ， _repr_
print(ret.all())
#
ret = session.query(User).all()
#
obj = ret[0]
print(ret)
print(obj)
#
print(obj.nid)
print(obj.username)
print(obj.group_id)
--------
SELECT user.nid AS user_nid, user.username AS user_username, user.group_id AS user_group_id
FROM user
WHERE user.username = %(username_1)s
[1 - alex1: 1, 3 - alex1: 1]
[1 - alex1: 1, 2 - alex2: 2, 3 - alex1: 1, 4 - alex2: 2]
1 - alex1: 1
1
alex1
1上記に基づいて、結合テーブルクエリを実行するには、2つの方法があります.
1つ目は、joinによって実現されます.上の例を直してください.
たとえば
from sqlalchemy import create_engine
from 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, <__main__.group>), (2 - alex2: 2, <__main__.group>), (3 - alex1: 1, <__main__.group>), (4 - alex2: 2, <__main__.group>), (5 - alex1: 1, <__main__.group>), (6 - alex2: 2, <__main__.group>), (7 - alex1: 1, <__main__.group>), (8 - alex2: 2, <__main__.group>)]2つ目はrelationで実現
from sqlalchemy import create_engine
from 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'))
# relationship， User Group ‘Group’， Group user u， ，
group=relationship('Group',backref='u')
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()
#
ret=session.query(User).all()
print(ret)
for obj in ret:
print(obj.username,obj.group.caption)
#
ret=session.query(Group).filter(Group.caption=='dba').first()
print(ret.caption,ret.u)
for j in ret.u:
print(j.username)
--------------
[1 - alex1: 1, 2 - alex2: 2, 3 - alex1: 1, 4 - alex2: 2, 5 - alex1: 1, 6 - alex2: 2, 7 - alex1: 1, 8 - alex2: 2]
alex1 dba
alex2 ddd
alex1 dba
alex2 ddd
alex1 dba
alex2 ddd
alex1 dba
alex2 ddd
#
dba [1 - alex1: 1, 3 - alex1: 1, 5 - alex1: 1, 7 - alex1: 1]
alex1
alex1
alex1
alex1次のページでは、マルチテーブル間の共同クエリーの学習を続けます.