<jupyter_start><jupyter_text>Adding Information to a Database Getting and Saving Data*<NAME>*In this notebook I create and add data to a database table.First, load in required libraries.<jupyter_code>import pymysql
from sqlalchemy import create_engine
import pandas as pd
from pandas import DataFrame
def pymysql_sqlalchemy_stringgen(user, passwd, host, dbname):
    """Generate a connection string for use with SQLAlchemy for MySQL and PyMySQL connections
    
    Args:
        user (str): The username of the connecting user
        passwd (str): The user's password
        host (str): The host for where the database is located
        dbname (str): The name of the database to connect with
    
    Returns:
        (str) A SQLAlchemy connection string suitable for use with create_engine()
    
    Additional options for the connection are not supported with this function.
    """
    
    return "mysql+pymysql://" + user + ":" + passwd + "@" + host + "/" + dbname
conn = create_engine(pymysql_sqlalchemy_stringgen("root", pswd, "localhost", "poppyramids")).connect()    # Connect to database<jupyter_output><empty_output><jupyter_text>Read in a pandas `DataFrame` with the information we want to add to the database.<jupyter_code>pops = pd.read_csv("PopPyramids.csv", index_col=["Country", "Year", "Age"])
pops.head()<jupyter_output><empty_output><jupyter_text>These column names could be trouble in a database, so let's make names lower case and use `_` instead of spaces.<jupyter_code>pops.columns = pd.Index(pd.Series(pops.columns).map(lambda x: x.lower().replace(" ", "_")))    # Format column names
pops.index.rename([n.lower() for n in pops.index.names], inplace=True)    # Rename the names of the MultiIndex levels
pops.head()<jupyter_output><empty_output><jupyter_text>Create a table in the database using SQL to hold this data; it starts with no rows.<jupyter_code>make_table = """CREATE TABLE `populations` (
                    `region` varchar(20),
                    `year` int(4),
                    `both_sexes_population` bigint(20),
                    `male_population` bigint(20),
                    `female_population` bigint(20),
                    `percent_both_sexes` double,
                    `percent_male` double,
                    `percent_female` double,
                    `sex_ratio` double,
                    `age` char(5) NOT NULL,
                    `country` char(28) NOT NULL,
                    PRIMARY KEY (`country`,`year`,`age`)
                );"""

conn.execute(make_table)<jupyter_output><empty_output><jupyter_text>Add the rows to the table, appending them. Keys will be properly matched.<jupyter_code>pops.to_sql("populations",         # The name of the table in the database
            con=conn,              # The connection object
            if_exists='append')    # If the table exists, add rows (other options: 'fail' to do nothing, 'replace' to 
                                   # delete and make new table)<jupyter_output><empty_output><jupyter_text>Under the hood, `to_sql()` is issuing `INSERT` commands to MySQL.<jupyter_code>conn.close()    # Always close the connection<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0986180904522613, 'fraction_numerical': 0.0037688442211055275, 'mean_word_length': 3.0521628498727735, 'pattern_counts': {'":': 1, '<': 26, '<?xml version=': 0, '>': 26, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '215607', 'license': 'MIT', 'n_tokens_mistral': 952, 'n_tokens_neox': 898, 'n_words': 289, 'path': 'Section 1/AddingDatabase.ipynb', 'repo_name': 'PacktPublishing/Data-Acquisition-and-Manipulation-with-Python-Video-', 'size': '3191'}