Πώς μπορούμε να υπολογίσουμε τα ασφάλιστρα αυτοκινήτων που έχουμε σε SQL Server με χρήση Python (χωρίς API)

Πώς μπορούμε να υπολογίσουμε τα ασφάλιστρα αυτοκινήτων που έχουμε σε SQL Server με χρήση Python (χωρίς API)
Πώς μπορούμε να υπολογίσουμε τα ασφάλιστρα αυτοκινήτων που έχουμε σε SQL Server με χρήση Python (χωρίς API)

Ας πούμε ότι έχουμε στον SQL Server έναν πίνακα με αυτοκίνητα και οδηγούς και θέλουμε να βρούμε το φθηνότερο ασφάλιστρο τους. Αυτό σίγουρα θα είναι εφικτό με ένα API / Web Service. Η λύση αυτή όμως θα απαιτούσε την συνεργασία των IT τμημάτων (μεταξύ της ασφαλιστικής και την δική μας) στη δημιουργία και τη χρήση του.

Τι ακριβώς θέλουμε να πετύχουμε

Με λίγα λόγια θέλουμε έχοντας έναν πίνακα στη βάση δεδομένων κάπως έτσι:

Πώς μπορούμε να υπολογίσουμε τα ασφάλιστρα αυτοκινήτων που έχουμε σε SQL Server με χρήση Python (χωρίς API)

Να συμπληρωθεί η τιμή του ασφαλίστρου και να είναι κάπως έτσι:

Πώς μπορούμε να υπολογίσουμε τα ασφάλιστρα αυτοκινήτων που έχουμε σε SQL Server με χρήση Python (χωρίς API)

Υπάρχει άλλη λύση;

Προφανώς για να υπάρχει το άρθρο υπάρχει και ο τρόπος 🙂 .

Μέ χρήση της βιβλιοθήκης selenium και pyodbc της Python μπορούμε ουσιαστικά να αντιγράψουμε τις ανθρώπινες κινήσεις που θα κάναμε στο browser του Chrome ώστε να υπολογίσουμε τα ασφάλιστρα και να ενημερώσουμε το πεδίο στη κάθε εγγραφή της βάσης.

Παρόμοια λύση είχαμε δει σε αυτό το άρθρο ώστε να μεταφράσουμε πεδία από τα Αγγλικά στα Ελληνικά.

Στο παράδειγμα μας θα χρησιμοποιήσω ένα site μιας τυχαίας ασφαλιστικής εταιρείας που έχει calculator. Δεν θα το αναφέρω από την στιγμή που το άρθρο υπάρχει για εκπαιδευτικούς μόνο λόγους.

Το site που θα χρησιμοποιήσουμε έχει αυτή την μορφή. Αρκετά πεδία χρειάζονται για την τιμολόγηση, κάποια από αυτά είναι dropdown και στο τέλος υπάρχει και το κουμπί του υπολογισμού.

Πώς μπορούμε να υπολογίσουμε τα ασφάλιστρα αυτοκινήτων που έχουμε σε SQL Server με χρήση Python (χωρίς API)

Προαπαιτούμενα

Θα πρέπει να κατεβάσουμε την αντίστοιχη έκδοση που είναι ο chrome μας του Chromedriver.

Στο command prompt με την παρακάτω εντολή μας κάνει εγκατάσταση την python:

cmd python

Στο command prompt  τρέχουμε τις εντολές αυτές ώστε να εγκατασταθούν τα modules που χρειαζόμαστε:

pip install –U selenium

pip install pyodbc

Επίσης θα χρειαστούμε έναν χρήστη της βάσεις που έχει δικαιώματα στον πίνακα αυτόν.

Η προετοιμασία στον πινακάς μας

Θα φτιάξουμε τώρα έναν staging πίνακα που θα μπορούσε να γεμίζει τιμές από άλλους πίνακες που υπάρχουν στη βάση.

Καθώς όπως είδαμε στο site τα dropdown μενού λειτουργούν μόνο με προεπιλεγμένες τιμές θα πρέπει να μετατρέψουμε τα δεδομένα μας σε αυτές τις τιμές:

create table car_calc(
id int identity(1,1) primary key,
insurance varchar(10) default null,
birth_d varchar(10),
birth_m varchar(10),
birth_y varchar(10),
email varchar(50),
plate varchar(10),
plate2 varchar(10),
cc varchar(10),
value varchar(10),
tk varchar(10),
dmg varchar(10) default '0',
duration varchar(10) default '6 μήνες',
car_age varchar(10),
sex varchar(10),
licence_age varchar(20))

Αφού πρόκειται για παράδειγμα και δεν έχω άλλους πίνακες απλά θα κάνω insert 3 εγγραφές προσέχοντας όμως όσα πεδία είναι dropdown να έχω ακριβώς το ίδιο κείμενο:

insert into car_calc
(birth_d,birth_m,birth_y,email,plate,plate2,cc,value,tk,dmg,duration,car_age,sex,licence_age)
values
('24','01','1990','patatakia@hotmail.com','ΟΕΖ','5120','1600','2000','15773','2','6 μήνες','5 ετών','Γυναίκα','3')
,('15','05','2000','dokimi@hotmail.com','ΜΥΕ','2300','1000','4500','81100','0','6 μήνες','2 ετών','''Ανδρας','8')
,('12','03','1962','calc@hotmail.com','ΙΑΕ','3920','2400','15000','15770','1','6 μήνες','3 ετών','''Ανδρας','7'); 

select * from car_calc;
Πώς μπορούμε να υπολογίσουμε τα ασφάλιστρα αυτοκινήτων που έχουμε σε SQL Server με χρήση Python (χωρίς API)

Η προετοιμασία για το Python script

Το πιο δύσκολο κομμάτι της διαδικασίας είναι αυτό. Θα χρειαστεί να κρατήσουμε από το site το xPath από κάθε κουτί ή λίστα που συμπληρωθεί, το κουμπί που πραγματοποιεί τον υπολογισμό και τέλος από το κουτί που θα εμφανίσει τα αποτελέσματα.

Κάνουμε δεξί κλικ στο καθένα – Inspect Copy Copy xPath:

Πώς μπορούμε να υπολογίσουμε τα ασφάλιστρα αυτοκινήτων που έχουμε σε SQL Server με χρήση Python (χωρίς API)

Πώς μπορούμε να υπολογίσουμε τα ασφάλιστρα αυτοκινήτων που έχουμε σε SQL Server με χρήση Python (χωρίς API)

Κρατάμε τις τιμές αυτές μία μία για χρήση μετέπειτα σε μεταβλητές π.χ.:

D_birthbox ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_Day"]'

Σε όσα έχουν dropdown θα χρειαστεί να κρατήσουμε την τιμή των Options προσθέτοντάς στο τέλος την παράμετρο της λίστας /li[@data-val=”%s”]’ και με αυτό τον τρόπο με το % duration_parameter στο τέλος θα πάρει σαν επιλογή την λίστα την τιμή της μεταβλητής αυτής:

duration_select_box_option ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_InsuraceDurationDropDownSelectBoxItOptions"]/li[@data-val="%s"]' % duration_parameter

Επίσης για τα dropdown θα χρειαστούμε να κρατήσουμε και το xPath που εμφανίζει τα options ώστε να μας αφήνει να τα επιλέξουμε. Κάνοντα έτσι μίμηση την διαδικασία του click:

duration_select_box ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_InsuraceDurationDropDownSelectBoxIt"]'

driver.find_element_by_xpath(duration_select_box).click()
time.sleep(1);
driver.find_element_by_xpath(duration_select_box_option).click()

Τέλος δεν θα πρέπει να ξεχάσουμε να κρατήσουμε τo xPath από το κουμπί του υπολογισμού και το κουτιού που εμφανίζει το ποσό αντίστοιχα:

submit_button = '//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_Submit"]'

output_box = '//*[@id="ResultsTable"]/tbody/tr[20]/td[2]/b'

Η επεξήγηση του κώδικα βήμα βήμα

Με .get στο webdriver δηλώνουμε την σελίδα που θα μας ανοίξει στον Chrome:

chromedriver_location = "C:\\Users\\smatzouranis\\Desktop\\python\\chromedriver"

driver = webdriver.Chrome(chromedriver_location)

driver.get('https://www............./calculator/')

Με το module pyodbc κάνουμε connect στη βάση:

conn = pyodbc.connect("Driver={SQL Server};"
"Server=SMATZOURANISLP\SQL19;"
"Database=db_test;"
"username=python;"
"password=python;"
"trusted_connection = yes;")

Ανοίγουμε έναν cursor στον οποίον θα έχουμε το select query μας με τα δεδομένα από τη βάση:

cursor = conn.cursor()
cursor.execute('select id,birth_d,birth_m,birth_y,email,plate,plate2,cc,value,tk,dmg,duration,car_age,sex,licence_age from car_calc')
results = cursor.fetchall()

Πλέον μπορούμε στη μεταβλητή results έχουμε έναν δισδιάστατο πίνακα όπου results[i][0] ως i ορίζουμε την γραμμή του πίνακα και ως 0 την πρώτη στήλη του. Π.χ. results[0][4] είναι το email της πρώτης εγγραφής. Έτσι με ένα loop μπορούμε να κάνουμε περάσουμε ένα ένα όλα τα πεδία στα κελιά του site να κάνουμε τον υπολογισμό και να προχωρήσουμε στην επόμενη εγγραφή.

Ορίζουμε σε μια μεταβλητή το update statement που θα περνάει στη βάση μας το ποσό του ασφαλίστρου:

update = """update car_calc set insurance=? where id=?"""

Ο συνολικός κώδικας του Python script

#python

from selenium import webdriver 
from selenium.common.exceptions  import StaleElementReferenceException

import pyodbc,time

chromedriver_location = "C:\\Users\\smatzouranis\\Desktop\\python\\chromedriver"

driver = webdriver.Chrome(chromedriver_location)

driver.get('https://www............./calculator/')

conn = pyodbc.connect("Driver={SQL Server};"
                        "Server=SMATZOURANISLP\SQL19;"
                        "Database=db_test;"
                        "username=python;"
                        "password=python;"
                        "trusted_connection = yes;")

output_box = '//*[@id="ResultsTable"]/tbody/tr[20]/td[2]/b'
cursor = conn.cursor()
cursor.execute('select id,birth_d,birth_m,birth_y,email,plate,plate2,cc,value,tk,dmg,duration,car_age,sex,licence_age from car_calc')
results = cursor.fetchall()
print(results)
update = """update car_calc set insurance=? where id=?"""

for i in range(len(results)):
        try:


                D_birthbox ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_Day"]'
                D_birth_parameter = results[i][1]
                driver.find_element_by_xpath(D_birthbox).clear()
                driver.find_element_by_xpath(D_birthbox).send_keys(D_birth_parameter)

                M_birthbox ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_Month"]'
                M_birth_parameter = results[i][2]
                driver.find_element_by_xpath(M_birthbox).clear()
                driver.find_element_by_xpath(M_birthbox).send_keys(M_birth_parameter)

                Y_birthbox ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_Year"]'
                Y_birth_parameter = results[i][3]
                driver.find_element_by_xpath(Y_birthbox).clear()
                driver.find_element_by_xpath(Y_birthbox).send_keys(Y_birth_parameter)

                mail_textbox ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_EmailCar"]'
                mail_parameter = results[i][4]
                driver.find_element_by_xpath(mail_textbox).clear()
                driver.find_element_by_xpath(mail_textbox).send_keys(mail_parameter)


                PLATEp1_textbox ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_RegistrationNumber1"]'
                PLATEp1_parameter = results[i][5]
                driver.find_element_by_xpath(PLATEp1_textbox).clear()
                driver.find_element_by_xpath(PLATEp1_textbox).send_keys(PLATEp1_parameter)

                PLATEp2_textbox ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_RegistrationNumber2"]'
                PLATEp2_parameter = results[i][6]
                driver.find_element_by_xpath(PLATEp2_textbox).clear()
                driver.find_element_by_xpath(PLATEp2_textbox).send_keys(PLATEp2_parameter)

                CC_textbox = '//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_CC"]'
                CC_parameter = results[i][7]
                driver.find_element_by_xpath(CC_textbox).clear()
                driver.find_element_by_xpath(CC_textbox).send_keys(CC_parameter)

                value_textbox = '//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_VehicleValue"]'
                value_parameter = results[i][8]
                driver.find_element_by_xpath(value_textbox).clear()
                driver.find_element_by_xpath(value_textbox).send_keys(value_parameter)


                TK_textbox = '//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_ZipCode"]'
                TK_parameter = results[i][9]
                driver.find_element_by_xpath(TK_textbox).clear()
                driver.find_element_by_xpath(TK_textbox).send_keys(TK_parameter)


                dmg_parameter = results[i][10]
                dmg_select_box_option ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_CrashCountDropDownSelectBoxItOptions"]/li[@data-val="%s"]' % dmg_parameter
                dmg_select_box ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_CrashCountDropDownSelectBoxIt"]'
                driver.find_element_by_xpath(dmg_select_box).click()
                time.sleep(1);
                driver.find_element_by_xpath(dmg_select_box_option).click()

                
                duration_parameter = results[i][11]
                duration_select_box_option ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_InsuraceDurationDropDownSelectBoxItOptions"]/li[@data-val="%s"]' % duration_parameter
                duration_select_box ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_InsuraceDurationDropDownSelectBoxIt"]'
                driver.find_element_by_xpath(duration_select_box).click()
                time.sleep(1);
                driver.find_element_by_xpath(duration_select_box_option).click()



                carage_parameter = results[i][12]
                carage_select_box_option ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_VehicleAgeDropDownSelectBoxItOptions"]/li[@data-val="%s"]' % carage_parameter
                carage_select_box ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_VehicleAgeDropDownSelectBoxItText"]'
                driver.find_element_by_xpath(carage_select_box).click()
                time.sleep(1);
                driver.find_element_by_xpath(carage_select_box_option).click()


                SEX_parameter = results[i][13]
                SEX_select_box_option ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_SexTypeDropDownSelectBoxItOptions"]/li[@data-val="%s"]' % SEX_parameter
                SEX_select_box ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_SexTypeDropDownSelectBoxIt"]'
                driver.find_element_by_xpath(SEX_select_box).click()
                time.sleep(1);
                driver.find_element_by_xpath(SEX_select_box_option).click()

                Y_parameter = results[i][14]
                Y_select_box_option ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_YearsDiplomaDropDownSelectBoxItOptions"]/li[@data-val="%s"]' % Y_parameter
                Y_select_box ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_YearsDiplomaDropDownSelectBoxItText"]'
                driver.find_element_by_xpath(Y_select_box).click()
                time.sleep(1);
                driver.find_element_by_xpath(Y_select_box_option).click()


                submit_button = '//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_Submit"]'
                driver.find_element_by_xpath(submit_button).click()

                time.sleep(4);
                
                price = driver.find_element_by_xpath(output_box).text
                cursor.execute(update,(price,i+1))

                print(driver.find_element_by_xpath(output_box).text)
                
        except StaleElementReferenceException:
                pass
conn.commit();
cursor.close();
driver.quit;
conn.close();
exit();

Το αποτέλεσμα

Τρέχοντας λοιπόν το Python script θα δούμε ότι όταν κάνουμε select στον πίνακα θα έχει για το καθένα στη δεύτερη στήλη το ποσό του ασφαλίστρου:

Πώς μπορούμε να υπολογίσουμε τα ασφάλιστρα αυτοκινήτων που έχουμε σε SQL Server με χρήση Python (χωρίς API)

Αν θέλουμε να το εκτελούμε μέσα από job στον SQL Server

Ενεργοποιούμε τη δυνατότητα στον SQL Server να τρέχει direct cmd script:

exec sp_configure 'show advanced option',1
go
sp_configure 'xp_cmdshell',1
go
reconfigure with override

Στην περίπτωση που θα το τρέχει ο SQL Server Agent. Φτιάχνουμε και ένα Job που θα περιέχει το full path με το όνομα του αρχείου που θα εκτελεί:

π.χ.

"C:\Users\smatzouranis\Desktop\python\python_chrome_automation_insurance.py"
Πώς μπορούμε να υπολογίσουμε τα ασφάλιστρα αυτοκινήτων που έχουμε σε SQL Server με χρήση Python (χωρίς API)

Είναι έτοιμο το job προς εκτέλεση:

exec msdb.dbo.sp_start_job 'callPythonw'

Κλείνοντας θα πρέπει να αναφέρω ότι απλά αλλάζοντας τις παραμέτρους στο pyodbc το script θα μπορούσε να λειτουργήσει σε οποιαδήποτε συμβατή με αυτό βάση δεδομένων.

Μοιράσου το

Αφήστε μία απάντηση