<jupyter_start><jupyter_text>Extracting data from relatinal databases SQLite Database Import package<jupyter_code># import sqlite3 package
import sqlite3<jupyter_output><empty_output><jupyter_text>Connect to database<jupyter_code># create a connection to a database : create the database if not available
connection = sqlite3.connect("classroomDB.db")<jupyter_output><empty_output><jupyter_text>create table<jupyter_code># open connection
connection = sqlite3.connect("classroomDB.db")

# open cursor
cursor = connection.cursor()
#query for creating table
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
                );"""
# execute query
cursor.execute(create_table)
# commit changes
connection.commit()
# close connection
connection.close()<jupyter_output><empty_output><jupyter_text>Insert data<jupyter_code># sample data
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),]
# open connection
connection = sqlite3.connect("classroomDB.db")
# open cursor
cursor = connection.cursor()
# insert each student record
for student in classroom_data:
    # formatted query string
    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])
    # execute insert query
    cursor.execute(insert_statement)

# commit the changes
connection.commit()
# close the connection
connection.close()<jupyter_output><empty_output><jupyter_text>extract data<jupyter_code># open connection
connection = sqlite3.connect("classroomDB.db")
# open cursor
cursor = connection.cursor()
# query
query = "SELECT * FROM classroom"
# execute query
cursor.execute(query)
# fetch results
result = cursor.fetchall()
# print results
for row in result:
    print(row)
# close connection
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)<jupyter_text>MySQL database install package<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 database<jupyter_code># Connection details (needs a AWS database to use the host address like this one: "'host': 'mysqldb.c12wj3xlqsae.us-west-2.rds.amazonaws.com'")
cnx = ('host': 'mysqldb.c12wj3xlqsae.us-west-2.rds.amazonaws.com',
      'username': 'test',
      'password': '<PASSWORD>',
      'db': 'classroomDB')


# Connect to the database
connection = pymysql.connect(cnx['host'], cnx['username'], cnx['password'], cnx['db'])

# close database
connection.close()<jupyter_output><empty_output><jupyter_text>create table<jupyter_code># open connection
connection = pymysql.connect(cnx['host'], cnx['username'], cnx['password'], cnx['db'])
# open cursor
cursor = connection.cursor()
# query for creating table
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
                );"""
# execute query
cursor.execute(create_table)
# commit changes
connection.commit()
# close connection
connection.close()<jupyter_output><empty_output><jupyter_text>insert data<jupyter_code># sample data
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),]
# open connection
connection = sqlite3.connect("classroomDB.db")
# open cursor
cursor = connection.cursor()
# insert each student record
for student in classroom_data:
    # formatted query string
    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])
    # execute insert query
    cursor.execute(insert_statement)

# commit the changes
connection.commit()
# close the connection
connection.close()<jupyter_output><empty_output><jupyter_text>extract data<jupyter_code># open connection
connection = pymysql.connect(cnx['host'], cns['username'], cnx['password'], cnx['db'])
# open cursor
cursor = connection.cursor()
# query
query = "SELECT * FROM classroom"
# execute query
cursor.execute(query)
# fetch results
result = cursor.fetchall()
# print results
for row in result:
    print(row)
# close connection
connection.close()<jupyter_output><empty_output><jupyter_text>Microsoft SQL Server database install package<jupyter_code>!conda install -y -q pymssql<jupyter_output><empty_output><jupyter_text>import package (para conectar, basta seguir os passos acima do mysql, com atenção ao fato de que precisa ter criado uma base na AWS previamente, para que seja possíve realizar a conexao)<jupyter_code>import pymssql<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12791107974594212, 'fraction_numerical': 0.02275935074100212, 'mean_word_length': 3.9640980735551663, 'pattern_counts': {'":': 0, '<': 52, '<?xml version=': 0, '>': 52, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '226009', 'license': 'MIT', 'n_tokens_mistral': 1874, 'n_tokens_neox': 1738, 'n_words': 462, 'path': 'notebooks/01 Extraction data from relational database.ipynb', 'repo_name': 'rudsonramon/kaggle-projects-titanic', 'size': '5668'}