<jupyter_start><jupyter_text>Reading and writing CSV files with NumPy<jupyter_code># import genfromtxt function
from numpy import genfromtxt

# Read comma separated file
product_data = genfromtxt('demo.csv', delimiter=',')

# display initial 5 records
print(product_data)
# import numpy
import numpy as np

# Create a sample array
sample_array = np.asarray([ [1,2,3], [4,5,6], [7,8,9] ])

# Write sample array to CSV file
np.savetxt("my_first_demo.csv", sample_array, delimiter=",")<jupyter_output><empty_output><jupyter_text>Reading and writing CSV files with Pandas<jupyter_code># import pandas
import pandas as pd

# Read CSV file
sample_df=pd.read_csv('demo.csv', sep=',' , header=None)

# display initial 5 records
sample_df.head()
# Save DataFrame to CSV file
sample_df.to_csv('demo_sample_df.csv')<jupyter_output><empty_output><jupyter_text>Reading and Writing Data from Excel<jupyter_code># Read excel file
df=pd.read_excel('employee.xlsx',sheet_name='performance')

# display initial 5 records
df.head()
df.to_excel('employee_performance.xlsx')
# Read excel file
emp_df=pd.read_excel('employee.xlsx',sheet_name='employee_details')
# write multiple dataframes to single excel file
with pd.ExcelWriter('new_employee_details.xlsx') as writer:
    emp_df.to_excel(writer, sheet_name='employee')
    df.to_excel(writer, sheet_name='perfromance')<jupyter_output><empty_output><jupyter_text>Reading and Writing Data from JSON<jupyter_code># Reading JSON file
df=pd.read_json('employee.json')

# display initial 5 records
df.head()
# Writing DataFrame to JSON file
df.to_json('employee_demo.json',orient="columns")<jupyter_output><empty_output><jupyter_text>Reading and Writing Data from HDF5<jupyter_code># Write DataFrame to hdf5
df.to_hdf('employee.h5', 'table', append=True)
# Read a hdf5 file
df=pd.read_hdf('employee.h5', 'table')

# display initial 5 records
df.head()<jupyter_output><empty_output><jupyter_text>Reading and Writing Data from HTML Tables<jupyter_code>import pandas as pd
# Reading HTML table from given URL
table_url = 'https://en.wikipedia.org/wiki/List_of_sovereign_states_and_dependent_territories_in_North_America'
df_list = pd.read_html(table_url)
print("Number of DataFrames:",len(df_list))
# Check first DataFrame
print(df_list[0].head())
# Write DataFrame to raw HTML
df_list[1].to_html('country.html')<jupyter_output><empty_output><jupyter_text>Reading and Writing Data from parquet<jupyter_code>!pip install pyarrow
# Write to a parquet file.
df.to_parquet('employee.parquet', engine='pyarrow')
# Read parquet file
employee_df = pd.read_parquet('employee.parquet', engine='pyarrow')

# display initial 5 records
employee_df.head()<jupyter_output><empty_output><jupyter_text>Reading and Writing Data from Pickle Pandas Object<jupyter_code># import pandas
import pandas as pd

# Read CSV file
df=pd.read_csv('demo.csv', sep=',' , header=None)

# Save DataFrame object in pickle file
df.to_pickle('demo_obj.pkl')
#Read DataFrame object from pickle file
pickle_obj=pd.read_pickle('demo_obj.pkl')

# display initial 5 records
pickle_obj.head()<jupyter_output><empty_output><jupyter_text>Lightweight access with sqllite3<jupyter_code># Import sqlite3
import sqlite3

# Create connection. This will create the connection with employee database. If the database does not exist it will create the database
conn = sqlite3.connect('employee.db')

# Create cursor
cur = conn.cursor()

# Execute SQL query and create the database table
cur.execute("create table emp(eid int,salary int)")

# Execute SQL query and Write the data into database
cur.execute("insert into emp values(105, 57000)")

# commit the transaction
conn.commit()

# Execute SQL query and Read the data from the database
cur.execute('select * from emp')

# Fetch records
print(cur.fetchall())

# Close the Database connection
conn.close()<jupyter_output>[(105, 57000)]<jupyter_text>Reading and Writing Data from MySQL<jupyter_code># import pymysql connector module
import pymysql

# Create a connection object using connect() method 

connection = pymysql.connect(host='localhost', # IP address of the MySQL database server
                             user='root', # user name
                             password='<PASSWORD>', # password
                             db='emp', # database name
                             charset='utf8mb4', # character set
                             cursorclass=pymysql.cursors.DictCursor) # cursor type

try:
    with connection.cursor() as cur:
        # Inject a record in database
        sql_query = "INSERT INTO `emp` (`eid`, `salary`) VALUES (%s, %s)"
        cur.execute(sql_query, (104,43000))


    # Commit the record insertion explicitly.
    connection.commit()

    with connection.cursor() as cur:
        # Read records from employee table
        sql_query = "SELECT * FROM `emp`"
        cur.execute(sql_query )
        table_data = cur.fetchall()
        print(table_data)
except:
    print("Exception Occurred")
finally:
    connection.close()
# Import the required connector
import mysql.connector
import pandas as pd

# Establish a database connection to mysql
connection=mysql.connector.connect(user='root',password='<PASSWORD>',host='localhost',database='emp')

# Create a cursor
cur=connection.cursor()

# Running sql query
cur.execute("select * from emp")

# Fetch all the records and print it one by one
records=cur.fetchall()
for i in records:
    print(i)

# Create a DataFrame from fetched records.
df = pd.DataFrame(records)

# Assign column names to DataFrame
df.columns = [i[0] for i in cur.description]

# close the connection
connection.close()
# Import the sqlalchemy engine
from sqlalchemy import create_engine

# Instantiate engine object
en = create_engine("mysql+pymysql://{user}:{pw}@localhost/{db}"
                .format(user="root", pw="root", 
                        db="emp"))

# Insert the whole dataframe into the database
df.to_sql('emp', con=en, if_exists='append',chunksize=1000, index= False)<jupyter_output><empty_output><jupyter_text>Reading and Writing Data from MongoDB<jupyter_code>!pip install pymongo
# Import pymongo
import pymongo

# Create mongo client
client = pymongo.MongoClient()

# Get database
db = client.employee

# Get the collection from database
collection = db.emp

# Write the data using insert_one() method
employee_salary = {"eid":114, "salary":25000}
collection.insert_one(employee_salary)

# Create a dataframe with fetched data
data = pd.DataFrame(list(collection.find()))
data.head()<jupyter_output><empty_output><jupyter_text>Reading and Writing Data from Cassandra<jupyter_code>!pip install cassandra-driver
# Import the cluster
from cassandra.cluster import Cluster

# Creating a cluster object
cluster = Cluster()

# Create connections by calling Cluster.connect():
conn = cluster.connect()

# Execute the insert query
conn.execute("""INSERT INTO employee.emp_details (eid, ename, age) VALUES (%(eid)s, %(ename)s, %(age)s)""", {'eid':101, 'ename': "<NAME>", 'age': 42})

# Execute the select query
rows = conn.execute('SELECT * FROM employee.emp_details')

# Print the results
for emp_row in rows:
    print(emp_row.eid, emp_row.ename, emp_row.age)

# Create a dataframe with fetched data
data = pd.DataFrame(rows)<jupyter_output>101 <NAME> 42<jupyter_text>Reading and Writing Data from Redis<jupyter_code>!pip install redis
# Import module
import redis

# Create connection
r = redis.Redis(host='localhost', port=6379, db=0)

# Setting key-value pair
r.set('eid', '101')

# Get value for given key
value=r.get('eid')

# Print the value
print(value)<jupyter_output>b'101'<jupyter_text>Pony ORM<jupyter_code>!pip install pony
# Import pony module
from pony.orm import *

# Create database
db = Database()

# Define entities
class Emp(db.Entity):
    eid = PrimaryKey(int,auto=True)
    salary = Required(int)

# Check entity definition
show(Emp)

# Bind entities to MySQL database
db.bind('mysql', host='localhost', user='root', passwd='<PASSWORD>', db='emp')

# Generate required mappings for entities
db.generate_mapping(create_tables=True)

# turn on the debug mode
sql_debug(True)

# Select the records from Emp entities or emp table
select(e for e in Emp)[:]

# Show the values of all the attribute
select(e for e in Emp)[:].show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10978260869565218, 'fraction_numerical': 0.010507246376811594, 'mean_word_length': 5.179850746268657, 'pattern_counts': {'":': 2, '<': 59, '<?xml version=': 0, '>': 59, 'https://': 1, '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': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '376307', 'license': 'MIT', 'n_tokens_mistral': 2766, 'n_tokens_neox': 2621, 'n_words': 747, 'path': 'Chapter06/Ch6.ipynb', 'repo_name': 'MichaelCovelli/Python-Data-Analysis-Third-Edition', 'size': '8272'}