4GfHrebz

1from sqlalchemy import create_engine
2from sqlalchemy import Table, Column, Integer, String, Text, MetaData
3
4
5# "echo=true" causes the console to display the actual SQL query for table creation
6engine = create_engine('mysql://root:mysql345@localhost:3306', echo=True)
7
8# Assign the Database name to Scrapes and create the database if it doesn't exist already
9DATABASE = "scrapes"
10create_str = "CREATE DATABASE IF NOT EXISTS %s ;" % DATABASE
11engine.execute(create_str)
12
13# Tell the engine to use the Scrapes database
14use_str = "USE %s" % DATABASE
15engine.execute(use_str)
16
17meta = MetaData()
18
19query = Table(
20    'query', meta,
21    Column('id', Integer, primary_key=True),
22    Column('what', String(256)),
23    Column('where', String(256)),
24    Column("num_of_pages", Integer),
25    Column("num_of_posts", Integer)
26    # Column("page_soups", String), # Store Soups in the Page table because otherwise the data is too long
27
28
29    # What else do I want to have in my Query Table?
30)
31
32page = Table(
33    "page", meta,
34    Column('id', Integer, primary_key=True),
35    Column('what', String(256)),
36    Column('where', String(256)),
37    Column("page_url", String(256)),
38    Column("num_of_posts", String(256)),
39    Column("soup", Text)
40)
41
42posting = Table(
43    "posting", meta,
44    Column('id', Integer, primary_key=True),
45    Column('what', String(256)),
46    Column('where', String(256)),
47    Column("lang_keywords", String(256)),
48    Column("pay", String(256)),
49    Column("title", String(256)),
50    Column("company", String(256)),
51    Column("soup", Text)
52)
53
54# Check if the tables exist before creating them
55query_table_exists = engine.dialect.has_table(engine, "query")
56page_table_exists = engine.dialect.has_table(engine, "page")
57posting_table_exists = engine.dialect.has_table(engine, "posting")
58if not query_table_exists:
59    if not page_table_exists:
60        if not posting_table_exists:
61            meta.create_all(engine)