forked from kacox/food-trends
-
Notifications
You must be signed in to change notification settings - Fork 0
/
create_tables.py
55 lines (44 loc) · 2.33 KB
/
create_tables.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
"""Code to create tables in Food Trends application.
Run directly to create (empty) tables from scratch.
"""
from sqlalchemy import create_engine
from sqlalchemy import Table, Column, MetaData, ForeignKey
from sqlalchemy import Integer, String, BigInteger, DateTime
"""
NOTE TO SELF:
The Engine, when first returned by create_engine(), has not actually tried to
connect to the database yet; that happens only the first time it is asked to
perform a task against the database. So, the first time a method like
Engine.execute() or Engine.connect() is called, THEN the Engine establishes a
real DBAPI connection to the database, which is then used to emit the SQL.
"""
metadata = MetaData()
food_terms = Table("food_terms", metadata,
Column("id", Integer, primary_key=True, autoincrement=True),
Column("term", String(30), nullable=False, unique=True))
pairings = Table("pairings", metadata,
Column("pairing_id", BigInteger, primary_key=True, autoincrement=True),
Column("food_id1", Integer, ForeignKey("food_terms.id"), nullable=False),
Column("food_id2", Integer, ForeignKey("food_terms.id"), nullable=False),
Column("search_id", BigInteger, ForeignKey("searches.id"), nullable=False),
Column("occurences", Integer, nullable=False))
searches = Table("searches", metadata,
Column("id", BigInteger, primary_key=True, autoincrement=True),
Column("user_timestamp", DateTime, nullable=False),
Column("search_window", String(10), nullable=False),
Column("food_id", Integer, ForeignKey("food_terms.id"), nullable=False),
Column("num_matches_total", Integer, nullable=False),
Column("num_matches_returned", Integer, nullable=False))
results = Table("results", metadata,
Column("id", BigInteger, primary_key=True, autoincrement=True),
Column("publish_date", DateTime, nullable=False),
Column("index_date", DateTime, nullable=False),
Column("url", String(300), nullable=False),
Column("search_id", BigInteger, ForeignKey("searches.id"), nullable=False))
if __name__ == '__main__':
"""Create tables if script run directly."""
engine = create_engine("postgresql:///food_trends", echo=True)
ans = input("Are you sure you want to make the tables? (Y/N) ")
if ans.upper() == "Y":
metadata.create_all(engine, checkfirst=True)
print("Tables made in db.")