Python / S3 - Functions to list keys in an S3 bucket using Python

Reference : https://alexwlchan.net/2017/07/listing-s3-keys/

from glob import glob
import boto3


class Versions:
    def __init__(self):
        """Gets the latest version from local or s3"""        

         pass

    def get_latest_version_from_local(path):
        """Gets the latest version from Local"""        

        versions_paths = glob((path + "/*"), recursive=True)
        versions = []
        for i in enumerate(versions_paths):
            split_path = i[1].rstrip('/').split("/")
            version = split_path.pop()
            versions.append(version)
        versions.sort(reverse=True)
        return versions[0]

    def get_latest_version_from_s3(bucket_name, path):
        """Gets the latest version from s3 """       

        key = path.rstrip('/').split("/").pop()
        s3 = boto3.client('s3')
        response = s3.list_objects_v2(
            Bucket=bucket_name,            

            Prefix=key,            

            MaxKeys=100)
        versions = []
        for obj in response['Contents']:
            split_path = obj['Key'].rstrip('/').split("/")
            versions.append(split_path[1])
        versions.sort(reverse=True)
        return versions[0]
 

    def get_all_s3_keys(bucket_name):
        """Get a list of all keys in an S3 bucket."""        

        versions = []

        kwargs = {'Bucket': bucket_name}
        s3 = boto3.client('s3')
        while True:
            resp = s3.list_objects_v2(**kwargs)
            for obj in resp['Contents']:
                if 'well_production' in str(obj['Key']) 

                    and '$folder$' not in str(obj['Key']):
                    versions.append(obj['Key'])
            try:
                kwargs['ContinuationToken'] = resp['NextContinuationToken']
            except KeyError:
                break        

        versions.sort(reverse=True)
        return versions[0]

SQL - table-valued function to get all months between two date range

CREATE FUNCTION [dbo].[GetMonths](@StartDate DATETIME, @EndDate DATETIME)

RETURNS @MonthList TABLE(MonthValue VARCHAR(15) NOT NULL)

AS

BEGIN

    --Variable used to hold each new date value

    DECLARE @DateValue DATETIME

    --Start with the starting date in the range

    SET @DateValue=@StartDate

    --Load output table with the month part of each new date

    WHILE @DateValue <= @EndDate

    BEGIN

        INSERT INTO @MonthList(MonthValue)

        SELECT cast(@DateValue as date)

        --Move to the next month

        SET @DateValue=DATEADD(mm,1,@DateValue)

    END

    RETURN 

END

SQL - Track where a Stored Procedure is being used

 SELECT o.name
 FROM syscomments AS c
 INNER JOIN sysobjects AS o
 ON c.id = o.id
 WHERE c.text LIKE '%stored_procedure_name%';

Regular Expression - good practice and a good place to check

A good place to check if you regular expression does the work is https://regex101.com/

e.g. 

I have a list of company list extracted from some website's filter:

By using regular expression, I can easily extract the company number in two lines:

regex = re.compile(r'.*\((\d{5})\)')
company_value_list = [regex.match(item.text).group(1) for item in company_list
                      if re.match(regex, item.text) is not None]

So the result is a list of company numbers:

['39227', '65860', '39639', '68942', '68979', '68998', '68938', '62950'.....]

SQL - Pivot and Unpivot

Just for me to remember :)


Pivot:

SELECT API,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10]
FROM (
             SELECT API, row_no, decLiquidBoe/24 AS boed
     FROM #tbl_Liquid_Prod_All
     )d
PIVOT
(SUM(boed) FOR row_no IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) AS pt


Unpivot:

 SELECT [API], [metric_date], [value]
 FROM #OH_Oil_Output
 UNPIVOT
 ([value] FOR metric_date IN ( [2020-01-01], [2020-02-01], [2020-03-01])) AS u

Python - usaging of "__init__.py"

create __init__.py file to get all files in the folder.

from os.path import dirname, basename, isfile, join
import glob

modules = glob.glob(join(dirname(__file__), "*.py"))
__all__ = [basename(f)[:-3] for f in modules if isfile(f) and not f.endswith('__init__.py')]

Python - scraping website with redirect links

This website (https://portalweb.cammesa.com/memnet1/Pages/descargas.aspx) has redirect link, and the post information is hidden in the response. you have to use the information to do the post, then you can get to the next step.

e.g.

from bs4 import BeautifulSoup
import requests
import zipfile


def find_between(s, first, last):
    try:
        start = s.index(first) + len(first)
        end = s.index(last, start)
        return s[start:end]
    except ValueError:
        return ""

headers = {
    'Content-Type': 'xxxxxxxxxxxx',    

    'User-Agent': 'xxxxxxxxxxx'}

session = requests.Session()

start_link = ''text = session.get(start_link, headers=headers).text
soup = BeautifulSoup(text, 'html.parser')

links = soup.find_all('a')

for link in links:
    if 'informe mensual' in str(link).lower():
        c_link = link.get('href')
        monthly_report_link_redirect = 'xxxxxxxx' + str(c_link)

        resp_informe_mensual = session.get(monthly_report_link_redirect, headers=headers)
        soup2_informe_mensual = BeautifulSoup(resp_informe_mensual.text, 'html.parser')

        # redirect to login page

        login_link = 'xxxxxxxxxxxxx'        

        data = {
            'Username': 'xxxxxx',            

            'Password': 'xxxxxx',            

            'RedirectTo': 'xxxxxxx',            

            'Remote_Addr': 'xxxxxxx'        

        }
        resp_open_frame_set = session.post(login_link, headers=headers, data=data)
        soup_open_frame_set = BeautifulSoup(resp_open_frame_set.text, 'html.parser')

        # final data page        data_link = 'xxxxxxxxxxxxx'

        resp_open_page = session.get(data_link, headers=headers)
        soup_open_page = BeautifulSoup(resp_open_page.text, 'html.parser')

        zip_link = soup_open_page.find_all('a')[2].get('href')
        full_link = 'xxxxxxx' + find_between(str(zip_link), '=/', 'zip') + 'zip'        

        file_name = find_between(str(zip_link), '$File/', 'zip') + 'zip'        

        print(full_link)
        print(file_name)

        # Download XXXX.zip file        

        r = requests.get(full_link,  auth=('xxxxxx', 'xxxxx'))
        with open(file_name, "wb") as code:
            code.write(r.content)

        # Unzip files        

        with zipfile.ZipFile(file_name, "r") as zip_ref:
            zip_ref.extractall()