<jupyter_start><jupyter_text>Create a Database to use<jupyter_code>mysql = mysql.connector.connect(
    host='mysql',
    user='root',
    password='<PASSWORD>'
)
database = "testing_db"
#
cur = mysql.cursor()
cur.execute(f"""

CREATE DATABASE IF NOT EXISTS {database}

""")
cur.fetchall()
cur.execute(f"""

SHOW tables FROM {database}

""")
cur.fetchall()<jupyter_output><empty_output><jupyter_text>Create Testing Tables<jupyter_code>def modify_sql(sql_string):
    sql_string = sql_string.replace(" string", " varchar")
    return sql_string
conn = presto.Connection(
    host="presto", 
    port=8080, 
    user="demo"
)
cur = conn.cursor()
cur
cur.execute(modify_sql(f"""

CREATE TABLE IF NOT EXISTS target_db.testing_table_1(
    id string,
    color string,
    type int,
    number_of_coins bigint,
    measure decimal(19,3),
    start_date date
)

""").replace(" target_db.",f" mysql.{database}."))
cur.fetchall()<jupyter_output><empty_output><jupyter_text>Add in Table data<jupyter_code>cur.execute(modify_sql(f"""

SHOW COLUMNS FROM target_db.testing_table_1

""").replace(" target_db.",f" mysql.{database}."))
cur.fetchall()
def format_table_values(df):
    """format a dataframe into the syle of values typical for sql
    example: VALUES (1, 'a'), (2, 'b'), (3, 'c')
    """
    sending = []
    for i, row in df.iterrows():
        values = []
        for raw_value in row.tolist():
            if (type(raw_value) == str):
                if (raw_value.lower().startswith("date")):
                    values.append(str(raw_value))
                else: values.append(f"'{raw_value}'")
            else: values.append(str(raw_value))
        joined_values = ", ".join(values)
        sending.append(f"({joined_values})")
        
    if (len(sending) == 1):
        return sending[0]        
    else: return ", ".join(sending)
count = 10
example_df = pd.DataFrame(index=range(count))
example_df["id"] = random.choices(["123abc","456dfg","789hij"], k=count)
example_df["color"] = random.choices(["red","blue"],weights=[5, 1],k=count)
example_df["type"] = 7
example_df["number_of_coins"] = [(1 + x) * 100_000 for x in range(count)]
example_df["measure"] = [round(np.cos(m),2) for m in range(count)]
example_df["start_date"] = "date('"+pd.date_range(end='12/01/2021',periods=count,freq='D').astype(str)+"')"
example_df
cur.execute(f"""

INSERT INTO target_db.testing_table_1 VALUES {format_table_values(example_df)}

""".replace(" target_db.",f" mysql.{database}."))
cur.fetchall()
cur.execute(modify_sql(f"""

SELECT * FROM target_db.testing_table_1

""").replace(" target_db.",f" mysql.{database}."))
cur.fetchall()
conn.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1739618406285073, 'fraction_numerical': 0.017209128320239433, 'mean_word_length': 4.3373253493013975, 'pattern_counts': {'":': 0, '<': 14, '<?xml version=': 0, '>': 14, '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': '16902', 'license': 'Apache-2.0', 'n_tokens_mistral': 989, 'n_tokens_neox': 951, 'n_words': 179, 'path': 'jupyter/Testing-Presto-SQL.ipynb', 'repo_name': 'uno-sebastian/f8-2019-demo', 'size': '2668'}