Closed mrbone closed 6 years ago
[TOC]
git clone https://github.com/udacity/fullstack-nanodegree-vm
cd fullstack-nanodegree-vm/vagrant
vagrant up
vagrant ssh
需求: 假设需要给一个餐馆建立一个菜单系统,菜单里面每个菜品有价格
/菜名
/餐程
/介绍
信息,那么我们会按照如下格式建表(不是唯一正确解)
代码如下:
import sqlite3
conn = sqlite3.connect('restaurantmenu.db')
c = conn.cursor()
c.execute('''
CREATE TABLE restaurant(
id INTEGER PRIMARY KEY ASC,
name varchar(250)
)
''')
c.execute('''
CREATE TABLE menu_item(
id INTEGER PRIMARY KEY ASC,
name varchar(250),
price varchar(250),
description varchar(250) NOT NULL,
restaurant_id INTEGER NOT NULL,
FOREIGN KEY(restaurant_id) REFERENCES restaurant(id)
)
''')
conn.commit()
conn.close()
python 有多个 ORM 工具,我们这里使用 sqlalchemy。
sqlalchemy 由四部分代码组成:
import sys
from sqlalchemy import
Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import
declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import create_engine
Base = declarative_base()
engine = create_engine('sqlite:///restaurantmenu.db')
Base.metadata.create.create_all(engine)
sqlalchemy 还有一个特性是 python class 内部 __tabelname__
代表数据库的表名
真实代码:
import sys
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import create_engine
Base = declarative_base()
class Restaurant(Base):
__tablename__ = 'restaurant'
id = Column(Integer, primary_key = True)
name = Column(String(250), nullable = False)
class MenuItem(Base):
__tablename__ = 'menu_item'
id = Column(Integer, primary_key = True)
name = Column(String(80), nullable = False)
price = Column(String(8))
description = Column(String(250), nullable = False)
restaurant_id = Column(Integer, ForeignKey('restaurant.id'))
restaurant = relationship(Restaurant)
#insert below at end of file
engine = create_engine('sqlite:///restaurantmenu.db')
Base.metadata.create_all(engine)
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from database_setup import Base, Restaurant, MenuItem
engin = create_engine('sqlite:///restaurantmenu.db') #1. 创建到 db 文件的 engine
Base.metadata.bind = engin #2. 绑定 engine 到 declarative 的 base 类上
DBSession = sessionmaker(bind = engin) #3. 用 engine 创建 DBSession 类
session = DBSession()
myFirstRestaurant = Restaurant(name = 'Pizza hut') # below: 4. 操作 DBSession 的实例
session.add(myFirstRestaurant)
session.commit()
session.query(Restaurant).all()
建立 DBSession 的步骤主要有:
重复上面的操作,使用一个循环打印出需要查询的数据
... #略
items = session.query(MenuItem).all()
for item in items
print item.name
就能看到所有 name 被打印出来了。
使用 sqlalchemy
有四个步骤做数据库的 update。
python basic course done