<jupyter_start><jupyter_text>Import Libraries<jupyter_code>import mysql.connector as mysql
import os #because of the environment variables (user_MySQL and passwd_MySQL)<jupyter_output><empty_output><jupyter_text>Setting the environment variables (user_MySQL and passwd_MySQL)<jupyter_code>user_MySQL = os.environ.get('MYSQL_USER')
passwd_MySQL = os.environ.get('MYSQL_PASSWORD')<jupyter_output><empty_output><jupyter_text>Connection<jupyter_code>conn = mysql.connect(
                host = 'localhost',
                user = user_MySQL,
                passwd = passwd_MySQL,
                database = 'study_example'
                )<jupyter_output><empty_output><jupyter_text>Creating Table  Creating Cursor<jupyter_code>cur = conn.cursor()<jupyter_output><empty_output><jupyter_text>Creating Table<jupyter_code>cur.execute('CREATE Table student (id INT AUTO_INCREMENT PRIMARY KEY,\
                                    name VARCHAR(100),\
                                    age SMALLINT,\
                                    email VARCHAR(120) UNIQUE NOT NULL,\
                                    interest VARCHAR(100))')<jupyter_output><empty_output><jupyter_text>Insert  Option 01 Insert one value (string)<jupyter_code>cur.execute('INSERT INTO student (name, age, email, interest) VALUES \
                    ("<NAME>", 36, "<EMAIL>", "Data Science")')<jupyter_output><empty_output><jupyter_text>Option 02 Insert one value (variable)<jupyter_code>query = 'INSERT INTO student (name, age, email, interest) VALUES (%s, %s, %s, %s)'
value = ("<NAME>", 27 ,"<EMAIL>", "Data Science")
cur.execute(query,value)<jupyter_output><empty_output><jupyter_text>Option 03 (Insert one by one - Iteration)<jupyter_code>query = 'INSERT INTO student (name, age, email, interest) VALUES (%s, %s, %s, %s)'
values = [("<NAME>", 15 ,"<EMAIL>", "Business Intelligence"),
         ("<NAME>", 23 ,"<EMAIL>", "DBA"),
         ("<NAME>", 42 ,"<EMAIL>", "Data Science"),
         ("<NAME>", 34, "<EMAIL>", "Business Intelligence")]
for value in values:
    cur.execute(query,value)<jupyter_output><empty_output><jupyter_text>Option 04 (Bulk Insert)<jupyter_code>query = 'INSERT INTO student (name, age, email, interest) VALUES (%s, %s, %s, %s)'
values = [("<NAME>", 21 ,"<EMAIL>", "Data Science"),
         ("<NAME>", 22 ,"<EMAIL>", "Business Intelligence"),
         ("<NAME>", 23 ,"<EMAIL>", "DBA"),
         ("<NAME>", 35, "<EMAIL>", "Business Intelligence")]
cur.executemany(query,values)<jupyter_output><empty_output><jupyter_text>Update<jupyter_code>cur.execute('Update student SET email= "<EMAIL>" WHERE name="<NAME>"')<jupyter_output><empty_output><jupyter_text>Delete<jupyter_code>cur.execute('DELETE FROM student WHERE name = "<NAME>"')<jupyter_output><empty_output><jupyter_text>Select<jupyter_code>cur.execute('SELECT * FROM student')
students = cur.fetchall()
for student in students:
    print(student)
cur.execute('SELECT * FROM student WHERE email = "<EMAIL>"')
student = cur.fetchone()
print(student)
cur.execute("SELECT DISTINCT interest FROM student")
cur.fetchall()<jupyter_output><empty_output><jupyter_text>Upsert (Insert or Update)<jupyter_code>query = 'INSERT INTO student (name, age, email, interest) VALUES (%s, %s, %s, %s)\
                ON DUPLICATE KEY UPDATE \
                        name = name, age = age, email = email, interest = interest'

values = [("<NAME>", 36, "<EMAIL>", "Data Science"),
         ("<NAME>", 42 ,"<EMAIL>", "Data Science"),
         ("<NAME>", 34, "<EMAIL>", "Business Intelligence")]
lst_row_id = list()
for value in values:
    cur.execute(query,value)
    row_id = cur.lastrowid
    lst_row_id.append(row_id)
print(lst_row_id)<jupyter_output>[11, 0, 13]<jupyter_text>Commit and Close Connection<jupyter_code>conn.commit()<jupyter_output><empty_output><jupyter_text>Drop Table<jupyter_code>cur.execute('DROP TABLE student')<jupyter_output><empty_output><jupyter_text>Close Connection<jupyter_code>conn.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15210843373493976, 'fraction_numerical': 0.012048192771084338, 'mean_word_length': 4.534722222222222, 'pattern_counts': {'":': 0, '<': 94, '<?xml version=': 0, '>': 94, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '16', 'dir': 'jupyter-structured-clean-dedup', 'id': '642234', 'license': 'MIT', 'n_tokens_mistral': 1386, 'n_tokens_neox': 1278, 'n_words': 276, 'path': 'Notebooks/Table Statments.ipynb', 'repo_name': 'FabioCaffarello/MySQL-Python', 'size': '4286'}