Python -Start scraping - 2. website data scrape using bs4 and post request

# -*- coding: utf-8 -*-
"""
Created on Mon Apr  9 11:51:03 2018

"""
from urllib.parse import urlencode
import requests
from bs4 import BeautifulSoup
from datetime import datetime

# Headers; both required!
headers = {
    'Content-Type': 'application/x-www-form-urlencoded',
    'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/64.0.3282.140 Safari/537.36',
}
#-----------------------------------------------------------------------------------------------------------
# Temp parameters: These will be passed in later
#-----------------------------------------------------------------------------------------------------------
# Set up request criteria
data = urlencode({
        """        put parameters here 
        """
})

# Start session and "set query context" by first calling builder page.  This call will acquire session cookie.
session = requests.Session()

try:
    # Post query that includes request criteria by calling submit page
    text = session.post('http://www.xxxxxxxx', headers=headers, data=data).text

    # Loop through each record
    soup = BeautifulSoup(text, 'html.parser')
 
    class_type = ['Item','Alternating']

    for c_type in class_type:
        data_list_items = soup.find_all(class_= c_type)
 
        for data_item in data_list_items:
            columns = permit_item.find_all("td")
     
            data1 = columns[0].contents[0].replace('\xa0', ' ')
            data2 = columns[1].contents[0].replace('\xa0', ' ')
            data3 = columns[2].contents[0].replace('\xa0', ' ')

        
            # reformat permit date to YYYY-mm-dd
            data1 = datetime.strptime(data1, '%m/%d/%Y').strftime('%Y-%m-%d')
     
            # Build document
            document = {
                'data1': data1,
                'data2': data1,
                'data3': data3,
             
                }
       
            print(document)
            
# Raise Exception  
except:
    raise Exception('No data found.')

Python -Start scraping - 1. Zip file download

"""
Created on Tue Mar 20 09:23:10 2018
"""

import  requests
import zipfile

url = 'https://www.xxxxxxx.zip'
username = 'xxxx'
password = 'xxxx'
file_name = 'xxxxxx.zip'


# Download wellIndex.zip file
print("downloading with requests")
r = requests.get(url, auth=(username, password))
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()

SQL - SCHEMABING thing

To check which views are using a table, you can use:

SELECT VIEW_NAME
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE TABLE_NAME = 'tbl_well'

To check a certain column in a table being used by other process, you can use:

SELECT DISTINCT *
FROM sys.all_sql_modules
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%tbl_well%'
AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%latitude%';

By including the SCHEMABINDING clause you protect the view/function from unexpected changes to the tables underneath them:

CREATE VIEW [dbo].[v_QC_well]

WITH SCHEMABINDING
...

Reference: 

https://sqlstudies.com/2014/08/06/schemabinding-what-why/

https://www.mssqltips.com/sqlservertip/1692/using-schema-binding-to-improve-sql-server-udf-performance/

Python - Some concepts used in Scrapy, BeautifulSoup, Mechanize

Reference:
CSS - https://www.w3schools.com/css/default.asp
XPath - https://www.w3schools.com/xml/xpath_intro.asp
yield - https://stackoverflow.com/questions/231767/what-does-the-yield-keyword-do

Example -  https://www.digitalocean.com/community/tutorials/how-to-scrape-web-pages-with-beautiful-soup-and-python-3

Module "Mechanize"documentation - http://wwwsearch.sourceforge.net/mechanize/faq.html (Python 3 is not yet supported)

SQL - Want your query run faster?

Sometimes if you want to check if data in table A not exist in table B, but there is no data in the table B. If table A has large datasets, usually it takes very long time for the query to finish. Here is an alternative way to use not exists

set statistics time on 

select top 100000 a.*

from xxxxx.dbo.tbl_well_bridge a

     left join #Temp b  on a.api = b.api

where b.api is null;

select  top 100000 a.*

from xxxxxx.dbo.tbl_well_bridge a

where api not in (select api from #Temp)

select  top 100000  *

from xxxxx.dbo.tbl_well_bridge a

where not exists (select api from #Temp b where a.api = b.api )

set statistics time off

And the comparison is below. You can see clearly that which one is saving more time in total!

(100000 rows affected)

 SQL Server Execution Times:

   CPU time = 140 ms,  elapsed time = 22396 ms.

(100000 rows affected)

 SQL Server Execution Times:

   CPU time = 63 ms,  elapsed time = 8155 ms.

(100000 rows affected)

 SQL Server Execution Times:

   CPU time = 78 ms,  elapsed time = 6028 ms.

SQL - Want to make your Alias Column Name dynamic?

I was dealing with a not very cleaned CSV file, which is comma delimited, with "" around each column data, yet, data itself has comma, and worse, even double quote.

sample data:

It is hard for SSIS packages to load such a CSV file clean without column shifting. So what I did was to load the data into just one column, and try to separate them to each column in SQL.

From

To

Here is the way I do it: I combined cursor and dynamic SQL to make the column alias name dynamic:

/****** Script for SelectTopNRows command from SSMS  ******/

IF OBJECT_ID('tempdb..#Names') IS NOT NULL DROP TABLE #Names;

create table #Names ( ColID INT, ColName VARCHAR(50));

INSERT INTO #Names (ColId, ColName)   

Values(1,'[APINo]'),  (2,'[FileNo]'),(3,'[CurrentOperator]'), (4,'[CurrentWellName]'), (5,'[LeaseName]'),(6,'[LeaseNumber]'),

      (7,'[OriginalOperator]'),(8,'[OriginalWellName]'), (9,'[SpudDate]'),(10,'[TD]'),  (11,'[CountyName]'), (12,'[Township]'),

      (13,'[Range]'),(14,'[Section]'), (15,'[QQ]'), (16,'[Footages]'),(17,'[FieldName]'), (18,'[ProducedPools]'),

      (19,'[OilWaterGasCums]'),(20,'[IPTDateOilWaterGas]'),(21,'[Wellbore]'),(22,'[Latitude]'), (23,'[Longitude]'),

      (24,'[WellType]'), (25,'[WellStatus]'), (26,'[CTB]'),  (27,'[WellStatusDate]');

  

SELECT ColId, ColName 

from #Names;

-------------Calculate distance based on county---------------

DECLARE @ColId INT;

DECLARE CURSOR_1 CURSOR 

LOCAL STATIC READ_ONLY FORWARD_ONLY

FOR 

SELECT DISTINCT ColId

FROM  #Names

OPEN CURSOR_1;

FETCH NEXT FROM CURSOR_1 INTO @ColId;

WHILE @@FETCH_STATUS = 0

BEGIN 

    

DECLARE @ColName VARCHAR(50);

DECLARE CURSOR_2 CURSOR 

FOR 

SELECT DISTINCT ColName

FROM  #Names 

WHERE colId =   @ColId

OPEN CURSOR_2;

FETCH NEXT FROM CURSOR_2 INTO @ColName;

WHILE @@FETCH_STATUS = 0

BEGIN 

DECLARE @sql NVARCHAR(max)

DECLARE @column_alias VARCHAR(50)

SET @column_alias = @ColName

SET @sql = '

with tbl_raw as(

SELECT *

from [dbo].[ND_WellIndex_Load]

), tbl_extract as (

select a.id,right(a.raw_data,len(a.raw_data)-charindex(''",'',a.raw_data)-1) as raw_data,left(a.raw_data,charindex(''",'',a.raw_data)) as' + @column_alias +

'from tbl_raw a 

)

update a

set a.raw_Data = b.raw_data ,a.' + @column_alias + '= b.' + @column_alias +

'from tbl_raw a,

tbl_extract b

where a.id = b.id '

exec sp_executesql @sql

FETCH NEXT FROM CURSOR_2 INTO @ColName;

END;

CLOSE CURSOR_2;

DEALLOCATE CURSOR_2;

        FETCH NEXT FROM CURSOR_1 INTO @ColId;

END;

CLOSE CURSOR_1;

DEALLOCATE CURSOR_1;

SQL - Create Stored Procedures with optional parameters

If you want to make your code generic, say, sometimes you want to put certain criteria to the code to make it run faster,  and sometimes you just want to see all data no matter how long it will take. You can specify your declared parameter part like this:


ALTER PROCEDURE  dbo.sp_NAWAT_MISC_SpotfireInternalQC
(@state_name nvarchar(255) = NULL,
 @play_name  nvarchar(255) =  NULL
 )

 AS
BEGIN 

    ---- your code
           WHERE         (state_name = @state_name OR state_name is null)
                        AND  (play_name = @play_name OR  play_name is null) ;

END 
GO

or
(reference:SQL - Passing multiple values through one parameter in a stored procedure)

 AS
BEGIN 
  SET @Sql = '

    ---- your code