{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Creating our Database\n",
"\n",
"This notebook walks through how I put together our tables for the MySQL portion of the course."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"import pymysql\n",
"\n",
"# Connect to the database\n",
"connection = pymysql.connect(host='',\n",
" cursorclass=pymysql.cursors.DictCursor)\n",
"\n",
"cursor = connection.cursor()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Dropping Existing Tables\n",
"Generally 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.\n",
"\n",
"Therefore we first **DROP** all of the tables we'll be using if they exist."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"\"\"\"Drop tables\"\"\"\n",
"tables = ['friends', 'favorites', 'retweets', 'statuses', 'users']\n",
"for table in tables:\n",
" print(table)\n",
" drop_query = f\"\"\"DROP TABLE IF EXISTS {table};\"\"\"\n",
" cursor.execute(drop_query)\n",
"connection.commit()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Creating Our Tables\n",
"Once we've cleared things out, we can start creating the tables we'll be using.\n",
"\n",
"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."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"\"\"\"Create users table to track twitter users\"\"\"\n",
"make_user_table = \"\"\"CREATE TABLE users(\n",
" created_date DATE,\n",
" description VARCHAR(255),\n",
" favorites_count INT,\n",
" friends_count INT,\n",
" user_id VARCHAR(255),\n",
" listed_count INT,\n",
" location VARCHAR(255),\n",
" name VARCHAR(255),\n",
" screen_name VARCHAR(255),\n",
" statuses_count INT,\n",
" url VARCHAR(255),\n",
" verified BOOL,\n",
" PRIMARY KEY(user_id));\"\"\"\n",
"\n",
"cursor.execute(make_user_table)\n",
"connection.commit()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Foreign Keys\n",
"All 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).\n",
"\n",
"Notes:\n",
" - **REFERENCES *table_name(coll in table)**: This details where the foreign relationship can be found\n",
" - **ON DELETE CASCADE**: This isn't required, but states that if the foreign key is removed, also remove this record"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"\"\"\"Drop and create friends table to track twitter friends\"\"\"\n",
"make_friends_table = \"\"\"CREATE TABLE friends(\n",
" user_id VARCHAR(255),\n",
" user_screen_name VARCHAR(255),\n",
" friend_id VARCHAR(255),\n",
" friend_screen_name VARCHAR(255),\n",
" PRIMARY KEY (user_id, friend_id),\n",
" FOREIGN KEY (user_id)\n",
" REFERENCES users(user_id)\n",
" ON DELETE CASCADE,\n",
" FOREIGN KEY (friend_id)\n",
" REFERENCES users(user_id)\n",
" ON DELETE CASCADE);\"\"\"\n",
"\n",
"cursor.execute(make_friends_table)\n",
"connection.commit()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Now we can simply create the remaining tables:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"\"\"\"Drop and create friends table to track twitter friends\"\"\"\n",
"make_status_table = \"\"\"CREATE TABLE statuses(\n",
" created_date DATE,\n",
" favorites_count INT,\n",
" status_id VARCHAR(255),\n",
" lang VARCHAR(255),\n",
" retweet_count INT,\n",
" source VARCHAR(255),\n",
" text TEXT,\n",
" truncated BOOL,\n",
" user_id VARCHAR(255),\n",
" PRIMARY KEY (status_id),\n",
" FOREIGN KEY (user_id)\n",
" REFERENCES users(user_id)\n",
" ON DELETE CASCADE);\"\"\"\n",
"\n",
"cursor.execute(make_status_table)\n",
"connection.commit()\n",
"\n",
"\n",
"\"\"\"Drop and create favorites table to track favorited tweets\"\"\"\n",
"make_favs_table = \"\"\"CREATE TABLE favorites(\n",
" status_id VARCHAR(255),\n",
" user_id VARCHAR(255),\n",
" user_screen_name VARCHAR(255),\n",
" PRIMARY KEY (status_id, user_id),\n",
" FOREIGN KEY (user_id)\n",
" REFERENCES users(user_id)\n",
" ON DELETE CASCADE,\n",
" FOREIGN KEY (status_id)\n",
" REFERENCES statuses(status_id)\n",
" ON DELETE CASCADE);\"\"\"\n",
"\n",
"cursor.execute(make_favs_table)\n",
"connection.commit()\n",
"\n",
"\n",
"\"\"\"Drop and create favorites table to track favorited tweets\"\"\"\n",
"make_retweets_table = \"\"\"CREATE TABLE retweets(\n",
" created_date DATE,\n",
" status_id VARCHAR(255),\n",
" retweeted_status VARCHAR(255),\n",
" lang VARCHAR(255),\n",
" retweet_count INT,\n",
" source VARCHAR(255),\n",
" text TEXT,\n",
" user_id VARCHAR(255),\n",
" PRIMARY KEY (status_id),\n",
" FOREIGN KEY (user_id)\n",
" REFERENCES users(user_id)\n",
" ON DELETE CASCADE,\n",
" FOREIGN KEY (retweeted_status)\n",
" REFERENCES statuses(status_id)\n",
" ON DELETE CASCADE);\"\"\"\n",
"\n",
"cursor.execute(make_retweets_table)\n",
"connection.commit()"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.8.5"
}
},
"nbformat": 4,
"nbformat_minor": 4
}