開始行:
[[FrontPage/python/Flask]]
* Sqlalchemy [#va7a3848]
dbの定義
#prettify(python){{
#! coding:utf-8
"""
使い方
# Generate DB
import db
db.metadata.create_all()
"""
from sqlalchemy import create_engine, MetaData
# Generate Engine
engine = create_engine('sqlite:///db.sqlite3', echo=True)
metadata = MetaData() # まじない
metadata.bind = engine # まじない
## Define Table
from sqlalchemy import (Table, Column, Integer, String)
menus = Table(
'menus', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('kcal', Integer)
)
if __name__ == '__main__':
# DB Create
# python -c "import db;db.metadata.create_all()"
metadata.create_all()
}}
// code
** DBを使う(CRUB) [#g5eb33fa]
#prettify(python){{
#! coding:utf-8
"""
使い方
# Tables
menus = Table(
'menus', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('kcal', Integer)
)
# CRUD(クラッド)
Create, Read, Update, Delete
"""
import db
db.metadata.create_all()
# [C] Create
db.menus.insert().execute(name='チュッパチャプス', kcal=1...
db.menus.insert().execute(name='カレーメシ', kcal=500)
db.menus.insert().execute(name='ラーメン', kcal=700.1)
# [R] Read
data = db.menus.select().execute().fetchall()
for d in data:
print(">> Select:{}".format(d))
# [R] Read
datas = db.menus.select().where(db.menus.c.kcal < 200).or...
for d in datas:
print(">> Select:{}".format(d))
# [R] Read
datas = db.menus.select().where(db.menus.c.kcal > 400).or...
for d in datas:
print(">> Select:{}".format(d))
# [U] Update
db.menus.update().where(db.menus.c.id == 1).execute(kcal=...
# [D] Delete
db.menus.delete().where(db.menus.c.kcal < 1000).execute()
}}
* SESSION [#v546718a]
** models.py [#f2f9e332]
#prettify(python){{
#! coding:utf-8
"""
"""
import unittest
from datetime import datetime
from sqlalchemy import create_engine
from sqlalchemy import (Column, Integer, String)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
passward = Column(String)
if __name__ == '__main__':
engine = create_engine('sqlite:///db.sqlite3', echo=T...
Base.metadata.create_all(engine)
unittest.main()
}}
** crud.py [#rddb5c03]
#prettify(python){{
#! coding:utf-8
"""
"""
import unittest
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from model import User
# Generate Session
engine = create_engine('sqlite:///db.sqlite3', echo=True)
Session = sessionmaker(bind=engine)
session = Session()
# [D] DELETE ALL
session.query(User).delete()
session.commit()
# [C] Create
ed_user = User(name='ed', fullname='Ed Jones', passward='...
session.add(ed_user)
# [C] Create
session.add_all([
User(name='wendy', fullname='Wendy Williams', passwar...
User(name='mary', fullname='Mary Williams', passward=...
User(name='fred', fullname='Fred Williams', passward=...
])
# Send commit - トランザクションをコミット
session.commit()
# [R] READ
for row in session.query(User).all():
print(">>Read:",row.id, row.name, row.fullname, row.p...
# [U] Update
row = session.query(User).filter_by(id=1).one()
row.name = "torina update"
session.add(row)
session.commit()
# [D] DELETE
row = session.query(User).filter_by(id=2).one()
session.delete(row)
session.commit()
# [R] READ
for row in session.query(User).all():
print(">>Read:",row.id, row.name, row.fullname, row.p...
}}
終了行:
[[FrontPage/python/Flask]]
* Sqlalchemy [#va7a3848]
dbの定義
#prettify(python){{
#! coding:utf-8
"""
使い方
# Generate DB
import db
db.metadata.create_all()
"""
from sqlalchemy import create_engine, MetaData
# Generate Engine
engine = create_engine('sqlite:///db.sqlite3', echo=True)
metadata = MetaData() # まじない
metadata.bind = engine # まじない
## Define Table
from sqlalchemy import (Table, Column, Integer, String)
menus = Table(
'menus', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('kcal', Integer)
)
if __name__ == '__main__':
# DB Create
# python -c "import db;db.metadata.create_all()"
metadata.create_all()
}}
// code
** DBを使う(CRUB) [#g5eb33fa]
#prettify(python){{
#! coding:utf-8
"""
使い方
# Tables
menus = Table(
'menus', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('kcal', Integer)
)
# CRUD(クラッド)
Create, Read, Update, Delete
"""
import db
db.metadata.create_all()
# [C] Create
db.menus.insert().execute(name='チュッパチャプス', kcal=1...
db.menus.insert().execute(name='カレーメシ', kcal=500)
db.menus.insert().execute(name='ラーメン', kcal=700.1)
# [R] Read
data = db.menus.select().execute().fetchall()
for d in data:
print(">> Select:{}".format(d))
# [R] Read
datas = db.menus.select().where(db.menus.c.kcal < 200).or...
for d in datas:
print(">> Select:{}".format(d))
# [R] Read
datas = db.menus.select().where(db.menus.c.kcal > 400).or...
for d in datas:
print(">> Select:{}".format(d))
# [U] Update
db.menus.update().where(db.menus.c.id == 1).execute(kcal=...
# [D] Delete
db.menus.delete().where(db.menus.c.kcal < 1000).execute()
}}
* SESSION [#v546718a]
** models.py [#f2f9e332]
#prettify(python){{
#! coding:utf-8
"""
"""
import unittest
from datetime import datetime
from sqlalchemy import create_engine
from sqlalchemy import (Column, Integer, String)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
passward = Column(String)
if __name__ == '__main__':
engine = create_engine('sqlite:///db.sqlite3', echo=T...
Base.metadata.create_all(engine)
unittest.main()
}}
** crud.py [#rddb5c03]
#prettify(python){{
#! coding:utf-8
"""
"""
import unittest
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from model import User
# Generate Session
engine = create_engine('sqlite:///db.sqlite3', echo=True)
Session = sessionmaker(bind=engine)
session = Session()
# [D] DELETE ALL
session.query(User).delete()
session.commit()
# [C] Create
ed_user = User(name='ed', fullname='Ed Jones', passward='...
session.add(ed_user)
# [C] Create
session.add_all([
User(name='wendy', fullname='Wendy Williams', passwar...
User(name='mary', fullname='Mary Williams', passward=...
User(name='fred', fullname='Fred Williams', passward=...
])
# Send commit - トランザクションをコミット
session.commit()
# [R] READ
for row in session.query(User).all():
print(">>Read:",row.id, row.name, row.fullname, row.p...
# [U] Update
row = session.query(User).filter_by(id=1).one()
row.name = "torina update"
session.add(row)
session.commit()
# [D] DELETE
row = session.query(User).filter_by(id=2).one()
session.delete(row)
session.commit()
# [R] READ
for row in session.query(User).all():
print(">>Read:",row.id, row.name, row.fullname, row.p...
}}
ページ名:
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
