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

• Σχετικά με
• τελευταίες δημοσιεύσεις

Stratos Matzouranis

Το ταξίδι άρχισε το 2008 όπου ξεκίνησα να εργάζομαι επίσημα στον τομέα της τεχνολογίας πληροφορικής (IT). Ξεκινώντας το πρώτο εξάμηνο της σχολής κατάλαβα μια ιδιαίτερη έλξη προς τις βάσεις δεδομένων και τους αυτοματισμούς.Έχω ασχοληθεί με βάσεις δεδομένων όπως Microsoft SQL Server / Oracle Database, ανάλυση δεδομένων και αυτοματισμούς με τη χρήση γραμμής εντολών (CLI), Visual Basic for Applications και Python. Μέσα από τα χρόνια εμπειρίας εξέλιξα αυτές τις δυνατότητες ώστε να μπορώ να κάνω τη ζωή μου πιο εύκολη. Για μένα ο σκοπός του κάθε IT guy αλλά και κάθε υπαλλήλου γραφείου είναι το να έχει τις γνώσεις ώστε μέσω εργαλείων να μπορεί να δουλεύει λίγο αλλά να παράγει πολύ. Μέσα από αυτό τον ιστότοπο του DataPlatform.gr προσπαθώ να προσφέρω γνώσεις και να προτείνω λύσεις σε καθημερινά προβλήματα.

Πιστοποιήσεις:

Τελευταίες δημοσιεύσεις από Stratos Matzouranis (Προβολή όλων)

• Πώς αυτοματοποιούμε τη διαδικασία ελέγχου ακεραιότητας των βάσεων δεδομένων σε SQL Server χωρίς την χρήση maintenance plan - 10 Μάιος 2024
• Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Windows - 5 Απρίλιος 2024
• Πώς μπορούμε να λαμβάνουμε email κάθε φορά που ξεπερνάει ένα όριο η μνήμη PGA σε βάση δεδομένων της Oracle - 1 Μάρτιος 2024

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

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

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

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

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

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

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

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

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

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

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

Θα πρέπει να κατεβάσουμε την αντίστοιχη έκδοση που είναι ο 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;

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

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

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

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

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 στον πίνακα θα έχει για το καθένα στη δεύτερη στήλη το ποσό του ασφαλίστρου:

Αν θέλουμε να το εκτελούμε μέσα από 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"

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

exec msdb.dbo.sp_start_job 'callPythonw'

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