<jupyter_start><jupyter_text>create_ta_db_tablesa notebook to create the database tables for the ta data.<jupyter_code>import connect_aws_db as cadb
engine = cadb.connect_aws_db(write_unicode=True)
conn = engine.connect()<jupyter_output><empty_output><jupyter_text>create the hotels table<jupyter_code>cmd = "DROP TABLE ta_hotels"
result = conn.execute(cmd)
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)
        )
        """

conn.execute(cmd)<jupyter_output><empty_output><jupyter_text>create the reviews table<jupyter_code>cmd = "DROP TABLE ta_reviews"
result = conn.execute(cmd)
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)
        )
        """

conn.execute(cmd)
cmd = "ALTER TABLE ta_reviews MODIFY COLUMN review_title VARCHAR(255) "
cmd += "CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;"
conn.execute(cmd)
cmd = "ALTER TABLE ta_reviews MODIFY COLUMN review_text VARCHAR(5000) "
cmd += "CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;"
conn.execute(cmd)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08453718566078117, 'fraction_numerical': 0.0299625468164794, 'mean_word_length': 3.2596810933940774, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 13, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '153159', 'license': 'MIT', 'n_tokens_mistral': 667, 'n_tokens_neox': 615, 'n_words': 134, 'path': 'code/create_ta_db_tables.ipynb', 'repo_name': 'mattgiguere/doglodge', 'size': '1869'}