<jupyter_start><jupyter_text>Import Package and Connect<jupyter_code>#import sqlite3 package
import sqlite3
#create a connection to a database. Create the db if not available.
connection = sqlite3.connect("classroomDB.db")
connection.close()<jupyter_output><empty_output><jupyter_text>Create Table<jupyter_code>connection = sqlite3.connect("classroomDB.db")
cursor = connection.cursor()

create_table = """
                CREATE TABLE classroom(
                student_id INTEGER PRIMARY KEY,
                name VARCHAR(20),
                gender CHAR(1),
                physics_marks INTEGER,
                chemistry_marks INTEGER,
                mathematics_marks INTEGER
                );"""

cursor.execute(create_table)

connection.commit()

connection.close()<jupyter_output><empty_output><jupyter_text>Insert Rows<jupyter_code>classroom_data = [(1, "Raj", "M", 70, 84, 92),
                  (2, "Poonam", "F", 87, 69, 93),
                  (3, "Nik", "M", 65, 83, 90),
                  (4, "Rahul", "F", 83, 76, 89),
                  (5, "Brandon", "M", 100, 100, 100)]

connection = sqlite3.connect("classroomDB.db")

cursor = connection.cursor()

for student in classroom_data:
    insert_statement = """INSERT INTO classroom
                          (student_id, name, gender, physics_marks, chemistry_marks, mathematics_marks)
                          VALUES
                          ({0}, "{1}", "{2}", {3}, {4}, {5});""".format(student[0], student[1], student[2],
                                                                       student[3], student[4], student[5])
    cursor.execute(insert_statement)
    
connection.commit()

connection.close()<jupyter_output><empty_output><jupyter_text>Extract Data<jupyter_code>connection = sqlite3.connect("classroomDB.db")

cursor = connection.cursor()

query = "SELECT * FROM classroom"

cursor.execute(query)

result = cursor.fetchall()

for row in result:
    print(row)
    
connection.close()<jupyter_output>(1, u'Raj', u'M', 70, 84, 92)
(2, u'Poonam', u'F', 87, 69, 93)
(3, u'Nik', u'M', 65, 83, 90)
(4, u'Rahul', u'F', 83, 76, 89)
(5, u'Brandon', u'M', 100, 100, 100)<jupyter_text>MySql Database<jupyter_code>!conda install -y -q pymysql<jupyter_output>Solving environment: ...working... done

# All requested packages already installed.<jupyter_text>Import Package<jupyter_code>import pymysql<jupyter_output><empty_output><jupyter_text>Connect To Host<jupyter_code>cnx = {'host': 'mysqldb.cx2cs9jyfcll.us-east-1.rds.amazonaws.com',
       'username': 'test',
       'password' : '<PASSWORD>',
       'db' : 'classroomDB'}

connection = pymysql.connect(cnx['host'], cnx['username'], cnx['password'], cnx['db'])

connection.close()<jupyter_output><empty_output><jupyter_text>Create Table<jupyter_code>connection = pymysql.connect(cnx['host'], cnx['username'], cnx['password'], cnx['db'])

cursor = connection.cursor()

create_table = """
               CREATE TABLE classroom(
               student_id INTEGER PRIMARY KEY,
                name VARCHAR(20),
                gender CHAR(1),
                physics_marks INTEGER,
                chemistry_marks INTEGER,
                mathematics_marks INTEGER
               );
               """

cursor.execute(create_table)

connection.commit()

connection.close()<jupyter_output><empty_output><jupyter_text>Insert Data<jupyter_code>classroom_data = [(1, "Raj", "M", 70, 84, 92),
                  (2, "Poonam", "F", 87, 69, 93),
                  (3, "Nik", "M", 65, 83, 90),
                  (4, "Rahul", "F", 83, 76, 89),
                  (5, "Brandon", "M", 100, 100, 100)]

connection = connection = pymysql.connect(cnx['host'], cnx['username'], cnx['password'], cnx['db'])

cursor = connection.cursor()

for student in classroom_data:
    insert_statement = """INSERT INTO classroom
                          (student_id, name, gender, physics_marks, chemistry_marks, mathematics_marks)
                          VALUES
                          ({0}, "{1}", "{2}", {3}, {4}, {5});""".format(student[0], student[1], student[2],
                                                                       student[3], student[4], student[5])
    cursor.execute(insert_statement)
    
connection.commit()

connection.close()<jupyter_output><empty_output><jupyter_text>Extract Data<jupyter_code>connection = connection = connection = pymysql.connect(cnx['host'], cnx['username'], cnx['password'], cnx['db'])

cursor = connection.cursor()

query = "SELECT * FROM classroom"

cursor.execute(query)

result = cursor.fetchall()

for row in result:
    print(row)
    
connection.close()<jupyter_output>(1, 'Raj', 'M', 70, 84, 92)
(2, 'Poonam', 'F', 87, 69, 93)
(3, 'Nik', 'M', 65, 83, 90)
(4, 'Rahul', 'F', 83, 76, 89)
(5, 'Brandon', 'M', 100, 100, 100)
{'fraction_non_alphanumeric': 0.15464994775339602, 'fraction_numerical': 0.039916405433646815, 'mean_word_length': 2.8690379951495553, 'pattern_counts': {'":': 0, '<': 39, '<?xml version=': 0, '>': 39, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '389506', 'license': 'MIT', 'n_tokens_mistral': 1724, 'n_tokens_neox': 1535, 'n_words': 336, 'path': 'Untitled.ipynb', 'repo_name': 'millbj92/Titanic', 'size': '4785'}