create_ta_db_tables

a notebook to create the database tables for the ta data.



In [1]:

    
import connect_aws_db as cadb



In [2]:

    
engine = cadb.connect_aws_db(write_unicode=True)



In [3]:

    
conn = engine.connect()

create the hotels table



In [9]:

    
cmd = "DROP TABLE ta_hotels"
result = conn.execute(cmd)



In [10]:

    
cmd = """
        CREATE TABLE ta_hotels
        (
        hotel_id MEDIUMINT AUTO_INCREMENT,
        hotel_url VARCHAR(512),
        hotel_img_url VARCHAR(512),
        hotel_name VARCHAR(512),
        hotel_address VARCHAR(1024),
        hotel_city VARCHAR(512),
        hotel_state VARCHAR(32),
        hotel_rating INT(11),
        hotel_latitude FLOAT,
        hotel_longitude FLOAT,
        hotel_price FLOAT,
        business_id VARCHAR(256),
        review_count INT,
        dog_review_count INT,
        PRIMARY KEY (hotel_id)
        )
        """



In [11]:

    
conn.execute(cmd)









    Out[11]:





<sqlalchemy.engine.result.ResultProxy at 0x10668c990>

create the reviews table



In [9]:

    
cmd = "DROP TABLE ta_reviews"
result = conn.execute(cmd)



In [10]:

    
cmd = """
        CREATE TABLE ta_reviews
        (
        review_id MEDIUMINT AUTO_INCREMENT,
        hotel_id VARCHAR(256),
        business_id VARCHAR(256),
        biz_review_id BIGINT,
        biz_member_id VARCHAR(128),
        username VARCHAR(128),
        review_title VARCHAR(255),
        review_rating INT,
        review_text VARCHAR(5000),
        review_date VARCHAR(512),
        PRIMARY KEY (review_id)
        )
        """



In [11]:

    
conn.execute(cmd)









    Out[11]:





<sqlalchemy.engine.result.ResultProxy at 0x1066da990>



In [12]:

    
cmd = "ALTER TABLE ta_reviews MODIFY COLUMN review_title VARCHAR(255) "
cmd += "CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;"
conn.execute(cmd)









    Out[12]:





<sqlalchemy.engine.result.ResultProxy at 0x106709910>



In [13]:

    
cmd = "ALTER TABLE ta_reviews MODIFY COLUMN review_text VARCHAR(5000) "
cmd += "CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;"
conn.execute(cmd)









    Out[13]:





<sqlalchemy.engine.result.ResultProxy at 0x106709e50>



In [ ]: