<jupyter_start><jupyter_text>Library<jupyter_code>import os

from io import StringIO
import datetime
import pandas as pd

import psycopg2

from pprint import pprint 

# version info
print('', 'Pandas version: ', pd.__version__, '\n', 'Psycopg 2 version:',
      psycopg2.__version__)<jupyter_output><empty_output><jupyter_text>Connecting to PostgreSQL<jupyter_code># Connection params
dbname = "postgres"
user = "postgres"
password = "****"
host = "localhost"
port = "5432"
# --+ Basic interaction:
# create a new database session
connection = psycopg2.connect(dbname=dbname,
                            user=user,
                            password=password,
                            host=host,
                            port=port)

# open a cursor to perform database commands and queries
cursor = connection.cursor()

# --+ Run some SQL command:
# get current date
cursor.execute("SELECT current_date;")
date = cursor.fetchone() # fetchone (returns a single tuple)
print("Current date: ", date[0].isoformat()) # print the info


# --+ Close the session:
# close the cursor
cursor.close()
# close the connection
connection.close()
# Let's try to connect and print some info
try:
    connection = psycopg2.connect(dbname=dbname,
                            user=user,
                            password=password,
                            host=host,
                            port=port)
    cursor = connection.cursor()

    # Print some info
    cursor.execute("SELECT current_user;")
    user = cursor.fetchone()

    cursor.execute("SELECT current_database();")
    db = cursor.fetchone()

    cursor.execute("SELECT version();")
    version = cursor.fetchone()

    print('', "Current user: ", user[0], "\n", "Current database: ", db[0], "\n",
          "Version info: ", '-'.join(version[0].split()[:4]), "\n")


except psycopg2.Error as error:
    print("Warning!", error)

finally:
        if connection is not None:
            cursor.close()
            connection.close()<jupyter_output><empty_output><jupyter_text>Homework 1 1. Create a new Database named smm6952. Create a new Schema named homework13. Create a new Table with the following characteristics:| column_name | data_type | length/precision ||-------------|-----------|------------------|| id          | serial    |                  || first_name  | varchar   | 20               || last_name   | varchar   | 20               || email       | varchar   | 80               || salary      | numeric   |                  |4. Insert the following values:| id | first_name | last_name | email                 | salary ||----|------------|-----------|-----------------------|--------|| 1  | Dave       | Alstom    | <EMAIL> | 50000  || 2  | Hunter     | Reese     | <EMAIL>  | 37000  || 3  | Kerys      | Mcclure   | <EMAIL>     | 28000  |5. Create a table that could contain the following values:| id | name    | country        | postal_code | date_of_birth ||----|---------|----------------|-------------|---------------|| 1  | Martina | Italy          | 04929       | 1995-06-18    || 2  | Lenny   | United Kingdom | E2 9AD      | 1980-05-13    || 3  | Taha    | China          | 100023      | 1994-09-12    |  Create a new Database<jupyter_code># CREATE DATABASE
try:
    connection = psycopg2.connect(dbname=dbname,
                            user=user,
                            password=password,
                            host=host,
                            port=port)

    # Set autocommit
    connection.autocommit = True
    cursor = connection.cursor()

    # Create a new database
    cursor.execute("CREATE DATABASE smm695;")
    print(cursor.statusmessage)


except psycopg2.Error as error:
    print("Warning!", error)

finally:
        if connection is not None:
            cursor.close()
            connection.close()<jupyter_output><empty_output><jupyter_text>CREATE SCHEMA and TABLES<jupyter_code># Set the new db
dbname = 'smm695'
# Connect to smm695 and perform some tasks
try:
    connection = psycopg2.connect(dbname=dbname,
                            user=user,
                            password=password,
                            host=host,
                            port=port)
    cursor = connection.cursor()

    # Create a schema
    cursor.execute("CREATE SCHEMA homework1;")

    print(cursor.statusmessage, '\n')

    # Create a table storing employees data
    cursor.execute("""CREATE TABLE homework1.employees (
    id serial, 
    first_name  varchar(20),
    last_name varchar(20),
    email varchar(80),
    salary numeric);""")

    print(cursor.statusmessage, '\n')

    # Create a table storing people data

    second_table_sql = """CREATE TABLE homework1.person (
    id serial,
    name varchar(10),
    country varchar(20),
    postal_code text,
    dob date);"""

    cursor.execute(second_table_sql)

    print(cursor.statusmessage)

    # Commit changes
    connection.commit()

except psycopg2.Error as error:
    print("Warning!", error)
    
finally:
        if connection is not None:
            cursor.close()
            connection.close()<jupyter_output><empty_output><jupyter_text>Insert values: loop<jupyter_code># data to insert
employees = """
first_name, last_name, email,                 salary
Dave,       Alstom,    <EMAIL>, 50000
Hunter,     Reese,     <EMAIL>,  37000
Kerys,      Mcclure,   <EMAIL>,     28000
"""
# loading the database
db = pd.read_csv(StringIO(employees),
                 skiprows=1,
                 skipinitialspace=True,
                 engine='python')

# obtaining tuples
values = list(zip(*map(db.get, db)))

# array of tuples 
values
# Connect to smm695 and perform some tasks
try:
    connection = psycopg2.connect(dbname=dbname,
                            user=user,
                            password=password,
                            host=host,
                            port=port)
    cursor = connection.cursor()
    
    # SQL INSERT
    insert_sql = """INSERT INTO homework1.employees (first_name, last_name, email, salary)
    VALUES (%s, %s, %s, %s)"""
    
    # Insert data
    for value in values:
        cursor.execute(insert_sql, value)
        print(cursor.statusmessage)

    # Commit changes
    connection.commit()

except  psycopg2.Error as error:
    print("Warning!", error)

finally:
        if connection is not None:
            cursor.close()
            connection.close()<jupyter_output><empty_output><jupyter_text>Inserting values: executemany<jupyter_code>values = [
('Martina',  'Italy', '04929',datetime.date(1995,6,19)),
('Lenny', 'United Kingdom',  'E2 9AD',  datetime.date(1980,5,13)),
('Taha', 'China', '100023',   datetime.date(1994,9,12))]
# Connect to smm695 and perform some tasks
try:
    connection = psycopg2.connect(dbname=dbname,
                            user=user,
                            password=password,
                            host=host,
                            port=port)
    cursor = connection.cursor()
    
    # SQL INSERT
    insert_sql = """INSERT INTO homework1.person (name, country, postal_code, dob)
    VALUES (%s, %s, %s, %s);"""
    
    # Insert data
    cursor.executemany(insert_sql, values)

    # Commit changes
    connection.commit()


except psycopg2.Error as error:
    print("Warning!", error)

finally:
        if connection is not None:
            cursor.close()
            connection.close()<jupyter_output><empty_output><jupyter_text>Homework 2 1. Create a schema named homework 2 2. Create a table with the following characteristics:| column_name   | data_type | length/precision ||---------------|-----------|------------------|| id            | serial    |                  || name          | varchar   | 20               || department_id | int       |                  || phone_number  | varchar   | 20               || salary        | numeric   |                  |3. Apply the following constraints:| column        | constraints                                  ||---------------|----------------------------------------------|| id            | PRIMARY KEY                                  || name          | NOT NULL                                     || department_id | CHECK(department_id = 1 OR department_id =2) || phone_number  | UNIQUE                                       || salary        | NOT NULL                                     |4. Insert the following observations:| id | name     | department_id | phone_number | salary ||----|----------|---------------|--------------|--------|| 1  | John     | 2             | 690.623.6568 | 20000  || 2  | Leo      | 1             | 690.623.6708 | 80000  || 3  | Diana    | 1             | 690.623.0007 | 110000 || 4  | Paula    | 2             | 690.623.6500 | 35000  || 5  | Simon    | 2             | 690.623.9834 | 45000  || 6  | Jennifer | 2             |              | 18000  |5. Calculate the average salary by department | department_id | avg_salary |  |---------------|------------| | 1             | 95000.00   | | 2             | 29500.00   |6. Calculate the maximum salary by department | department_id | avg_salary | |---------------|------------| | 1             | 110000     | | 2             | 45000      |7. Count the number of observations that contain the letter 'o' in the 'name' field| count_o ||---------|| 3       |8. Import location.csv and car.csv9. Count the number of female observations with _`dob'_ between 1990 and 1992| count ||-------|| 112   |10. Select the two car models with the highest price for the last ten years | car_model | max    | |-----------|--------| | Caliber   | 999718 | | MKT       | 995884 |  Create Schema and Table<jupyter_code># Connect to smm695 and perform some tasks
try:
    connection = psycopg2.connect(dbname=dbname,
                            user=user,
                            password=password,
                            host=host,
                            port=port)
    cursor = connection.cursor()

    # Create a schema
    cursor.execute("CREATE SCHEMA homework2;")

    print(cursor.statusmessage, '\n')

    # Create a table storing employees data
    cursor.execute("""CREATE TABLE homework2.employee (
    id serial PRIMARY KEY,
    name varchar(20) NOT NULL,
    department_id int,
    phone_number varchar(20) UNIQUE,
    salary numeric NOT NULL,
    CHECK (department_id = 1 OR department_id = 2));""")

    print(cursor.statusmessage, '\n')

    # Commit changes
    connection.commit()


except psycopg2.Error as error:
    print("Warning!", error)

finally:
        if connection is not None:
            cursor.close()
            connection.close()<jupyter_output><empty_output><jupyter_text>Insert values: dictionary<jupyter_code>employees = """
name,     department_id, phone_number, salary
John,     2,            690.623.6568,  20000
Leo,      1,            690.623.6708,  80000
Diana,    1,            690.623.0007,  110000
Paula,    2,            690.623.6500,  35000
Simon,    2,            690.623.9834,  45000
Jennifer, 2,                        ,  18000
"""

# loading the database
db = pd.read_csv(StringIO(employees),
                 skiprows=1,
                 skipinitialspace=True,
                 engine='python')

# data to dictionary
array_dict = db.to_dict('records')

pprint(array_dict)
# Connect to smm695 and perform some tasks
try:
    connection = psycopg2.connect(dbname=dbname,
                            user=user,
                            password=password,
                            host=host,
                            port=port)
    cursor = connection.cursor()
    
    # SQL INSERT
    insert_sql = """INSERT INTO homework2.employee (name, department_id, phone_number, salary)
    VALUES (%(name)s, %(department_id)s, %(phone_number)s, %(salary)s)"""
    
    # Insert data:
    cursor.executemany(insert_sql, array_dict)

    # Commit changes
    connection.commit()


except  psycopg2.Error as error:
    print("Warning!", error)

finally:
        if connection is not None:
            cursor.close()
            connection.close()<jupyter_output><empty_output><jupyter_text>Tasks 5 to 7<jupyter_code>#Connect to smm695 and perform some tasks
try:
    connection = psycopg2.connect(dbname=dbname,
                            user=user,
                            password=password,
                            host=host,
                            port=port)
    cursor = connection.cursor()

    # 5. Calculate the average salary by department
    cursor.execute("""
    SELECT department_id, ROUND(AVG(salary),2) AS avg_salary  FROM homework2.employee 
    GROUP BY department_id ORDER BY department_id;
    """)
    five = cursor.fetchall()

    # 6. Calculate the maximum salary by department
    cursor.execute("""
    SELECT department_id, MAX(salary) AS max_salary  FROM homework2.employee 
    GROUP BY department_id ORDER BY department_id;
    """)
    six = cursor.fetchall()
    
    # 7. Count the number of observations that contain the letter 'o' in the 'name' field
    cursor.execute("""
    SELECT COUNT(*) AS count_o  FROM homework2.employee 
    WHERE name LIKE '%o%';
    """)
    seven = cursor.fetchone()
    
    # Show results
    print("""
    Average salary by department: 
        {}
    
    Maximum salary by department: 
        {}
    
    Observations that contain the letter o: 
        {}
    """.format(five, six, seven[0]))
    

except psycopg2.Error as error:
    print("Warning!", error)

finally:
        if connection is not None:
            cursor.close()
            connection.close()<jupyter_output><empty_output><jupyter_text>Import csv<jupyter_code>SRV = '/Insert/Path'
PATH = 'To/Your'
FOLDER = 'Target/Folder'
dbname = 'mydb'

# Connect to smm695 and perform some tasks
try:
    connection = psycopg2.connect(dbname=dbname,
                            user=user,
                            password=password,
                            host=host,
                            port=port)
    cursor = connection.cursor()

    # Create car table
    cursor.execute("""
    CREATE TABLE people.car (
    id bigserial PRIMARY KEY,
    car_make varchar(50),
    car_model varchar(50),
    car_year int,
    price numeric);
    """)
    print(cursor.statusmessage, '\n')

    # Create location table
    cursor.execute("""
    CREATE TABLE people.location (
    id bigserial PRIMARY KEY,
    country varchar(50),
    city varchar(50),
    street_name varchar(50),
    street_number int,
    postal_code varchar(50));
    """)
    print(cursor.statusmessage, '\n')

    # Copy car.csv
    car = open(os.path.join(SRV, PATH, FOLDER, 'car.csv'), 'r') #file-like object
    copy_car = """
           COPY people.car(car_make, car_model, car_year, price) FROM stdin WITH CSV HEADER
           DELIMITER as ','
           """
    cursor.copy_expert(copy_car, file=car)
    
    cursor.execute("""SELECT * FROM people.car;""")
    car_fetch = cursor.fetchmany(2)
    print( 'Car:', [car_fetch[i] for i in range(0, len(car_fetch), 1)], '\n')

    # Copy location.csv
    location = open(os.path.join(SRV, PATH, FOLDER, 'location.csv'), 'r') #file-like object
    copy_location = """
           COPY people.location(country, city, street_name, street_number, postal_code) FROM stdin WITH CSV HEADER
           DELIMITER as ','
           """
    cursor.copy_expert(copy_location, file=location)
    
    cursor.execute("""SELECT * FROM people.location;""")
    location_fetch = cursor.fetchmany(2)
    print( 'Location:', [location_fetch[i] for i in range(0, len(location_fetch), 1)])

    # Commit changes
    connection.commit()


except psycopg2.Error as error:
    print("Warning!", error)

finally:
        if connection is not None:
            cursor.close()
            connection.close()<jupyter_output><empty_output><jupyter_text>Task 9 and 10<jupyter_code># Connect to smm695 and perform some tasks
try:
    connection = psycopg2.connect(dbname=dbname,
                            user=user,
                            password=password,
                            host=host,
                            port=port)
    cursor = connection.cursor()

    # 9. Count the number of female observations with `dob' between 1990 and 1992
    cursor.execute("""
    SELECT COUNT(*) FROM people.person  
    WHERE gender = 'Female' AND EXTRACT(YEAR FROM dob) BETWEEN 1990 AND 1992;
    """)
    nine = cursor.fetchall()

    # 10. Select the two car models with the highest price for the last ten years
    cursor.execute("""
    SELECT car_model, MAX(price) FROM people.car 
    WHERE car_year BETWEEN 2010 AND 2020 
    GROUP BY car_model 
    ORDER BY MAX(price) DESC LIMIT 2;
    """)
    ten = cursor.fetchall()

    # Show results
    print("""
    Count: {}
    
    Carn models: 
        1. {}, $ {}
        2. {}, $ {}
    """.format(nine[0][0], ten[0][0], ten[0][1], ten[1][0], ten[1][1]))


except psycopg2.Error as error:
    print("Warning!", error)

finally:
        if connection is not None:
            cursor.close()
            connection.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1283014438314356, 'fraction_numerical': 0.03140039910787651, 'mean_word_length': 1.9201371036846615, 'pattern_counts': {'":': 0, '<': 51, '<?xml version=': 0, '>': 51, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 10, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '71649', 'license': 'MIT', 'n_tokens_mistral': 5339, 'n_tokens_neox': 4832, 'n_words': 1307, 'path': 'week-2/webinar-2/wn_2.ipynb', 'repo_name': 'eiriniavraam/dms-smm695', 'size': '17112'}