<jupyter_start><jupyter_text>Creating our DatabaseThis notebook walks through how I put together our tables for the MySQL portion of the course.<jupyter_code>import pymysql

# Connect to the database
connection = pymysql.connect(host='',
                             cursorclass=pymysql.cursors.DictCursor)

cursor = connection.cursor()<jupyter_output><empty_output><jupyter_text>Dropping Existing TablesGenerally a good first step for setting up a database is to remove everything that is already there. This avoids any potential conflicts on creations/inserts.Therefore we first **DROP** all of the tables we'll be using if they exist.<jupyter_code>"""Drop tables"""
tables = ['friends', 'favorites', 'retweets', 'statuses', 'users']
for table in tables:
    print(table)
    drop_query = f"""DROP TABLE IF EXISTS {table};"""
    cursor.execute(drop_query)
connection.commit()<jupyter_output><empty_output><jupyter_text>Creating Our TablesOnce we've cleared things out, we can start creating the tables we'll be using.We create a table by calling **CREATE TABLE *table_name*(*colls*)**. One thing to keep in mind is the use of **PRIMARY KEY(*coll*)**, this simply identifies the column that will be used as the primary key.<jupyter_code>"""Create users table to track twitter users"""
make_user_table = """CREATE TABLE users(
                        created_date DATE,
                        description VARCHAR(255),
                        favorites_count INT,
                        friends_count INT,
                        user_id VARCHAR(255),
                        listed_count INT,
                        location VARCHAR(255),
                        name VARCHAR(255),
                        screen_name VARCHAR(255),
                        statuses_count INT,
                        url VARCHAR(255),
                        verified BOOL,
                        PRIMARY KEY(user_id));"""

cursor.execute(make_user_table)
connection.commit()<jupyter_output><empty_output><jupyter_text>Foreign KeysAll subsequent table creation queries will look relatively the same, the key difference is the **FOREIGN KEY(*coll*)** statement. This statement defines a relationship to another table (which is a hard rule - no insertions that don't meet constraints).Notes: - **REFERENCES *table_name(coll in table)**: This details where the foreign relationship can be found - **ON DELETE CASCADE**: This isn't required, but states that if the foreign key is removed, also remove this record<jupyter_code>"""Drop and create friends table to track twitter friends"""
make_friends_table = """CREATE TABLE friends(
                        user_id VARCHAR(255),
                        user_screen_name VARCHAR(255),
                        friend_id VARCHAR(255),
                        friend_screen_name VARCHAR(255),
                        PRIMARY KEY (user_id, friend_id),
                        FOREIGN KEY (user_id)
                            REFERENCES users(user_id)
                            ON DELETE CASCADE,
                        FOREIGN KEY (friend_id)
                            REFERENCES users(user_id)
                            ON DELETE CASCADE);"""

cursor.execute(make_friends_table)
connection.commit()<jupyter_output><empty_output><jupyter_text>Now we can simply create the remaining tables:<jupyter_code>"""Drop and create friends table to track twitter friends"""
make_status_table = """CREATE TABLE statuses(
                        created_date DATE,
                        favorites_count INT,
                        status_id VARCHAR(255),
                        lang VARCHAR(255),
                        retweet_count INT,
                        source VARCHAR(255),
                        text TEXT,
                        truncated BOOL,
                        user_id VARCHAR(255),
                        PRIMARY KEY (status_id),
                        FOREIGN KEY (user_id)
                            REFERENCES users(user_id)
                            ON DELETE CASCADE);"""

cursor.execute(make_status_table)
connection.commit()


"""Drop and create favorites table to track favorited tweets"""
make_favs_table = """CREATE TABLE favorites(
                        status_id VARCHAR(255),
                        user_id VARCHAR(255),
                        user_screen_name VARCHAR(255),
                        PRIMARY KEY (status_id, user_id),
                        FOREIGN KEY (user_id)
                            REFERENCES users(user_id)
                            ON DELETE CASCADE,
                        FOREIGN KEY (status_id)
                            REFERENCES statuses(status_id)
                            ON DELETE CASCADE);"""

cursor.execute(make_favs_table)
connection.commit()


"""Drop and create favorites table to track favorited tweets"""
make_retweets_table = """CREATE TABLE retweets(
                        created_date DATE,
                        status_id VARCHAR(255),
                        retweeted_status VARCHAR(255),
                        lang VARCHAR(255),
                        retweet_count INT,
                        source VARCHAR(255),
                        text TEXT,
                        user_id VARCHAR(255),
                        PRIMARY KEY (status_id),
                        FOREIGN KEY (user_id)
                            REFERENCES users(user_id)
                            ON DELETE CASCADE,
                        FOREIGN KEY (retweeted_status)
                            REFERENCES statuses(status_id)
                            ON DELETE CASCADE);"""

cursor.execute(make_retweets_table)
connection.commit()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07157931762223003, 'fraction_numerical': 0.01160745691171298, 'mean_word_length': 1.755329457364341, 'pattern_counts': {'":': 0, '<': 21, '<?xml version=': 0, '>': 21, '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': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '497918', 'license': 'CC-BY-3.0', 'n_tokens_mistral': 1492, 'n_tokens_neox': 1325, 'n_words': 426, 'path': 'assets/EMSE6586/MySQL Table Creation.ipynb', 'repo_name': 'ngau9567/ngau9567.github.io', 'size': '5686'}